SQLite: временные ступеньки

Автор: 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;

#