Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: 1 2 3 4      [все]
 Использование оконных (аналитических) функций  [new]
ARRay001
Member

Откуда:
Сообщений: 50
Доброго времени суток!

Подскажите, как с помощью оконных (аналитических) функций найти значение нужного столбца по значению другого.
Пример: select формирует таблицу, где в первом столбце идут даты (st0), в остальных (st1, st2, st3...) какие-то значения.
Надо с текущей позиции select найти значение st3, соответствующее определенной дате, находящейся выше текущей на неопределенное количество строк, т.е. по значению даты.

Простой lag предполагает знание точного количества строк, на которые поднимаешься. А как по значению? Или как правильно указать условие равенства дат?
17 ноя 20, 10:26    [22233527]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6749
ARRay001,

Приведите пример данных в формате
with t as ( select ....
и образец желаемого результата.
17 ноя 20, 10:38    [22233537]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
AmKad
Member

Откуда:
Сообщений: 5222
first_value/last_value(case when ... end ignore nulls) over (order by ... range/rows between ...)


Сообщение было отредактировано: 17 ноя 20, 10:35
17 ноя 20, 10:39    [22233538]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
AmKad
Member

Откуда:
Сообщений: 5222
AmKad,

+ ignore nulls. Поправил предыдущее сообщение.

Сообщение было отредактировано: 17 ноя 20, 10:35
17 ноя 20, 10:40    [22233539]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
ARRay001
Member

Откуда:
Сообщений: 50
env,

with t1 (id, date1, fld2, fld3) as
(
              select 111, to_date('10.10.2020', 'DD.MM.YYYY'), 10, '11' from dual
    union all select 222, to_date('11.10.2020', 'DD.MM.YYYY'), 15, '22' from dual
    union all select 333, to_date('12.10.2020', 'DD.MM.YYYY'), 30, '43' from dual
    
)
select t1.*
from t1;


Результат: В строке с датой '12.10.2020' надо, допустим, значение fld2 сделать равным значению fld3 на у даты '10.10.2020'.

Сообщение было отредактировано: 17 ноя 20, 10:49
17 ноя 20, 10:53    [22233542]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2798
ARRay001,

окно в три строки и окно в три дня ето разные окнa



значение st3 на передыдущие st1 дня относительно текущего st0
  1  with t as (
  2  select date '2020-01-01' st0, 1 st1,101 st3 from dual union all
  3  select date '2020-01-02' st0, 1 st1,102 st3 from dual union all
  4  select date '2020-01-03' st0, 2 st1,103 st3 from dual union all
  5  select date '2020-01-04' st0, 2 st1,104 st3 from dual union all
  6  select date '2020-01-10' st0, 7 st1,105 st3 from dual union all
  7  select date '2020-01-11' st0, 6 st1,106 st3 from dual union all
  8  select date '2020-01-12' st0, 2 st1,107 st3 from dual)
  9  select
 10   t.*
 11  ,first_value(st3) over (order by st0 range st1 preceding) f
 12* from t
SQL> /

ST0               ST1        ST3          F
---------- ---------- ---------- ----------
01.01.2020          1        101        101
02.01.2020          1        102        101
03.01.2020          2        103        101
04.01.2020          2        104        102
10.01.2020          7        105        103
11.01.2020          6        106        105
12.01.2020          2        107        105

7 rows selected.



......
stax
17 ноя 20, 10:54    [22233543]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54398
ARRay001
env,

with t1 (id, date1, fld2, fld3) as
(
              select 111, to_date('10.10.2020', 'DD.MM.YYYY'), 10, '11' from dual
    union all select 222, to_date('11.10.2020', 'DD.MM.YYYY'), 15, '22' from dual
    union all select 333, to_date('12.10.2020', 'DD.MM.YYYY'), 30, '43' from dual
    
)
select t1.*
from t1;



Результат: В строке с датой '12.10.2020' надо, допустим, значение fld2 сделать равным значение fld3 на у даты '10.10.2020'.
вы даже сами не уверены?
17 ноя 20, 10:54    [22233544]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
ARRay001
Member

Откуда:
Сообщений: 50
andreymx,
это всего лишь пример. Мой реальный пример намного сложнее. Допустим - это значит один из возможных вариантов равенства. Считайте, что именно так.
17 ноя 20, 10:57    [22233546]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2798
ARRay001,

SQL> ed
Wrote file afiedt.buf

  1  with t1 (id, date1, fld2, fld3) as
  2  (
  3                select 111, to_date('10.10.2020', 'DD.MM.YYYY'), 1, '11' from dual
  4      union all select 222, to_date('11.10.2020', 'DD.MM.YYYY'), 2, '22' from dual
  5      union all select 333, to_date('12.10.2020', 'DD.MM.YYYY'), 2, '43' from dual
  6      union all select 444, to_date('13.10.2020', 'DD.MM.YYYY'), 3, '55' from dual
  7      union all select 555, to_date('14.10.2020', 'DD.MM.YYYY'), 2, '77' from dual
  8      union all select 777, to_date('15.10.2020', 'DD.MM.YYYY'), 4, '88' from dual
  9  )
 10  select t1.*
 11  ,lag(fld3,fld2) over (order by date1) l
 12* from t1
SQL> /

        ID DATE1            FLD2 FL L
---------- ---------- ---------- -- --
       111 10.10.2020          1 11
       222 11.10.2020          2 22
       333 12.10.2020          2 43 11
       444 13.10.2020          3 55 11
       555 14.10.2020          2 77 43
       777 15.10.2020          4 88 22

6 rows selected.

SQL>


.....
stax
17 ноя 20, 11:05    [22233550]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
ARRay001
Member

Откуда:
Сообщений: 50
Stax,
что-то не то. Смещение в lag не должно равняться значению fld2. Или я чего-то не понимаю...
17 ноя 20, 11:14    [22233556]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2798
ARRay001
Stax,
что-то не то. Смещение в lag не должно равняться значению fld2. Или я чего-то не понимаю...


ето я не понимаю что Вам надо

смещение (в строках!) задается в столбце fld2

.....
stax
17 ноя 20, 11:24    [22233561]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
ARRay001
Member

Откуда:
Сообщений: 50
Stax,

оно нигде не задается. Есть таблица с полями fld0(с датами), fld1(со значениями 1), fld2(со значениями 2), fld3(со значениями 3).
Надо каждое значение fld2 поменять на значение fld3, соответствующее конкретной дате (в примере это '10.10.2020') - конкретное смещение не известно. Известно только то, что эта дата находится выше текущей (на которой курсор select).

Тут вероятно что-то типо: max() keep (dense_rank ...)

Сообщение было отредактировано: 17 ноя 20, 11:26
17 ноя 20, 11:28    [22233567]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6749
ARRay001,

Вы задачу-то свою сформулируйте без "допустим" и "возможно". Пока что логика выбора строк не понятна.
17 ноя 20, 11:32    [22233569]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6749
ARRay001
Известно только то, что эта дата находится выше текущей

Любая дата выше текущей? Критерии внятно пропишите, без этого решать нечего.

Приведите пример на десяток строк, ожидаемый результат и пояснение - "выбрана из строки N потому что..."

Сообщение было отредактировано: 17 ноя 20, 11:29
17 ноя 20, 11:33    [22233572]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
ARRay001
Member

Откуда:
Сообщений: 50
env,
читайте постом выше
17 ноя 20, 11:34    [22233576]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2798
ARRay001,

откуда берется 10.10.2020? параметр?

.....
stax
17 ноя 20, 11:34    [22233577]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
ARRay001
Member

Откуда:
Сообщений: 50
env,
любая соответствующая значению, например sysdate. Объясняю смысл: ищется последняя дата предыдущего месяца и смотрится значение fld3 на эту дату. Найденное значение используется при расчете текущего значения fld2 по текущей дате.
17 ноя 20, 11:36    [22233578]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6749
ARRay001
ищется последняя дата предыдущего месяца

О, наконец-то конкретика пошла.
17 ноя 20, 11:41    [22233585]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
ARRay001
Member

Откуда:
Сообщений: 50
env,

я думал описанного выше хватит для понимания. А расчёт значения даты может быть любым.
17 ноя 20, 11:42    [22233586]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
AmKad
Member

Откуда:
Сообщений: 5222
ARRay001
env,

я думал описанного выше хватит для понимания.
А мы думали, тебе last_value/first_value хватит для понимания.
17 ноя 20, 11:43    [22233587]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
ARRay001
Member

Откуда:
Сообщений: 50
AmKad,
пример был универсальный. Если я не так выразился и не указал слово "константа" - это мой промах. Надеюсь теперь с условием проблем нет.

Сообщение было отредактировано: 17 ноя 20, 11:41
17 ноя 20, 11:46    [22233590]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6749
ARRay001,

Так вам в ответ и дали универсальное решение )
17 ноя 20, 11:47    [22233592]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
AmKad
Member

Откуда:
Сообщений: 5222
ARRay001
Надеюсь теперь с условием проблем нет.
Лично я уже потерял интерес от необходимости собирать задачу по крупицам с каждого твоего поста. Надеюсь, энтузиазма Stax-а еще хватит.
17 ноя 20, 11:49    [22233594]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
ARRay001
Member

Откуда:
Сообщений: 50
Давайте ещё раз, чтобы не было вопросов по условию:

