挑战100天不停更之hive sql第4天 - 窗口大小的控制

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

NUM:第4天 - 窗口大小的控制

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

🧨不废话，刷题~~🧨

表结构

建表

INSERT into t4 values('2014', 'A', 3),('2014' ,'B' ,1 ),('2014' ,'C' ,2 ),('2015' ,'A' ,4),('2015', 'D' ,3);
SELECT * FROM t4;

1）按a分组按照b字段排序，求c的前一行后后一行的和

思路

LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值 第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

与LAG相反
LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值 第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）

SQL

select
a,
b,
lag(c,1,0) over(partition by a order by b)+ lead(c,1,0) over(partition by a order by b) as sum_c
from
t4;

输出结果

2)按照a分组，b排序，前一行和当前行求平均值

思路

子查询当前行的上一行，判断lag_c 是否为null，默认第一行为null

SQL

SELECT a,
       b,
--       if(lag_c is NULL, c, (c + lag_c) / 2) as avg_c
       case when lag_c is null then c else (c + lag_c)/2 end as avg_c 
FROM (select a,
             b,
             c,
             lag(c, 1) over (partition by a order by b) as lag_c
      from t4) tmp;

输出结果