使用MAX与RANK函数未获预期结果的技术求助

你原本想要按IT_NO分组，获取每组的最早FEE_TRAN_DT，但现有SQL输出了大量重复的日期记录，无法达到预期效果，核心问题出在以下几点：

1. 开窗函数分区逻辑错误
   你在RANK()的PARTITION BY中包含了FEE_TRAN_DT和RUN_DATE，这会将IT_NO + FEE_TRAN_DT + RUN_DATE的组合作为分组依据，每个日期都会成为独立分组，完全起不到筛选每组最早日期的作用。

2. 聚合函数使用错误
   需求是获取最早日期，但你用了MAX(FEE_TRAN_DT)，这会返回每组的最晚日期，应替换为MIN(FEE_TRAN_DT)。

3. 外层分组逻辑冗余
   外层GROUP BY包含了FEE_TRAN_DT，导致每个日期都被单独保留，无法聚合到IT_NO维度的最早日期。

修正后的SQL（获取每个IT_NO的最早收费日期） #

根据你的需求，提供两种可行方案：

方案1：用开窗函数筛选最早记录 #

如果需要保留RUN_DATE等其他字段，同时确保每个IT_NO只显示最早日期的记录：

WITH FEES AS (
    SELECT
        IT_NO AS ITEM_NO,
        FEE_TRAN_DT AS FIRST_FEE_ASSESSED_DATE,
        RUN_DATE,
        -- 按IT_NO分组，按日期升序排序，最早的记录排名为1
        ROW_NUMBER() OVER (PARTITION BY IT_NO ORDER BY FEE_TRAN_DT ASC) AS RNK
    FROM DM_MORTGAGE.FEE 
    WHERE 
        FEE_TRAN_DT > '01-MAY-20' 
        AND FEE_TRAN_TY = 'A'
)
SELECT ITEM_NO, FIRST_FEE_ASSESSED_DATE, RUN_DATE
FROM FEES
WHERE RNK = 1; -- 只保留每组最早的一条记录

• 若同一IT_NO存在多条相同最早日期的记录，ROW_NUMBER()会随机选取一条；若要保留所有符合条件的记录，可替换为RANK()或DENSE_RANK()。

方案2：直接聚合（无需开窗） #

如果不需要保留额外字段，或RUN_DATE在同一IT_NO的最早日期下是唯一的，可直接用聚合函数：

SELECT
    IT_NO AS ITEM_NO,
    MIN(FEE_TRAN_DT) AS FIRST_FEE_ASSESSED_DATE,
    RUN_DATE -- 若同一IT_NO对应多个RUN_DATE，需用MIN(RUN_DATE)/MAX(RUN_DATE)或确认业务逻辑
FROM DM_MORTGAGE.FEE 
WHERE 
    FEE_TRAN_DT > '01-MAY-20' 
    AND FEE_TRAN_TY = 'A'
GROUP BY IT_NO, RUN_DATE;

注意事项 #

• 建议使用标准日期格式（如'2020-05-01'）替代'01-MAY-20'，避免不同数据库的日期解析错误。

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