专栏首页SQL实现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

本文分享自微信公众号 - SQL实现(gh_684ee9235a26),作者:zero

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-11-19

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 如何用Redis HyperLogLog统计日活月活?

    HyperLogLog 是一种概率数据结构,用来估算数据的基数。数据集可以是网站访客的 IP 地址,E-mail 邮箱或者用户 ID。

    Bug开发工程师
  • 用户日活月活怎么统计 - Redis HyperLogLog 详解

    HyperLogLog 是一种概率数据结构,用来估算数据的基数。数据集可以是网站访客的 IP 地址,E-mail 邮箱或者用户 ID。

    程序员历小冰
  • 用户日活月活怎么统计 - Redis HyperLogLog 详解

    HyperLogLog 是一种概率数据结构,用来估算数据的基数。数据集可以是网站访客的 IP 地址,E-mail 邮箱或者用户 ID。

    程序员历小冰
  • SQL 统计实际的活动天数

    有一张促销活动表 promotion,它记录了各品牌进行促销活动的开始时间和结束时间,同一个品牌在某段时间内可能会参与多次促销活动,即同一个品牌的活动时间范围可...

    白日梦想家
  • 一场pandas与SQL的巅峰大战(六)

    具体来讲,第一篇文章一场pandas与SQL的巅峰大战涉及到数据查看,去重计数,条件选择,合并连接,分组排序等操作。

    超哥的杂货铺
  • 链家面试题:如何分析留存率?

    手机中的相机是深受大家喜爱的应用之一,下图是某手机厂商数据库中的用户行为信息表中部分数据的截图。

    猴子聊数据分析
  • SQL数据分析淘宝用户分析实操

    常见的数据清洗,预处理,数据分类,数据筛选,分类汇总,以及数据透视等操作,用SQL一样可以实现(除了可视化,需要放到Excel里呈现)。SQL不仅可以从数据库中...

    1480
  • “提需求的”与“拉SQL的”如何有效沟通

    这个题目改了又改,最终还是感觉这样写更具有可读性,毕竟一篇文章最终还是以能够读懂为初衷的,现阶段,各个公司里科技开发、数据编制成为必不可少的岗位,技术和业务的沟...

    Python数据科学
  • 一种计算用户留存的方法

    用户留存分析是互联网时代常用的一种数据分析方法。而很多快速发展的公司并没有相应的方法论沉淀,这就导致了在计算用户留存的时候会出现下面的一些问题:1)用户留存的定...

    木东居士
  • UAVStack的慢SQL数据库监控功能及其实现

    UAVStack是一个全维监控与应用运维平台。UAV.Monitor具备监控功能,包含基础监控、应用/服务性能监控、日志监控、业务监控等。在应用监控中,UAV可...

    宜信技术学院
  • 微博基于ClickHouse灵活监控百亿流量下的业务指标

    朱伟,微博广告SRE团队负责人,《智能运维:从0搭建大规模分布式AIOps系统》作者之一。目前负责微博广告业务可用性的保障与优化、资源利用率的提升、监控报警系统...

    Fayson
  • 同期群分析解读用户生命周期,剖析真实用户行为和价值

    漂亮的平均数并不是数据分析的最优解,只是用数据造出来的虚幻景象,会运营决策造成误导。数据不会说谎,只是做数据的人没有做到精准的分析而导致对数据呈现的错误解读。在...

    数据万花筒
  • 王者荣耀大数据运营总结

    围绕王者荣耀大数据运营,依托对局日志和好友关系,开展了王者周报、赛季总结和周年庆活动等项目。这些案例中,遇到了哪些挑战?

    LemonLu
  • 一个执行计划异常变更的案例 - 外传之AWR

    之前的几篇文章: 《一个执行计划异常变更的案例 - 前传》 《一个执行计划异常变更的案例 - 外传之绑定变量窥探》 《一个执行计划异常变更的案例 -...

    bisal
  • 系列文章一:精选大数据面试真题10道(混合型)-附答案详细解析

    大数据笔面试系列文章分为两种类型:混合型(即一篇文章中会有多个框架的知识点—融会贯通);专项型(一篇文章针对某个框架进行深入解析—专项演练)。

    五分钟学大数据
  • 模板银行 | 点击获取模板监控MySQL、PostgreSQL、Hadoop、ES数据库

    本MySQL模板采集数据使用mysqladmin/mysql命令连接数据库,并将获取的数据写入本地文件,然后通过Zabbix agent(active)方式获取...

    Zabbix
  • 一周极客热文:微软将推新编程语言M#:系统编程级别的C#

    据国外媒体VB报道,微软正在开发一款新的编程语言:M# 。它在C#的基础上添加了系统编程特性,可用来构建各种类型的应用,尤其是云计算应用。 微软员工Joe Du...

    钱曙光
  • 技能 | 如何轻松搞定APP数据分析?

    《三个要点解构数据分析的思维模式》提到——为什么要数据分析?APP数据分析有意义吗?当然!数据分析的用意本不在于数据本身,而是要打造一个数据反馈闭环。设计基...

    灯塔大数据
  • 高级SQL查询技巧——利用SQL改善和增强你的数据

    关系数据库系统和混合/云数据管理解决方案的用户都可以使用SQL灵活地访问业务数据,并以创新的方式进行转换或显示。

    云原生

扫码关注云+社区

领取腾讯云代金券