hive sql 窗口函数

1) 窗口函数 Lag, Lead, First_value,Last_value

Lag, Lead、这两个函数为常用的窗口函数,可以返回上下数据行的数据.

LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值

LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值, 与LAG相反

-- 组内排序后,向后或向前偏移
-- 如果省略掉第三个参数,默认为NULL,否则补上。

select
    dp_id,
    mt,
    payment,
    LAG(mt,2) over(partition by dp_id order by mt) mt_new
from test2;

结果如图:



-- 组内排序后,向后或向前偏移
-- 如果省略掉第三个参数,默认为NULL,否则补上。

select
   dp_id,
   mt,
   payment,
   LEAD(mt,2,'1111-11') over(partition by dp_id order by mt) mt_new
from test2;

结果如图:



FIRST_VALUE, LAST_VALUE

first_value:  取分组内排序后,截止到当前行,第一个值

last_value:  取分组内排序后,截止到当前行,最后一个值

-- FIRST_VALUE      获得组内当前行往前的首个值
-- LAST_VALUE       获得组内当前行往前的最后一个值
-- FIRST_VALUE(DESC) 获得组内全局的最后一个值

select
   dp_id,
   mt,
   payment,
   FIRST_VALUE(payment) over(partition by dp_id order by mt) payment_g_first,
   LAST_VALUE(payment) over(partition by dp_id order by mt) payment_g_last,
  FIRST_VALUE(payment) over(partition by dp_id order by mt desc) payment_g_last_global
from test2
ORDER BY dp_id,mt;



2)排名函数 Rank,Dense_Rank, Row_Number

R()  over  (partion  by  col1...  order  by  col2...  desc/asc)

select
   class1,
   score,
   rank() over(partition by class1 order by score desc) rk1,
   dense_rank() over(partition by class1 order by score desc) rk2,
   row_number() over(partition by class1 order by score desc) rk3
from zyy_test1;



如上图所示,rank  会对相同数值,输出相同的序号,而且下一个序号不间断;

       dense_rank  会对相同数值,输出相同的序号,但下一个序号,间断

posted @ 2020-03-25 17:20  DB乐之者  阅读(1804)  评论(0编辑  收藏  举报