题解 | #平均活跃天数和月活人数#
思路
这道题的关键点:
- 日期格式函数
date_format
- 格式函数
round
- 正确理解:
- 平均活跃天数怎么算?
平均活跃天数=
活跃的总天数
/ 活跃人数 活跃人数
怎么算?
所以关键点就是算出活跃的总天数
和活跃人数
步骤
- 首先将数据划分成2021年的月内数据:分组
- 分别计算:
- 活跃人数
count(distinct uid)
- 活跃的总天数
count(distinct uid, date_format (submit_time, '%y%m%d'))
会将相同的 uid 和日期视为一个单独的项,然后进行记数。这就意味着它会返回所有用户的活跃天数之和
,即活跃的总天数
,而不包含重复的值。
题解
select
date_format (submit_time, '%Y%m') as month,
round(
(
count(distinct uid, date_format (submit_time, '%y%m%d'))
) / count(distinct uid),
2
) as avg_active_days,
count(distinct uid) as mau
from
exam_record
where
submit_time is not null
and year (submit_time) = 2021
group by
date_format (submit_time, '%Y%m')