- 微信
- 微博
  
  分享文章到微博
- 复制链接
  
  复制链接到剪贴板

对比MySQL和Pandas计算用户最大连续登录天数

小小明-代码实体发表于 2022/04/12 22:40:28 2022/04/12

【摘要】数据来源于道才的文章：《用SQL计算用户连续登录天数》需求如下：计算每一个用户的最大连续登录天数，由左变换到右边。 MySQL8.0窗口函数实现实现思路：对用户ID和登录日期去重对每...

数据来源于道才的文章：《用SQL计算用户连续登录天数》

需求如下：

计算每一个用户的最大连续登录天数，由左变换到右边。

MySQL8.0窗口函数实现

实现思路：

对用户ID和登录日期去重
对每个用户ID按照日期顺序进行编号
将登录日期减去编号对应的天数，使连续的日期转换为同一天

将连续日期转换为同一个日期之后就可以按照这个字段分组，后面就简单了。下面我们一步步看：

对用户ID和登录日期去重：

SELECT DISTINCT role_id,$part_date `date` FROM role_login;

  
 
  1

对每个用户ID按照日期顺序进行编号，并将登录日期减去该编号对应的天数（可以一步到位）：

SELECT
	role_id,`date`,
	DATE_SUB(`date`,INTERVAL (row_number() OVER(PARTITION BY role_id ORDER BY `date`)) DAY) data_group
FROM(
	SELECT DISTINCT role_id,$part_date `date` FROM role_login
) a;

  
 
  1
  2
  3
  4
  5
  6

从结果我们可以看到已经成功的使连续的日期都转换到同一天。

然后我们就可以基于该结果统计每个用户的所有连续日期段：

SELECT
	role_id,
	MIN(DATE) begin_date,
	MAX(DATE) end_date,
	COUNT(*) continuous_days
FROM(
	SELECT
		role_id,`date`,
		DATE_SUB(`date`,INTERVAL (row_number() OVER(PARTITION BY role_id ORDER BY `date`)) DAY) data_group
	FROM(
		SELECT DISTINCT role_id,$part_date `date` FROM role_login
	) a
) b
GROUP BY role_id,data_group;

  
 
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14

结果：

然后通过窗口函数标注每个用户的连续日期排名：

SELECT
	role_id,begin_date,end_date,
	continuous_days ,
	row_number() OVER (PARTITION BY role_id ORDER BY continuous_days DESC) rk
FROM(
	SELECT
		role_id,
		MIN(DATE) begin_date,
		MAX(DATE) end_date,
		COUNT(*) continuous_days
	FROM(
		SELECT
			role_id,`date`,
			DATE_SUB(`date`,INTERVAL (row_number() OVER(PARTITION BY role_id ORDER BY `date`)) DAY) data_group
		FROM(
			SELECT DISTINCT role_id,$part_date `date` FROM role_login
		) a
	) b
	GROUP BY role_id,data_group
) c;

  
 
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20

注意：有时同一个用户的最大连续日期可能存在多个，这里以第一个为准；如果需要获取全部的最大日期可以使用rank或dense_rank窗口函数，可以保证天数一致时排名一致。

SELECT
	role_id,begin_date,end_date,continuous_days max_continuous_days
FROM(
	SELECT
		role_id,begin_date,end_date,
		continuous_days ,
		row_number() OVER (PARTITION BY role_id ORDER BY continuous_days DESC) rk
	FROM(
		SELECT
			role_id,
			MIN(DATE) begin_date,
			MAX(DATE) end_date,
			COUNT(*) continuous_days
		FROM(
			SELECT
				role_id,`date`,
				DATE_SUB(`date`,INTERVAL (row_number() OVER(PARTITION BY role_id ORDER BY `date`)) DAY) data_group
			FROM(
				SELECT DISTINCT role_id,$part_date `date` FROM role_login
			) a
		) b
		GROUP BY role_id,data_group
	) c
) d
WHERE rk=1
ORDER BY max_continuous_days DESC,role_id;

  
 
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26

成功得到结果：

MySQL变量实现(5.0也可以使用)

在MySQL5.0中并没有窗口函数，但是我们可以通过变量来实现。

思路仍然与上述相同，首先我们对数据集去重并排序：

SELECT DISTINCT role_id,$part_date `date` FROM role_login
ORDER BY role_id,$part_date;

  
 
  1
  2

然后利用变量逐行扫描数据集：

SELECT
	role_id,`date`,
	IF(DATE_ADD(`date`,INTERVAL -1 DAY)=@prev_date,@r,@r:=@r+1) group_id, -- 日期变化大于1天（不连续）改变r值
	@prev_date:=`date` -- 记录前一条记录的日期
