SQL 统计日活、周活指标

白日梦想家

发布于 2020-11-26 11:08:00

2.7K0

发布于 2020-11-26 11:08:00

文章被收录于专栏：SQL实现SQL实现

有一张用户访问记录表 user_trace，它记录了每个用户每次在APP上打开的页面和操作时间。

user_trace 的表结构及部分数据如下：

CREATE TABLE `user_trace` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL COMMENT '用户ID',
  `page` varchar(127) NOT NULL COMMENT '访问页面',
  `create_ts` timestamp NOT NULL COMMENT '操作时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

    id  user_id  page                      create_ts  
------  -------  --------------  ---------------------
     1  1000000  /product/view     2020-11-01 11:40:32
     2  1000002  /home/banner      2020-11-04 10:12:32
     3  1000001  /shop/23456       2020-11-06 21:12:32
     4  1000002  /card/list        2020-11-08 11:20:32
     5  1000001  /home/welcome     2020-11-09 09:08:32
     6  1000002  /product/list     2020-11-10 23:52:32
     7  1000003  /product/1111     2020-11-11 17:40:32
     8  1000002  /home/banner      2020-11-11 19:42:32
     9  1000003  /product/list     2020-11-11 20:52:32
    10  1000002  /product/12345    2020-11-14 02:30:32
    11  1000003  /card/list        2020-11-14 12:56:32
    12  1000002  /shop/123456      2020-11-15 19:10:32
    13  1000003  /product/list     2020-11-15 23:04:32
    14  1000001  /product/view     2020-11-16 19:23:32

我们根据这张表，统计过去一段时间内每天的日活（每日活跃用户数）、周活指标。

先来看日活的定义：每天打开APP的用户数。

假设我们要统计的是 2020.11.10 ~ 2020.11.16 这段时间的日活、周活。

由于需要统计的是用户数，而不是访问次数，因此，我们先根据用户ID和访问日期去重，再统计每天的访问用户数。

SELECT  
  COUNT(DISTINCT user_id) AS user_cnt,
  DATE(create_ts) AS view_day 
FROM
  user_trace 
GROUP BY DATE(create_ts);


user_cnt  view_day    
--------  ------------
       1  2020-11-01  
       1  2020-11-04  
       1  2020-11-06  
       1  2020-11-08  
       1  2020-11-09  
       1  2020-11-10  
       2  2020-11-11  
       2  2020-11-14  
       2  2020-11-15  
       1  2020-11-16

我们只需要统计 2020.11.10 ~ 2020.11.16 这段时间的日活，从上面的结果看，这段时间内有些天并没有用户访问，因此，我们需要枚举出这段时间的所有日期。

使用 CTE 的递归方式可生成我们需要的日期。

WITH recursive full_day(bizdate) AS 
(SELECT 
  '2020-11-10' AS bizdate
UNION ALL 
SELECT 
  DATE_ADD(bizdate,INTERVAL 1 DAY)
FROM
  full_day 
WHERE bizdate < '2020-11-16') 
SELECT 
  *
FROM
  cte ;
  

bizdate     
------------
2020-11-10  
2020-11-11  
2020-11-12  
2020-11-13  
2020-11-14  
2020-11-15  
2020-11-16

将日期结果集左连接每天的访问用户数结果集就可以获取到这段时间的日活。

WITH recursive full_day (bizdate) AS 
(SELECT 
  '2020-11-10' AS bizdate 
UNION ALL 
SELECT 
  DATE_ADD(bizdate, INTERVAL 1 DAY) 
FROM
  full_day 
WHERE bizdate < '2020-11-16'),
ua_1d AS 
(SELECT 
  COUNT(DISTINCT user_id) AS user_cnt,
  DATE(create_ts) AS view_day 
FROM
  user_trace 
GROUP BY DATE(create_ts)) 

SELECT 
  a.bizdate,
  IFNULL(b.user_cnt,0) AS user_cnt 
FROM
  full_day a 
  LEFT JOIN ua_1d b 
    ON b.view_day = a.bizdate

2020.11.10 ~ 2020.11.16 这段时间的日活数据>>>

bizdate     user_cnt  
----------  ----------
2020-11-10           1
2020-11-11           2
2020-11-12           0
2020-11-13           0
2020-11-14           2
2020-11-15           2
2020-11-16           1

