mysql - 如何找到客户的第二个最新订单
问题描述
我通过在 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
推荐阅读
- git - 什么 git 命令会在不删除文件或创建更多分支的情况下撤消最近的提交?
- oracle - 应用我所读过的关于错误 02270 的所有内容后仍然出现错误
- node.js - 通过 Firebase Admin SDK 本地节点服务器进行 Firestore 递归删除
- php - 未从布局文件调用 Magento v2 模板文件
- pandas - Pandas gropuby[['col_name', '来自另一个数据框的值']]
- python - Python SMTP 验证电子邮件
- python-3.x - plotly中的漏斗图
- java - 颜色代码的十进制表示中减去 16 ^ 6 的原因是什么?
- python-3.x - 修复在 0 经度附近出现的不正确等高线
- java - 如何在java中逐字比较两个字符串