Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Как правильно сгруппировать набор данных? / 16 сообщений из 16, страница 1 из 1
25.12.2019, 20:12
    #39908053
Alibek B
Alibek B
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как правильно сгруппировать набор данных?
Есть набор данных, в которых есть столбец MOMENT (дата и время события) и столбец FN (некое число).
Мне нужно сгруппировать этот набор и для каждой группы определить минимальную и максимальную дату.
Группой является последовательность строк с нарастающим MOMENT и одинаковым FN.
Например тут три группы:
idmomentfn12019-01-011022019-01-021032019-01-032042019-01-042052019-01-0510
И соответственно мне нужно получить такой результат:
fnminmax102019-01-012019-01-02202019-01-032019-01-04102019-01-052019-01-05

Для начала я нумерую строки: row_number() over(order by moment)
А как затем пронумеровать или выделить группы?
Ведь если просто использовать dense_rank, то для повторяющихся значений FN он даст повторяющиеся значения ранга.
А если использовать lead, то я не знаю, как задать в условиях, что мне нужна запись не только со следующим номером, но и с таким же FN.

Пока что у меня получилось такое:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
with FD as
(
  select ...
  , row_number() over(order by MOMENT) RN
  from ...
)
select FD.*
, lead(RN) over (partition by FN order by RN)
from FD


Но оно правильно работает только в том случае, если FN в разных группах не повторяются.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.12.2019, 20:16
    #39908055
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как правильно сгруппировать набор данных?
STFF start_of_group
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.12.2019, 20:20
    #39908056
ora_code
ora_code
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как правильно сгруппировать набор данных?
как то так
Код: plsql
1.
2.
3.
4.
-- 1 разрыв последовательности, начало новой группы
case when lead(moment) over (partition by FN order by moment) between moment and moment + 1 then 0 else 1 end as flag 
...
sum(flag) over (partition by FN order by moment rows between unbounded preceding and current row) as grp
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.12.2019, 20:27
    #39908058
Alibek B
Alibek B
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как правильно сгруппировать набор данных?
Попробовал так:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
with FD as
(
  select ...
  , dense_rank() over (order by FN) G
  from ...
)
select G, FN as FN, min(MOMENT) as MIN, max(MOMENT) as MAX, count(*) as CNT
from FD
group by G, FN



Но видимо что-то неправильно.
У меня должно быть три группы, а запрос возвращает две группы.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.12.2019, 20:36
    #39908063
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как правильно сгруппировать набор данных?
Alibek B.
Но видимо что-то неправильно.
Ага. Чукча не читатель.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.12.2019, 21:09
    #39908079
AmKad
AmKad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как правильно сгруппировать набор данных?
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
with s (id, moment, fn) as (
select 1, date '2019-01-01', 10 from dual union all
select 2, date '2019-01-02', 10 from dual union all
select 3, date '2019-01-03', 20 from dual union all
select 4, date '2019-01-04', 20 from dual union all
select 5, date '2019-01-05', 10 from dual
)
select *
from s
match_recognize (
order by id
measures v.fn as fn, first(v.moment) as dt1, last(v.moment) as dt2
pattern (v+)
define v as v.fn = first(v.fn)
);

        FN DT1                 DT2
---------- ------------------- -------------------
        10 2019-01-01 00:00:00 2019-01-02 00:00:00
        20 2019-01-03 00:00:00 2019-01-04 00:00:00
        10 2019-01-05 00:00:00 2019-01-05 00:00:00
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.12.2019, 21:52
    #39908103
Alibek B
Alibek B
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как правильно сгруппировать набор данных?
Не осилил я аналитику.
Тем более, что в примерах с start_of_group все те же многоэтажные вложенные подзапросы.
Поэтому сделал по старинке:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
with RS as
(
  select ID, M, FN
  , row_number() over(order by TIMESTAMP) R
  from CYBERCOM.FISCAL_DATA
  where STATUS = 1
)
, T as
(
  select RS.*
  , decode(PS.ID,null,1,null) as S
  from RS
  left join RS PS on (PS.R = RS.R-1 and PS.FN = RS.FN)
  order by RS.R
)
, G as
(
  select *
  from T
  model
  dimension by (R)
  measures (ID, M, FN, S, 0 G)
  rules ( G[R] = nvl(G[CV(R)-1],0) + nvl(S[CV()],0) )
)
select G, FN
, count(*) as CNT
, min(ID) as "MIN#"
, max(ID) as "MAX#"
, min(M) as "MIN"
, max(M) as "MAX"
from G
group by G, FN
order by 1, 2
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.12.2019, 22:03
    #39908109
