題目來源：小紅書。

1 題目

現有一張訂單表 t_order 有訂單ID、用戶ID、商品ID、購買商品數量、購買時間，請查詢出每個用戶的第一條記錄和最后一條記錄。樣例數據如下：

+-----------+----------+-------------+-----------+------------------------+
| order_id  | user_id  | product_id  | quantity  |     purchase_time      |
+-----------+----------+-------------+-----------+------------------------+
| 1         | 1        | 1001        | 1         | 2023-03-13 08:30:00.0  |
| 2         | 1        | 1002        | 1         | 2023-03-13 10:45:00.0  |
| 3         | 1        | 1001        | 1         | 2023-03-13 10:45:01.0  |
| 4         | 2        | 1001        | 3         | 2023-03-13 14:20:00.0  |
| 5         | 3        | 1003        | 1         | 2023-03-13 16:15:00.0  |
| 6         | 3        | 1002        | 1         | 2023-03-13 12:10:00.0  |
| 7         | 3        | 1001        | 1         | 2023-03-13 12:10:01.0  |
| 8         | 4        | 1002        | 2         | 2023-03-13 09:00:00.0  |
| 9         | 4        | 1003        | 1         | 2023-03-13 11:30:00.0  |
| 10        | 4        | 1004        | 3         | 2023-03-13 13:40:00.0  |
| 11        | 4        | 1001        | 1         | 2023-03-13 17:25:00.0  |
| 12        | 4        | 1002        | 2         | 2023-03-13 15:05:00.0  |
| 13        | 4        | 1004        | 1         | 2023-03-13 11:55:00.0  |
+-----------+----------+-------------+-----------+------------------------+

2 建表語句

--建表語句
CREATE TABLE t_order (order_id INT,user_id INT,product_id INT,quantity INT,purchase_time TIMESTAMP
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE;--數據插入語句
INSERT INTO t_order VALUES
(1, 1, 1001, 1, '2023-03-13 08:30:00'),
(2, 1, 1002, 1, '2023-03-13 10:45:00'),
(3, 1, 1001, 1, '2023-03-13 10:45:01'),
(4, 2, 1001, 3, '2023-03-13 14:20:00'),
(5, 3, 1003, 1, '2023-03-13 16:15:00'),
(6, 3, 1002, 1, '2023-03-13 12:10:00'),
(7, 3, 1001, 1, '2023-03-13 12:10:01'),
(8, 4, 1002, 2, '2023-03-13 09:00:00'),
(9, 4, 1003, 1, '2023-03-13 11:30:00'),
(10, 4, 1004, 3, '2023-03-13 13:40:00'),
(11, 4, 1001, 1, '2023-03-13 17:25:00'),
(12, 4, 1002, 2, '2023-03-13 15:05:00'),
(13, 4, 1004, 1, '2023-03-13 11:55:00');

3 題解

（1）添加行號

使用row_number()根據用戶進行分組，根據時間分別進行正向排序和逆向排序，增加兩個行號,分別為asc_rn和desc_rn

select order_id,user_id,product_id,quantity,purchase_time,row_number() over (partition by user_id order by purchase_time asc)  as asc_rn,row_number() over (partition by user_id order by purchase_time desc) as desc_rn
from t_order;

執行結果

+-----------+----------+-------------+-----------+------------------------+---------+----------+
| order_id  | user_id  | product_id  | quantity  |     purchase_time      | asc_rn  | desc_rn  |
+-----------+----------+-------------+-----------+------------------------+---------+----------+
| 3         | 1        | 1001        | 1         | 2023-03-13 10:45:01.0  | 3       | 1        |
| 2         | 1        | 1002        | 1         | 2023-03-13 10:45:00.0  | 2       | 2        |
| 1         | 1        | 1001        | 1         | 2023-03-13 08:30:00.0  | 1       | 3        |
| 4         | 2        | 1001        | 3         | 2023-03-13 14:20:00.0  | 1       | 1        |
| 5         | 3        | 1003        | 1         | 2023-03-13 16:15:00.0  | 3       | 1        |
| 7         | 3        | 1001        | 1         | 2023-03-13 12:10:01.0  | 2       | 2        |
| 6         | 3        | 1002        | 1         | 2023-03-13 12:10:00.0  | 1       | 3        |
| 11        | 4        | 1001        | 1         | 2023-03-13 17:25:00.0  | 6       | 1        |
| 12        | 4        | 1002        | 2         | 2023-03-13 15:05:00.0  | 5       | 2        |
| 10        | 4        | 1004        | 3         | 2023-03-13 13:40:00.0  | 4       | 3        |
| 13        | 4        | 1004        | 1         | 2023-03-13 11:55:00.0  | 3       | 4        |
| 9         | 4        | 1003        | 1         | 2023-03-13 11:30:00.0  | 2       | 5        |
| 8         | 4        | 1002        | 2         | 2023-03-13 09:00:00.0  | 1       | 6        |
+-----------+----------+-------------+-----------+------------------------+---------+----------+

（2）取出第一條和最后一條記錄

限制asc_rn=1取第一條，desc_rn=1 取最后一條

select order_id,user_id,product_id,quantity,purchase_time
from (select order_id,user_id,product_id,quantity,purchase_time,row_number() over (partition by user_id order by purchase_time asc)  as asc_rn,row_number() over (partition by user_id order by purchase_time desc) as desc_rnfrom t_order) t1
where t1.asc_rn = 1or t1.desc_rn = 1

執行結果

+-----------+----------+-------------+-----------+------------------------+
| order_id  | user_id  | product_id  | quantity  |     purchase_time      |
+-----------+----------+-------------+-----------+------------------------+
| 3         | 1        | 1001        | 1         | 2023-03-13 10:45:01.0  |
| 1         | 1        | 1001        | 1         | 2023-03-13 08:30:00.0  |
| 4         | 2        | 1001        | 3         | 2023-03-13 14:20:00.0  |
| 5         | 3        | 1003        | 1         | 2023-03-13 16:15:00.0  |
| 6         | 3        | 1002        | 1         | 2023-03-13 12:10:00.0  |
| 11        | 4        | 1001        | 1         | 2023-03-13 17:25:00.0  |
| 8         | 4        | 1002        | 2         | 2023-03-13 09:00:00.0  |
+-----------+----------+-------------+-----------+------------------------+