mysql - 如何找到客户的第二个最新订单

Question

我通过在 created_at 字段(按电子邮件分组)上执行 MIN 和 MAX 来获得客户的第一个订单日期和最后一个订单日期，但我还需要获得客户的第二个最新订单日期(最后一个订单日期之前的订单日期)订购日期)

SELECT 
    customer_email, 
    COUNT(entity_id) AS NumberOfOrders, 
    MIN(CONVERT_TZ(created_at,'UTC','US/Mountain')) AS 'FirstOrder', 
    MAX(CONVERT_TZ(created_at,'UTC','US/Mountain')) AS 'MostRecentOrder',
    SUM(grand_total) AS TotalRevenue, 
    SUM(discount_amount) AS TotalDiscount
FROM sales_flat_order 
WHERE 
    customer_email IS NOT NULL
    AND store_id = 1
GROUP BY customer_email
LIMIT 500000

Answer 1

使用窗口函数 ROW_NUMBER()(在 MySQL 8.0 中可用):

SELECT *
FROM (
    SELECT 
        t.*, 
        ROW_NUMBER() OVER(PARTITION BY customer_email ORDER BY created_at) rn_asc,
        ROW_NUMBER() OVER(PARTITION BY customer_email ORDER BY created_at DESC) rn_desc
    FROM sales_flat_order
    WHERE customer_email IS NOT NULL AND store_id = 1
) x
WHERE rn_asc = 1 OR rn_desc <= 2

这将为您获取每个客户下的最早订单和最新订单。

注意:尚不清楚时区转换的目的。我将它们分开，因为它们显然不会影响排序顺序；您可以根据您的用例随意添加它们。

---

如果您想要每个客户的单个记录，连同它的订单总数，以及他的第一个、最后一个和最后一个订单的日期，那么您可以在外部查询中进行聚合:

SELECT 
    customer_email,
    NumberOfOrders,
    MAX(CASE WHEN rn_asc = 1 THEN created_at END) FirstOrder,
    MAX(CASE WHEN rn_desc = 1 THEN created_at END) MostRecentOrder,
    MAX(CASE WHEN rn_desc = 2 THEN created_at END) MostRecentButOneOrder,
    TotalRevenue,
    TotalDiscount
FROM (
    SELECT 
        customer_email,
        created_at,
        COUNT(*) OVER(PARTITION BY customer_email) NumberOfOrders,
        SUM(grand_total) OVER(PARTITION BY customer_email) TotalRevenue, 
        SUM(discount_amount) OVER(PARTITION BY customer_email) TotalDiscount,
        ROW_NUMBER() OVER(PARTITION BY customer_email ORDER BY created_at) rn_asc,
        ROW_NUMBER() OVER(PARTITION BY customer_email ORDER BY created_at DESC) rn_desc
    FROM sales_flat_order
    WHERE customer_email IS NOT NULL AND store_id = 1
) x
GROUP BY customer_email