SQL消费者消费间隔时间计算

发表于 分类于 阅读次数： 本文字数： 1.6k 阅读时长 ≈ 4 分钟

简介：本文将介绍在数据库中，使用SQL对消费者消费间隔时间进行计算。

1 技巧背景

在数据分析中，我们有时候会需要对消费者到店消费间隔时间进行计算，接下来将介绍如何在数据库中使用SQL进行计算分析。

2 测试数据导入

在数据库中，有一个表记录这消费者的ID与到店消费时间，如下所示：

测试数据导入代码：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
--创建测试数据表
CREATE TABLE #消费数据
(用户ID NVARCHAR(10),
消费时间 datetime)

--在测试数据表中导入数据
INSERT INTO #消费数据
(用户ID,消费时间)
VALUES
('1001','2021-12-01 11:12:00'),
('1001','2021-12-01 16:11:00'),
('1002','2021-12-05 08:10:00'),
('1003','2021-12-02 07:09:00'),
('1003','2021-12-03 08:08:00'),
('1003','2021-12-05 12:07:00'),
('1004','2021-12-03 11:06:00'),
('1004','2021-12-06 10:05:00'),
('1004','2021-12-10 08:04:00'),
('1004','2021-12-12 11:03:00')

3 计算方法

3.1 使用LEAD()函数匹配下次消费时间

这里我们使用LEAD()函数来生成新的一列：下次消费时间。

1
2
LEAD ( scalar_expression , [offset] , [default] )   
    OVER ( partition by [clause1] order by [clause2] )

scalar_expression
要根据指定偏移量返回的值，这里我们选择列用户ID。

offset
指定获取值的当前行前移的行数，如未指定，默认值为1，这里我们设置为1。

default
偏移量超出范围时返回的值，即偏移后无记录返回的值，如未指定默认值，则返回NULL。这里我们设置为NULL。

clause1
指定一个列作为分组依据，这里我们设置为用户ID。

clause2
指定一个列作为数据行的排序，这里我们设置为消费时间。

根据上述参数，我们可以写出以下代码：

1
2
3
4
5
6
SELECT
用户ID,
消费时间,
LEAD(消费时间, 1, NULL) OVER (PARTITION BY 用户ID ORDER BY 消费时间) AS '下次消费时间'
FROM #消费数据
ORDER BY 用户ID,消费时间

以上代码运行后可以得出以下结果：

3.2 使用DATEDIFF()函数计算间隔时间

在下次消费时间列为NULL的记录则为用户第一次消费记录，我们把以上数据结果保存在临时表#消费数据2，再使用DATEDIFF()函数计算间隔时间即可：
注：此处应剔除首次消费的记录。

1
2
3
4
5
6
7
8
SELECT
用户ID,
消费时间,           --非必要列
下次消费时间,       --非必要列
DATEDIFF(DAY, 消费时间, 下次消费时间) AS '两次消费间隔（天）',
DATEDIFF(HOUR, 消费时间, 下次消费时间) AS '两次消费间隔（小时）'
FROM #消费数据2
ORDER BY 用户ID

DATEDIFF()函数计算时间间隔的显示方式有多种，以上只列举了两种，具体请自行查找。

以上代码运行后可以得出以下结果：

在得到两次消费间隔时间后，就可以做后续的用户消费频率等维度的分析了。