挑战100天不停更hive sql 第21天 - 时间序列-取最新完成状态的前一个状态

🌿挑战100天不停更，刷爆 hive sql🧲

详情请点击🔗我的专栏🖲,共同学习,一起进步~

NUM: 第21天 - 时间序列-取最新完成状态的前一个状态

当我们看到这道题目的时候首先会想到取最新的时间,然后用lead()开窗函数向下取一行,也许还有不同的解法,虽然sql并不长,但是我觉的有点绕,自己也写了很久,这道题也是非常考察对开窗取值函数的理解,特别是开窗中嵌套开窗, 就会有点绕,思路会断开

🧨不废话，刷题~~🧨

🎈表结构

B为完整状态,A为未完成状态

🎉建表

-- 建表并插入数据
create table t21
(
    date_id string,
    a       string,
    b       string
);
-- B为完整状态,A为未完成状态
insert into t21 (date_id, a, b)
values ('2014', '1', 'A'),
       ('2015', '1', 'B'),
       ('2016', '1', 'A'),
       ('2017', '1', 'B'),
       ('2013', '2', 'A'),
       ('2014', '2', 'B'),
       ('2015', '2', 'A'),
       ('2014', '3', 'A'),
       ('2015', '3', 'A'),
       ('2016', '3', 'B'),
       ('2017', '3', 'A');

👓问题一:取最新完成状态的前一个状态

✨先看执行结果

解法一:利用关联查询

🎨思考

1. 通过a分组取得B和最大的时间
2. 关联查询出结果
   

🧨SQL

select t2.date_id
     , t2.a
     , t2.b
from (
         select max(date_id) date_id
              , a
         from t21
         where b = 'B'
         group by a
     ) t1
         join t21 t2 on t2.date_id = t1.date_id - 1 and t1.a = t2.a;

解法二:开窗函数

🎨思考

1. 通过row_number()求行号
2. 向下取一行 date_id 和 b
3. 再嵌套一层子查询根据a和b分区,求得nk的最小值
4. 当最小的min_b=nk ,并且b =B时,求得最新的下一行数据
   

🧨SQL

select next_date_id as date_id
     , a
     , next_b       as b
from (
         select *
              , min(nk) over (partition by a,b) as minb
         from (
                  select date_id
                       , a
                       , b
                       , row_number() over (partition by a order by date_id desc)  nk
                       , lead(date_id) over (partition by a order by date_id desc) next_date_id
                       , lead(b) over (partition by a order by date_id desc)       next_b
                  from t21
              ) t
     ) t
where minb = nk
  and b = 'B';

问题二:将完成过程中的状态合并

✨先看执行结果

🎨思考

1. 先用row_number()求行号
2. 再使用开窗函数min()过滤完成状态B的值,注意:当开窗函数里面min(null)时,此时的结果为null
3. 当行号>=min_b时,此时就满足完成状态的集合,用collect_list()收集为集合就可
   

🧨SQL

select a
     , collect_list(b) as b
from (
         select *
              , min(if(b = 'B', nk, null)) over (partition by a) as minb
         from (
                  select *
                       , row_number() over (partition by a order by date_id desc) nk
                  from t21
              ) t
     ) t
where nk >= minb
group by a;

关于sql的规范问题

尽量把逗号放在前面,为什么?
1,方便排查,不会遗漏逗号
2,方便注释,可以单行直接注释,不用再改逗号
3,排版看起来更紧密,我用datagrip快捷键ctrl + shift + L可以快速缩进
4,特别用datagrip进行快速复制一行的时候,只需要Ctrl + D不需要关注逗号，写起来很快