with t1 (id, date1, fld2, fld3) as
(
              select 111, to_date('31.10.2020', 'DD.MM.YYYY'), 10, '11' from dual
    union all select 222, to_date('01.11.2020', 'DD.MM.YYYY'), 15, '22' from dual
    union all select 333, to_date('05.11.2020', 'DD.MM.YYYY'), 30, '43' from dual
    
)
select t1.*
from t1;


Что надо найти: Значение fld3 на последний день предыдущего месяца. Т.е. для строки с датой '05.11.2020' это значение будет равно '11'. В условии может быть несколько строк по дате '31.10.2020' - в таком случае берётся последняя в сортировке (сортировать можно по id).

Сообщение было отредактировано: 17 ноя 20, 11:58
17 ноя 20, 11:56    [22233599]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6749
ARRay001
В условии может быть несколько строк по дате '31.10.2020'.

Да что ж из вас клещами всё тянуть-то надо. И какую из них тогда брать?

Выдохните. Сформулируйте свою проблему максимально полно. У форумчан нет вашего контекста для восстановления лакун в описании.
17 ноя 20, 12:00    [22233604]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
ARRay001
Member

Откуда:
Сообщений: 50
env,
добавил коммент в условие выше. Сортировка по id не обязательна, так как даты итак будут отсортированы по времени.

Сообщение было отредактировано: 17 ноя 20, 12:01
17 ноя 20, 12:03    [22233605]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
AmKad
Member

Откуда:
Сообщений: 5222
with t1 (id, date1, fld2, fld3) as
(
              select 111, to_date('31.10.2019', 'DD.MM.YYYY'), 10, '11' from dual
    union all select 222, to_date('01.11.2019', 'DD.MM.YYYY'), 15, '22' from dual
    union all select 333, to_date('05.11.2019', 'DD.MM.YYYY'), 30, '43' from dual
    union all select 111, to_date('26.08.2020', 'DD.MM.YYYY'), 10, '07' from dual
    union all select 111, to_date('28.09.2020', 'DD.MM.YYYY'), 10, '10' from dual
    union all select 111, to_date('30.09.2020', 'DD.MM.YYYY'), 10, '09' from dual
    union all select 111, to_date('30.10.2020', 'DD.MM.YYYY'), 10, '11' from dual
    union all select 111, to_date('31.10.2020', 'DD.MM.YYYY'), 10, '11' from dual
    union all select 222, to_date('01.11.2020', 'DD.MM.YYYY'), 15, '22' from dual
    union all select 333, to_date('05.11.2020', 'DD.MM.YYYY'), 30, '43' from dual    
)
select t1.*,
last_value(decode(last_day(date1), date1, fld3) ignore nulls) 
    over (order by trunc(date1, 'month') range between interval '1' month preceding and interval '1' month preceding) lv
from t1
order by date1;

        ID DATE1             FLD2 FLD3 LV
---------- ----------- ---------- ---- --
       111 31.10.2019          10 11   
       222 01.11.2019          15 22   11
       333 05.11.2019          30 43   11
       111 26.08.2020          10 07   
       111 28.09.2020          10 10   
       111 30.09.2020          10 09   
       111 30.10.2020          10 11   09
       111 31.10.2020          10 11   09
       222 01.11.2020          15 22   11
       333 05.11.2020          30 43   11

10 rows selected
17 ноя 20, 12:11    [22233611]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
123йй
Member

Откуда:
Сообщений: 1639
ARRay001
Сортировка по id не обязательна, так как даты итак будут отсортированы по времени.


date1 содержит время ?
17 ноя 20, 12:25    [22233615]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
ARRay001
Member

Откуда:
Сообщений: 50
AmKad,
интересное решение - надо осознать до конца и проверить у себя. Спасибо!
17 ноя 20, 12:26    [22233618]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
ARRay001
Member

Откуда:
Сообщений: 50
123йй,
к сожалению, оказалось, что не содержит. Но и повторов в конкретной выборке, скорее всего не будет. Так что, прошу прощения - условие упрощается. Сортировки по одной дате не нужно.
17 ноя 20, 12:32    [22233626]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2798
AmKad,

не будет ли last_value плавающим/случайным из-за неуникальной сортировки order by trunc(date1, 'month')?

.....
stax
17 ноя 20, 13:10    [22233663]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
AmKad
Member

Откуда:
Сообщений: 5222
Stax
AmKad,

не будет ли last_value плавающим/случайным из-за неуникальной сортировки order by trunc(date1, 'month')?

.....
stax
Буйки ограничивают плавающих предыдущим месяцем. А вот чтобы не было коллизий с определением последней даты месяца - нужно, чтобы не было ее повторений.
17 ноя 20, 13:26    [22233672]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2798
AmKad
Stax
AmKad,

не будет ли last_value плавающим/случайным из-за неуникальной сортировки order by trunc(date1, 'month')?

.....
stax
Буйки ограничивают плавающих предыдущим месяцем. А вот чтобы не было коллизий с определением последней даты месяца - нужно, чтобы не было ее повторений.

допустим date1 без повторений но trunc(date1, 'month') для сортировки уже с повторениями (первое число)


.....
stax
17 ноя 20, 15:21    [22233858]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
ARRay001
Member

Откуда:
Сообщений: 50
Задачка опять видоизменилась. Берётся не последний день месяца, а последний день месяца, имеющийся в списке дат. Т.е. любая последняя дата предыдущего месяца. Наверное изменится интервал?
19 ноя 20, 15:51    [22235462]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2798
ARRay001
Задачка опять видоизменилась. Берётся не последний день месяца, а последний день месяца, имеющийся в списке дат. Т.е. любая последняя дата предыдущего месяца. Наверное изменится интервал?


не совсем понятно
мож достаточно убрать last_day

SQL> ed
Wrote file afiedt.buf

  1  with t1 (id, date1, fld2, fld3) as
  2  (
  3                select 111, to_date('17.10.2019', 'DD.MM.YYYY'), 10, '11' from dual
  4      union all select 222, to_date('01.11.2019', 'DD.MM.YYYY'), 15, '22' from dual
  5      union all select 333, to_date('05.11.2019', 'DD.MM.YYYY'), 30, '43' from dual
  6      union all select 111, to_date('26.08.2020', 'DD.MM.YYYY'), 10, '07' from dual
  7      union all select 111, to_date('28.09.2020', 'DD.MM.YYYY'), 12, '10' from dual
  8      union all select 111, to_date('22.10.2020', 'DD.MM.YYYY'), 14, '13' from dual
  9      union all select 111, to_date('27.10.2020', 'DD.MM.YYYY'), 15, '17' from dual
 10      union all select 222, to_date('01.11.2020', 'DD.MM.YYYY'), 16, '22' from dual
 11      union all select 333, to_date('05.11.2020', 'DD.MM.YYYY'), 30, '43' from dual
 12  )
 13  select t1.*,
 14  --last_value(decode(last_day(date1), date1, fld3) ignore nulls)
 15  last_value(fld3 ignore nulls)
 16      over (order by trunc(date1, 'month') range between interval '1' month preceding and interval '1' month preceding) lv
 17  from t1
 18* order by date1
SQL> /

        ID DATE1            FLD2 FL LV
---------- ---------- ---------- -- --
       111 17.10.2019         10 11
       222 01.11.2019         15 22 11
       333 05.11.2019         30 43 11
       111 26.08.2020         10 07
       111 28.09.2020         12 10 07
       111 22.10.2020         14 13 10
       111 27.10.2020         15 17 10
       222 01.11.2020         16 22 17
       333 05.11.2020         30 43 17

9 rows selected.

SQL>


.....
stax
19 ноя 20, 16:00    [22235471]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2798
ARRay001,

я не могу понять (токо время потратил вместо чтоб ...) как работает order by trunc(date1, 'month') range between interval '1' month preceding and interval '1' month preceding

сделал влоб (наверняка красивее можно напр exact day и тд)

мож пригодится



SQL> ed
Wrote file afiedt.buf

  1  with t1 (id, date1, fld2, fld3) as
  2  (
  3                select 1, to_date('30.10.2019', 'DD.MM.YYYY'), 10, '1122      ' from dual
  4      union all select 2, to_date('01.11.2019', 'DD.MM.YYYY'), 15, '22' from dual
  5      union all select 3, to_date('25.11.2019', 'DD.MM.YYYY'), 30, '40' from dual
  6      union all select 4, to_date('20.11.2019', 'DD.MM.YYYY'), 30, '44' from dual
  7      union all select 5, to_date('27.11.2019', 'DD.MM.YYYY'), 30, '41' from dual
  8      union all select 6, to_date('05.11.2019', 'DD.MM.YYYY'), 30, '06' from dual
  9      union all select 7, to_date('11.12.2019', 'DD.MM.YYYY'), 10, '07' from dual
 10      union all select 8, to_date('01.12.2019', 'DD.MM.YYYY'), 10, '57' from dual
 11      union all select 9, to_date('28.09.2020', 'DD.MM.YYYY'), 12, '10' from dual
 12      union all select 10, to_date('27.10.2020', 'DD.MM.YYYY'), 14, '13' from dual
 13      union all select 11, to_date('23.10.2020', 'DD.MM.YYYY'), 15, '17' from dual
 14      union all select 12, to_date('01.11.2020', 'DD.MM.YYYY'), 16, '22' from dual
 15      union all select 13, to_date('05.11.2020', 'DD.MM.YYYY'), 30, '43' from dual
 16  )
 17  select t1.*,
 18  last_value(fld3)
 19      over (order by date1 range between to_char(date1,'dd')+to_char(trunc(date1,'mm')-1,'dd')-1 preceding
 20                                    and  to_char(date1,'dd') preceding) prev_month
 21  from t1
 22* order by date1
