ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Группировка значений по временным интервалам
3 сообщений из 3, страница 1 из 1
Группировка значений по временным интервалам
    #39850068
urdada
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
urdada
Гость
Просьба помочь решить следующую задачу.

Есть таблицы, в которых хранится история изменения атрибутов сущности (в каждой - значение атрибута / дата начала действия значения / дата окончания действия значения). Хотелось бы при помощи SQL вывести список всех существовавших комбинаций значений атрибутов и периоды действия этих комбинаций.
Например:

Таблица значений атрибута 1 (для сущностей с id 1 и 2):
main_idattrstart_dateend_date1A02.01.201910.01.20191A11.01.201912.01.20191B13.01.201915.01.20191A15.01.201920.01.20192A02.01.201905.01.20192A05.01.201910.01.2019

Таблица значений атрибута 2 (для сущностей с id 1 и 2):
main_idattrstart_dateend_date1a05.01.201907.01.20191c08.01.201920.01.20192b02.01.201910.01.2019

Нужно получить список периодов, в которых действовали различные комбинации атрибутов для каждой сущности, если в виде кода, то нужно переписать запрос
Код: plsql
1.
SELECT * FROM tbl1 JOIN tbl2 ON tbl1.main_id=tbl2.main_id

в этом коде:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
WITH tbl1 AS (
select 1 as main_id, 'A' as attr, to_date('02.01.2019', 'dd.mm.yyyy') as start_date, to_date('10.01.2019', 'dd.mm.yyyy') as end_date from dual union all 
select 1 as main_id, 'A' as attr, to_date('11.01.2019', 'dd.mm.yyyy') as start_date, to_date('12.01.2019', 'dd.mm.yyyy') as end_date from dual union all 
select 1 as main_id, 'B' as attr, to_date('13.01.2019', 'dd.mm.yyyy') as start_date, to_date('15.01.2019', 'dd.mm.yyyy') as end_date from dual union all 
select 1 as main_id, 'A' as attr, to_date('15.01.2019', 'dd.mm.yyyy') as start_date, to_date('20.01.2019', 'dd.mm.yyyy') as end_date from dual union all 
select 2 as main_id, 'A' as attr, to_date('02.01.2019', 'dd.mm.yyyy') as start_date, to_date('05.01.2019', 'dd.mm.yyyy') as end_date from dual union all 
select 2 as main_id, 'A' as attr, to_date('05.01.2019', 'dd.mm.yyyy') as start_date, to_date('10.01.2019', 'dd.mm.yyyy') as end_date from dual
),
tbl2 AS (
select 1 as main_id, 'a' as attr, to_date('05.01.2019', 'dd.mm.yyyy') as start_date, to_date('07.01.2019', 'dd.mm.yyyy') as end_date from dual union all 
select 1 as main_id, 'c' as attr, to_date('08.01.2019', 'dd.mm.yyyy') as start_date, to_date('20.01.2019', 'dd.mm.yyyy') as end_date from dual union all 
select 2 as main_id, 'b' as attr, to_date('02.01.2019', 'dd.mm.yyyy') as start_date, to_date('10.01.2019', 'dd.mm.yyyy') as end_date from dual
)
SELECT * FROM tbl1 JOIN tbl2 ON tbl1.main_id=tbl2.main_id


чтобы в результате получить таблицу такого вида:

