挑战100天不停更hive sql第18天 - 构造累计时间

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

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

NUM: 第18天 -构造累计时间

为什么会构造累计时间呢? 因为有时候表的时间字段并不是连续的,又要根据时间对其他字段进行操作,这个时候就需要手动构造时间来解决问题,思路也很简单,用炸裂函数求出连续时间,并和原来的表进行关联后,再进行开窗即可

🧨不废话，刷题~~🧨

🎈表结构

🎉建表

create table t18
(
    a string,
    b string,
    c int
);
insert into t18 (a, b, c)
values ('101', '2018-01-01', 10),
       ('101', '2018-01-03', 20),
       ('101', '2018-01-06', 40),
       ('102', '2018-01-02', 20),
       ('102', '2018-01-04', 30),
       ('102', '2018-01-07', 60);

👓将b字段扩充为[2018-01-01, 2018-01-07]并累计对c进行求和

👙b字段时间不连续,c为稀疏字段,对c进行累计求和

请注意:是累计求和,并不是全量求和

✨先看执行结果

🎨思考

1. 从表数据中可以观察出,101有三个，并没有1-7号的连续值,所以，c值是不连续,要想对c求和，而且时间还要有连续，可以用查询结果连续的表关联查询查出结果并去除空值
2. 那怎么求出连续的呢?? 一行转多行,那就得用炸裂函数了 ,又要根据时间累加,需要用posexplode()
   求出连续表后,关联查询,去除null值
3. 对c进行sum()求和即可
   注意:这里不能使用group by需要使用sum()开窗对a进行分区后,对c进行累加

🧨SQL

详细内容请看注释

select a
     , add_b                                       as b
     , c
     , sum(c) over (partition by a order by add_b) as sum_c
from (
-- 4,关联查询,并筛选null值
         select t4.a
              , t4.add_b
              , case when t18.b is not null then t18.c else 0 end as c
         from (
                  -- 3,根据多行,进行时间累加
                  select a
                       , date_add(s, pos) as add_b
                  from (
                           -- 2,求出指定时间,并炸裂出多行
                           select a
                                , '2018-01-01' as s
                                , '2018-01-07' as e
                           from (
                                    --1,根据a分组,将a去重
                                    select a
                                    from t18
                                    group by a) t1
                       ) t2 lateral view posexplode(split(
                          space(datediff(from_unixtime(unix_timestamp(e, 'yyyy-MM-dd')),
                                         from_unixtime(unix_timestamp(s, 'yyyy-MM-dd')))),
                          '')) t3 as pos, val
              ) t4
                  left join t18 on t18.a = t4.a
             and t18.b = t4.add_b
     ) t5
;