Автор: Artix (user.su) Задача: есть объекты, которые в разные интервалы времени могут иметь разную величину Нужно построить график-ступеньку, отображающую в какие интервалы величина объекта возрастает, а в какие - опускается до 0. Подход к решению: нужно составить "контрольные точки" - последовательную карту интервалов для каждого объекта Затем, двигаясь по карте интервалов, применять подход "скользящего множества", ограничивая интервал суммирования границами интервала Пример1: интервалы (1,12) и (4,10) образуют 3 шкалы (1,4) (4,10) (10,12) Пример2: интервалы (1,5) (6,12) тоже образуют 3 шкалы (1,5) (5,6) (6,12) Пример3: интервалы (1,10) (2,5) (6,12) образуют (1,2) (2,5) (5,6) (6,10) (10,12) Подготовка: drop table if exists prt; create table prt ( id integer primary key, pstart integer, pstop integer, obj integer, val integer ); insert into prt (pstart, pstop, obj, val) values (1,12,1,25); insert into prt (pstart, pstop, obj, val) values (4,10,1,25); insert into prt (pstart, pstop, obj, val) values (1,10,2,25); insert into prt (pstart, pstop, obj, val) values (2,5,2,25); insert into prt (pstart, pstop, obj, val) values (6,12,2,25); insert into prt (pstart, pstop, obj, val) values (1,5,4,25); insert into prt (pstart, pstop, obj, val) values (6,12,4,25); insert into prt (pstart, pstop, obj, val) values (1,12,5,25); insert into prt (pstart, pstop, obj, val) values (8,12,5,25); .mode column .headers on select * from prt order by obj, pstart; Решение 1 - "классическое" with T as ( select A.obj, A.pstart as dt from prt A union all select B.obj, B.pstop from prt B group by 1,2 ), Q as ( select T1.obj, T1.dt as pstart, T2.dt as pstop from T T1 inner join T T2 on T1.obj=T2.obj and T1.dt<T2.dt and (not exists( select * from T X WHERE X.obj=T1.obj and (X.dt>T1.dt and X.dt<T2.dt) )) ) select obj, pstart, pstop, (select coalesce(sum(val),0) from prt where prt.obj=Q.obj and ( (Q.pstart between prt.pstart and prt.pstop) and (Q.pstop between prt.pstart and prt.pstop) )) as S from Q group by 1,2,3 order by obj, pstart; Решение 2 - с нумератором строк with T as ( select obj, dt, row_number() over (order by obj, dt) as rn from ( select A.obj, A.pstart as dt from prt A union all select B.obj, B.pstop from prt B group by 1,2 ) order by 1,2 ) select T1.obj, T1.dt as pstart, T2.dt as pstop, (select coalesce(sum(val),0) from prt where prt.obj=T1.obj and ( (T1.dt between prt.pstart and prt.pstop) and (T2.dt between prt.pstart and prt.pstop) )) as S from T T1 inner join T T2 on T1.obj=T2.obj and T1.rn=T2.rn-1 order by 1,2; Решение 3 - с оконной функцией with T as ( select obj, dt, lead(dt,1) over (order by obj, dt) as nt from ( select A.obj, A.pstart as dt from prt A union all select B.obj, B.pstop from prt B group by 1,2 ) order by obj, dt ) select T.obj, T.dt as pstart, T.nt as pstop, (select coalesce(sum(val),0) from prt where prt.obj=T.obj and ( (T.dt between prt.pstart and prt.pstop) and (T.dt between prt.pstart and prt.pstop) )) as S from T where dt<nt; #
SQL Справочник v0.05 © 2007-2025 Igor Salnikov aka SunDoctor