大数据 - ODS&DWD&DIM-SQL分享

数据,ods,dwd,dim,sql,分享 · 浏览次数 : 177

小编点评

## 大数据 ODS&DWD&DIM-SQL分享需求思路一：等差数列断2天、3天，嵌套太多1.1 开窗，按照 id 分组，同时按照 dt 排序，求 Rank **思路一：使用 window functions** * **等差1天:** * 首先，计算每行日期减去RK值，如果之前是连续的日期，则相减之后为相同日期z。 * 使用 `date_sub` 函数进行日期差值计算。 * **等差2天:** * 类似地，进行日期差值计算，但需要考虑日期跨越两个窗口的特殊情况。 * 使用 `date_sub` 和 `date_add` 函数进行日期范围控制。 * **等差3天:** * 继续采用 `date_sub` 和 `date_add` 的方法进行日期差值计算，但需要考虑更长的日期跨越三个窗口的特殊情况。 * 可以使用 `case when` 语句对日期进行处理。 **思路二：使用 subquery 进行分组和计算** * 使用 subquery 对数据分组，并进行日期差值计算。 * 在计算中，需要考虑日期跨越多个窗口的特殊情况。 * 可以利用 `window functions` 中的 `LAG` 和 `LEAD` 函数进行日期间关系的处理。 **示例 SQL：** **1. 等差1天** ```sql select id, dt, datediff(dt, rk) as dtDiff from ( select id, dt, rank() over(partition by id order by dt) rk from tx ) t1 union all select id, dt, datediff(dt, rk) as dtDiff from ( select id, dt, rank() over(partition by id order by dt) rk from tx ) t2 ``` **2. 等差2天** ```sql select id, dt, datediff(dt, rk) as dtDiff from ( select id, dt, rank() over(partition by id order by dt) rk from tx ) t1 union all select id, dt, datediff(dt, rk) as dtDiff from ( select id, dt, rank() over(partition by id order by dt) rk from tx ) t2 where dt between '2023-03-01' and '2023-03-03' ``` **3. 等差3天** ```sql select id, dt, datediff(dt, rk) as dtDiff from ( select id, dt, rank() over(partition by id order by dt) rk from tx ) t1 union all select id, dt, datediff(dt, rk) as dtDiff from ( select id, dt, rank() over(partition by id order by dt) rk from tx ) t2 where dt between '2023-03-01' and '2023-03-03' and dt > '2023-03-01' ``` **4. 计算连续天数** ```sql select id, flag, count(*) as daysfrom ( select id, dt, date_sub(dt, rk) flg from ( select id, dt, rank() over(partition by id order by dt) rk from tx) t1 ) t2 group by id, flag ``` **5. 计算最大连续天数** ```sql select id, max(days) as max_days from t6group by id ```

正文

大数据 ODS&DWD&DIM-SQL分享需求

思路一：等差数列

断2天、3天，嵌套太多

1.1 开窗，按照 id 分组，同时按照 dt 排序，求 Rank

-- linux 中空格不能用　tab 键
select id,dt,rank() over(partition by id order by dt) rk from tx;

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

z: 等差
(x1+z)-(y1+z)=x1-y1

select id,dt,date_sub(dt,rk) flg 
from (select id,dt,rank() over(partition by id order by dt) rk from tx) t1;

断一天的数据，flag 变成了连续

1.3 计算绝对连续的天数

select id,flag,count(*) days
from (
  select id,dt,date_sub(dt,rk) flg
  from (select id,dt,rank() over(partition by id order by dt) rk from tx) t1;
)t2 group by id,flag;

1.4 再计算连续问题

select id,flag,days,rank() over(partition by id order by flag) newFlag
from t3;

1.5 将 flag 减去 newflag

select id,days,date_sub(flag,newFlag) flag
from t4;t5

1.6 计算每个用户连续登录的天数，断一天也算

select id,sum(days)+count(1) days
from t5
group by id,flag;[t6]

1.7 计算最大连续天数

select id,max(days)
from t6
group by id;

准后再-1

思路二

2.1 将上一行数据下移

--下移默认值，一般给 1970-01-01，上移默认值一般 9999-01-01
select id,dt,lag(dt,1,'1970-01-01') over(partition by id order by dt) lagDt
from tx; t1

2.2 将当前行日期减去下移的日期

select id,dt,datediff(dt,lagDt) dtDiff
from t1; t2

执行

select id,dt,datediff(dt,lagDt) dtDiff
from (
select id,dt,lag(dt,1,'1970-01-01') over(partition by id order by dt) lagDt
from tx) t1;

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

2.3 分组

select id,dt,sum(if(dtDiff>2,1,0)) over(partition by id order by dt) flag
from t2; t3

select id,dt,sum(if(dtDiff>2,1,0)) over(partition by id order by dt) flag
from (
select id,dt,datediff(dt,lagDt) dtDiff
from (
select id,dt,lag(dt,1,'1970-01-01') over(partition by id order by dt) lagDt
from tx) t1
) t2;

select id,flag,datediff(max(dt),min(dt))+1
from t3
group by id,flag;

带入执行

--断3天把2改成3，断4天把2改成4
select id,flag,datediff(max(dt),min(dt))+1
from (
select id,dt,sum(if(dtDiff>2,1,0)) over(partition by id order by dt) flag
from (
select id,dt,datediff(dt,lagDt) dtDiff
from (
select id,dt,lag(dt,1,'1970-01-01') over(partition by id order by dt) lagDt
from tx) t1
) t2
)t3
group by id,flag;

2.3 求分组后的最大值

HiveOnSpark: 有个BUG， datediff over 子查询 => null point
解决方案：

换MR引擎
将时间字段由 String 类型改成 Date 类型

https://www.bilibili.com/video/BV1Ju411o7f8/?p=69