一般在统计销售情况或者签到情况中会用到连续记录,求解连续记录问题可以使用窗口函数。
【场景】:每个用户连续登录的最大天数、连续登录2天及以上时间的用户及天数
【知识点】:窗口函数、date(登录日期) - row_number() over(partition by 用户ID order by dt)
一、分析思路
1、连续登录如何定义?
将连续登录定义为用户在至少两个不同日期登录到系统。
难点: 如何确定登录记录是连续的,并对连续登录记录打标
2、求解思路
要计算每个用户的连续登录天数,我们可以按照以下步骤进行:
对用户和日期进行去重:首先使用
date()
函数把登录时间转换为日期记为 dt 并去重;标记连续登录记录:用
row_number()
函数对登录日期按照升序顺序排序记为 rk,用并 dt 减去rk得到date_diff, 数据一样的就是连续的记录;根据条件筛选登录记录:例如每个用户最大连续登录天数。
举个例子:如果用户1在4月1号、2号、3号、5号、6号登录;用户2在4月1号、2号、4号登录。那么每个用户的最大连续登录天数是多少?
- 把上面的登录记录按照用户、登录日期、排序、日期-排序整理成下表:
用户 登录日期 排序 日期-排序 用户1 4月1日 1 0 用户1 4月2日 2 0 用户1 4月3日 3 0 用户1 4月5日 4 1 用户1 4月6日 5 1 用户2 4月1日 1 0 用户2 4月2日 2 0 用户2 4月4日 3 1 可以发现日期-排序的数据相同就表示为连续记录。用户1、用户2的最大连续登录天数分别是3,2。
3、MySQL执行步骤如下:
(1)获取去重登录记录表
对用户和日期进行去重:首先使用
date()
函数把登录时间转换为日期记为 dt 并去重;select distinct 用户ID, date(登录日期) as dt from 登录记录表
这样就得到了去重登录记录表。
(2)获取标记好的连续记录表
标记连续登录记录:用
row_number()
函数对登录日期按照升序顺序排序记为 rk,用并 dt 减去rk得到date_diff, 数据一样的就是连续的记录;select 用户ID, date(登录日期) as dt, row_number() over(partition by 用户ID order by dt) as rk, date(登录日期) - row_number() over(partition by 用户ID order by dt) as date_diff from 去重登录记录表
这样就得到了标记好的连续记录表。
(3)筛选登录记录
根据条件筛选登录记录:例如每个用户最大连续登录天数。
select 用户ID, max(连续登录天数) as 最大连续登录天数 from( select 用户ID, count(date_diff) as 连续登录天数, row_number() over(partition by 用户ID order by dt) as rk, date(登录日期) - row_number() over(partition by 用户ID order by dt) as date_diff from 标记好的连续记录表 group 用户ID,date_diff ) group 用户ID
二、实例
(1)连续2天及以上登录用户的登录天数
数据来自:SQL167 连续签到领金币
问题:统计连续2天及以上购物的用户及其对应的次数(若有多个用户,按user_id升序排序)
示例:用户行为日志表tb_user_log如下(id:主键,user_id:用户ID,login_time:登录时间,sign_in:是否签到):
id | user_id | login_time | sign_in |
---|---|---|---|
1 | 101 | 2021-07-07 10:00:00 | 1 |
2 | 101 | 2021-07-08 10:00:00 | 1 |
3 | 101 | 2021-07-09 10:00:00 | 1 |
4 | 101 | 2021-07-10 10:00:00 | 1 |
5 | 101 | 2021-07-11 23:59:55 | 1 |
6 | 101 | 2021-07-12 10:00:28 | 1 |
7 | 101 | 2021-07-13 10:00:28 | 1 |
8 | 102 | 2021-10-01 10:00:28 | 1 |
9 | 102 | 2021-10-02 10:00:01 | 1 |
10 | 102 | 2021-10-03 10:00:55 | 1 |
11 | 102 | 2021-10-04 10:00:45 | 0 |
12 | 102 | 2021-10-05 10:00:53 | 1 |
13 | 102 | 2021-10-06 10:00:45 | 0 |
14 | 102 | 2021-10-06 11:00:45 | 1 |
根据示例,你的查询应返回以下结果:
user_id | days_count |
---|---|
10 | 2 |
求解代码:
with
main as(
#对用户、日期进行去重
select distinct
date(login_time) as dt,
user_id
from tb_user_log
)
,main1 as(
#统计日期、用户、日期减去排序的值(连续签到)
select
dt,
user_id,
dt - row_number() over(partition by user_id order by dt) as date_diff
from main
)
#连续2天及以上登录的用户及连续天数
select distinct
user_id,
count(date_diff) as days_count
from main1
group by user_id having count(date_diff) >= 2
- 对用户和登录日期去重的记录
dt user_id
2021-07-07 101
2021-07-08 101
2021-07-09 101
2021-07-10 101
2021-07-11 101
2021-07-12 101
2021-07-13 101
2021-10-01 102
2021-10-02 102
2021-10-03 102
2021-10-04 102
2021-10-05 102
2021-10-06 102
- 统计日期、用户、日期减去排序的值(连续签到)
dt user_id date_diff
2021-07-07 101 20210706
2021-07-08 101 20210706
2021-07-09 101 20210706
2021-07-10 101 20210706
2021-07-11 101 20210706
2021-07-12 101 20210706
2021-07-13 101 20210706
2021-10-01 102 20211000
2021-10-02 102 20211000
2021-10-03 102 20211000
2021-10-04 102 20211000
2021-10-05 102 20211000
2021-10-06 102 20211000
- 连续2天及以上登录的用户及连续天数
user_id days_count
101 7
102 6
(2)某个时刻的在线人数
案例来自:SQL167 连续签到领金币
使用上述数据。
问题:计算每个用户2021年7月以来每月获得的金币数(该活动到10月底结束,11月1日开始的签到不再获得金币)。结果按月份、ID升序排序。
场景逻辑说明:
- 从2021年7月7日0点开始,用户每天签到可以领1金币,并可以开始累积签到天数,连续签到的第3、7天分别可额外领2、6金币。
- 每连续签到7天后重新累积签到天数(即重置签到天数:连续第8天签到时记为新的一轮签到的第一天,领1金币)
示例数据的输出结果如下:
user_id | login_month | coin |
---|---|---|
101 | 202107 | 15 |
102 | 202110 | 7 |
解释:
101在活动期内连续签到了7天,因此获得1*7+2+6=15金币;
102在10.01~10.03连续签到3天获得5金币
10.04断签了,10.05~10.06连续签到2天获得2金币,共得到7金币。文章来源:https://www.toymoban.com/news/detail-415919.html
求解代码:
with
temp as(
#统计对用户、签到日期去重后的记录
select distinct
user_id,
date_format(login_time,'%Y%m') as login_month,
date(login_time) as login_date
from tb_user_log
where sign_in = 1
and date(login_time) between '2021-07-07' and '2021-10-31'
)
,temp1 as(
#统计用户签到的月份、日期、按用户分组对日期排序、日期减去排序的值
select
user_id,
login_month,
login_date,
row_number() over (partition by user_id order by login_date) as rk,
login_date - row_number() over (partition by user_id order by login_date) as dt_diff
from temp
)
,temp2 as(
#统计每一行根据用户、日期减去排序的值分组排序的值
select
*,
row_number() over(partition by user_id,dt_diff order by login_date) as diff_rk
from temp1
)
#对每一行取余来判断是否额外加2或者额外加6;取余也解决了7天之后重置的情况
select
user_id,
login_month,
sum(case
when diff_rk%7=0 then 7
when diff_rk%7=3 then 3
else 1
end) as coin
from temp2
group by user_id,login_month
order by login_month,user_id
- 对用户、签到日期去重后,统计用户签到的月份、日期、按用户分组对日期排序、日期减去排序的值
user_id login_month login_date rk dt_diff
101 202107 2021-07-07 1 20210706
101 202107 2021-07-08 2 20210706
101 202107 2021-07-09 3 20210706
101 202107 2021-07-10 4 20210706
101 202107 2021-07-11 5 20210706
101 202107 2021-07-12 6 20210706
101 202107 2021-07-13 7 20210706
102 202110 2021-10-01 1 20211000
102 202110 2021-10-02 2 20211000
102 202110 2021-10-03 3 20211000
102 202110 2021-10-05 4 20211001
102 202110 2021-10-06 5 20211001
- #统计每一行根据用户、日期减去排序的值分组排序的值
user_id login_month login_date rk dt_diff diff_rk
101 202107 2021-07-07 1 20210706 1
101 202107 2021-07-08 2 20210706 2
101 202107 2021-07-09 3 20210706 3
101 202107 2021-07-10 4 20210706 4
101 202107 2021-07-11 5 20210706 5
101 202107 2021-07-12 6 20210706 6
101 202107 2021-07-13 7 20210706 7
102 202110 2021-10-01 1 20211000 1
102 202110 2021-10-02 2 20211000 2
102 202110 2021-10-03 3 20211000 3
102 202110 2021-10-05 4 20211001 1
102 202110 2021-10-06 5 20211001 2
- 对每一行取余来判断是否额外加2或者额外加6;取余也解决了7天之后重置的情况
user_id login_month coin
101 202107 15
102 202110 7
扩展:
前往查看:MySQL 窗口函数文章来源地址https://www.toymoban.com/news/detail-415919.html
到了这里,关于MySQL 连续记录 场景分析的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!