查询指定日期及之前5天的有效数据（跳过无数据日期）

问题背景 #

我有一张存储每日数据的表my_table，表结构及数据初始化语句如下：

create table my_table(visitors_count int, dt date);

select setseed(.42);

insert into my_table 
select (random()*100)::int, 
       current_date+(-random()*14)::int
from generate_series(1,42000);

部分日期因假期等原因无数据，例如已删除2024-03-08的数据：

delete from my_table where dt = '2024-03-08';

需求：编写SQL查询，先检查指定初始日期是否存在数据；若存在，则获取该日期及之前的共5个有效数据日期（跳过无数据日期，中间存在无数据日期时向前补充）。

示例：

• 若指定日期为2024-03-12且该日期有数据，正常获取2024-03-08至2024-03-12（共5个有效日期）；
• 若2024-03-08无数据，则获取2024-03-07、2024-03-09至2024-03-12（共5个有效日期，向前补充缺失的日期对应的有效数据）。

解决方案 #

我们可以通过CTE（公共表表达式）结合LIMIT或窗口函数实现需求，核心逻辑是先筛选指定日期及之前的所有有效数据，按日期倒序排序后取最近的5条（包含指定日期），同时先验证指定日期是否存在数据。

方法1：使用LIMIT筛选 #

-- 替换其中的'2024-03-12'为你需要指定的日期
WITH valid_dates AS (
    SELECT dt, visitors_count
    FROM my_table
    WHERE dt <= '2024-03-12'
    ORDER BY dt DESC -- 按日期倒序，最新的有效日期在前
),
date_validation AS (
    SELECT EXISTS(SELECT 1 FROM my_table WHERE dt = '2024-03-12') AS has_target_data
)
SELECT v.dt, v.visitors_count
FROM valid_dates v
CROSS JOIN date_validation dv
WHERE dv.has_target_data = true -- 仅当指定日期有数据时返回结果
ORDER BY v.dt DESC
LIMIT 5 -- 取最近的5个有效日期
ORDER BY v.dt ASC; -- 最终按日期升序返回

方法2：使用窗口函数ROW_NUMBER() #

-- 替换其中的'2024-03-12'为你需要指定的日期
WITH ranked_valid_dates AS (
    SELECT 
        dt, 
        visitors_count,
        -- 按日期倒序排名，指定日期若存在则排名为1
        ROW_NUMBER() OVER (ORDER BY dt DESC) AS date_rank
    FROM my_table
    WHERE dt <= '2024-03-12'
),
date_validation AS (
    SELECT EXISTS(SELECT 1 FROM my_table WHERE dt = '2024-03-12') AS has_target_data
)
SELECT dt, visitors_count
FROM ranked_valid_dates rvd
CROSS JOIN date_validation dv
WHERE dv.has_target_data = true
AND rvd.date_rank <= 5 -- 取排名前5的有效日期（即最近的5个）
ORDER BY dt ASC;

逻辑说明 #

1. valid_dates/ranked_valid_dates CTE：筛选出指定日期及之前的所有有效数据，并按日期倒序排列（或生成倒序排名），确保最新的有效日期排在最前面。
2. date_validation CTE：检查指定日期是否存在数据，只有存在时才返回结果，符合需求中的前置检查要求。
3. 主查询：从筛选后的有效数据中取最近的5条（包含指定日期），最后按日期升序返回，与示例的输出顺序一致。

参数化调整 #

如果需要将指定日期作为动态参数（例如在应用程序中调用），可以将SQL中的'2024-03-12'替换为参数占位符（如PostgreSQL中的?，MySQL中的%s），便于动态传入指定日期。

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