SQL-查询每个用户的第一条和最后一条记录

Gu—谷

已于 2024-07-29 10:58:18 修改

阅读量146

点赞数 3

分类专栏： SQL练习文章标签： sql 数据库

于 2024-07-29 10:42:58 首次发布

本文链接：https://blog.csdn.net/qq_44242891/article/details/140762250

版权

SQL练习专栏收录该内容

2 篇文章 0 订阅

订阅专栏

--建表语句
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');

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;

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_rn
      from t_order) t1
where t1.asc_rn = 1
   or t1.desc_rn = 1