MySQL 连续记录 场景分析

这篇具有很好参考价值的文章主要介绍了MySQL 连续记录 场景分析。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

一般在统计销售情况或者签到情况中会用到连续记录,求解连续记录问题可以使用窗口函数。

【场景】:每个用户连续登录的最大天数连续登录2天及以上时间的用户及天数

【知识点】:窗口函数、date(登录日期) - row_number() over(partition by 用户ID order by dt)

一、分析思路

1、连续登录如何定义?

将连续登录定义为用户在至少两个不同日期登录到系统。

难点: 如何确定登录记录是连续的,并对连续登录记录打标

2、求解思路

要计算每个用户的连续登录天数,我们可以按照以下步骤进行:

  1. 对用户和日期进行去重:首先使用date() 函数把登录时间转换为日期记为 dt 并去重;

  2. 标记连续登录记录:用row_number()函数对登录日期按照升序顺序排序记为 rk,用并 dt 减去rk得到date_diff, 数据一样的就是连续的记录;

  3. 根据条件筛选登录记录:例如每个用户最大连续登录天数。


举个例子:如果用户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金币。

求解代码:
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模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • python——手机销售统计

    类型:集合 文件中包含有2018和2019手机销售榜单数据(市场份额百分数),请根据要求升序输出分析结果: 输入’1’时,以列表形式在两行中分别输出2019年和2018年上榜品牌 输入’2’时,以列表形式输出2019年和2018年都上榜的品牌 输入’3’时,以列表形式输出2019年和2018年

    2024年02月04日
    浏览(40)
  • 数据处理与统计分析——MySQL与SQL

    数据库:DB(DataBase) 概念 :数据仓库,软件,安装在操作系统之上 作用 :存储数据,管理数据 关系型数据库:SQL(Structured Query Language) MySQL、Oracle、Sql Server、DB2、SQLlite 通过表和表之间,行和列之间的关系进行数据的存储 通过外键关联来建立表与表之间的关系 非关系型

    2024年02月12日
    浏览(42)
  • Hadoop MapReduce 统计汽车销售信息

    本文将讨论如何使用Hadoop MapReduce来统计汽车销售信息。 汽车销售的记录文件名叫Cars.csv,里面记录了汽车的销售信息,数据内容如下: 格式为: 第1列:销售的省份 第3列:销售的城市 第7列:汽车生产商 第8列:汽车品牌名 第12列:汽车销售数量 已经将Cars.csv上传到HDFS文件

    2024年02月03日
    浏览(57)
  • SQL统计连续登陆3天的用户(连续活跃超3天用户)

    1. 数据准备 2. 方法一: 差值计算 user_id active_date rn 10001 2023-02-01 1 10001 2023-02-03 2 10001 2023-02-04 3 10001 2023-02-05 4 10002 2023-02-02 1 10002 2023-02-03 2 10002 2023-02-04 3 10002 2023-02-05 4 10002 2023-02-07 5 … … … user_id active_date rn sub_date 10001 2023-02-01 1 2023-01-31 10001 2023-02-03 2 2023-02-01 10001 2023-02-04 3

    2024年04月25日
    浏览(30)
  • MySQL 在线人数 场景分析

    一般在直播或者游戏中经常会统计用户在线人数,主要分为求每个时刻的在线人数和求某个时刻的在线人数两种。 【场景】: 某个时刻的在线人数 、 每个时刻的在线人数 【知识点】: 窗口函数 、 时间函数 、sum(tag) over (order by dt,tag desc rows between unbounded preceding and current

    2023年04月12日
    浏览(43)
  • 物流RFID设备一般在哪些场景应用?

    随着现代物流行业的快速发展,传统条码技术信息量少,易脏污损毁,耐用性不高等问题很难满足物流企业多样化的需求,物流RFID设备的应用也越来越广泛。下面我们就跟大家一起来分析一下,物流RFID设备可以在哪些场景中应用。 物流RFID设备可以在哪些场景中应用 现在物

    2024年02月13日
    浏览(54)
  • Mysql索引失效的场景分析

    前言: 日常使用Mysql做一些业务时,发现很慢,跟踪日志返现是有慢查询语句,于是使用explain查看执行计划发现是没有使用到索引,一般这些情况都不是java框架导致的,一般框架里都会根据主键或者指定的条件去做简单的查询,复杂的查询都是通过sql原生写法来实现的,这

    2023年04月20日
    浏览(44)
  • 【Kafka+Flume+Mysql+Spark】实现新闻话题实时统计分析系统(附源码)

    需要源码请点赞关注收藏后评论区留言私信~~~ 新闻话题实时统计分析系统以搜狗实验室的用户查询日志为基础,模拟生成用户查询日志,通过Flume将日志进行实时采集、汇集,分析并进行存储。利用Spark Streaming实时统计分析前20名流量最高的新闻话题,并在前端页面实时显示

    2024年02月06日
    浏览(52)
  • sql高频面试题-连续完成两个指定动作的用户统计

    业务背景 某购物APP最近上线了一个新功能,用户签到后可以跳转到大转盘抽奖,抽奖获得的奖金可以抵消购物的费用,以此来培养用户使用app的习惯。 数据表介绍 现有一张用户行为表action_log,主要字段如下,记录了用户在app上的所有行为日志,即何人userid在何时action_time进

    2024年02月13日
    浏览(44)
  • 线上SQL超时场景分析-MySQL超时之间隙锁

    之前遇到过一个由MySQL间隙锁引发线上sql执行超时的场景,记录一下。 分布式事务消息表 :业务上使用消息表的方式,依赖本地事务,实现了一套分布式事务方案 消息表名 :mq_messages 数据量 :3000多万 索引 :create_time 和 status status :有两个值,1 和 2, 其中99%以上的状态都

    2024年02月05日
    浏览(52)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包