SQL> /

        ID DATE1            FLD2 FLD3       PREV_MONTH
---------- ---------- ---------- ---------- ----------
         1 30.10.2019         10 1122
         2 01.11.2019         15 22         1122
         6 05.11.2019         30 06         1122
         4 20.11.2019         30 44         1122
         3 25.11.2019         30 40         1122
         5 27.11.2019         30 41         1122
         8 01.12.2019         10 57         41
         7 11.12.2019         10 07         41
         9 28.09.2020         12 10
        11 23.10.2020         15 17         10
        10 27.10.2020         14 13         10
        12 01.11.2020         16 22         13
        13 05.11.2020         30 43         13

13 rows selected.


.....
stax

Сообщение было отредактировано: 19 ноя 20, 17:37
19 ноя 20, 17:41    [22235569]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
AmKad
Member

Откуда:
Сообщений: 5222
ARRay001
Задачка опять видоизменилась. Берётся не последний день месяца, а последний день месяца, имеющийся в списке дат. Т.е. любая последняя дата предыдущего месяца. Наверное изменится интервал?
Stax

мож достаточно убрать last_day

 15  last_value(fld3 ignore nulls)
 16      over (order by trunc(date1, 'month') range between interval '1' month preceding and interval '1' month preceding) lv
В этом случае не гарантируется сортировка дат внутри месяца.
19 ноя 20, 18:26    [22235608]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
AmKad
Member

Откуда:
Сообщений: 5222
ARRay001
Задачка опять видоизменилась. Берётся не последний день месяца, а последний день месяца, имеющийся в списке дат. Т.е. любая последняя дата предыдущего месяца.

last_value(fld3) over 
    (order by date1 range between date1 - add_months(trunc(date1, 'month'), -1) preceding 
                              and date1            - trunc(date1, 'month') + 1  preceding) lv
19 ноя 20, 18:27    [22235612]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2798
AmKad

ARRay001
Задачка опять видоизменилась. Берётся не последний день месяца, а последний день месяца, имеющийся в списке дат. Т.е. любая последняя дата предыдущего месяца. Наверное изменится интервал?
Stax

мож достаточно убрать last_day

 15  last_value(fld3 ignore nulls)
 16      over (order by trunc(date1, 'month') range between interval '1' month preceding and interval '1' month preceding) lv
В этом случае не гарантируется сортировка дат внутри месяца.


22233858

я вообще не понял как оно работает (какое окно выщитывает)
  1  with t1 (id, date1, fld2, fld3) as
  2  (
  3                select 1, to_date('30.10.2019', 'DD.MM.YYYY'), 10, '1122      ' from dual
  4      union all select 2, to_date('01.11.2019', 'DD.MM.YYYY'), 15, '22' from dual
  5      union all select 3, to_date('25.11.2019', 'DD.MM.YYYY'), 30, '40' from dual
  6      union all select 4, to_date('20.11.2019', 'DD.MM.YYYY'), 30, '44' from dual
  7      union all select 5, to_date('27.11.2019', 'DD.MM.YYYY'), 30, '41' from dual
  8      union all select 6, to_date('05.11.2019', 'DD.MM.YYYY'), 30, '06' from dual
  9      union all select 7, to_date('11.12.2019', 'DD.MM.YYYY'), 10, '07' from dual
 10      union all select 8, to_date('01.12.2019', 'DD.MM.YYYY'), 10, '57' from dual
 11      union all select 9, to_date('28.09.2020', 'DD.MM.YYYY'), 12, '10' from dual
 12      union all select 10, to_date('27.10.2020', 'DD.MM.YYYY'), 14, '13' from dual
 13      union all select 11, to_date('23.10.2020', 'DD.MM.YYYY'), 15, '17' from dual
 14      union all select 12, to_date('01.11.2020', 'DD.MM.YYYY'), 16, '22' from dual
 15      union all select 13, to_date('05.11.2020', 'DD.MM.YYYY'), 30, '43' from dual
 16  )
 17  select t1.*,
 18  last_value(fld3)
 19      over (order by date1 range between to_char(date1,'dd')+to_char(trunc(date1,'mm')-1,'dd')-1 preceding
 20                                    and  to_char(date1,'dd') preceding) prev_month
 21  ,last_value(fld3 ignore nulls)
 22        over (order by trunc(date1, 'month') range between interval '1' month preceding and interval '1' month preceding) lv_interval
 23  from t1
 24* order by date1
SQL> /

        ID DATE1            FLD2 FLD3       PREV_MONTH LV_INTERVA
---------- ---------- ---------- ---------- ---------- ----------
         1 30.10.2019         10 1122
         2 01.11.2019         15 22         1122       1122
         6 05.11.2019         30 06         1122       1122
         4 20.11.2019         30 44         1122       1122
         3 25.11.2019         30 40         1122       1122
         5 27.11.2019         30 41         1122       1122
         8 01.12.2019         10 57         41         44
         7 11.12.2019         10 07         41         44
         9 28.09.2020         12 10
        11 23.10.2020         15 17         10         10
        10 27.10.2020         14 13         10         10
        12 01.11.2020         16 22         13         17
        13 05.11.2020         30 43         13         17

13 rows selected.


зі
забыл за -1 add_month (старость)

.....
stax
19 ноя 20, 18:36    [22235626]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
AmKad
Member

Откуда:
Сообщений: 5222
Stax

order by trunc(date1, 'month') range between interval '1' month preceding and interval '1' month preceding)
Это имело смысл только для первичной постановки задачи вкупе с last_day + ignore nulls. Для новой постановки такая сортировка не подходит.
19 ноя 20, 18:46    [22235633]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
ARRay001
Member

Откуда:
Сообщений: 50
Однако, за время пути... опять новость: надо ещё эту дату сравнивать с результатом запроса, который показывает выходной это или нет. Если выходной, то брать первый день следующего месяца вместо последней даты из предыдущего месяца...
Запрос вида: select t.wd from wdate t where t.date = date1, где t.wd = 0 - не рабочие дни, 1 - рабочие

Сообщение было отредактировано: 23 ноя 20, 14:51
23 ноя 20, 14:55    [22237242]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6749
ARRay001,

Сделайте уже календарь со всеми необходимыми датами и признаками
23 ноя 20, 15:25    [22237267]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2798
ARRay001

то брать первый день следующего месяца вместо последней даты из предыдущего месяца...


а потом окажется что первый день следующего месяца выходной и надо ...

зы
рассчитайте два поля 1-последний день пред. месяца и первый день текущего (если я правильно понял)
и в зависимости от выходного выбирайте одну из зол

зыы
мож проще создать ф-цию и в ней "любе"

.....
stax
23 ноя 20, 15:27    [22237268]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
ARRay001
Member

Откуда:
Сообщений: 50
Stax,
функция не впишется в концепцию данного решения - это всё сначала делать в pl/sql.
Я бы делал что-то вроде: проверяю последнюю, имеющуюся в наличии, дату предыдущего месяца. Если она не вых - берём её, если вых - ищу первую имеющуюся рабочую дату следующего месяца. Вот как лучше запихнуть всё это в один аналитический запрос - что-то с case скорее всего...

Сообщение было отредактировано: 23 ноя 20, 15:31
23 ноя 20, 15:30    [22237271]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2798
ARRay001

Вот как лучше запихнуть всё это в один аналитический запрос - что-то с case скорее всего...


как луче я не знаю, как через два поля я описал (если я правильно понимаю задачку)

приведите данные (with t1 (id, date1, fld2, fld3) as ... )
и что получить


.....
stax
23 ноя 20, 17:22    [22237364]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
НеофитSQL
Member

Откуда: Маями
Сообщений: 760
ARRay001
env,

with t1 (id, date1, fld2, fld3) as
(
              select 111, to_date('10.10.2020', 'DD.MM.YYYY'), 10, '11' from dual
    union all select 222, to_date('11.10.2020', 'DD.MM.YYYY'), 15, '22' from dual
    union all select 333, to_date('12.10.2020', 'DD.MM.YYYY'), 30, '43' from dual
    
)
select t1.*
from t1;


Результат: В строке с датой '12.10.2020' надо, допустим, значение fld2 сделать равным значению fld3 на у даты '10.10.2020'.


Дословный ответ.

select id, date1, 
       case date1 when to_date('12.10.2020', 'DD.MM.YYYY')
                  then to_number((select max(fld3) from t1 where date1=to_date('10.10.2020', 'DD.MM.YYYY'))) 
                  else fld2 end,
       fld3
       from t1;
23 ноя 20, 19:30    [22237444]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
НеофитSQL
Member

Откуда: Маями
Сообщений: 760
ARRay001
Задачка опять видоизменилась. Берётся не последний день месяца, а последний день месяца, имеющийся в списке дат. Т.е. любая последняя дата предыдущего месяца. Наверное изменится интервал?


Напишите новые таблицы задачи и желаемый результат,
чтобы не бегать между начальником и форумом "А это, правильный ответ?"

Добрая воля имеет пределы :)
23 ноя 20, 19:33    [22237445]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
ARRay001
Member

