通过在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
使用窗口函数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