求PostgreSQL中指定日期的达标连续天数SQL实现

需求说明 #

给定目标日期，计算从该日期向前连续的天数，要求这些日期的number字段值≥指定阈值（示例阈值为1000），核心规则：

• 目标日期不在数据中或number未达标，返回0
• 仅统计从目标日期开始，往前连续存在且number达标的天数，出现断档（日期不连续或number不达标）即停止统计

示例验证 #

实现方案 #

假设两组数据分别存储在data_set1和data_set2表中，以下提供两种实现方式：

1. 单次查询版本（直接替换目标日期） #

WITH combined_data AS (
    SELECT date, number FROM data_set1
    UNION ALL
    SELECT date, number FROM data_set2
),
ranked_days AS (
    SELECT
        date,
        -- 为连续达标日期生成唯一分组ID
        date - INTERVAL '1 day' * ROW_NUMBER() OVER (ORDER BY date DESC) AS group_key
    FROM combined_data
    WHERE number >= 1000 -- 替换为实际阈值
),
target_group AS (
    SELECT group_key FROM ranked_days WHERE date = '2024-11-15' -- 替换为目标日期
)
SELECT COALESCE(COUNT(*), 0) AS consecutive_days
FROM ranked_days
WHERE group_key = (SELECT group_key FROM target_group)
AND date <= '2024-11-15'; -- 替换为目标日期

2. 通用函数版本（支持动态传参） #

创建可复用的函数，方便多次调用：

CREATE OR REPLACE FUNCTION calculate_consecutive_days(p_target_date DATE, p_threshold INT)
RETURNS INT AS $$
WITH combined_data AS (
    SELECT date, number FROM data_set1
    UNION ALL
    SELECT date, number FROM data_set2
),
ranked_days AS (
    SELECT
        date,
        date - INTERVAL '1 day' * ROW_NUMBER() OVER (ORDER BY date DESC) AS group_key
    FROM combined_data
    WHERE number >= p_threshold
),
target_group AS (
    SELECT group_key FROM ranked_days WHERE date = p_target_date
)
SELECT COALESCE(COUNT(*), 0)
FROM ranked_days
WHERE group_key = (SELECT group_key FROM target_group)
AND date <= p_target_date;
$$ LANGUAGE sql;

-- 调用示例
SELECT calculate_consecutive_days('2024-11-15', 1000); -- 输出2
SELECT calculate_consecutive_days('2024-12-14', 1000); -- 输出2
SELECT calculate_consecutive_days('2024-11-08', 1000); -- 输出0

逻辑说明 #

1. 合并数据集：通过UNION ALL整合两组原始数据
2. 分组连续日期：利用ROW_NUMBER()倒序排序达标日期，计算group_key——连续的达标日期会拥有相同的group_key（日期逐天减1，行号逐天加1，差值固定）
3. 定位目标分组：找到目标日期所在的达标分组
4. 统计连续天数：统计该分组中所有≤目标日期的记录数，即为连续达标天数；若目标日期不存在或未达标，COALESCE确保返回0

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