PostgreSQL如何用窗口函数统计间隔<1分钟且连续≥3次的支付数据

可以通过窗口函数实现该需求，这类场景属于典型的时序数据孤岛统计问题，具体方案如下：

1. 按卖家分组，对每笔支付按时间排序，计算当前支付与该卖家上一笔支付的时间差
2. 当时间差≥60秒时，生成新的分组标记，将连续间隔小于60秒的支付划分到同一个分组
3. 筛选分组内支付笔数≥3的分组，统计符合要求的卖家数量与总支付笔数即可

WITH pay_diff AS (
    -- 计算每笔支付与同卖家上一笔支付的间隔（秒）
    SELECT 
        *,
        EXTRACT(EPOCH FROM payment_time - LAG(payment_time) OVER(PARTITION BY seller_id ORDER BY payment_time)) AS prev_second_diff
    FROM T
),
pay_grp AS (
    -- 生成连续支付分组标记
    SELECT 
        *,
        SUM(CASE WHEN prev_second_diff >= 60 OR prev_second_diff IS NULL THEN 1 ELSE 0 END) 
        OVER(PARTITION BY seller_id ORDER BY payment_time) AS continuous_grp_id
    FROM pay_diff
),
qualified_grp AS (
    -- 筛选连续支付笔数≥3的分组
    SELECT 
        seller_id,
        continuous_grp_id,
        COUNT(*) AS grp_pay_count
    FROM pay_grp
    GROUP BY seller_id, continuous_grp_id
    HAVING COUNT(*) >= 3
)
-- 统计最终结果
SELECT 
    COUNT(DISTINCT seller_id) AS 符合条件的卖家数量,
    SUM(grp_pay_count) AS 符合条件的总支付笔数
FROM qualified_grp;

运行上述SQL后，得到的结果为符合条件的卖家数量=2，符合条件的总支付笔数=10，与预期完全一致。

内容的提问来源于stack exchange，提问作者bwoah