SQL如何查询上月各item最大业务日期对应最新ETL记录

关系型数据库中存在业务表myTable，表结构参考如下：

表字段核心逻辑：

• date：业务日期字段，记录对应业务日期的系统状态
• date_created：ETL任务实际运行时间，同一个业务日期date会对应多个date_created值（ETL会重复重跑该业务日期的数据）
• 示例：2022年6月28日的item a，ETL分别在6月28日、29日两次运行，对应记录的value值分别为69、70。

取数核心规则：获取指定范围的最新有效记录，需要先按item分组找到范围内的最大业务日期max(date)，再针对该最大业务日期取对应date_created最大的记录即可，注意不同item对应的最大业务日期可能不一致。

当前使用如下SQL获取全量时间范围下每个item的最新有效记录：

SELECT      
    fa.item,
    fa.value
FROM 
(
    SELECT m1.item,  m1.max_date, m2.max_created_date 
    FROM 
        (
            SELECT  
                item,
                max(date) AS max_date
            FROM myTable
            GROUP BY item
        ) as m1
    LEFT JOIN
        (
            SELECT item, 
                date ,
                max(date_created) as max_created_date 
            FROM myTable 
            GROUP BY item, date
        ) AS m2
    ON m1.item = m2.item AND m1.max_date = m2.date
) AS mdv
LEFT JOIN myTable AS fa
    ON fa.item = mdv.item
    AND fa.date = mdv.max_date
    AND fa.date_created = mdv.max_created_date;

需要筛选获取上个月的最新数据：例如当前日期为2021年6月29日时，item "a"上月最后一个可用业务日期为2022年5月30日，对应该业务日期的最后一次ETL运行时间为2022年6月1日，需要查询出这部分高亮记录，目标输出参考如下：

兼容所有关系型数据库的通用写法 #

直接在原有逻辑基础上，限制计算最大业务日期的范围为上个月自然月区间即可，同时在子查询中同步增加时间过滤条件减少全表扫描，提升查询性能，日期函数可根据所用数据库的语法微调：

SELECT      
    fa.item,
    fa.value
FROM 
(
    SELECT m1.item,  m1.max_date, m2.max_created_date 
    FROM 
        (
            SELECT  
                item,
                max(date) AS max_date
            FROM myTable
            -- 限制业务日期范围为上个月：上月1日0点至当月1日0点前
            WHERE date >= DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, '%Y-%m-01')
              AND date < DATE_FORMAT(CURDATE(), '%Y-%m-01')
            GROUP BY item
        ) as m1
    LEFT JOIN
        (
            SELECT item, 
                date ,
                max(date_created) as max_created_date 
            FROM myTable
            WHERE date >= DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, '%Y-%m-01')
              AND date < DATE_FORMAT(CURDATE(), '%Y-%m-01')
            GROUP BY item, date
        ) AS m2
    ON m1.item = m2.item AND m1.max_date = m2.date
) AS mdv
LEFT JOIN myTable AS fa
    ON fa.item = mdv.item
    AND fa.date = mdv.max_date
    AND fa.date_created = mdv.max_created_date;

支持窗口函数的简化写法（MySQL8+、PostgreSQL、Oracle等适用） #

使用ROW_NUMBER()窗口函数可以大幅简化逻辑，可读性和执行效率更高：

WITH ranked_records AS (
    SELECT 
        item,
        value,
        -- 按item分组，先按业务日期倒序、再按ETL运行时间倒序打排名
        ROW_NUMBER() OVER (
            PARTITION BY item 
            ORDER BY date DESC, date_created DESC
        ) AS rn
    FROM myTable
    -- 过滤业务日期在上月区间内的记录
    WHERE date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
      AND date < DATE_TRUNC('month', CURRENT_DATE)
)
SELECT item, value
FROM ranked_records
WHERE rn = 1;

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