Откуда:
Сообщений: 50
Исходные данные не поменялись. Пример приводили много раз выше. Вот, вариант генерации:
with t1 (id, date1, fld2, fld3) as
(
              select 111, to_date('31.10.2019', 'DD.MM.YYYY'), 10, '11' from dual
    union all select 222, to_date('01.11.2019', 'DD.MM.YYYY'), 15, '22' from dual
    union all select 333, to_date('05.11.2019', 'DD.MM.YYYY'), 30, '43' from dual
    union all select 111, to_date('26.08.2020', 'DD.MM.YYYY'), 10, '07' from dual
    union all select 111, to_date('28.09.2020', 'DD.MM.YYYY'), 10, '10' from dual
    union all select 111, to_date('30.09.2020', 'DD.MM.YYYY'), 10, '09' from dual
    union all select 111, to_date('30.10.2020', 'DD.MM.YYYY'), 10, '11' from dual
    union all select 111, to_date('31.10.2020', 'DD.MM.YYYY'), 10, '11' from dual
    union all select 222, to_date('01.11.2020', 'DD.MM.YYYY'), 15, '22' from dual
    union all select 333, to_date('05.11.2020', 'DD.MM.YYYY'), 30, '43' from dual    
)
select t1.*,
 last_value(fld3) over 
    (order by date1 range between date1 - add_months(trunc(date1, 'month'), -1) preceding 
                              and date1            - trunc(date1, 'month') + 1  preceding) lv
from t1
order by date1;


В данном примере находится значение lv, равное значению fld3 по последней дате из предыдущего месяца, имеющейся в таблице (НЕ ПОСЛЕДНЕЙ ДАТЕ предыдущего месяца, а ПОСЛЕДНЕЙ ИМЕЮЩЕЙСЯ ДАТЕ предыдущего месяца).
Задача: видоизменить расчёт lv, чтобы если последняя имеющаяся дата предыдущего месяца является выходным днём (т.е. сравнивается с результатом из таблицы с датами select t.wd from wdate t where t.date = date1, где t.wd = 0 - не рабочие дни, 1 - рабочие), то берётся первая имеющаяся рабочая дата следующего имеющегося в наличии месяца.
25 ноя 20, 12:12    [22238245]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2798
ARRay001,

'30.09.2020' и '30.10.2020' выходные
для
union all select 111, to_date('30.10.2020', 'DD.MM.YYYY'), 10, '11' from dual
что насчитаете?

ps
желательно ид сделать уникальными, так легче общатся

.....
stax
25 ноя 20, 14:15    [22238331]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
ARRay001
Member

Откуда:
Сообщений: 50
Опять прошу прощения за некорректную постановку, но наконец сам понял что именно надо:
Исходные данные (вариант генерации):
with t1 (id, date1, fld2, fld3) as
(
              select 111, to_date('31.10.2019', 'DD.MM.YYYY'), 10, '11' from dual
    union all select 222, to_date('01.11.2019', 'DD.MM.YYYY'), 15, '22' from dual
    union all select 333, to_date('05.11.2019', 'DD.MM.YYYY'), 30, '43' from dual
    union all select 111, to_date('26.08.2020', 'DD.MM.YYYY'), 10, '07' from dual
    union all select 111, to_date('28.09.2020', 'DD.MM.YYYY'), 10, '10' from dual
    union all select 111, to_date('30.09.2020', 'DD.MM.YYYY'), 10, '09' from dual
    union all select 111, to_date('30.10.2020', 'DD.MM.YYYY'), 10, '11' from dual
    union all select 111, to_date('31.10.2020', 'DD.MM.YYYY'), 10, '11' from dual
    union all select 222, to_date('01.11.2020', 'DD.MM.YYYY'), 15, '22' from dual
    union all select 222, to_date('02.11.2020', 'DD.MM.YYYY'), 15, '22' from dual
    union all select 333, to_date('05.11.2020', 'DD.MM.YYYY'), 30, '43' from dual    
)
select t1.*,
 last_value(fld3) over 
    (order by date1 range between date1 - add_months(trunc(date1, 'month'), -1) preceding 
                              and date1            - trunc(date1, 'month') + 1  preceding) lv
from t1
order by date1;


В данном примере находится значение lv, равное значению fld3 по последней дате из предыдущего месяца, имеющейся в таблице (НЕ ПОСЛЕДНЕЙ ДАТЕ предыдущего месяца, а ПОСЛЕДНЕЙ ИМЕЮЩЕЙСЯ ДАТЕ предыдущего месяца).
Задача: если если текущая дата конца месяца (здесь '31.10.2020' суббота) является выходным днём (т.е. сравнивается с результатом из таблицы с датами select t.wd from wdate t where t.date = date1, где t.wd = 0 - не рабочие дни, 1 - рабочие), то вычисленное значение lv ставится в fld2, соответствующее первой имеющейся рабочей дате (здесь это '02.11.2020') следующего имеющегося в наличии месяца (ближайшая дата следующего месяца обязательно будет присутствовать). Если текущая дата конца месяца рабочая, то значение lv подставляется в fld2 по текущей дате.
Трудновато выделить всё в отдельный пример. Изменится структура данного запроса.

Сообщение было отредактировано: 25 ноя 20, 17:15
25 ноя 20, 17:19    [22238391]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6749
ARRay001,

И что же будет, если у вас последняя запись предыдущего месяца - первая суббота, а единственная следующего - последнее воскресенье?

Вы для себя на бумажке логику распишите сначала, не надо ждать чуда от пока ещё добрых людей с форума.
Оракл умеет реализовывать только ту логику, которая поддаётся алгоритмизации, не более того.

Сообщение было отредактировано: 25 ноя 20, 17:23
25 ноя 20, 17:27    [22238398]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
ARRay001
Member

Откуда:
Сообщений: 50
env,
Такого быть не может. Обязательно будет присутствовать рабочий день следующего за текущим месяца в fld2 которого надо будет подставить значение fld3, вычисленное по последней имеющейся дате предыдущего текущей дате (на момент расчета) месяца. Более того, он будет равен первому рабочему дню после выходных.

Сообщение было отредактировано: 25 ноя 20, 17:31
25 ноя 20, 17:34    [22238404]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2798
ARRay001
Опять прошу прощения за некорректную постановку, но наконец сам понял что именно надо:
Исходные данные (вариант генерации):
with t1 (id, date1, fld2, fld3) as
(
              select 111, to_date('31.10.2019', 'DD.MM.YYYY'), 10, '11' from dual
    union all select 222, to_date('01.11.2019', 'DD.MM.YYYY'), 15, '22' from dual
    union all select 333, to_date('05.11.2019', 'DD.MM.YYYY'), 30, '43' from dual
    union all select 111, to_date('26.08.2020', 'DD.MM.YYYY'), 10, '07' from dual
    union all select 111, to_date('28.09.2020', 'DD.MM.YYYY'), 10, '10' from dual
    union all select 111, to_date('30.09.2020', 'DD.MM.YYYY'), 10, '09' from dual
    union all select 111, to_date('30.10.2020', 'DD.MM.YYYY'), 10, '11' from dual
    union all select 111, to_date('31.10.2020', 'DD.MM.YYYY'), 10, '11' from dual
    union all select 222, to_date('01.11.2020', 'DD.MM.YYYY'), 15, '22' from dual
    union all select 222, to_date('02.11.2020', 'DD.MM.YYYY'), 15, '22' from dual
    union all select 333, to_date('05.11.2020', 'DD.MM.YYYY'), 30, '43' from dual    
)
select t1.*,
 last_value(fld3) over 
    (order by date1 range between date1 - add_months(trunc(date1, 'month'), -1) preceding 
                              and date1            - trunc(date1, 'month') + 1  preceding) lv
from t1
order by date1;


В данном примере находится значение lv, равное значению fld3 по последней дате из предыдущего месяца, имеющейся в таблице (НЕ ПОСЛЕДНЕЙ ДАТЕ предыдущего месяца, а ПОСЛЕДНЕЙ ИМЕЮЩЕЙСЯ ДАТЕ предыдущего месяца).
Задача: если если текущая дата конца месяца (здесь '31.10.2020' суббота) является выходным днём (т.е. сравнивается с результатом из таблицы с датами select t.wd from wdate t where t.date = date1, где t.wd = 0 - не рабочие дни, 1 - рабочие), то вычисленное значение lv ставится в fld2, соответствующее первой имеющейся рабочей дате (здесь это '02.11.2020') следующего имеющегося в наличии месяца (ближайшая дата следующего месяца обязательно будет присутствовать). Если текущая дата конца месяца рабочая, то значение lv подставляется в fld2 по текущей дате.
Трудновато выделить всё в отдельный пример. Изменится структура данного запроса.


для строки с '01.11.2020' какой роезультат
распишите как получить (берем предыдущий месяц (10), ищем последнюю дату (31.10.2020), смотрим выходной ли -продолжте)

автор
Если текущая дата конца месяца рабочая, то значение lv подставляется в fld2 по текущей дате.

я пока вообще не понял

зы
в примере id, fld2, fld3 сделайте уникальными, удобнее будет говорить о строках(рядках))

.....
stax
25 ноя 20, 17:42    [22238413]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6749
ARRay001
Обязательно будет присутствовать рабочий день следующего за текущим месяца

Ок. Пусть будет. Последний день следующего месяца.

Пока не расписаны имеющиеся ограничения и нет алгоритма действий - так и будете переделывать раз за разом.
25 ноя 20, 17:51    [22238423]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
ARRay001
Member

