翼度科技»论坛 编程开发 mysql 查看内容

sql 连续活跃天数

5

主题

5

帖子

15

积分

新手上路

Rank: 1

积分
15
1. 背景

已知数据集为:

目的:
计算每个uid的连续活跃天数,并且每一段活跃期内的开始时间和结束时间
2. 步骤

第一步:处理数据集
处理数据集,使其满足每个uid每个日期只有一条数据。
第二步:以uid为主键,按照日期进行排序,计算row_number.
  1. SELECT  uid
  2.           ,`征信查询日期`
  3.           ,ROW_NUMBER() OVER(PARTITION BY uid ORDER BY `征信查询日期` ASC) AS `rn`
  4.           ,first_value(`征信查询日期`)over(PARTITION BY uid ORDER BY `征信查询日期` ASC) `fir`
  5.           ,first_value(`征信查询日期`)over(PARTITION BY uid ORDER BY `征信查询日期` desc) `las`
  6. FROM   input
复制代码

两个关键点:

  • 序号rn可以看做一直活跃的情况下,活跃日期最大值和活跃日期最小值之间的天数差。那么,日期最大值与日期最小值之差如果不等于序号,就表明中间有不连续。
  • 用'征信查询日期' - rn 可以计算一列"关键列",连续时间段内,它的关键列值是一样的
  1. select *,DATE_SUB(`征信查询日期`,`rn`) as `关键列` from (SELECT  uid
  2.           ,`征信查询日期`
  3.           ,ROW_NUMBER() OVER(PARTITION BY uid ORDER BY `征信查询日期` ASC) AS `rn`
  4.           ,first_value(`征信查询日期`)over(PARTITION BY uid ORDER BY `征信查询日期` ASC) `fir`
  5.           ,first_value(`征信查询日期`)over(PARTITION BY uid ORDER BY `征信查询日期` desc) `las`
  6. FROM   input)
复制代码

第三步:以uid和关键列作为主键。
  1. select uid, `关键列`,count(*) as `连续活跃天数`, min(`征信查询日期`) as `活跃开始时间`, max(`征信查询日期`) as `活跃结束时间` from (select *, DATE_SUB(`征信查询日期`,`rn`) as `关键列` from (SELECT  uid
  2.           ,`征信查询日期`
  3.           ,ROW_NUMBER() OVER(PARTITION BY uid ORDER BY `征信查询日期` ASC) AS `rn`
  4.           ,first_value(`征信查询日期`)over(PARTITION BY uid ORDER BY `征信查询日期` ASC) `fir`
  5.           ,first_value(`征信查询日期`)over(PARTITION BY uid ORDER BY `征信查询日期` desc) `las`
  6. FROM   input  ) )group by uid, `关键列`
复制代码


来源:https://www.cnblogs.com/tian1022/p/17310496.html
免责声明:由于采集信息均来自互联网,如果侵犯了您的权益,请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容,谢谢合作!

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有账号?立即注册

x

举报 回复 使用道具