mysql_进阶篇_计算用户首次购买与再次购买的时间间隔

作者：YXN-sql 阅读量：82 发布日期：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