Откуда:
Сообщений: 50
Stax,
with t1 (id, date1, fld2, fld3) as
(
              select 111, to_date('31.10.2019', 'DD.MM.YYYY'), 10, '11' from dual
    union all select 222, to_date('01.11.2019', 'DD.MM.YYYY'), 15, '22' from dual
    union all select 333, to_date('05.11.2019', 'DD.MM.YYYY'), 30, '43' from dual
    union all select 444, to_date('26.08.2020', 'DD.MM.YYYY'), 10, '07' from dual
    union all select 555, to_date('28.09.2020', 'DD.MM.YYYY'), 10, '10' from dual
    union all select 666, to_date('30.09.2020', 'DD.MM.YYYY'), 10, '09' from dual
    union all select 777, to_date('30.10.2020', 'DD.MM.YYYY'), 10, '11' from dual
    union all select 888, to_date('31.10.2020', 'DD.MM.YYYY'), 10, '11' from dual
    union all select 999, to_date('01.11.2020', 'DD.MM.YYYY'), 15, '22' from dual
    union all select 112, to_date('02.11.2020', 'DD.MM.YYYY'), 15, '22' from dual
    union all select 113, to_date('05.11.2020', 'DD.MM.YYYY'), 30, '43' from dual    
)
select t1.*,
 last_value(fld3) over 
    (order by date1 range between date1 - add_months(trunc(date1, 'month'), -1) preceding 
                              and date1            - trunc(date1, 'month') + 1  preceding) lv
from t1
order by date1;


Мы считаем на дату '31.10.2020' (последняя дата октября) - анализ следующий:
- В '01.11.2020' (воскресенье) fld2 = 0.
- В '02.11.2020' (первый рабочий день после '31.10.2020') fld2 = '09' (значение fld3 на дату '30.09.2020' (это последний день месяца, предшествующего '31.10.2020'),
Если бы '01.11.2020' был бы рабочим днём, то для него также бралось бы значение fld3 на дату '30.09.2020', т.е. fld2 = '09' .

Сообщение было отредактировано: 25 ноя 20, 17:59
25 ноя 20, 18:03    [22238437]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6749
ARRay001
union all select 666, to_date('30.09.2020', 'DD.MM.YYYY'), 10, '09' from dual
    union all select 777, to_date('30.10.2020', 'DD.MM.YYYY'), 10, '11' from dual

О, великолепно. А если там будут 27.09.2020 и 31.10.2020 и нет других дат, то что брать?

упд. цитату взял как пример того, что разрыв в данных может быть от конца месяца, до конца месяца

Сообщение было отредактировано: 25 ноя 20, 18:04
25 ноя 20, 18:08    [22238440]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
ARRay001
Member

Откуда:
Сообщений: 50
env,
Тогда расчёт не делается или 0 (даты для расчёта просто нет). Расчёт делается только в последний рабочий день месяца, либо на первый после выходных день следующего месяца. Если эта дата отсутствует - значит и расчёт ещё не сделан. Если вместо 29.09.2020 будет дата 27.09.2020 (любая максимальная дата сентября), то fld3 берётся по ней.

Сообщение было отредактировано: 25 ноя 20, 18:09
25 ноя 20, 18:11    [22238441]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2798
ARRay001

Stax,
with t1 (id, date1, fld2, fld3) as
(
              select 111, to_date('31.10.2019', 'DD.MM.YYYY'), 10, '11' from dual
    union all select 222, to_date('01.11.2019', 'DD.MM.YYYY'), 15, '22' from dual
    union all select 333, to_date('05.11.2019', 'DD.MM.YYYY'), 30, '43' from dual
    union all select 444, to_date('26.08.2020', 'DD.MM.YYYY'), 10, '07' from dual
    union all select 555, to_date('28.09.2020', 'DD.MM.YYYY'), 10, '10' from dual
    union all select 666, to_date('30.09.2020', 'DD.MM.YYYY'), 10, '09' from dual
    union all select 777, to_date('30.10.2020', 'DD.MM.YYYY'), 10, '11' from dual
    union all select 888, to_date('31.10.2020', 'DD.MM.YYYY'), 10, '11' from dual
    union all select 999, to_date('01.11.2020', 'DD.MM.YYYY'), 15, '22' from dual
    union all select 112, to_date('02.11.2020', 'DD.MM.YYYY'), 15, '22' from dual
    union all select 113, to_date('05.11.2020', 'DD.MM.YYYY'), 30, '43' from dual    
)
select t1.*,
 last_value(fld3) over 
    (order by date1 range between date1 - add_months(trunc(date1, 'month'), -1) preceding 
                              and date1            - trunc(date1, 'month') + 1  preceding) lv
from t1
order by date1;


Мы считаем на дату '31.10.2020' (последняя дата октября) - анализ следующий:
- В '01.11.2020' (воскресенье) fld2 = 0.
- В '02.11.2020' (первый рабочий день после '31.10.2020') fld2 = '09' (значение fld3 на дату '30.09.2020' (это последний день месяца, предшествующего '31.10.2020'),
Если бы '01.11.2020' был бы рабочим днём, то для него также бралось бы значение fld3 на дату '30.09.2020', т.е. fld2 = '09' .


откуда '30.09.2020' для строки с '01.11.2020' (выходной)?

напишите все без если бы

01.11.2020, 31.10.2020 выходной
... в результате получим
222 01.11.2020 15 22 ??

......
stax
25 ноя 20, 18:56    [22238472]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6749
ARRay001
только в последний рабочий день месяца, либо на первый после выходных день следующего месяца

О, ещё кусочек пазла.

ARRay001,
ну нет у остальных вашего контекста в голове. А хрустальные шары опять не работают. Полнее задачу ставьте, если хотите помощи.
26 ноя 20, 09:40    [22238713]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2798
env
ARRay001
только в последний рабочий день месяца, либо на первый после выходных день следующего месяца

О, ещё кусочек пазла.


непонятно
в будущее надо гаглядывать, или нет?

.....
stax
26 ноя 20, 10:44    [22238741]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
ARRay001
Member

Откуда:
Сообщений: 50
Stax,
нет - только те даты, что есть...
26 ноя 20, 11:04    [22238747]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
ARRay001
Member

Откуда:
Сообщений: 50
Stax,

откуда '30.09.2020' для строки с '01.11.2020' (выходной)? - для строки с '01.11.2020' (выходной) расчёт делать не надо - там '0'

напишите все без если бы - я написал вроде максимально точно, если что не ясно уточняйте что именно

01.11.2020, 31.10.2020 выходной
... в результате получим такие результаты:

666 '30.09.2020' (посл. раб день месяца) - , 10, '09'
777 '30.10.2020' (раб) - , '0' т.к. для этой даты можно не считать, т.к. это не конец месяца', '11'
888 '31.10.2020' (вых) - , '0', '11'
999 '01.11.2020' (вых) - , '0', '22'
112 '02.11.2020' (раб) - , '09' (=fld3 за '30.09.2020'),'22'

Могу так всю таблицу расписать, хотя закономерность тут показана. Если так будет сложно, можно вместо '0' дублировать значение, вычисленное на предыдущий конец месяца.
26 ноя 20, 11:33    [22238763]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6749
ARRay001
если что не ясно уточняйте что именно

Может лучше вы напишете уже внятно, что вам надо? Без необходимости уточнять.

Это не нам надо решить вашу задачу, это вам нужно решение.
26 ноя 20, 11:42    [22238766]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
ARRay001
Member

Откуда:
Сообщений: 50
env,
так я расписал всё подробно
26 ноя 20, 11:47    [22238769]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6749
ARRay001,

Соберите описание в одно сообщение. Укажите внятно граничные случаи и обязательные данные.
И заодно покажите, а что сами-то сделали, чтобы решить задачу со всеми "подробными уточнениями".
26 ноя 20, 12:08    [22238774]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2798
ARRay001

так я расписал всё подробно

давайте я попробую

чутку поменял (чтоб понятней было)
    union all select 777, to_date('30.10.2020', 'DD.MM.YYYY'), 10, '11' from dual
    union all select 888, to_date('31.10.2020', 'DD.MM.YYYY'), 10, '110' from dual
    union all select 999, to_date('01.11.2020', 'DD.MM.YYYY'), 15, '22' from dual
    union all select 112, to_date('02.11.2020', 'DD.MM.YYYY'), 15, '220' from dual


1) считаем показатель (lv) для каждой строки (over по другому не умеют)
2) пример
31.10.2020 01.11.2020 выходные wd=0

считаем для строки с 999, to_date('01.11.2020'

2.1 берем предыдущий месяц - 10 -й
2.2 ищем в предыдущем месяце последний день - 31/10 (не обязательно 31)
2.3 последний в передыдущем 31/10 выходной, не подходит
2.3.1 берем текуший для строки месяц - 10
2.3.2 ищем в текущем месяце первый рабочий - 02/11 (будущее относительно текущего 01.11.2020)
2.3.3 ответ для строки с ид=999 ,берем значение fld3 со строки с ид=112 - lv=220

что не так?

ps
если для выходного дня "не считать", а возвращать lv напр -1 то окно будет по текущую строку (дату)


.....
stax
26 ноя 20, 12:36    [22238781]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
ARRay001
Member

Откуда:
Сообщений: 50
Stax
ARRay001

так я расписал всё подробно

давайте я попробую

чутку поменял (чтоб понятней было)
    union all select 777, to_date('30.10.2020', 'DD.MM.YYYY'), 10, '11' from dual
    union all select 888, to_date('31.10.2020', 'DD.MM.YYYY'), 10, '110' from dual
    union all select 999, to_date('01.11.2020', 'DD.MM.YYYY'), 15, '22' from dual
    union all select 112, to_date('02.11.2020', 'DD.MM.YYYY'), 15, '220' from dual


1) считаем показатель (lv) для каждой строки (over по другому не умеют) - если нельзя иначе, то да
2) пример
31.10.2020 01.11.2020 выходные wd=0

