ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Как правильно сгруппировать набор данных?
16 сообщений из 16, страница 1 из 1
Как правильно сгруппировать набор данных?
    #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:12
| Ответить | Цитировать | Написать  
Как правильно сгруппировать набор данных?
    #39908055
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
STFF start_of_group
...
Рейтинг: 0 / 0
25.12.2019, 20:16
| Ответить | Цитировать | Написать  
Как правильно сгруппировать набор данных?
    #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:20
| Ответить | Цитировать | Написать  
Как правильно сгруппировать набор данных?
    #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:27
| Ответить | Цитировать | Написать  
Как правильно сгруппировать набор данных?
    #39908063
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
Alibek B.
Но видимо что-то неправильно.
Ага. Чукча не читатель.
...
Рейтинг: 0 / 0
25.12.2019, 20:36
| Ответить | Цитировать | Написать  
Как правильно сгруппировать набор данных?
    #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:09
| Ответить | Цитировать | Написать  
Как правильно сгруппировать набор данных?
    #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, 21:52
| Ответить | Цитировать | Написать  
Как правильно сгруппировать набор данных?
    #39908109
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
AmKad
match_recognize

Впечатляет.
Правда мне использовать это не получится из-за версии, но выглядит намного красивее моего способа.
...
Рейтинг: 0 / 0
25.12.2019, 22:03
| Ответить | Цитировать | Написать  
Как правильно сгруппировать набор данных?
    #39908157
Фотография AmKad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
AmKad
Участник
Alibek B.
Не осилил я аналитику.
Тем более, что в примерах с start_of_group все те же многоэтажные вложенные подзапросы.
Поэтому сделал по старинке:
Так у тебя ж еще сложнее получилось: и row_number, и self-join, и model, да и еще group by поверх всего этого в одном запросе. Из всего, что можно, по воробьям пострелял.
...
Рейтинг: 0 / 0
25.12.2019, 23:09
| Ответить | Цитировать | Написать  
Как правильно сгруппировать набор данных?
    #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, 17:53
| Ответить | Цитировать | Написать  
Как правильно сгруппировать набор данных?
    #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:51
| Ответить | Цитировать | Написать  
Как правильно сгруппировать набор данных?
    #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, 18:55
| Ответить | Цитировать | Написать  
Как правильно сгруппировать набор данных?
    #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
26.12.2019, 23:18
| Ответить | Цитировать | Написать  
Как правильно сгруппировать набор данных?
    #39908818
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
Alibek B.
Или я что-то пропустил?
Начни быть читателем, а не писателем. Перестань считать себя слишком.
...
Рейтинг: 0 / 0
27.12.2019, 07:54
| Ответить | Цитировать | Написать  
Как правильно сгруппировать набор данных?
    #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, 14:59
| Ответить | Цитировать | Написать  
Как правильно сгруппировать набор данных?
    #39909151
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
andrey_anonymous
Вы ведь проверили это утверждение перед тем, как опубликовали, верно?

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


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