Postgresql 如何计算每100条订阅记录批次之间的日期间隔

假设你的订阅数据表名为subscriptions，如果实际表名不同请替换查询语句中的对应部分即可。

分两列输出（标准结构化结果） #

WITH numbered_subs AS (
    SELECT
        date,
        ROW_NUMBER() OVER (ORDER BY date ASC) AS rn
    FROM subscriptions
),
batched_subs AS (
    SELECT
        rn,
        date,
        (rn - 1) / 100 AS batch_num
    FROM numbered_subs
)
SELECT
    CONCAT(MIN(rn), '-', MAX(rn)) AS "订阅批次范围",
    (MAX(date) - MIN(date))::INTEGER AS "间隔天数"
FROM batched_subs
GROUP BY batch_num
ORDER BY batch_num ASC;

直接拼接为示例要求的字符串格式输出 #

WITH numbered_subs AS (
    SELECT
        date,
        ROW_NUMBER() OVER (ORDER BY date ASC) AS rn
    FROM subscriptions
),
batched_subs AS (
    SELECT
        rn,
        date,
        (rn - 1) / 100 AS batch_num
    FROM numbered_subs
)
SELECT
    CONCAT(MIN(rn), '-', MAX(rn), ' | ', (MAX(date) - MIN(date))::INTEGER, '天') AS "订阅批次范围 | 间隔天数"
FROM batched_subs
GROUP BY batch_num
ORDER BY batch_num ASC;

查询逻辑说明：

1. 首先对所有订阅记录按订阅日期升序排序生成唯一行号
2. 按行号每100条划分为一个批次
3. 统计每个批次的行号范围、批次内最早订阅日期到最晚订阅日期的间隔天数
   如果你的间隔天数需求是当前批次结束日期到下一个批次结束日期的间隔，可以在分组后加LAG窗口函数取上一个批次的最大日期做差即可。

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