MySQL题库

VQ1 统计自然周平均登录次数

题目描述

  • 现有用户登录日志表user_login_tb(id 自增主键, uid 用户ID, login_date 登录日期)。 示例数据如下：

img

计算每个自然周内（周一到周日算一个自然周）所有用户的平均登录次数，用户可能在一天内登录多次。结果按每一周的周一升序排序，平均次数保留两位小数。

示例数据对应输出为：

img

思路解析

用库函数WEEKDAY得到每个日期是星期几，然后使用DATE_SUB转换到当周周一的日期，再按周一日期分组，计算每个每组内用户登录总数（count(id)）和用户数（count(distinct uid)），总次数除以总人数就是平均登录次数。保留小数位数用round()。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT
	DATE_SUB(
		login_date,
		INTERVAL WEEKDAY(login_date) DAY
	) AS week_begin,
	ROUND(
		COUNT(id) / COUNT(DISTINCT(uid)),
		2
	) AS avg_times
FROM
	user_login_tb
GROUP BY
	week_begin
ORDER BY
	week_begin

关键知识点

  • WEEKDAY(date): 返回周几，周1返回0，周2返回1。。。周日返回6
  • DATE_SUB(date, INTERVAL expr type): 从日期减去指定的时间间隔
  • COUNT(DISTINCT(列名)): 记录去重后计数
  • ROUND(x, y): 对参数x进行四舍五入的操作，返回值保留小数点后面指定的y位。