目標:
1、掌握 sum()、avg()等用於累計計算的聚合函數,學會對行數的限制(移動計算);
2、掌握 row_number(),rank()、dense_rank()用於排序的函數;
3、掌握 ntile()用於分組查詢的函數;
4、掌握 lag()、lead()偏移分析函數
窗口函數(window function):
與聚合函數類似,但是窗口函數是每壹行數據都生成壹個結果,聚合函數可以將多行數據按照規定聚合為壹行,壹般來說聚合後的行數要少於聚合前的行數,但是有時我們想要既顯示聚合前的數據,又要顯示聚合後的數據,這時便引入了窗口函數, 窗口函數是在 select 時執行的,位於 order by 之前 。
在日常工作中,經常遇到 計算截止某月或某天的累計數值 ,在Excel可以通過函數來實現,
在HiveSQL裏,可以利用窗口函數實現。
1)2018年每月的支付總額和當年累計支付總額
2)對2017年和2018年公司的支付總額按月度累計進行分析,按年度進行匯總
說明:1、over中的 partition by 起到分組的作用;
2、order by 按照什麽順序進行累加,升序ASC、降序DESC,默認升序
3、正確的分組是非常重要的,partition by 後面的字段是需要累計計算的區域,需要仔細理解
(計算三日留存、七日留存、三十日留存等方式可以使用這個函數。)
3)對2018年每個月的近三個月進行移動的求平均支付金額
用法:這三個函數的作用都是返回相應規則的排序序號,由於排序函數不是二次聚合計算,因此不壹定要使用子查詢
4)2019年1月,用戶購買商品品類數量的排名
5)選出2019年支付金額排名在第10、20、30名的用戶
6)將2019年1月的支付用戶,按照支付金額分成5組
7)選出2019年退款金額排名前10%的用戶
說明:Lag和Lead分析函數可以在同壹次查詢中取出同壹字段的 前N行數據(Lag)和後N行的數據(Lead) 作為獨立的列。
在實際應用當中,若要用到取今天和昨天的某字段差值時,Lag和Lead函數的應用就顯得尤為重要。
當然,這種操作可以用表的 自連接實現 ,但是Lag和Lead與 left join、 right join等自連接相比,效率更高,SQL語句更簡潔。
8)支付時間間隔超過100天的用戶數(這壹次購買距離下壹次購買的時間?,註意datediff函數是日期大的在前面)
9)每個城市,不同性別,2018年支付金額最高的TOP3用戶
步驟總結:
1、首先篩選出每個用戶和每個用戶總的消費金額;
2、對兩個表進行連接提取需要的字段;
3、對連接後的表進行二次聚合計算,計算出不同城市、性別的金額排名;
4、對二次聚合計算的表進行條件篩選提取
10)每個手機品牌退款金額前25%的用戶
步驟總結:
1、首先篩選出每個用戶和每個用戶的總退款金額;
2、對兩個表進行連接提取需要的字段;
3、對連接後的表進行按手機品牌內分組;
4、對分組後的表進行條件篩選提取