FROM(
	SELECT DISTINCT role_id,$part_date `date` FROM role_login
	ORDER BY role_id,$part_date
) a,(SELECT @prev_date:=NULL,@r:=0) t;

  
 
  1
  2
  3
  4
  5
  6
  7
  8

从结果可以看到，对于每个用户下连续的日期都给出了完全相同的分组编号：

然后就可以计算连续天数了：

SELECT
	role_id,
	MIN(DATE) begin_date,
	MAX(DATE) end_date,
	COUNT(*) continuous_days
FROM(
	SELECT
		role_id,`date`,
		IF(DATE_ADD(@prev_date,INTERVAL 1 DAY)=`date`,@r,@r:=@r+1) group_id, -- 日期变化大于1天（不连续）改变r值
		@prev_date:=`date` -- 记录前一条记录的日期
	FROM(
		SELECT DISTINCT role_id,$part_date `date` FROM role_login
		ORDER BY role_id,$part_date
	) a,(SELECT @prev_date:=NULL,@r:=0) t
) b
GROUP BY role_id,group_id;

  
 
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16

然后借助变量标注排名：

SELECT
	role_id,begin_date,end_date,continuous_days ,
	IF(@prev_id=role_id,@r2:=@r2+1,@r2:=1) rk,
	@prev_id:=role_id -- 记录前一条记录的用户ID
FROM(
	SELECT
		role_id,
		MIN(DATE) begin_date,
		MAX(DATE) end_date,
		COUNT(*) continuous_days
	FROM(
		SELECT
			role_id,`date`,
			IF(DATE_ADD(@prev_date,INTERVAL 1 DAY)=`date`,@r,@r:=@r+1) group_id, -- 日期变化大于1天（不连续）改变r值
			@prev_date:=`date` -- 记录前一条记录的日期
		FROM(
			SELECT DISTINCT role_id,$part_date `date` FROM role_login
			ORDER BY role_id,$part_date
		) a,(SELECT @prev_date:=NULL,@r:=0) t1
	) b
	GROUP BY role_id,group_id
) c,(SELECT @prev_id:=NULL,@r2:=0) t2
ORDER BY role_id,continuous_days DESC;

  
 
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23

可以看到变量已经成功实现了row_number的效果。

最后再进行一次过滤即可：

SELECT
	role_id,begin_date,end_date,continuous_days max_continuous_days
FROM(
	SELECT
		role_id,begin_date,end_date,continuous_days ,
		IF(@prev_id=role_id,@r2:=@r2+1,@r2:=1) rk,
		@prev_id:=role_id -- 记录前一条记录的用户ID
	FROM(
		SELECT
			role_id,
			MIN(DATE) begin_date,
			MAX(DATE) end_date,
			COUNT(*) continuous_days
		FROM(
			SELECT
				role_id,`date`,
				IF(DATE_ADD(@prev_date,INTERVAL 1 DAY)=`date`,@r,@r:=@r+1) group_id, -- 日期变化大于1天（不连续）改变r值
				@prev_date:=`date` -- 记录前一条记录的日期
			FROM(
				SELECT DISTINCT role_id,$part_date `date` FROM role_login
				ORDER BY role_id,$part_date
			) a,(SELECT @prev_date:=NULL,@r:=0) t1
		) b
		GROUP BY role_id,group_id
	) c,(SELECT @prev_id:=NULL,@r2:=0) t2
	ORDER BY role_id,continuous_days DESC
) d
WHERE rk=1
ORDER BY max_continuous_days DESC,role_id;

  
 
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  27
  28
  29

成功得到结果：

Pandas实现

下面我们用MySQL窗口函数的实现思路，用pandas实现一遍。

首先读取数据集并去重：

import pandas as pd

df = pd.read_excel("role_login.xlsx")
df = df[["role_id", "$part_date"]].drop_duplicates()
df

  
 
  1
  2
  3
  4
  5

对每个用户ID按照日期顺序进行编号，并将登录日期减去该编号对应的天数：

data_group = df["$part_date"]-pd.to_timedelta(df.groupby("role_id")["$part_date"].rank(method="dense"), unit='d')

  
 
  1

统计每个用户的所有连续日期段：

data_group = df["$part_date"]-pd.to_timedelta(df.groupby("role_id")["$part_date"].rank(method="dense"), unit='d')
df = df.groupby(["role_id", data_group], as_index=False).agg(
    begin_date=("$part_date", "min"),
    end_date=("$part_date", "max"),
    max_continuous_days=("$part_date", "count")
)
df

  
 
  1
  2
  3
  4
  5
  6
  7

然后我们过滤出每个用户的最大连续日期：

ids = df.groupby("role_id")["max_continuous_days"].idxmax()
r1 = df.loc[ids].sort_values(
    ["max_continuous_days", "role_id"], ascending=[False, True])
r1

  
 
  1
  2
  3
  4

按照窗口函数的思路代码如下：

