如何使用SQL在一行中获得连续的日期?

Question

我想获得连续日期的最小开始时间和最大结束时间(必须是同一个月)，但所有日期必须是连续的。

我想把它们合并成一行。我怎样才能得到下面的表格?(表2)

Endtime0.115/12/20220.126/12/2022 strong>26/12/20220.127/12/20220.128/12/2022 strong>0.111/01/2023 strong>11/01/20230.112/01/20230.113/01/20230.114/01/20230.115/01/2023 strong>

在第一次传递时使用LAG(endtime)或LEAD(starttime) OVER (PARTITION BY ip_address ORDER BY starttime)，这样每一行都可以看到相邻行的日期(您可以朝任何一个方向进行)。然后使用CASE或DECODE来测试日期是否连续。如果不是，则发出日期，否则发出NULL。然后在父块中，您可以使用带有IGNORE NULLS选项的LAST_VALUE()和在当前行之前的UNBOUNDED之间的ROWS来获取这些有条件计算的日期中的最近日期。然后可以在第三个父查询块的groupby中使用它来最终确定结果。

或者使用经典的match_recognition来合并区间:

select * from data 
match_recognize(
partition by ipaddress
order by starttime, endtime
measures first(starttime) as starttime, max(endtime) as endtime
pattern( merged* strt )
define
merged as endtime = next(starttime) - 1 
);