Hive 窗口函數最全講解和實戰
- 2019 年 12 月 18 日
- 筆記
一、窗口函數的概念
在不同的窗口執行的函數
在深入研究Over字句之前，一定要注意：在SQL處理中，窗口函數都是最後一步執行，而且僅位於Order by子句之前 可以想像成sql的輸出結果，就是窗口函數輸入的結果。
主要的函數有：
- over()
- partition_by
- order_by
- window函數
- row_number()
- rank()
- dense_rank()
- lag()lead()
- first_value()
- last_value()
二、實例
建立一個用戶消費表：
drop table tempon.t_user_cost; create external table if not exists tempon.t_user_cost( name string comment '用戶名', date string comment '月份', cost int comment '花費' ) comment '用戶花費表' row format delimited fields terminated by "," location '/tmp/person_cost'
1、聚合函數 + over()
select name,count(1) over() from tempon.t_user_cost where substring(date,1,7) = '2015-04'
一般，聚合函數返回行數都會比原有行數少。
但我們又想看原始函數，又想看聚合的值，怎麼辦，在聚合函數上加over()
二、partition by 子句
也叫查詢分區子句，將數據按照邊界值分組，而over()之前的函數在每個分組內執行。
select name,date,cost,sum(cost) over(partition by month(date)) from tempon.t_user_cost;
三、order by 子句
上面的場景，假如我們想把cost按月進行累加，這時我們引入order by 子句
order by 子句會讓輸入數據強制排序
select name,date,cost,sum(cost) over(partition by month(date) order by cost) from tempon.t_user_cost;
四、window子句
（不同的窗口互不影響，自己算自己的）
window是為了更加細粒度的劃分
兩個概念：
如果只使用了partition by子句，未指定order by的話，我們的聚合是分組內的聚合
如果使用了order by子句，未使用window子句，默認從起點到當前行
PRECEDING：往前
FOLLOWING：往後
CURRENT ROW：當前行
UNBOUNDED：起點 （UNBOUNDED PRECEDING 表示從前面的起點， UNBOUNDED FOLLOWING：表示到後面的終點）
也就是可以指定，在指定窗口裡，可以把當前行的前面N行和當前行的後面N行，聚合。即小窗口的概念，只是這個窗口的口徑是行數
select name,date,cost, sum(cost) over() sample1, -- 所有行累加 sum(cost) over(partition by name) sample2, -- 按照name相加 sum(cost) over(partition by name order by cost) sample3, --按照name累加 sum(cost) over(partition by name order by cost rows between unbounded preceding and current row) sample4, --和sample3一樣的效果 sum(cost) over(partition by name order by cost rows between 1 preceding and current row) sample5, -- 當前行和上一行相加 sum(cost) over(partition by name order by cost rows between 1 preceding and 1 following) sample6, -- 上一行、當前行、後一行相加 sum(cost) over(partition by name order by cost rows between current row and unbounded following) sample7 -- 當前行到末尾 from tempon.t_user_cost;
五、row_number() 和 rank() 和 dense_rank()
這三個函數是為了排序，但是有區別
select name,date,cost, row_number() over(partition by name order by cost), rank() over(partition by name order by cost), dense_rank() over(partition by name order by cost) from tempon.t_user_cost;
row_number() 是無腦排序
rank() 是相同的值排名相同，接下來的排名會加
dense_rank()也是相同的值排名相同，接下來的排名不會加
六、lag和lead函數
lag()函數是在窗口內，在指定列上，取上N行的數據，並且有默認值。沒有設置默認值的話，為null
select name,date,cost, lag(date,1,'1990-01-01') over(partition by name order by date), lag(date,2,'1990-01-01') over(partition by name order by date), lead(date,1,'1990-01-01') over(partition by name order by date) from tempon.t_user_cost
第一個參數是列名，第二個參數是取上多少行的數據，第三個參數是默認值
lead相反，往下取
七、first_value() 和 last_value()
select name,date,cost, first_value(date) over(partition by name order by date), last_value(date) over(partition by name order by date) from tempon.t_user_cost;
當前分區的第一個值和最後一個值
