google-bigquery - Bigquery - 如何组合连续日期范围内的数据

Question

我想将同一产品的产品促销开始时间和结束时间以及开始时间和结束时间范围内的时间结合起来。例如：ID1 的结束时间和ID2 的开始时间是一个继续时间。ID3的开始时间在ID2的开始和结束时间范围内。因此，ID1 ID2 ID3 应该组合在一起。

ID	产品	开始时间	时间结束
1	一个	2020-10-15	2020-10-20
2	一个	2020-10-21	2020-10-24
3	一个	2020-10-23	2020-10-25
4	一个	2020-10-28	2020-10-29
5	b	2020-10-29	2020-10-31

预期的

ID	产品	开始时间	时间结束	合并计数
1	一个	2020-10-15	2020-10-25	3
4	一个	2020-10-28	2020-10-29	1
5	b	2020-10-29	2020-10-31	1

你有什么建议吗？

标签： google-bigquery

Answer 1

考虑下面

select min(id) id,
    product,
    min(start_time) start_time,
    max(end_time) end_time,
    count(1) Combine_Count
from (
  select *, 
    countif(ifnull(isNew, true)) over win grp
  from (
    select *, 
      start_time > lag(end_time) over win + 1 isNew
    from `project.dataset.table`
    window win as (partition by product order by start_time)
  )
  window win as (partition by product order by start_time)
) t
group by product, grp    

当应用于您问题中的样本数据时 - 输出是