DB2基于字符串日期按位置、产品分组取最新交易记录的问题

原测试SQL的核心问题 #

此前编写的测试语句存在两处关键偏差，导致结果完全不符合预期：

1. 分组维度缺失：子查询仅按LOCATION单维度分组取最大日期，未将PRODUCT纳入分组范围，日期匹配跨产品错位，最终出现同位置同产品组合返回多条重复记录的问题
2. 时间范围错配：语句中DATE > '20210401'的筛选条件，与预期结果对应的2021年1月交易日期范围完全冲突，会提前过滤掉所有目标记录

由于表中DATE为YYYYMMDD格式字符串，字符串字典序与真实日期的先后顺序完全一致，无需额外类型转换即可直接完成大小比较、最大取值计算。

可落地的正确实现方案 #

方案1：窗口函数法（推荐，全版本DB2兼容，无重复风险） #

利用DB2原生支持的ROW_NUMBER()窗口函数，严格按LOCATION+PRODUCT双维度分组，取每组内日期最大的单条记录。即使同组同日期存在多条交易记录，也能稳定返回单条结果，从逻辑上避免重复问题。
实现代码：

SELECT DATE, LOCATION, PRODUCT, QTY
FROM (
    SELECT 
        DATE,
        LOCATION,
        PRODUCT,
        QTY,
        -- 按位置+产品划分独立分组，按交易日期倒序排序
        ROW_NUMBER() OVER (
            PARTITION BY LOCATION, PRODUCT 
            ORDER BY DATE DESC
        ) AS row_rank
    FROM t
    -- 若需叠加业务时间范围过滤，将条件写在此处，避免逻辑错位
) ranked_records
WHERE row_rank = 1
ORDER BY LOCATION, PRODUCT;

该方案执行后的结果完全匹配预期目标：

• 位置A、产品P1：返回日期20210112、数量7的记录
• 位置B、产品P1：返回日期20210105、数量1的记录
• 位置A、产品P2：返回日期20210105、数量5的记录

方案2：关联匹配法（适配不支持窗口函数的老旧DB2版本） #

先通过双维度分组算出每个位置+产品组合对应的最大交易日期，再将原表与计算结果做内关联，匹配对应维度的最新交易记录。
实现代码：

SELECT t.DATE, t.LOCATION, t.PRODUCT, t.QTY
FROM t
INNER JOIN (
    -- 按位置+产品双维度计算每个组合的最新交易日期
    SELECT LOCATION, PRODUCT, MAX(DATE) AS latest_date
    FROM t
    GROUP BY LOCATION, PRODUCT
) max_date_map
ON t.LOCATION = max_date_map.LOCATION 
   AND t.PRODUCT = max_date_map.PRODUCT
   AND t.DATE = max_date_map.latest_date
ORDER BY t.LOCATION, t.PRODUCT;

注意：该方案下如果同个位置+产品+日期存在多条交易记录，会将所有匹配的记录全部返回；若需要仅保留单条记录，可在SELECT字段列表前加DISTINCT关键字完成去重。

常见踩坑提醒 #

• 计算每组最大日期时，必须完整匹配要求的分组维度，仅按单维度计算的逻辑天然不满足双维度取数的需求
• 叠加业务时间范围过滤时，需将条件写在分组计算的子查询内部，不要写在外层结果集上，避免提前过滤掉目标记录
• 由于表无主键、无交易唯一标识，若同个位置+产品同日期存在多条交易，可根据实际业务规则增加辅助排序字段，让窗口函数的排序逻辑更稳定

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