main_idtbl1.attrtbl2.attrstart_dateend_date1Anull02.01.201905.01.20191Aa05.01.201907.01.20191Anull07.01.201908.01.20191Ac08.01.201910.01.20191nullc10.01.201911.01.20191Ac11.01.201912.01.20191nullc12.01.201913.01.20191Bc13.01.201915.01.20191Ac15.01.201920.01.20192Ab02.01.201910.01.2019
...
Рейтинг: 0 / 0
15.08.2019, 22:16
| Ответить | Цитировать | Написать  
Группировка значений по временным интервалам
    #39850097
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
urdada,

 
Код: 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.
WITH tbl1 AS (
select 1 as main_id, 'A' as attr, to_date('02.01.2019', 'dd.mm.yyyy') as start_date, to_date('10.01.2019', 'dd.mm.yyyy') as end_date from dual union all 
select 1 as main_id, 'A' as attr, to_date('11.01.2019', 'dd.mm.yyyy') as start_date, to_date('12.01.2019', 'dd.mm.yyyy') as end_date from dual union all 
select 1 as main_id, 'B' as attr, to_date('13.01.2019', 'dd.mm.yyyy') as start_date, to_date('15.01.2019', 'dd.mm.yyyy') as end_date from dual union all 
select 1 as main_id, 'A' as attr, to_date('15.01.2019', 'dd.mm.yyyy') as start_date, to_date('20.01.2019', 'dd.mm.yyyy') as end_date from dual union all 
select 2 as main_id, 'A' as attr, to_date('02.01.2019', 'dd.mm.yyyy') as start_date, to_date('05.01.2019', 'dd.mm.yyyy') as end_date from dual union all 
select 2 as main_id, 'A' as attr, to_date('05.01.2019', 'dd.mm.yyyy') as start_date, to_date('10.01.2019', 'dd.mm.yyyy') as end_date from dual
),
tbl2 AS (
select 1 as main_id, 'a' as attr, to_date('05.01.2019', 'dd.mm.yyyy') as start_date, to_date('07.01.2019', 'dd.mm.yyyy') as end_date from dual union all 
select 1 as main_id, 'c' as attr, to_date('08.01.2019', 'dd.mm.yyyy') as start_date, to_date('20.01.2019', 'dd.mm.yyyy') as end_date from dual union all 
select 2 as main_id, 'b' as attr, to_date('02.01.2019', 'dd.mm.yyyy') as start_date, to_date('10.01.2019', 'dd.mm.yyyy') as end_date from dual
)
,dates as ( -- получаем все интервалы:
   select lag(dt)over(order by dt) as start_date, dt as end_date
   from (-- получаем все точки:
      select distinct dt
      from (select start_date,end_date from tbl1
            union all
            select start_date,end_date from tbl2
           )
      unpivot (
         dt for tp in (start_date,end_date)
      )
   )
)
,split_data as ( -- выводим данные по каждому интервалу:
   select dt.* 
        , nvl(tbl1.main_id, tbl2.main_id) main_id
        , tbl1.attr as attr1
        , tbl2.attr as attr2
   from dates dt
        left join tbl1 
             on  dt.start_date< tbl1.end_date
             and dt.end_date  > tbl1.start_date
        left join tbl2
             on  (tbl1.main_id is null or tbl1.main_id=tbl2.main_id)
             and dt.start_date< tbl2.end_date
             and dt.end_date  > tbl2.start_date
   where dt.start_date is not null
   order by 3,1
)
-- группируем последовательные интервалы, где аттрибуты не меняются:
select *
from split_data
match_recognize (
   order by main_id,start_date
   MEASURES  STRT.main_id    as main_id
            ,STRT.start_date as start_date
            ,LAST(end_date)  as end_date
            ,STRT.attr1      as attr1  
            ,STRT.attr2      as attr2  
   PATTERN (STRT P*)
     DEFINE
         P AS  P.main_id    = prev(P.main_id)
           and P.start_date = prev(P.end_date)
           and P.attr1      = prev(P.attr1)
           and P.attr2      = prev(P.attr2)
   )
/

...
Рейтинг: 0 / 0
16.08.2019, 02:13
| Ответить | Цитировать | Написать  
Группировка значений по временным интервалам
    #39850101
urdada
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
urdada
Гость
xtender, снимаю шляпу!

это решение даже поизящнее того, до которого я додумался, хотя в общих чертах похоже
 