mask=df.groupby("role_id")["max_continuous_days"].rank(method="first", ascending=False) == 1
r2 = df[mask].sort_values(["max_continuous_days", "role_id"], ascending=[False, True])
r2

  
 
  1
  2
  3

两种思路的结果一致：

(r1 == r2).all()

  
 
  1

role_id                True
begin_date             True
end_date               True
max_continuous_days    True
dtype: bool

  
 
  1
  2
  3
  4
  5

但是窗口函数的思路相对idxmax快了近百倍：

所以针对这取分组最大的问题还是使用rank函数效果更高一些。

RANK 和 DENSE_RANK的差异

本题在一个用户存在多个最大连续日期时只要求取第一个，如果需要取每个用户所有的最大连续日期，则需要使用rank或dense_rank窗口函数。

首先我们看看pandas中rank函数的几种method的差异：

import pandas as pd

t1 = pd.DataFrame(data={'num': [2, 4, 4, 8, 8]})
t1['default_rank'] = t1['num'].rank()
t1['min_rank'] = t1['num'].rank(method='min')
t1['max_rank'] = t1['num'].rank(method='max')
t1['dense_rank'] = t1['num'].rank(method='dense')
t1['first_rank'] = t1['num'].rank(method='first')
t1

  
 
  1
  2
  3
  4
  5
  6
  7
  8
  9

结果：

	num	default_rank	min_rank	max_rank	dense_rank	first_rank
0	2	1.0	1.0	1.0	1.0	1.0
1	4	2.5	2.0	3.0	2.0	2.0
2	4	2.5	2.0	3.0	2.0	3.0
3	8	4.5	4.0	5.0	3.0	4.0
4	8	4.5	4.0	5.0	3.0	5.0

可以看到MySQL的rank函数相当于pandas的min_rank，row_number相当于first_rank：

SELECT
    cookieid,
    createtime,
    pv,
    RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn1,
    DENSE_RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn2,
    ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn3
FROM xxm_t2
WHERE cookieid = 'cookie1';

  
 
  1
  2
  3
  4
  5
  6
  7
  8
  9

cookieid day           pv       rn1     rn2     rn3
-------------------------------------------------- 
cookie1 2022-04-12      7       1       1       1
cookie1 2022-04-11      5       2       2       2
cookie1 2022-04-15      4       3       3       3
cookie1 2022-04-16      4       3       3       4
cookie1 2022-04-13      3       5       4       5
cookie1 2022-04-14      2       6       5       6
cookie1 2022-04-10      1       7       6       7

  
 
  1
  2
  3
  4
  5
  6
  7
  8
  9

文章来源: xxmdmst.blog.csdn.net，作者：小小明-代码实体，版权归原作者所有，如需转载，请联系作者。

原文链接：xxmdmst.blog.csdn.net/article/details/124135668

点赞
收藏
关注作者

0/1000

抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认取消

加入云驻计划，成为创作者

华为云周边好礼
免费体验产品
特殊身份标识
线下官方门票
内部专家零距离
与10000+优质创作者共同成长

立即加入

对比MySQL和Pandas计算用户最大连续登录天数

MySQL8.0窗口函数实现

MySQL变量实现(5.0也可以使用)

Pandas实现

RANK 和 DENSE_RANK的差异

全部回复

设置昵称

关于作者

目录

加入云驻计划，成为创作者

	num	default_rank	min_rank	max_rank	dense_rank	first_rank
0	2	1.0	1.0	1.0	1.0	1.0
1	4	2.5	2.0	3.0	2.0	2.0
2	4	2.5	2.0	3.0	2.0	3.0
3	8	4.5	4.0	5.0	3.0	4.0
4	8	4.5	4.0	5.0	3.0	5.0

	num	default_rank	min_rank	max_rank	dense_rank	first_rank
0	2	1.0	1.0	1.0	1.0	1.0
1	4	2.5	2.0	3.0	2.0	2.0
2	4	2.5	2.0	3.0	2.0	3.0
3	8	4.5	4.0	5.0	3.0	4.0
4	8	4.5	4.0	5.0	3.0	5.0

对比MySQL和Pandas计算用户最大连续登录天数

MySQL8.0窗口函数实现

MySQL变量实现(5.0也可以使用)

Pandas实现

RANK 和 DENSE_RANK的差异

全部回复

设置昵称

关于作者

目录

加入云驻计划，成为创作者

推荐阅读

相关产品

	num	default_rank	min_rank	max_rank	dense_rank	first_rank
0	2	1.0	1.0	1.0	1.0	1.0
1	4	2.5	2.0	3.0	2.0	2.0
2	4	2.5	2.0	3.0	2.0	3.0
3	8	4.5	4.0	5.0	3.0	4.0
4	8	4.5	4.0	5.0	3.0	5.0