MYSQL获取按用户分组的第一个(MIN)和最后一个(MAX)订单记录

Question

我有一个mysql表，它存储所有客户订单，如下所示(为解决问题而简化(：

+----------+---------------+------+
| Field    | Type          | Null | 
+----------+---------------+------+
| id (pk)  | int(10)       | NO   |
| cust_id  | int(10)       | NO   |
| total    | decimal(10,2) | NO   |
| created  | datetime)     | NO   |
+----------+---------------+------+

在一个查询中，我希望获得每个用户的第一个订单和订单总数，以及他们最近的订单和订单总额

这样我就应该有这样的结果：

+----------+------------------+---------------+------------------+---------------+
| cust_id  | first_ord_total  | first_ord_date| last_ord_total   | last_ord_date |
+----------+------------------+---------------+------------------+---------------+
| 123      | 150.48           | 2018-03-01    | 742.25           | 2020-05-19    |
| 456      | 20.99            | 2019-08-01    | 67.22            | 2020-09-17    | 
| 789      | 259.99           | 2019-01-01    | 147.15           | 2020-08-31    | 
+----------+------------------+---------------+------------------+---------------+

我似乎可以使用MIN和MAX获得第一个和最后一个订单日期，但我无法将其链接回来，也无法给出同一订单/记录的订单总数

我知道这是可能的，但我正在努力把它做好

Answer 1

如果您的MySql版本支持窗口函数，使用MIN((、MAX((和FIRST_VALUE((：

select distinct cust_id,
first_value(total) over (partition by cust_id order by created) first_order_total,
min(created) over (partition by cust_id) first_order_date,
first_value(total) over (partition by cust_id order by created desc) last_order_total,
max(created) over (partition by cust_id) last_order_date
from customers

在没有窗口函数的情况下，使用一个查询来返回每个客户的第一个和最后一个订单日期，并将其加入到使用条件聚合的表中：

select c.cust_id,
max(case when c.created = t.min_created then c.total end) first_order_total,
max(case when c.created = t.min_created then c.created end) first_order_date,
max(case when c.created = t.max_created then c.total end) last_order_total,
max(case when c.created = t.max_created then c.created end) last_order_date
from customers c 
inner join (
select cust_id, min(created) min_created, max(created) max_created
from customers
group by cust_id  
) t on t.cust_id = c.cust_id and c.created in (t.min_created, t.max_created)
group by c.cust_id

Answer 2

在MySQL 8+上，ROW_NUMBER在这里派上了用场：

WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY cust_id ORDER BY created) rn_first,
ROW_NUMBER() OVER (PARTITION BY cust_id ORDER BY created DESC) rn_last
FROM orders
)
SELECT
cust_id,
MAX(CASE WHEN rn_first = 1 THEN total END)   AS first_ord_total,
MAX(CASE WHEN rn_first = 1 THEN created END) AS first_ord_date,
MAX(CASE WHEN rn_last = 1  THEN total END)   AS last_ord_total,
MAX(CASE WHEN rn_last = 1  THEN created END) AS last_ord_date
FROM cte
GROUP BY
cust_id;

这里的策略是为每个客户使用ROW_NUMBER来标识CTE中的第一个和最后一个事务记录。然后，我们按客户汇总，以找到第一个和最后一个总金额和日期。