如何用SQL统计用户复购（or留存）数据

在工作中，如果涉及到用户交易的数据，我们可能会经常统计每天的新户（用户在当日首次完成交易）交易用户数、新户在次日、T+n日、次周、下下周、在自然月当月、在下月、在下下月等不同的时间周期内的复购数据。

用户在首次交易的日期之后的第n天，再次发生了交易行为，我们称之为用户在第n天的复购。其实，用户在第n天产生了复购行为，也就是用户在第n天之后还是留存的。复购和留存的意思是一样的。这些统计数据其实是非常常见的，所以我在这些常见的复购统计情况做个总结。在这之前，需要先了解一些常见的SQL函数。

统计复购数据涉及的SQL函数

• datediff(date1,date2):返回日期date1和date2之间相差的天数

select datediff('2020-08-13','2020-07-31')
输出：13

• months_between(date1,date2):返回日期date1和date2之间相差的月份数，以小数形式展示。

select months_between('2020-08-13','2020-07-20')
输出：0.77419355

但在实际中，我们一般想要的两个日期之间相差的月份数是一个整数，比如2020-07-20和2020-08-13，相差了一个月，我们希望返回的结果是1，而不是一个很精确的小数。这时可以用round函数来取整。

select   round(months_between('2020-08-13','2020-07-20'))
输出：1.0

• min(trans_date) over(partition by uid):以用户的UID进行分区，计算出该用户最小的交易日期,也就是用户首次交易的日期
• max(trans_date) over(partition by uid):以用户的UID进行分区，计算出该用户最大的交易日期,也就是用户最近一次完成交易的日期

使用SQL统计用户复购数据

假设我们现在有一张全量的用户交易数据表，记录了所有用户在历史所有时期的交易行为，用户交易一次，就产生一条记录。具体的表结构如下：

create table t_user_trans_info(
index_id    int(11) NOT NULL AUTO_INCREMENT COMMENT '自增ID',
trans_date  datetime NOT NULL DEFAULT '1970-01-01 00:00:00' COMMENT '交易日期和时间',
uid         varchar(20) NOT NULL DEFAULT ' ' COMMENT '用户