Код: 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.
WITH tbl1 AS (
select 1 as main_id, 'A' as attr, to_date('02.01.2019', 'dd.mm.yyyy') as start_date, to_date('10.01.2019', 'dd.mm.yyyy') as end_date from dual union all 
select 1 as main_id, 'A' as attr, to_date('11.01.2019', 'dd.mm.yyyy') as start_date, to_date('12.01.2019', 'dd.mm.yyyy') as end_date from dual union all 
select 1 as main_id, 'B' as attr, to_date('13.01.2019', 'dd.mm.yyyy') as start_date, to_date('15.01.2019', 'dd.mm.yyyy') as end_date from dual union all 
select 1 as main_id, 'A' as attr, to_date('15.01.2019', 'dd.mm.yyyy') as start_date, to_date('20.01.2019', 'dd.mm.yyyy') as end_date from dual union all 
select 2 as main_id, 'A' as attr, to_date('02.01.2019', 'dd.mm.yyyy') as start_date, to_date('05.01.2019', 'dd.mm.yyyy') as end_date from dual union all 
select 2 as main_id, 'A' as attr, to_date('05.01.2019', 'dd.mm.yyyy') as start_date, to_date('10.01.2019', 'dd.mm.yyyy') as end_date from dual
),
tbl2 AS (
select 1 as main_id, 'a' as attr, to_date('05.01.2019', 'dd.mm.yyyy') as start_date, to_date('07.01.2019', 'dd.mm.yyyy') as end_date from dual union all 
select 1 as main_id, 'c' as attr, to_date('08.01.2019', 'dd.mm.yyyy') as start_date, to_date('20.01.2019', 'dd.mm.yyyy') as end_date from dual union all 
select 2 as main_id, 'b' as attr, to_date('02.01.2019', 'dd.mm.yyyy') as start_date, to_date('10.01.2019', 'dd.mm.yyyy') as end_date from dual
),
-- выпускайте кракена
period_dates AS ( -- все моменты периода
SELECT  period_date, main_id FROM
(SELECT start_date period_date, tbl1.main_id AS main_id  FROM tbl1 UNION SELECT end_date, tbl1.main_id FROM tbl1 UNION
SELECT start_date, tbl2.main_id FROM tbl2 UNION SELECT end_date, tbl2.main_id FROM tbl2) ORDER BY 1
),
periods AS ( -- значения атрибутов на момтенты
SELECT  p.period_date, LEAD(p.period_date) OVER (ORDER BY p.main_id, p.period_date) AS next_date,
p.main_id,
 NVL(tbl1.main_id,tbl2.main_id) AS tbl_main_id,
 tbl1.attr tbl1_attr,  tbl2.attr tbl2_attr,
 tbl1.start_date tbl1_start_date, tbl1.end_date tbl1_end_date,
 tbl2.start_date tbl2_start_date, tbl2.end_date tbl2_end_date
 FROM period_dates p
LEFT JOIN tbl1 ON  p.period_date BETWEEN tbl1.start_date AND tbl1.end_date-1/24/60/60 AND tbl1.main_id=p.main_id
LEFT JOIN tbl2 ON  p.period_date BETWEEN tbl2.start_date AND tbl2.end_date-1/24/60/60 AND tbl2.main_id=p.main_id AND
(
  tbl1.main_id=tbl2.main_id OR 
  (tbl1.main_id IS NULL OR tbl2.main_id IS NULL)
)
),
sverni AS ( -- еще нужно свернуть идущие подряд периоды
SELECT main_id, tbl1_attr, tbl2_attr, period_date, next_date FROM periods
WHERE tbl_main_id IS NOT NULL 
)
-------------------
SELECT main_id, tbl1_attr, tbl2_attr, 
 MIN(connect_by_root(t.period_date)) start_date, t.next_date end_date
  FROM sverni t
 WHERE connect_by_isleaf = 1
CONNECT BY (t.period_date = PRIOR t.next_date AND t.tbl1_attr = PRIOR tbl1_attr AND 
           t.tbl2_attr = PRIOR tbl2_attr AND t.main_id = PRIOR main_id)
 GROUP BY t.period_date,  t.next_date,  main_id, tbl1_attr, tbl2_attr
 ORDER BY main_id,  t.period_date



Премного благодарен )
...
Рейтинг: 0 / 0
16.08.2019, 03:04
| Ответить | Цитировать | Написать  
3 сообщений из 3, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Группировка значений по временным интервалам
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&fpage=69&tid=1882188]:
 0ms
get settings:
 11ms
get forum list:
 14ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 29ms
get topic data:
 13ms
get forum data:
 3ms
get page messages:
 56ms
get tp. blocked users:
 2ms
others: 15ms
total:  151ms
созд. / загр.: 151ms
x
x
Закрыть


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