считаем для строки с 999, to_date('01.11.2020'

2.1 берем предыдущий месяц - 10 -й
2.2 ищем в предыдущем месяце последний день - 31/10 (не обязательно 31)
2.3 последний в передыдущем 31/10 выходной, не подходит
2.3.1 берем текуший для строки месяц - 10
2.3.2 ищем в текущем месяце первый рабочий - 02/11 (будущее относительно текущего 01.11.2020)
2.3.3 ответ для строки с ид=999 ,берем значение fld3 со строки с ид=112 - lv=220

что не так? - сама суть. Вот мой вариант:
для строки с 999, to_date('01.11.2020' считать ничего не нужно - там 0)
Идём по порядку дат:
- 31.10.2020 - вых - не считаем
- 01.11.2020 - вых - не считаем
- 02.11.2020 - раб - считаем за октябрь по последней дате сентября (30.09.2020) вместо 31.10.2020.
Как искать - будущее относительно текущего или смотреть какие дни были до текущей (если не рабочие конца месяца, то считаем через месяц назад) - это вопрос выбора при проектировании алгоритма. Я пока не понял как лучше делать.
- соответственно, берём значение fld3 со строки где дата 30.09.2020

ps
если для выходного дня "не считать", а возвращать lv напр -1 то окно будет по текущую строку (дату)
это тоже вопрос проектирования алгоритма - главное, чтобы понятно было, что там нет значения или 0.


.....
stax
26 ноя 20, 14:02    [22238834]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2798
ARRay001


- 02.11.2020 - раб - считаем за октябрь по последней дате сентября (30.09.2020) вместо 31.10.2020.
Как искать - будущее относительно текущего или смотреть какие дни были до текущей (если не рабочие конца месяца, то считаем через месяц назад)


автор
если если текущая дата конца месяца (здесь '31.10.2020' суббота) является выходным днём, то вычисленное значение lv ставится в fld2, соответствующее первой имеющейся рабочей дате (здесь это '02.11.2020') следующего имеющегося в наличии месяца (ближайшая дата следующего месяца обязательно будет присутствовать).


противоречие через месяц назад и ближайшая дата следующего месяца

зы
автор
Я пока не понял как лучше делать.

как лучше пока(временно) не рассматриваем
будет несколько вариантов решения, тогда и лучшее выберите

терзают меня смутные сомнения, что мож тут over и не надо,
зачем для целого месяца повторять значения из "последний" записи из пред. месяца

.....
stax
26 ноя 20, 14:39    [22238861]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
ARRay001
Member

Откуда:
Сообщений: 50
Stax,

"противоречие через месяц назад и ближайшая дата следующего месяца" - противоречий нет:

"Через месяц назад" - это относится к значению какое брать, а "ближайшая дата следующего месяца" - это к месту, куда это значение выводить (первый рабочий день следующего месяца).
"терзают меня смутные сомнения, что мож тут over и не надо," - Может и не over,
"зачем для целого месяца повторять значения из "последний" записи из пред. месяца" - согласен - лучше писать значения только в последнюю дату месяца (если она рабочая), иначе в первый рабочий день следующего месяца.

Сообщение было отредактировано: 26 ноя 20, 14:45
26 ноя 20, 14:49    [22238869]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
graycode
Member

Откуда:
Сообщений: 468
Завести календарь уже предлагали?
26 ноя 20, 14:56    [22238873]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
env
Member

Откуда: Россия, Москва
Сообщений: 6749
graycode,

Да 22237267, не помогло
26 ноя 20, 15:03    [22238880]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
ARRay001
Member

Откуда:
Сообщений: 50
graycode,
что значит завести календарь? Есть таблица, где перечислены все рабочие и не рабочие дни. О ней я писал выше и от неё можно отталкиваться...
26 ноя 20, 15:05    [22238882]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
graycode
Member

Откуда:
Сообщений: 468
ARRay001,

Отлично, теперь осталось четко описать алгоритм получения искомой даты не привязываясь к реализации, после чего можно приступать к реализации.
26 ноя 20, 15:26    [22238898]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2798
ARRay001
Stax,

"противоречие через месяц назад и ближайшая дата следующего месяца" - противоречий нет:

"Через месяц назад" - это относится к значению какое брать, а "ближайшая дата следующего месяца" - это к месту, куда это значение выводить (первый рабочий день следующего месяца).

тоесть lv считать не для каждой строки, токо в первый рабочий день

ARRay001
Stax,
"терзают меня смутные сомнения, что мож тут over и не надо," - Может и не over,

тема Использование оконных (аналитических) функций

ARRay001

"зачем для целого месяца повторять значения из "последний" записи из пред. месяца" - согласен - лучше писать значения только в последнюю дату месяца (если она рабочая), иначе в первый рабочий день следующего месяца.


вопрос не в лучше, а куда писать (вернее в какой строке), запутали Вы меня

.....
stax
26 ноя 20, 15:28    [22238900]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
ARRay001
Member

Откуда:
Сообщений: 50
Постараюсь ещё раз описать алгоритм без привязки к реализации:
Смотрим каждую последнюю дату месяца:
1) если это не выходной день (смотрим по таблице дат) -> ищем значение fld3 на последнюю дату предыдущего месяца и выводим в текущее fld2
2) если это выходной день (смотрим по таблице дат) ->
-- ищем значение fld3 на последнюю дату предыдущего месяца
-- ищем первый рабочий день следующего месяца и выводим в fld2 этого дня найденное значение fld3

всё.
26 ноя 20, 16:45    [22238921]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2798
ARRay001,
про fld2 я не понял
вот что я нафантазировал
with t1 (id, date1, fld2, fld3) as (
              select 1, to_date('10.01.2020', 'DD.MM.YYYY'), 10, '11' from dual
    union all select 2, to_date('11.01.2020', 'DD.MM.YYYY'), 15, '22' from dual
    union all select 3, to_date('12.01.2020', 'DD.MM.YYYY'), 30, '43' from dual
--
    union all select 4, to_date('01.07.2020', 'DD.MM.YYYY'), 10, '17' from dual
    union all select 5, to_date('21.07.2020', 'DD.MM.YYYY'), 15, '27' from dual
    union all select 6, to_date('30.07.2020', 'DD.MM.YYYY'), 30, '07' from dual
--
    union all select 7, to_date('13.08.2020', 'DD.MM.YYYY'), 10, '14' from dual
    union all select 8, to_date('17.08.2020', 'DD.MM.YYYY'), 15, '24' from dual
    union all select 9, to_date('29.08.2020', 'DD.MM.YYYY'), 30, '02' from dual
--
    union all select 10, to_date('03.09.2020', 'DD.MM.YYYY'), 10, '14' from dual
    union all select 11, to_date('27.09.2020', 'DD.MM.YYYY'), 15, '24' from dual
    union all select 12, to_date('29.09.2020', 'DD.MM.YYYY'), 30, '04' from dual
--
    union all select 10, to_date('07.10.2020', 'DD.MM.YYYY'), 10, '12' from dual
    union all select 11, to_date('27.10.2020', 'DD.MM.YYYY'), 15, '22' from dual
    union all select 12, to_date('31.10.2020', 'DD.MM.YYYY'), 30, '42' from dual
--
    union all select 10, to_date('01.11.2020', 'DD.MM.YYYY'), 10, '15' from dual
    union all select 11, to_date('02.11.2020', 'DD.MM.YYYY'), 15, '25' from dual
    union all select 12, to_date('20.11.2020', 'DD.MM.YYYY'), 30, '05' from dual
)
,wdate (dat,wd) as( 
              select to_date('31.10.2019', 'DD.MM.YYYY'), 0 from dual
    union all select to_date('31.10.2020', 'DD.MM.YYYY'), 0 from dual
    union all select to_date('13.08.2020', 'DD.MM.YYYY'), 0 from dual
    union all select to_date('29.09.2020', 'DD.MM.YYYY'), 0 from dual
    union all select to_date('01.11.2020', 'DD.MM.YYYY'), 0 from dual
    union all select to_date('01.07.2020', 'DD.MM.YYYY'), 0 from dual
)
,w as ( -- +календар выходных
 select t1.*,nvl(wd,1) wd from t1 t1,wdate w where date1=dat(+)
-- order by 2
)
, l as ( --последняя строка в месяце не выходной
select 
  trunc(date1,'mm') date1
  ,max(fld3) KEEP (DENSE_RANK last ORDER BY date1) fld3
--  ,max(wd) KEEP (DENSE_RANK last ORDER BY date1) wd
from w
group by trunc(date1,'mm')
having max(wd) KEEP (DENSE_RANK last ORDER BY date1)=1
)
 select w.*
 ,decode(
   first_value(decode(wd,1,date1) ignore nulls) over (partition by trunc(date1,'mm') order by date1) --первый рабочий день в месяце
     ,date1
     ,(select max(l.fld3) KEEP (DENSE_RANK last ORDER BY l.date1) from l where l.date1<trunc(w.date1,'mm')) --пред месяцы 
    ) lv
