如何从数据表中获取每个unique_id对应的最近两个日期

现有原始数据表如下：

需要将其处理为每个unique_id对应的最近日期（latest_date）和第二近日期（2nd_latest_date），无第二近日期时显示Null，目标格式如下：

可以使用SQL窗口函数对分组内的日期排序，再通过条件聚合提取目标日期：

WITH ranked_dates AS (
    SELECT 
        unique_id,
        date,
        ROW_NUMBER() OVER (PARTITION BY unique_id ORDER BY date DESC) AS rn
    FROM your_table_name
)
SELECT 
    unique_id,
    MAX(CASE WHEN rn = 1 THEN date END) AS latest_date,
    MAX(CASE WHEN rn = 2 THEN date END) AS 2nd_latest_date
FROM ranked_dates
GROUP BY unique_id;

补充说明 #

如果日期字段是字符串格式，直接排序可能出现逻辑错误（比如10/1/2023会排在2/1/2023前面），需要先转换为日期类型再排序，以MySQL为例：

WITH ranked_dates AS (
    SELECT 
        unique_id,
        date,
        ROW_NUMBER() OVER (PARTITION BY unique_id ORDER BY STR_TO_DATE(date, '%d/%m/%Y') DESC) AS rn
    FROM your_table_name
)
SELECT 
    unique_id,
    MAX(CASE WHEN rn = 1 THEN date END) AS latest_date,
    MAX(CASE WHEN rn = 2 THEN date END) AS 2nd_latest_date
FROM ranked_dates
GROUP BY unique_id;

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