Alibek B
Alibek B
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как правильно сгруппировать набор данных?
AmKad
match_recognize

Впечатляет.
Правда мне использовать это не получится из-за версии, но выглядит намного красивее моего способа.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.12.2019, 23:09
    #39908157
AmKad
AmKad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как правильно сгруппировать набор данных?
Alibek B.
Не осилил я аналитику.
Тем более, что в примерах с start_of_group все те же многоэтажные вложенные подзапросы.
Поэтому сделал по старинке:
Так у тебя ж еще сложнее получилось: и row_number, и self-join, и model, да и еще group by поверх всего этого в одном запросе. Из всего, что можно, по воробьям пострелял.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.12.2019, 17:53
    #39908601
andrey_anonymous
andrey_anonymous
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как правильно сгруппировать набор данных?
Alibek B.
Не осилил я аналитику.
Поэтому сделал по старинке:
Код: plsql
1.
2.
3.
4.
  model
  dimension by (R)
  measures (ID, M, FN, S, 0 G)
  rules ( G[R] = nvl(G[CV(R)-1],0) + nvl(S[CV()],0) )


"По старинке", блин

По старинке это так:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
with t(id, moment, fn) as( select 1, date'2019-01-01', 10
       from dual union all select 2, date'2019-01-02', 10
       from dual union all select 3, date'2019-01-03', 20
       from dual union all select 4, date'2019-01-04', 20
       from dual union all select 5, date'2019-01-05', 10
       from dual 
       )
, t_sog as (select t.*
                 , case lag(fn) over(order by moment) when fn then null else 1 end sog -- маркируем начало группы
            from t)
, t_gid as ( select fn, moment, sum(sog) over(order by moment) gid -- назначаем группам идентификаторы
               from t_sog) 
select fn, min(moment), max(moment) 
  from t_gid 
 group by gid, fn
 order by 2



моделькой - можно вот так, хотя я бы поискал способ поинтереснее:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
with t(id, moment, fn) as( select 1, date'2019-01-01', 10
       from dual union all select 2, date'2019-01-02', 10
       from dual union all select 3, date'2019-01-03', 20
       from dual union all select 4, date'2019-01-04', 20
       from dual union all select 5, date'2019-01-05', 10
       from dual 
       )
, t_m as(
    select * 
      from t
     model ignore nav
     dimension by (row_number() over(order by moment) r)
     measures (moment d_from, moment d_till, fn, 0 flag)
     rules(flag[any] = case fn[cv()+1] when fn[cv()] then null else 1 end
     , d_from[any] = case fn[cv()-1] when fn[cv()] then d_from[cv()-1] else d_from[cv()] end
     )
)
select fn, d_from, d_till
  from t_m
 where flag = 1     
order by 2
;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.12.2019, 18:51
    #39908644
AmKad
AmKad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как правильно сгруппировать набор данных?
Решил поиграться с аналитикой для составления оптимального плана без фактических замеров производительности. Если выкинуть результирующий order by, который призван гарантировать сортировку итогового результата, то у меня получилась пара window sort + window nosort:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
with t(id, moment, fn) as( select 1, date'2019-01-01', 10
       from dual union all select 2, date'2019-01-02', 10
       from dual union all select 3, date'2019-01-03', 20
       from dual union all select 4, date'2019-01-04', 20
       from dual union all select 5, date'2019-01-05', 10
       from dual
       )
select fn, lv dt1, moment dt2
from
   (select t.*,
    last_value(decode(start_of_group, 1, moment) ignore nulls) over (order by moment rows unbounded preceding) lv
    from
       (select fn, id, moment,
        decode(fn, lag (fn) over (order by moment), null, 1) start_of_group,
        decode(fn, lead(fn) over (order by moment), null, 1) end_of_group
        from t
       ) t
   ) t
where end_of_group = 1;

        FN DT1                 DT2
---------- ------------------- -------------------
        10 2019-01-01 00:00:00 2019-01-02 00:00:00
        20 2019-01-03 00:00:00 2019-01-04 00:00:00
        10 2019-01-05 00:00:00 2019-01-05 00:00:00

Elapsed: 00:00:00.06

