按客户分组，基于订单日期查找上一笔已收货记录

订单表T1结构与样例数据 #

表T1包含字段：customer id、item、order date、recieved date，样例数据如下：

需求说明 #

筛选所有item为Bag的订单，按customer id分组，为每条这类订单找出该客户在当前订单的order date之前最后收到的商品及其收货日期。

预期输出结果 #

解决方案（SQL语句） #

以下以MySQL为例，针对日期格式为DD/MM/YYYY的场景编写SQL；若数据库中日期已为DATE类型，可去掉STR_TO_DATE转换函数：

SELECT
    t.`customer id`,
    t.item,
    t.`order date`,
    prev.item AS `Previous Item Received`,
    prev.`recieved date` AS `Prev Item Received Dt`
FROM
    T1 t
LEFT JOIN (
    -- 按客户分组，标记每个客户收货记录的最新排名
    SELECT
        `customer id`,
        item,
        `recieved date`,
        ROW_NUMBER() OVER (
            PARTITION BY `customer id` 
            ORDER BY STR_TO_DATE(`recieved date`, '%d/%m/%Y') DESC
        ) AS rn
    FROM T1
) prev 
    ON t.`customer id` = prev.`customer id`
    AND STR_TO_DATE(prev.`recieved date`, '%d/%m/%Y') < STR_TO_DATE(t.`order date`, '%d/%m/%Y')
    AND prev.rn = 1
WHERE
    t.item = 'Bag'
ORDER BY
    t.`customer id`, STR_TO_DATE(t.`order date`, '%d/%m/%Y');

逻辑说明 #

1. 子查询通过ROW_NUMBER()窗口函数，按客户分组、收货日期倒序排序，为每个客户的收货记录标记排名，排名1的是该客户最新的收货记录。
2. 将原表中所有Bag订单与子查询结果关联，筛选出收货日期早于当前订单下单日期的最新记录。
3. 若客户在当前Bag订单前无收货记录，对应字段显示NULL，匹配预期输出。

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