Postgres：对于每一行，在条件下评估所有连续的行

作者: 你瞎啊！撞我心了
发布时间: 2024-04-07 06:20:06 (17天前)
转自：

2 条回复

0#
回复此人
蜡笔小辛 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <P> 我会使用递归查询： </p> <pre class="lang-sql prettyprint-override"> <code> WITH RECURSIVE tmp AS ( SELECT id, datetime, row_number, 0 AS counting, datetime AS last_start FROM mytable WHERE row_number = 1 UNION ALL SELECT t1.id, t1.datetime, t1.row_number, CASE WHEN lateral_1.counting THEN 1 ELSE 0 END AS counting, CASE WHEN lateral_1.counting THEN tmp.last_start ELSE t1.datetime END AS last_start FROM mytable AS t1 INNER JOIN tmp ON (t1.id = tmp.id AND t1.row_number - 1 = tmp.row_number), LATERAL (SELECT (t1.datetime - tmp.last_start) < '1h 30m'::interval AS counting) AS lateral_1 ) SELECT id, datetime, counting FROM tmp ORDER BY id, datetime; </code> </pre> </DIV>

编辑

登录后才能参与评论