如何生成从当前日期倒序的5行序列ID？（日期缺失场景）

问题分析 #

原SQL的核心问题在于：

• 排序方向错误：ORDER BY date是升序排列，无法实现从当前日期倒序取数的效果
• 窗口范围逻辑不符：ROWS 5 PRECEDING EXCLUDE CURRENT ROW是基于当前行位置取前面的5行（且排除当前行），而非从当前日期开始倒序筛选存在的最近5条记录

可行SQL方案 #

使用CTE先对每个员工的记录按日期倒序排序并生成序列ID，再筛选最多5行：

WITH ranked_employees AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY date DESC) AS sequence_id
    FROM employee
    WHERE date <= CURRENT_DATE  -- 仅保留当前日期及之前的记录
)
SELECT *
FROM ranked_employees
WHERE sequence_id <= 5  -- 限制最多保留5行
ORDER BY employee_id, sequence_id;

逻辑说明 #

• 按employee_id分区，确保每个员工的记录单独处理
• ORDER BY date DESC实现从最新日期到最早日期的倒序排列，自动跳过日期缺失的情况
• ROW_NUMBER()生成的sequence_id从1开始（对应最近的日期），便于精准筛选前5条记录
• WHERE date <= CURRENT_DATE确保只处理当前日期及之前的有效数据

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