SQL之统计活动前后新用户留存及下单次数

数分小白龙 2021-12-07

1735

前几天有个运营同事问到一个问题，她们最近在上海（假设）新开了一个活动，想看下这个活动前后新用户的留存问题，从11月3号开始，以7天作为标准，统计新用户从首次下单当天往后7天内的平均下单次数，比如11月3号这天，就要统计11月3号到11月9号这7天内所有新用户的平均下单次数。

首先拿到这个需求，可能涉及到的表主要包含：订单表，城市表(如果订单表没有城市中文名称，只有城市code的话需要关联)，应该差不多就用到这三个表。表的字段有哪些呢，根据需求我简单理了理涉及的字段，如表格所示。

订单表(部分数据) order

createtime	userid	serialid	citycode
2021/12/01 06:32:10	ohmdTt3o2K9fcF	211007190919	411502
2021/12/03 14:12:43	4wbOZCy4ZnqUj	211007190201	341832
2021/12/05 20:50:21	8sdwQda3POdza	211007192105	642213

城市表(部分数据) city

citycode	cityname
411502	上海
341832	深圳

来找我之前呢，她自己写了段代码，让我来看下思路，代码如下，小伙伴们可以看看有没有什么问题呢？

select y.firstdate, AVG(x.orders) orders_mean
from 
(
    select userid, count(0) orders
    from order a
    left join city b on a.citycode = b.citycode
    where date(createtime) >= '2021-11-03' 
    and date(createtime) < '2021-11-10' 
    and b.cityname = '上海市'
    group by userid
) x
left join 
(
    select  userid, min(date(createtime)) firstdate
    from order
    group by userid
) y on x.userid = b.userid
where date(y.firstdate) = '2021-11-03' 
group by y.firstdate;

刚看到这段代码时，不太熟悉sql的话，可能会有点错觉，就是left join两侧的x和y表，为什么会使用left join呢，其实这里采用内连接也就是join来写也是ok的。这位运营姐姐写的代码总体是没有问题的，就是求得11月3号这一天首次下单的新用户再后面7天内依旧下单的平均次数。但是有个问题就是，她这段代码只能求11月3号的，如果想求11月4号呢？这就需要改下代码里的三个日期。如果想看11月3号到11月10号的呢？如果想要看每天实时更新的呢？是不是需要每次改一遍运行一遍呢？这样显然是很费时间的，有没有什么简便方法呢？

答案是：有。需求里谈到7天内留存次数，当首次下单日期更改时，7天的范围就会变化，比如11月3号需要统计从11月3号到11月10号平均每个用户的下单次数，比如11月4号就需要统计从11月4号到11月11号的数据，那么这个动态查询如何实现呢？这里就用到了一个函数，就是date_add()函数。

整体思路就是：先查询每个用户的首次下单日期，然后关联订单表，根据首次下单日期和用户userid进行分组，并判断订单表中创单日期是否在首次下单日期和首次下单日期+7天这个范围内，如果在的话，就统计订单数。最后，再根据首次下单日期，统计新用户数，订单数以及每个用户在7天内的平均下单次数。

最终实现的代码如下：

select firstdate
       ,sum(ord) orders
       ,count(distinct userid) users
       ,sum(orders)/count(distinct userid) orders_mean
from
(
     select b.firstdate
            ,a.userid
            ,count(case when a.createdate >= b.firstdate and a.createdate < date_add(b.firstdate, 7) then a.serial else null end) ord
    from 
    (
        select date(createtime) createdate, userid, serial
        from order a
        left join city b on a.citycode = b.citycode
        where b.cityname = '上海市'
    )a
    left join 
    (
        select userid, min(date(createtime)) firstdate
        from order
        group by userid
    )b on a.userid = b.userid
    where firstdate >= '2021-11-03'
    group by firstdate, a.userid
) x
group by firstdate

好了，本篇文章主要谈了谈如何统计首次下单的新用户再后7天内的平均下单次数，难点主要在于date_add()函数，之前我也没怎么用到过，也是通过查询资料发现的，特意记录下这个知识点。

下一篇会谈一谈这两天遇到的留存问题，之前写了一个统计用户留存的文章，但是那个思路可能存在一定小问题，这里先抛出一个例子，假设一个用户在3号访问的，然后5号也访问该网站，6号也访问该网站，那么这个用户是统计到2日留存呢，还是3日留存呢，还是两个指标都需要统计呢？小伙伴们可以思考思考，下一篇文章会详细讲讲这种情况如何处理。

END

龙小仔

数据库

文章转载自数分小白龙，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

SQL之统计活动前后新用户留存及下单次数

评论