Oracle如何按分组取前5条日期拼接为逗号分隔列并输出自定义计算字段

• 先使用窗口函数对每个Division、Region分组内的访问日期按倒序排名，筛选出每个分组的前5条最新记录
• 对筛选后的结果按Division、Region分组，使用Oracle内置的拼接函数完成日期拼接，同时计算两个要求的自定义字段

WITH ranked_visits AS (
    SELECT 
        Division,
        Region,
        "Date of Last Visit",
        -- 按分组倒序排名，取前5个最新日期
        ROW_NUMBER() OVER (PARTITION BY Division, Region ORDER BY "Date of Last Visit" DESC) AS rn
    FROM 你的表名 -- 替换为实际表名
)
SELECT 
    Division,
    Region,
    -- 拼接最多5个最新日期，按倒序排列
    LISTAGG(TO_CHAR("Date of Last Visit", 'MM/DD/YYYY'), ',') WITHIN GROUP (ORDER BY "Date of Last Visit" DESC) AS latest_5_visits,
    TRUNC(sysdate) AS "Today",
    -- 计算距最新访问的间隔天数
    TRUNC(sysdate) - MAX("Date of Last Visit") AS "Days since last visit"
FROM ranked_visits
WHERE rn <= 5
GROUP BY Division, Region;

• 如果你的Date of Last Visit字段为字符串类型存储，需要先通过TO_DATE("Date of Last Visit", 'MM/DD/YYYY')转换为日期类型后再进行排序、计算，避免逻辑错误
• 若使用Oracle 12c及以上版本，可在LISTAGG后加ON OVERFLOW TRUNCATE '...'处理极端情况下的拼接长度溢出问题，本需求最多拼接5个日期无需额外处理
• 查询结果中的Days since last visit为整数天数，若需要保留小数可去掉TRUNC函数对sysdate的截取

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