Execution Plan
----------------------------------------------------------
Plan hash value: 3164257219

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     5 |   115 |    11  (10)| 00:00:01 |
|*  1 |  VIEW            |      |     5 |   115 |    11  (10)| 00:00:01 |
|   2 |   WINDOW NOSORT  |      |     5 |    80 |    11  (10)| 00:00:01 |
|   3 |    VIEW          |      |     5 |    80 |    11  (10)| 00:00:01 |
|   4 |     WINDOW SORT  |      |     5 |    60 |    11  (10)| 00:00:01 |
|   5 |      VIEW        |      |     5 |    60 |    10   (0)| 00:00:01 |
|   6 |       UNION-ALL  |      |       |       |            |          |
|   7 |        FAST DUAL |      |     1 |       |     2   (0)| 00:00:01 |
|   8 |        FAST DUAL |      |     1 |       |     2   (0)| 00:00:01 |
|   9 |        FAST DUAL |      |     1 |       |     2   (0)| 00:00:01 |
|  10 |        FAST DUAL |      |     1 |       |     2   (0)| 00:00:01 |
|  11 |        FAST DUAL |      |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_NUMBER("END_OF_GROUP")=1)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        552  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          3  rows processed
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.12.2019, 18:55
    #39908648
AmKad
AmKad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как правильно сгруппировать набор данных?
Уберем неявное преобразование:
AmKad
Код: plsql
1.
2.
        decode(fn, lag (fn) over (order by moment), /*null*/2, 1) start_of_group,
        decode(fn, lead(fn) over (order by moment), /*null*/2, 1) end_of_group

...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.12.2019, 23:18
    #39908766
Alibek B
Alibek B
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как правильно сгруппировать набор данных?
andrey_anonymous
По старинке это так:

Это в примере у меня даты целочисленные и с интервалом в одни сутки.
В рабочих данных это дата/время (несколько сотен строк в сутки), поэтому lag(fn) over(order by moment) работать не будет, нужна именно нумерация строк. То есть плюс один подзапрос.

А вот это, честно говоря, не понял:
andrey_anonymous
select fn, moment, sum(sog) over(order by moment) gid -- назначаем группам идентификаторы

Как это работает?
Разве тут не нужен partition?

andrey_anonymous
моделькой - можно вот так, хотя я бы поискал способ поинтереснее:

Можно пояснить второе правило?
d_from ведь будет копироваться с предыдущей строки, а должен с начала группы.
Или я что-то пропустил?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.12.2019, 07:54
    #39908818
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как правильно сгруппировать набор данных?
Alibek B.
Или я что-то пропустил?
Начни быть читателем, а не писателем. Перестань считать себя слишком.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.12.2019, 14:59
    #39909086
andrey_anonymous
andrey_anonymous
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как правильно сгруппировать набор данных?
Alibek B.
поэтому lag(fn) over(order by moment) работать не будет

Вы ведь проверили это утверждение перед тем, как опубликовали, верно?

Alibek B.

andrey_anonymous
select fn, moment, sum(sog) over(order by moment) gid -- назначаем группам идентификаторы

Как это работает?

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

Alibek B.

andrey_anonymous
моделькой - можно вот так, хотя я бы поискал способ поинтереснее:

d_from ведь будет копироваться с предыдущей строки, а должен с начала группы.
Или я что-то пропустил?

Будет копироваться.
Для каждой строки с предыдущей.
При этом моделька учтет зависимость порядка исполнения правила, что приведет к копированию первого в группе значения через все промежуточные в последнее.

Общее замечание: когда я не понимаю "с ходу", как именно работает тот или иной финт ушами, я обычно исследую запрос по частям, рассматривая выборки от базового источника данных до последней группировки.
Это позволяет, с одной стороны, лучше понять коллег, с другой - существенно реже вызывать у них гомерический хохот.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.12.2019, 15:51
    #39909151
Alibek B
Alibek B
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как правильно сгруппировать набор данных?
andrey_anonymous
Вы ведь проверили это утверждение перед тем, как опубликовали, верно?

Действительно. Документацию LAG я читал изначально, видел что если offset не задан, то используется значение 1, но ошибочно решил, что offset задает разницу значений. Сейчас перечитал еще раз, offset задает позицию строки.
В любом случае спасибо за пояснения.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Как правильно сгруппировать набор данных? / 16 сообщений из 16, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 421ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1881706]:
 0ms
get settings:
 10ms
get forum list:
 15ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 42ms
get topic data:
 13ms
get forum data:
 3ms
get page messages:
 58ms
get tp. blocked users:
 2ms
others: 270ms
total:  421ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]