DB2 SQL查询需求：获取每个客户各步骤类型的最大编号记录

我有一张客户步骤关联表，每个客户对应若干步骤记录。例如CUSTOMER1关联BUY001、BUY002、BUY003及SEL001、SEL002步骤；CUSTOMER2关联BAD001、BAD002、BOP001等步骤。

表结构与示例数据 #

需求 #

编写DB2 SQL查询语句，筛选出每个客户下，每种步骤类型（由3个字母组成的前缀，如BUY、SEL）对应的最大编号的STEP_ID记录。

期望输出 #

注：步骤类型为固定3字母标识，约100种；每个步骤类型的编号为001至999的三位数字后缀。

DB2 SQL 查询语句 #

WITH step_groups AS (
    SELECT 
        CUSTOMER_ID,
        STEP_ID,
        TIMESTAMP,
        SUBSTR(STEP_ID, 1, 3) AS STEP_TYPE,
        ROW_NUMBER() OVER (
            PARTITION BY CUSTOMER_ID, SUBSTR(STEP_ID, 1, 3) 
            ORDER BY STEP_ID DESC
        ) AS rn
    FROM your_table_name  -- 替换为实际表名
)
SELECT CUSTOMER_ID, STEP_ID, TIMESTAMP
FROM step_groups
WHERE rn = 1
ORDER BY CUSTOMER_ID, STEP_TYPE;

思路说明 #

1. 分组排序标记：通过SUBSTR(STEP_ID, 1, 3)提取步骤类型前缀，使用ROW_NUMBER()窗口函数按CUSTOMER_ID和STEP_TYPE分组，对STEP_ID降序排序，每组中最大编号的记录会被标记为rn=1。
2. 筛选目标记录：仅保留rn=1的行，即可得到每个客户、每种步骤类型下的最大编号步骤记录。
3. 可选优化：如果担心字符串排序与数字排序出现差异（当前场景下三位固定后缀的字符串排序和数字排序结果一致），可将排序逻辑改为基于数字后缀：

ORDER BY CAST(SUBSTR(STEP_ID, 4) AS INTEGER) DESC

内容的提问来源于stack exchange，提问作者Petras Paludnevičius