折叠窗口间隔

Question

我具有以下数据库：

select * from (
  values ('A', 1, 2), ('A', 2, 3), ('A', 3, 4),
         ('B', 4, 5), ('B', 5, 6), ('A', 6, 7),
         ('C', 7, 8), ('C', 8, 9)
) example_table("state", "start", "end")
-- example table: 
-- state start stop
--     A    t1   t2
--     A    t2   t3
--     A    t3   t4
--     B    t4   t5
--     B    t5   t6
--     A    t6   t7
--     C    t7   t8
--     C    t8   t9

我想通过 state折叠间隔，同时也尊重中间状态开关：

state start stop
    A    t1   t4
    B    t4   t6
    A    t6   t7
    C    t7   t9

仅使用group by state无法使用，因为给定的state的输出中有多个行。

这似乎是窗口函数的用例，但是我不确定该行如何分区。

我想创建一个group_id，就像以下作为中间步骤：

group state start stop
    1     A    t1   t2
    1     A    t2   t3
    1     A    t3   t4
    2     B    t4   t5
    2     B    t5   t6
    3     A    t6   t7
    4     C    t7   t8
    4     C    t8   t9

我可以按group进行分组，然后选择min(start)和max(stop)，但我不知道如何（有效地）创建此变量。在r中，我会使用 rle函数来做到这一点，但我不知道任何presto等效。

Answer 1

此答案已更新以反映以下成功的答案

with example_table("state", "start", "end") as (
  values ('A', 1, 2), ('A', 2, 3), ('A', 3, 4),
         ('B', 4, 5), ('B', 5, 6), ('A', 6, 7),
         ('C', 7, 8), ('C', 8, 9)
), table_with_lags as (
  -- detect state changes by observing the lagged value
  select *, lag(state) over(order by start) as lag_state,
  -- need to track the final value since it may be lost below
  last_value("end") over(order by start rows between 
                         0 preceding and unbounded following)
  as end_period
  from example_table
)
select state, start, 
       -- force-re-establish the start(+1) = end(0) link;
       --   at the end of the period, override this with the
       --   final observed value instead of null
       lead(start, 1, end_period) over(order by start) as "end"
from table_with_lags
-- lag_state will be null for the first row
where state <> lag_state or lag_state is null
order by start

输出：

state start stop
    A     1    4
    B     4    6
    A     6    7
    C     7    9

rows between 0 preceding and unbounded following位有点冗长，因此您也可以翻转逻辑并做：

table_with_leads as (
  select state, start, "end", 
         lead(state) over(order by start) as lead_state,
         first_value(start) over(order by start) as start_period
  from example_table
)
select state, lag("end", 1, start_period) over(order by start) as start, "end"
from table_with_lags
where state <> lead_state or lead_state is null
order by start

---

原始答案

以下工作，但不是大规模的表现（即使在数据的10％子样本上，我也会得到"超过本地内存限制"错误）：

with switches as (
  -- coalesce since the first row will be NULL, need it false
  select *, coalesce(state <> lag(state) over(order by start), false) switched
  from (
    values ('A', 1, 2), ('A', 2, 3), ('A', 3, 4),
           ('B', 4, 5), ('B', 5, 6), ('A', 6, 7),
           ('C', 7, 8), ('C', 8, 9)
  ) example_table("state", "start", "stop")
), groups as (
  -- create the group ID as the accumulation of the state switches
  --   since only one state switch can happen per group
  select *, sum(cast(switched as bigint)) over (order by start) group_id
  from switches
)
select min(state) state, min(start) start, max(stop) stop
from groups group by group_id order by start;
-- state start stop
--     A     1    4
--     B     4    6
--     A     6    7
--     C     7    9

i 目前通过将groups暂时存储为表格，然后从groups分别将select置于表格中，这似乎是在RAM问题上遇到的（对我来说，有点令人惊讶）。这似乎并不理想，但是随着工作完成，我现在很满足于此。