from w order by 2
/
SQL> /

        ID DATE1            FLD2 FL         WD LV
---------- ---------- ---------- -- ---------- --
         1 10.01.2020         10 11          1
         2 11.01.2020         15 22          1
         3 12.01.2020         30 43          1
         4 01.07.2020         10 17          0
         5 21.07.2020         15 27          1 43
         6 30.07.2020         30 07          1
         7 13.08.2020         10 14          0
         8 17.08.2020         15 24          1 07
         9 29.08.2020         30 02          1
        10 03.09.2020         10 14          1 02
        11 27.09.2020         15 24          1
        12 29.09.2020         30 04          0
        10 07.10.2020         10 12          1 02
        11 27.10.2020         15 22          1
        12 31.10.2020         30 42          0
        10 01.11.2020         10 15          0
        11 02.11.2020         15 25          1 02
        12 20.11.2020         30 05          1

18 rows selected.


ps
мож надо некоторые with материализовать

.....
stax
26 ноя 20, 17:08    [22238932]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
graycode
Member

Откуда:
Сообщений: 468
ARRay001
Постараюсь ещё раз описать алгоритм без привязки к реализации:
Смотрим каждую последнюю дату месяца:
1) если это не выходной день (смотрим по таблице дат) -> ищем значение fld3 на последнюю дату предыдущего месяца и выводим в текущее fld2
2) если это выходной день (смотрим по таблице дат) ->
-- ищем значение fld3 на последнюю дату предыдущего месяца
-- ищем первый рабочий день следующего месяца и выводим в fld2 этого дня найденное значение fld3

всё.

Очень странное описание, почему в fld2, оно другого типа и с другим диапазоном значений, если 29, 30, 31 и 01 следующего месяца - нерабочие дни, то дата я так полагаю должна попасть в 02, т.е. предположение о последней дате месяца мягко говоря не выдерживает критики.

Итак, попробую описать как выглядит ваша задача на самом деле.

Есть некое поле fld3, есть периоды (месяцы), требуется для всех строк попадающих в определенный период вывести поле fld3_prev, со значением из строки с самой последней датой предыдущего периода (месяца), при этом даты которые есть не годятся для расчетов, т.е. эффективные даты нужно предварительно получить следующим образом - эффективная дата равна первому рабочему дню больше или равному исходной дате. Верное описание?
26 ноя 20, 19:00    [22238998]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
ARRay001
Member

Откуда:
Сообщений: 50
graycode
ARRay001
Постараюсь ещё раз описать алгоритм без привязки к реализации:
Смотрим каждую последнюю дату месяца:
1) если это не выходной день (смотрим по таблице дат) -> ищем значение fld3 на последнюю дату предыдущего месяца и выводим в текущее fld2
2) если это выходной день (смотрим по таблице дат) ->
-- ищем значение fld3 на последнюю дату предыдущего месяца
-- ищем первый рабочий день следующего месяца и выводим в fld2 этого дня найденное значение fld3

всё.

Очень странное описание, почему в fld2, оно другого типа и с другим диапазоном значений, если 29, 30, 31 и 01 следующего месяца - нерабочие дни, то дата я так полагаю должна попасть в 02, т.е. предположение о последней дате месяца мягко говоря не выдерживает критики.

Итак, попробую описать как выглядит ваша задача на самом деле.

Есть некое поле fld3, есть периоды (месяцы), требуется для всех строк попадающих в определенный период вывести поле fld3_prev, со значением из строки с самой последней датой предыдущего периода (месяца), при этом даты которые есть не годятся для расчетов, т.е. эффективные даты нужно предварительно получить следующим образом - эффективная дата равна первому рабочему дню больше или равному исходной дате. Верное описание?


А чем моё описание не так? Поля все, кроме даты имеют строковый тип в итоге. Мне важна суть решения. С предыдущим решением ещё не разобрался, но похоже на истину. Вот переделанная ваша трактовка:
Есть все поля: дата, fld3 и fld2 (у меня их намного больше и с unpivot). Требуется для всех строк, соответствующих последнему дню месяца (если они есть) и являющихся рабочими днями вывести поле fld3_prev, со значением из строки с самой последней датой предыдущего периода (месяца) в текущее поле fld2, при этом, если последний день месяца является нерабочим днем и не годится для подстановки (, т.е. эффективные даты для подстановки нужно предварительно получить следующим образом - эффективная дата равна первому рабочему дню больше или равному исходной дате), то fld3_prev выводится в полученную эффективную дату в поле fld2.

Можно это с другой стороны перевернуть - это как лучше для алгоритма расчета. Но суть такая.
26 ноя 20, 21:18    [22239039]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
graycode
Member

Откуда:
Сообщений: 468
ARRay001,

Что вы пытаетесь сделать мне не понятно, может быть Stax догадается, я пас.
26 ноя 20, 21:34    [22239042]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
ARRay001
Member

Откуда:
Сообщений: 50
graycode,
правда не понимаю, как ещё расписать условие.
Надо ставить в текущее поле fld2 значения fld3 с последней даты предыдущего месяца, если текущая дата рабочая. Если текущее не рабочая дата, то ставим то же самое fld3 не в текущее fld2, а в fld2 ниже - на первую рабочую дату следующего месяца. Причем отбирать в качестве текущих только даты окончания месяцев в качестве точки отсчета (значения fld2 в остальные даты месяца = 0). Также fld2=0 будет у дат окончания месяца, которые выходные.

Сообщение было отредактировано: 26 ноя 20, 23:54
26 ноя 20, 23:51    [22239090]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
ARRay001
Member

Откуда:
Сообщений: 50
Stax
ARRay001,
про fld2 я не понял
вот что я нафантазировал
with t1 (id, date1, fld2, fld3) as (
              select 1, to_date('10.01.2020', 'DD.MM.YYYY'), 10, '11' from dual
    union all select 2, to_date('11.01.2020', 'DD.MM.YYYY'), 15, '22' from dual
    union all select 3, to_date('12.01.2020', 'DD.MM.YYYY'), 30, '43' from dual
--
    union all select 4, to_date('01.07.2020', 'DD.MM.YYYY'), 10, '17' from dual
    union all select 5, to_date('21.07.2020', 'DD.MM.YYYY'), 15, '27' from dual
    union all select 6, to_date('30.07.2020', 'DD.MM.YYYY'), 30, '07' from dual
--
    union all select 7, to_date('13.08.2020', 'DD.MM.YYYY'), 10, '14' from dual
    union all select 8, to_date('17.08.2020', 'DD.MM.YYYY'), 15, '24' from dual
    union all select 9, to_date('29.08.2020', 'DD.MM.YYYY'), 30, '02' from dual
--
    union all select 10, to_date('03.09.2020', 'DD.MM.YYYY'), 10, '14' from dual
    union all select 11, to_date('27.09.2020', 'DD.MM.YYYY'), 15, '24' from dual
    union all select 12, to_date('29.09.2020', 'DD.MM.YYYY'), 30, '04' from dual
--
    union all select 10, to_date('07.10.2020', 'DD.MM.YYYY'), 10, '12' from dual
    union all select 11, to_date('27.10.2020', 'DD.MM.YYYY'), 15, '22' from dual
    union all select 12, to_date('31.10.2020', 'DD.MM.YYYY'), 30, '42' from dual
--
    union all select 10, to_date('01.11.2020', 'DD.MM.YYYY'), 10, '15' from dual
    union all select 11, to_date('02.11.2020', 'DD.MM.YYYY'), 15, '25' from dual
    union all select 12, to_date('20.11.2020', 'DD.MM.YYYY'), 30, '05' from dual
)
,wdate (dat,wd) as( 
              select to_date('31.10.2019', 'DD.MM.YYYY'), 0 from dual
    union all select to_date('31.10.2020', 'DD.MM.YYYY'), 0 from dual
    union all select to_date('13.08.2020', 'DD.MM.YYYY'), 0 from dual
    union all select to_date('29.09.2020', 'DD.MM.YYYY'), 0 from dual
    union all select to_date('01.11.2020', 'DD.MM.YYYY'), 0 from dual
    union all select to_date('01.07.2020', 'DD.MM.YYYY'), 0 from dual
)
,w as ( -- +календар выходных
 select t1.*,nvl(wd,1) wd from t1 t1,wdate w where date1=dat(+)
-- order by 2
)
, l as ( --последняя строка в месяце не выходной
select 
  trunc(date1,'mm') date1
  ,max(fld3) KEEP (DENSE_RANK last ORDER BY date1) fld3
--  ,max(wd) KEEP (DENSE_RANK last ORDER BY date1) wd
from w
group by trunc(date1,'mm')
having max(wd) KEEP (DENSE_RANK last ORDER BY date1)=1
)
 select w.*
 ,decode(
   first_value(decode(wd,1,date1) ignore nulls) over (partition by trunc(date1,'mm') order by date1) --первый рабочий день в месяце
     ,date1
     ,(select max(l.fld3) KEEP (DENSE_RANK last ORDER BY l.date1) from l where l.date1<trunc(w.date1,'mm')) --пред месяцы 
    ) lv
from w order by 2
/
SQL> /

        ID DATE1            FLD2 FLD3      WD LV
