Oracle 18c中基于最大时间戳筛选记录并使用LISTAGG聚合

测试表结构与数据 #

CREATE TABLE test (
    e_id        NUMBER(10),
    f_name      VARCHAR2(50),
    created_on  TIMESTAMP,
    created_by  VARCHAR2(50)
);

INSERT INTO test VALUES(11,'a','18-07-22 12:06:19.566000000 PM','aa');
INSERT INTO test VALUES(11,'b','18-07-22 11:06:19.566000000 PM','bb');
INSERT INTO test VALUES(11,'c','16-07-22 12:06:19.566000000 PM','cc');
INSERT INTO test VALUES(11,'a','15-07-22 12:06:19.566000000 PM','dd');
INSERT INTO test VALUES(11,'a','12-07-22 12:06:19.566000000 PM','ee');
INSERT INTO test VALUES(11,'a','11-07-22 12:06:19.566000000 PM','ff');

需求说明 #

• 获取created_on的最大值
• 获取created_by的最大值
• 筛选created_on为最大值的记录，将对应f_name去重后用分号分隔（使用LISTAGG函数）

初始尝试代码 #

WITH a AS(SELECT e_id, MAX(created_on)created_on,MAX(created_by)created_by
FROM test),
b AS(
SELECT e_id, LISTAGG(DISTINCT(f_name))
FROM test WHERE created_on = --需筛选出当日最大的created_on
);
-- 注：LISTAGG中使用DISTINCT是为了避免同一e_id下出现重复记录

完善后的SQL语句 #

WITH max_info AS (
    SELECT 
        e_id,
        MAX(created_on) AS max_created_on,
        MAX(created_by) AS max_created_by
    FROM test
    GROUP BY e_id
),
distinct_fnames AS (
    SELECT 
        e_id,
        LISTAGG(DISTINCT f_name, ';') WITHIN GROUP (ORDER BY f_name) AS f_name_list
    FROM test
    WHERE created_on = (SELECT max_created_on FROM max_info WHERE e_id = test.e_id)
    GROUP BY e_id
)
SELECT 
    mi.e_id,
    mi.max_created_on AS created_on,
    mi.max_created_by AS created_by,
    df.f_name_list AS f_name
FROM max_info mi
JOIN distinct_fnames df ON mi.e_id = df.e_id;

关键说明 #

1. max_info CTE：按e_id分组，计算每个e_id对应的created_on最大值和created_by最大值，确保分组逻辑匹配需求。
2. distinct_fnames CTE：关联max_info获取当前e_id的最大created_on，筛选符合条件的记录后，用LISTAGG(DISTINCT ...)去重并拼接f_name，指定分号为分隔符，同时通过ORDER BY控制拼接顺序。
3. 最后通过e_id关联两个CTE，输出所有需求字段。

预期输出 #

+------+--------------------------------+------------+--------+
| e_id |           created_on           | created_by | f_name |
+------+--------------------------------+------------+--------+
|   11 | 18-07-22 12:06:19.566000000 PM | aa         | a;b    |
+------+--------------------------------+------------+--------+

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