PGSQL如何查询日历表中以今日为起点的连续倒序无中断日期序列

现有一张存储无需执行操作日期的日历表，日期格式为YYYY-MM-DD，样例数据如下：

date        
2021-01-01  
2021-04-05
2021-04-06
2021-04-07  
2021-08-10
2021-11-22
2021-11-23
2021-11-24
2021-12-25
2021-12-31

以指定的今日日期为起点，查询倒序排列、无中断的连续日期集合：

• 若今日为2021-11-24，输出连续倒序的3条记录：2021-11-24、2021-11-23、2021-11-22
• 若今日为2021-12-25，仅输出该日期本身
• 若今日为2021-12-27（不在日历表中），无输出

使用PG内置窗口函数即可实现，无需多层嵌套子查询，逻辑简洁易读：

-- 假设日历表名为 holiday_calendar
WITH ranked_dates AS (
    SELECT 
        date,
        -- 按日期倒序生成行号
        ROW_NUMBER() OVER (ORDER BY date DESC) AS rn
    FROM holiday_calendar
    -- 提前过滤所有小于等于指定今日的日期，减少计算量
    WHERE date <= '2021-11-24'::DATE -- 此处替换为你需要指定的今日日期
)
SELECT date
FROM ranked_dates
-- 连续倒序的日期会满足「日期+行号」值统一等于「今日日期+1」
WHERE date + rn = '2021-11-24'::DATE + 1
ORDER BY date DESC;

1. 第一步先过滤掉所有晚于今日的日期，缩小计算范围
2. 给过滤后的日期按倒序分配行号，最新的日期行号为1
3. 连续倒序的日期天然满足日期 + 行号值相同：比如2021-11-24+1=2021-11-25、2021-11-23+2=2021-11-25、2021-11-22+3=2021-11-25，匹配这个固定值的就是我们需要的连续序列
4. 如果指定的今日不在日历表中，没有日期能满足日期+行号=今日+1的匹配规则，最终自然返回空结果，符合需求

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