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)。

如有侵权，请联系 support@oschina.cn 删除。

本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。