---------- ---------- ---------- -- ---------- --
         1 10.01.2020         10 11          1
         2 11.01.2020         15 22          1
         3 12.01.2020         30 43          1
         4 01.07.2020         10 17          0
         5 21.07.2020         15 27          1 43 ->ВЕРНО
         6 30.07.2020         30 07          1
         7 13.08.2020         10 14          0
         8 17.08.2020         15 24          1 07 ->ВЕРНО
         9 29.08.2020         30 02          1
        10 03.09.2020         10 14          1 02 ->ВЕРНО
        11 27.09.2020         15 24          1
        12 29.09.2020         30 04          0
        10 07.10.2020         10 12          1 02 ->ВЕРНО
        11 27.10.2020         15 22          1
        12 31.10.2020         30 42          0
        10 01.11.2020         10 15          0
        11 02.11.2020         15 25          1 02 ->НЕ ВЕРНО (должно быть 04, по дате 29.09.2020) 
        12 20.11.2020         30 05          1

18 rows selected.


ps
мож надо некоторые with материализовать

.....
stax


Логика почти верна, с той разницей, что не надо включать логику "нерабочие/рабочие дни" при поиске fld3 выше. Эта логика включается при выборе, напротив какой даты ставить найденное значение fld3. Выбивается последнее значение. Ну и писаться должно не в отдельный LV, а в fld2, но это уже фигня - это я сам додумаю.

Сообщение было отредактировано: 27 ноя 20, 00:18
27 ноя 20, 00:20    [22239094]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
ARRay001
Member

Откуда:
Сообщений: 50
Немного ошибся:

Как должно быть:

ID DATE1 FLD2 FLD3 WD LV
---------- ---------- ---------- -- ---------- --
1 10.01.2020 10 11 1
2 11.01.2020 15 22 1
3 12.01.2020 30 43 1
4 01.07.2020 10 17 0
5 21.07.2020 15 27 1
6 30.07.2020 30 07 1 43
7 13.08.2020 10 14 0
8 17.08.2020 15 24 1
9 29.08.2020 30 02 1 07
10 03.09.2020 10 14 1
11 27.09.2020 15 24 1
12 29.09.2020 30 04 0
10 07.10.2020 10 12 1 02
11 27.10.2020 15 22 1
12 31.10.2020 30 42 0
10 01.11.2020 10 15 0
11 02.11.2020 15 25 1 04
12 20.11.2020 30 05 1 42

Сообщение было отредактировано: 27 ноя 20, 01:10
27 ноя 20, 01:09    [22239100]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2798
ARRay001
Немного ошибся:

Как должно быть:


понял

чутку освобожусь, сделаю без "Ну и писаться должно не в отдельный LV, а в fld2, но это уже фигня - это я сам додумаю"
пока не оптимально (не знаю как луче over или select max(l.fld3) KEEP ...)
постараюсь к обеду

.....
stax
27 ноя 20, 09:47    [22239174]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
merch
Member

Откуда:
Сообщений: 170
а что, на этом форуме больше не посылают в ветку "помощь студентам" и т.д?
27 ноя 20, 12:09    [22239255]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2798
ARRay001,

with t1 (id, date1, fld2, fld3) as (
              select 1, to_date('10.01.2020', 'DD.MM.YYYY'), 10, '11' from dual
    union all select 2, to_date('11.01.2020', 'DD.MM.YYYY'), 15, '22' from dual
    union all select 3, to_date('12.01.2020', 'DD.MM.YYYY'), 30, '43' from dual
--
    union all select 4, to_date('01.05.2020', 'DD.MM.YYYY'), 10, '17' from dual
    union all select 5, to_date('21.05.2020', 'DD.MM.YYYY'), 15, '27' from dual
    union all select 6, to_date('30.05.2020', 'DD.MM.YYYY'), 30, '07' from dual
--
    union all select 7, to_date('13.08.2020', 'DD.MM.YYYY'), 10, '14' from dual
    union all select 8, to_date('17.08.2020', 'DD.MM.YYYY'), 15, '24' from dual
    union all select 9, to_date('29.08.2020', 'DD.MM.YYYY'), 30, '02' from dual
--
    union all select 10, to_date('03.09.2020', 'DD.MM.YYYY'), 10, '14' from dual
    union all select 11, to_date('27.09.2020', 'DD.MM.YYYY'), 15, '24' from dual
    union all select 12, to_date('29.09.2020', 'DD.MM.YYYY'), 30, '04' from dual
--
    union all select 10, to_date('07.10.2020', 'DD.MM.YYYY'), 10, '12' from dual
    union all select 11, to_date('27.10.2020', 'DD.MM.YYYY'), 15, '22' from dual
    union all select 12, to_date('31.10.2020', 'DD.MM.YYYY'), 30, '42' from dual
--
    union all select 10, to_date('01.11.2020', 'DD.MM.YYYY'), 10, '15' from dual
    union all select 11, to_date('02.11.2020', 'DD.MM.YYYY'), 15, '25' from dual
    union all select 12, to_date('20.11.2020', 'DD.MM.YYYY'), 30, '05' from dual
)
,wdate (dat,wd) as(
              select to_date('31.10.2019', 'DD.MM.YYYY'), 0 from dual
    union all select to_date('31.10.2020', 'DD.MM.YYYY'), 0 from dual
    union all select to_date('13.08.2020', 'DD.MM.YYYY'), 0 from dual
    union all select to_date('29.09.2020', 'DD.MM.YYYY'), 0 from dual
    union all select to_date('01.11.2020', 'DD.MM.YYYY'), 0 from dual
    union all select to_date('01.07.2020', 'DD.MM.YYYY'), 0 from dual
)
,w as ( -- +календар выходных
 select
  t1.*
  ,nvl(wd,1) wd
  ,decode(nvl(wd,1),1,date1) date_work
 from t1 t1,wdate w where date1=dat(+)
-- order by 2
)
, l as ( --последняя строка в месяце /*не выходной*/
select
  trunc(date1,'mm') date1
  ,max(fld3) KEEP (DENSE_RANK last ORDER BY date1) fld3
--  ,max(wd) KEEP (DENSE_RANK last ORDER BY date1) wd
from w
group by trunc(date1,'mm')
--having max(wd) KEEP (DENSE_RANK last ORDER BY date1)=1
)
select w.*
 ,case
    when lead(cast(null as number),1,wd) over (partition by trunc(date1,'mm') order by date1)=1 --последний  день тек месяца рабочий
              then (select max(l.fld3) KEEP (DENSE_RANK last ORDER BY l.date1) from l where l.date1<trunc(w.date1,'mm'))
    when lag(null,1,wd) over (partition by trunc(date_work,'mm') order by date1)=1 --первый рабочий день тек месяца
       and last_value(wd) over (order by date1 range between --последний день "предыдущего" месяца
                                                    UNBOUNDED PRECEDING AND date1-trunc(date1,'mm') PRECEDING)=0
              then (select max(l.fld3) KEEP (DENSE_RANK last ORDER BY l.date1) from l where l.date1<add_months(trunc(w.date1,'mm'),-1))
 end lv
from w order by 2

SQL> /

        ID DATE1            FLD2 FL         WD DATE_WORK  LV
---------- ---------- ---------- -- ---------- ---------- --
         1 10.01.2020         10 11          1 10.01.2020
         2 11.01.2020         15 22          1 11.01.2020
         3 12.01.2020         30 43          1 12.01.2020
         4 01.05.2020         10 17          1 01.05.2020
         5 21.05.2020         15 27          1 21.05.2020
         6 30.05.2020         30 07          1 30.05.2020 43
         7 13.08.2020         10 14          0
         8 17.08.2020         15 24          1 17.08.2020
         9 29.08.2020         30 02          1 29.08.2020 07
        10 03.09.2020         10 14          1 03.09.2020
        11 27.09.2020         15 24          1 27.09.2020
        12 29.09.2020         30 04          0
        10 07.10.2020         10 12          1 07.10.2020 02
        11 27.10.2020         15 22          1 27.10.2020
        12 31.10.2020         30 42          0
        10 01.11.2020         10 15          0
        11 02.11.2020         15 25          1 02.11.2020 04
        12 20.11.2020         30 05          1 20.11.2020 42


select max(l.fld3) KEEP можно заменить на аналитику last_value
w жалко было выбрасывать
или реализовать через джоины

вариантов много
напр посчитать и для выходного перенести на первое

первые/последнии дни искать по другому
и тд

так-как выборка не должна быть громадной макс 367*к-во лет
ничего не оптимизировал, делал влоб для старых версий

ps
допускаю что что-то не допонял
или понял но неправильно реализовал

.....
stax
27 ноя 20, 12:25    [22239268]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2798
merch
а что, на этом форуме больше не посылают в ветку "помощь студентам" и т.д?


к сожленью я уже не студент, хотя я только учусь

.....
stax
27 ноя 20, 12:27    [22239270]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
ARRay001
Member

Откуда:
Сообщений: 50
Stax,
супер, спасибо! Осталось мне только полностью осознать решение для того, чтобы перенести и поддерживать.
27 ноя 20, 12:39    [22239281]     Ответить | Цитировать Сообщить модератору
 Re: Использование оконных (аналитических) функций  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 2798
ARRay001,

повторюсь
делал влоб
вариантов много, наверняка можно улучшить

......
stax
27 ноя 20, 12:42    [22239285]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: 1 2 3 4      [все]
Все форумы / Oracle Ответить