mysql_进阶篇_计算用户首次购买与再次购买的时间间隔
作者:YXN-sql 阅读量:72 发布日期:2023-04-16
有一张用户交易记录表,记录了用户的ID和他们的交易时间。我们需要对每个用户进行统计,计算他们第一次和第二次交易的时间间隔天数。
对于有多于两条交易记录的用户,我们只需要计算第一次和第二次交易的时间间隔;而对于只有一条交易记录的用户,我们直接略过。
最终我们希望得到的结果是,对于每个用户,第二次交易时间减去第一次交易时间所得到的时间间隔天数。
数据准备:
-- 建表
CREATE TABLE buy_record
(
userid VARCHAR ( 5 ),
ordertime date
);
-- 插入数据
INSERT INTO buy_record
VALUES
( '10001', '2023/6/1' ),
( '10001', '2023/9/1' ),
( '10002', '2023/9/12' ),
( '10002', '2023/9/16' ),
( '10002', '2023/9/30' ),
( '10002', '2023/10/21' ),
( '10003', '2023/9/1' ),
( '10004', '2023/9/1' ),
( '10005', '2023/9/1' ),
( '10006', '2023/6/1' ),
( '10006', '2023/8/21' ),
( '10006', '2023/9/1' ),
( '10007', '2023/6/15' ),
( '10007', '2023/6/21' );
代码:
统计每个用户第一次和第二次购买的时间间隔:
SELECT
a.userid,
DATEDIFF( b.ordertime, a.ordertime ) AS 间隔天数
FROM
( SELECT
userid,
ordertime,
ROW_NUMBER() over ( PARTITION BY userid ORDER BY ordertime ASC ) AS num
FROM
buy_record ) a
LEFT JOIN
( SELECT
userid,
ordertime,
ROW_NUMBER() over ( PARTITION BY userid ORDER BY ordertime ASC ) AS num
FROM buy_record ) b
ON
a.userid = b.userid
WHERE
a.num = 1
AND b.num = 2;
以上SQL语句主要运用了DATEDIFF函数、窗口函数以及多表连接的知识点。
YXN-sql
2023-04-16