Oracle中MAX函数执行查询未返回预期结果问题求助

错误根源 #

你当前使用MIN()和MAX()函数的逻辑是对提取出的HYPO字符串做字典序排序后取最小/最大值，并非按CALL_ROW_ID的业务顺序取第一条/最后一条记录的HYPO值。你返回的错误结果完全符合字典序规则：

• GIVE ME BIRTHDAY的首字母G在字母表中早于NEW CREDIT的首字母N，所以会被MIN()选中
• YES的首字母Y晚于JULY 25th 2019的首字母J，所以会被MAX()选中
  这和你加不加ORDER BY没有关系，因为聚合函数的计算逻辑不受普通排序影响。

修复方案 #

使用窗口函数按CALL_ROW_ID排序，定位到分组内第一条和最后一条有效记录的HYPO值即可，同时修正了你原代码中TO_CHAR缺少右括号的语法错误，参考代码如下：

SELECT 
    DTE,
    UNIQUE_ID,
    MAX(CASE WHEN rn_asc = 1 THEN hypo_val END) AS FIRST_HYPO,
    MAX(CASE WHEN rn_desc = 1 THEN hypo_val END) AS LAST_HYPO
FROM (
    SELECT 
        TO_CHAR(DTE, 'MM/DD/YYYY') AS DTE,
        UNIQUE_ID,
        -- 提取HYPO对应值
        TRIM(SUBSTR(ROW_DATA, INSTR(ROW_DATA, 'HYPO=') + 5, 
            CASE WHEN INSTR(ROW_DATA, '|', INSTR(ROW_DATA, 'HYPO=') +5) > 0 
                THEN INSTR(ROW_DATA, '|', INSTR(ROW_DATA, 'HYPO=') +5) - (INSTR(ROW_DATA, 'HYPO=') +5)
                ELSE LENGTH(ROW_DATA) END
        )) AS hypo_val,
        -- 按CALL_ROW_ID正排取第一条
        ROW_NUMBER() OVER (PARTITION BY TO_CHAR(DTE, 'MM/DD/YYYY'), UNIQUE_ID ORDER BY CALL_ROW_ID ASC) AS rn_asc,
        -- 按CALL_ROW_ID倒排取最后一条
        ROW_NUMBER() OVER (PARTITION BY TO_CHAR(DTE, 'MM/DD/YYYY'), UNIQUE_ID ORDER BY CALL_ROW_ID DESC) AS rn_desc
    FROM DATATABLE
    -- 过滤掉没有HYPO的无效行
    WHERE INSTR(ROW_DATA, 'HYPO=') > 0
) t
WHERE rn_asc = 1 OR rn_desc = 1
GROUP BY DTE, UNIQUE_ID;

执行上述代码就可以得到你预期的结果：FIRST_HYPO为NEW CREDIT，LAST_HYPO为JULY 25th 2019。

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