再来看下统计周活的规则。如果要统计 2020.11.10 这一天的周活，那要计算的应该是从 2020.11.04 ~ 2020.11.10 这一周的访问人数。同理，要统计 2020.11.11 的周活，计算的范围就应该是从 2020.11.05 ~ 2020.11.11 。

有一种解决方案是用标量子查询，将当前的日期传入到子查询中，在子查询里面统计当前日期过去一周的日活。请看下面的 SQL 实现：

SELECT 
  DATE(create_ts) AS view_day,
  (SELECT 
    COUNT(DISTINCT user_id) 
  FROM
    user_trace b 
  WHERE b.create_ts >= DATE_SUB(
    DATE(a.create_ts), INTERVAL 6 DAY) 
    AND b.create_ts < DATE_ADD(
    DATE(a.create_ts), INTERVAL 1 DAY)
  ) AS user_cnt 
FROM
  user_trace a 
WHERE DATE(create_ts) BETWEEN '2020-11-10' 
  AND '2020-11-16' 
  

view_day    user_cnt  
----------  ----------
2020-11-10           2
2020-11-11           3
2020-11-11           3
2020-11-11           3
2020-11-14           3
2020-11-14           3
2020-11-15           3
2020-11-15           3
2020-11-16           3

不要被上面的子查询语句给吓到了，它只是在过滤条件那进行日期计算比较繁琐，真正的逻辑还是很简单的。

从查询结果来看，使用标量子查询的确是一种行得通的解决方案。不过，这还不是最终结果，因为我们还没有做去重处理和补全日期的操作。

再来看统计周活的最终的 SQL 。

WITH recursive full_day (bizdate) AS 
(SELECT 
  '2020-11-10' AS bizdate 
UNION
ALL 
SELECT 
  DATE_ADD(bizdate, INTERVAL 1 DAY) 
FROM
  full_day 
WHERE bizdate < '2020-11-16') 
SELECT 
  bizdate,
  (SELECT 
    COUNT(DISTINCT user_id) 
  FROM
    user_trace b 
  WHERE b.create_ts >= DATE_SUB(
    bizdate, INTERVAL 6 DAY) 
    AND b.create_ts < DATE_ADD(
    bizdate, INTERVAL 1 DAY)
  ) AS user_cnt 
FROM
  full_day a

2020.11.10 ~ 2020.11.16 这段时间的周活数据：

bizdate     user_cnt  
----------  ----------
2020-11-10           2
2020-11-11           3
2020-11-12           3
2020-11-13           3
2020-11-14           3
2020-11-15           3
2020-11-16           3

如果要把日活和周活合计到一块显示，那 SQL 可以这样写：

WITH recursive full_day (bizdate) AS 
(SELECT 
  '2020-11-10' AS bizdate 
UNION
ALL 
SELECT 
  DATE_ADD(bizdate, INTERVAL 1 DAY) 
FROM
  full_day 
WHERE bizdate < '2020-11-16'),
ua_1d AS 
(SELECT 
  COUNT(DISTINCT user_id) AS user_cnt,
  DATE(create_ts) AS view_day 
FROM
  user_trace 
GROUP BY DATE(create_ts))
SELECT 
  bizdate,
  IFNULL(b.user_cnt,0) AS ua_1d,
  (SELECT 
    COUNT(DISTINCT user_id) 
  FROM
    user_trace b 
  WHERE b.create_ts >= DATE_SUB(
    bizdate, INTERVAL 6 DAY) 
    AND b.create_ts < DATE_ADD(
    bizdate, INTERVAL 1 DAY)
  ) AS ua_7d 
FROM
  full_day a 
LEFT JOIN 
  ua_1d b 
ON b.view_day = a.bizdate

合并的结果展示>>>

bizdate      ua_1d   ua_7d  
----------  ------  --------
2020-11-10       1         2
2020-11-11       2         3
2020-11-12       0         3
2020-11-13       0         3
2020-11-14       2         3
2020-11-15       2         3
2020-11-16       1         3

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2020-11-19，如有侵权请联系 cloudcommunity@tencent.com 删除

sql

本文分享自 SQL实现微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

sql

登录后参与评论

0 条评论

热度

SQL 统计日活、周活指标

SQL 统计日活、周活指标

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