如何按customer_key分组查询客户最新的order_no与order_date

问题描述 #

需按customer_key分组，查询每个客户最新的order_no和order_date。数据表Cust_order结构及数据如下：

期望输出：

现有查询仅能获取每个客户的最新日期，无法关联对应order_no：

select Customer_key, max(Order_date) as DATE
from Cust_order
WHERE  Customer_key in (1233325,550089,1239689)
group by Customer_key

解决方案 #

方法1：窗口函数（推荐）

利用ROW_NUMBER()按客户分组，再按订单日期降序排序，取每组第一条数据即为最新订单。需先将字符串格式的Order_date转为日期类型，避免字符串排序逻辑错误。

WITH ranked_orders AS (
    SELECT 
        Order_no,
        Order_date,
        Customer_key,
        ROW_NUMBER() OVER (PARTITION BY Customer_key ORDER BY STR_TO_DATE(Order_date, '%d.%m.%y') DESC) AS rn
    FROM Cust_order
    WHERE Customer_key IN (1233325,550089,1239689)
)
SELECT Order_no, Order_date, Customer_key
FROM ranked_orders
WHERE rn = 1;

• 逻辑说明：STR_TO_DATE把字符串日期转为标准日期类型，确保排序准确；PARTITION BY Customer_key按客户分组，ORDER BY ... DESC让最新日期排在组内首位，最后筛选rn=1得到每组最新订单。

方法2：子查询关联

先通过子查询获取每个客户的最新日期，再关联原表匹配对应订单号：

SELECT c.Order_no, c.Order_date, c.Customer_key
FROM Cust_order c
JOIN (
    SELECT 
        Customer_key,
        MAX(STR_TO_DATE(Order_date, '%d.%m.%y')) AS latest_date
    FROM Cust_order
    WHERE Customer_key IN (1233325,550089,1239689)
    GROUP BY Customer_key
) t ON c.Customer_key = t.Customer_key AND STR_TO_DATE(c.Order_date, '%d.%m.%y') = t.latest_date;

• 逻辑说明：子查询先计算每个客户的最新日期（转日期类型后取最大值），再通过客户ID和日期关联原表，拿到对应的订单号。

注意事项 #

若Order_date字段本身是日期类型，可直接去掉STR_TO_DATE函数，用原字段进行排序和比较。

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