IT评测·应用市场-qidao123.com技术社区

标题: 大数据 ODS&DWD&DIM-SQL分享 [打印本页]
作者: 嚴華    时间: 2022-12-29 13:07
标题: 大数据 ODS&DWD&DIM-SQL分享
大数据 ODS&DWD&DIM-SQL分享 需求


思路一：等差数列

断2天、3天，嵌套太多
1.1 开窗，按照 id 分组，同时按照 dt 排序，求 Rank
  1. -- linux 中空格不能用　tab 键
  2. select id,dt,rank() over(partition by id order by dt) rk from tx;
复制代码

1.2 将每行日期减去RK值，如果之前是连续的日期，则相减之后为相同日期

z: 等差
(x1+z)-(y1+z)=x1-y1
  1. select id,dt,date_sub(dt,rk) flg
  2. from (select id,dt,rank() over(partition by id order by dt) rk from tx) t1;
复制代码

断一天的数据，flag 变成了连续
1.3 计算绝对连续的天数
  1. select id,flag,count(*) days
  2. from (
  3.   select id,dt,date_sub(dt,rk) flg
  4.   from (select id,dt,rank() over(partition by id order by dt) rk from tx) t1;
  5. )t2 group by id,flag;
复制代码


1.4 再计算连续问题
  1. select id,flag,days,rank() over(partition by id order by flag) newFlag
  2. from t3;
复制代码

1.5 将 flag 减去 newflag
  1. select id,days,date_sub(flag,newFlag) flag
  2. from t4;t5
复制代码

1.6 计算每个用户连续登录的天数，断一天也算
  1. select id,sum(days)+count(1) days
  2. from t5
  3. group by id,flag;[t6]
复制代码
1.7 计算最大连续天数
  1. select id,max(days)
  2. from t6
  3. group by id;
复制代码
准后再-1

思路二

2.1 将上一行数据下移
  1. --下移默认值，一般给 1970-01-01，上移默认值一般 9999-01-01
  2. select id,dt,lag(dt,1,'1970-01-01') over(partition by id order by dt) lagDt
  3. from tx; t1
复制代码
2.2 将当前行日期减去下移的日期
  1. select id,dt,datediff(dt,lagDt) dtDiff
  2. from t1; t2
复制代码
执行
  1. select id,dt,datediff(dt,lagDt) dtDiff
  2. from (
  3. select id,dt,lag(dt,1,'1970-01-01') over(partition by id order by dt) lagDt
  4. from tx) t1;
复制代码

每碰到一个 >2  的就分组 + 1

2.3 分组
  1. select id,dt,sum(if(dtDiff>2,1,0)) over(partition by id order by dt) flag
  2. from t2; t3
复制代码
  1. select id,dt,sum(if(dtDiff>2,1,0)) over(partition by id order by dt) flag
  2. from (
  3. select id,dt,datediff(dt,lagDt) dtDiff
  4. from (
  5. select id,dt,lag(dt,1,'1970-01-01') over(partition by id order by dt) lagDt
  6. from tx) t1
  7. ) t2;
复制代码
  1. select id,flag,datediff(max(dt),min(dt))+1
  2. from t3
  3. group by id,flag;
复制代码
带入执行
  1. --断3天把2改成3，断4天把2改成4
  2. select id,flag,datediff(max(dt),min(dt))+1
  3. from (
  4. select id,dt,sum(if(dtDiff>2,1,0)) over(partition by id order by dt) flag
  5. from (
  6. select id,dt,datediff(dt,lagDt) dtDiff
  7. from (
  8. select id,dt,lag(dt,1,'1970-01-01') over(partition by id order by dt) lagDt
  9. from tx) t1
  10. ) t2
  11. )t3
  12. group by id,flag;
复制代码

2.3 求分组后的最大值

HiveOnSpark: 有个BUG， datediff over 子查询  => null point
解决方案：
https://www.bilibili.com/video/BV1Ju411o7f8/?p=69

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！



欢迎光临 IT评测·应用市场-qidao123.com技术社区 (https://dis.qidao123.com/) Powered by Discuz! X3.4