1. Hivesql核心技能之窗口計算
目標：
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、對分組後的表進行條件篩選提取
2. hive sql語言問題，怎麼按一列聚合第二列的多個值合並成1個值，再按這個值group by
--假設表名為tb
selectys,count(*)
from
(
selectx,to_char(wm_concat(y))ys
fromtb
groupbyx
)w
groupbyys
;
3. 數據分析課程筆記 - 20 - HIVE 核心技能之窗口函數
大家好呀，這節課我們學習 Hive 核心技能中最難的部分——窗口函數。窗口函數我們之前在學 MySQL 的時候有學過一些，但是只學了三個排序的窗口函數。這節課我們會學習更多的窗口函數，包括累計計算、分區排序、切片排序以及偏移分析。
在正式學習之前，我們需要先明確一下窗口函數和GROUP BY分組的區別。二者在功能上有相似之處，但是它們存在本質區別。
1. 分組會改變表的結構，而窗口函數不會改變表的結構。比如原表有10行數據，分成兩組後只有兩行，而窗口函數仍然返回十行數據。
2. 分組只能查詢分組後的欄位，包括分組欄位（組名）和聚合函數欄位。而窗口函數對查詢欄位沒有限制，也就是可以查詢原表的任意欄位，再加上窗口函數新增的一列值。
好啦，現在讓我們一起進入窗口函數的世界吧~
本節課主要內容：
1、累計計算窗口函數
（1）sum(…) over(……)
（2）avg(…) over(……)
（3）語法總結
2、分區排序窗口函數
（1）row_number()
（2）rank()
（3）dense_rank()
3、切片排序窗口函數
（1）ntile(n) over(……)
4、偏移分析窗口函數
5、重點練習
大家在做報表的時候，經常會遇到計算截止某月的累計數值，通常在EXCEL里可以通過函數來實現。
那麼在HiveSQL里，該如何實現這種累計數值的計算呢？那就是利用窗口函數！
關於窗口函數的幾點說明：
需求分析 ：既然要進行按月累計，我們就先要把2018年的每筆交易時間轉換成月並按月分組聚合計算，得出一個2018年每月支付金額總合表，再基於這張表用窗口函數進行累計計算。
2018年每月支付金額總和表：
再用窗口函數進行月度累計：
年度進行匯總。
這個需求比需求1多了一個需求，那就是年度匯總。那我們只需要在上個需求的子查詢中加一個 year 欄位即可。
說明：
1、over 中的 partition by 起到了窗口內將數據分組的作用。事實上，加上partition by之後，可以理解為分成了多個窗口，並在每個窗口內進行累加計算或者分區。
如果不加 partition by a.year 的話，運行結果就是這樣單純按月份進行分組的：
2、order by 按照什麼順序進行累加，升序ASC、降序DESC，默認是升序。
大家看股票的時候，經常會看到這種K線圖，裡面經常用到的就是7日、30日移動平均的趨勢圖，那如何使用窗口函數來計算移動平均值呢？
需求分析 ：這個需求要求每個月近三個月的移動平均支付金額，這里我們要用到一個新知識點，在窗口函數 avg over 的 order by a.month 之後加一句 rows between 2 preceding and current row 來設定計算移動平均的范圍，這個語句的含義就是包含本行及前兩行。其他部分的寫法跟前面的需求類似，先取出2018年每個月的支付金額總和，再用窗口函數求移動平均。
注意：
sum(…A…) over(partition by …B… order by …C… rows between …D1… and …D2…)
avg(…A…) over(partition by …B… order by …C… rows between…D1… and …D2…)
A：需要被加工的欄位名稱
B：分組的欄位名稱
C：排序的欄位名稱
D：計算的行數范圍
rows between unbounded preceding and current row
——包括本行和之前所有的行
rows between current row and unbounded following
——包括本行和之後所有的行
rows between 3 preceding and current row
——包括本行以內和前三行
rows between 3 preceding and 1 following
——從前三行到下一行(5行)
max(……) over(partition by …… order by …… rows between ……and ……)
min(……) over(partition by …… order by …… rows between ……and ……)
row_number() 、rank()、dense_rank()
用法：這三個函數的作用都是返回相應規則的排序序號
row_number() over(partition by …A… order by …B… )
rank() over(partition by …A… order by …B… )
dense_rank() over(partition by …A… order by …B… )
A：分組的欄位名稱
B：排序的欄位名稱
注意： 這3個函數的括弧內是不加任何欄位名稱的！
row_number ：它會為查詢出來的每一行記錄生成一個序號，依次排序且不會重復。
rank&dense_rank ：在各個分組內， rank() 是跳躍排序，有兩個第一名時接下來就是第三名， dense_rank() 是連續排序，有兩個第一名時仍然跟著第二名。
實例練習：
再眼熟一下 user_trade 的表結構：
需求分析 ：先限定時間范圍，然後根據 user_name 進行分組，接著選出 分組去重後的 user_name，並計算每個用戶 goods_category 的數量（記得 distinct 去重），再然後就是用窗口函數對 goods_category 的數量進行排序，當然選擇哪一種排序方法要看具體要求，這里我們可以三種方法都試一下看看結果：
注意 ：窗口函數中的 order by 欄位不能用 select 中欄位的重命名，因為二者是同時執行的。
需求分析 ： 先用窗口函數將2019年每個用戶的支付總金額算出來並進行排序，再以此作為子查詢，從中取出排名在第10、20、30名的用戶名、支付總金額以及排名次序。企業一般會使用 dense_rank 進行排序，所以我們這里直接用 dense_rank。
2019年每個用戶的支付總金額排名：
2019年支付金額排名在第10、20、30名的用戶：
ntile(n) over(partition by …A… order by …B… )
n：切分的片數
A：分組的欄位名稱
B：排序的欄位名稱
需求分析 ：這個需求很簡單，把需求5第一步的排序窗口函數變成切片即可。注意時間篩選條件變成2019年1月。
需求分析 ： 排名前10%，也就是一共分成10組，取第1組。那麼我們先切片分組：
然後再取第一組：
說明：Lag和Lead分析函數可以在同一次查詢中取出同一欄位的前N行的數據(Lag)和後N行的數據(Lead)作為獨立的列。
在實際應用當中，若要用到取今天和昨天的某欄位差值時，Lag和Lead函數的應用就顯得尤為重要。當然，這種操作可以用表的自連接實現，但是LAG和LEAD與left join、right join等自連接相比，效率更高，SQL語句更簡潔。
lag(exp_str,offset,defval) over(partion by ……order by ……)
lead(exp_str,offset,defval) over(partion by ……order by ……)
lag() 函數示例：
lead() 函數示例：
需求分析： 先要從 user_trade 表中取出每個用戶的支付時間，把每個用戶放到一個窗口中，按照支付時間進行排序，取出偏移列： lead(dt,1,dt) over(partition by user_name order by dt)。接著基於該子查詢，篩選出時間間隔大於100天的用戶，並計算數量。
注意 ： 如果上面偏移分析函數寫成 lead(dt,1,dt) 就不用加後面的 dt is not null 了，因為有默認值的話，間隔就是0，肯定是不滿足條件的。
需求分析 ：
第一步 ：這個需求要用到 user_trade 和 user_info 兩張表，前者取支付時間和金額，後者取城市和性別。先對這兩張表基於 user_name 進行左連接，並取出相應欄位，用窗口函數進行分組排序：
這一步的運行結果是這樣的：
第二步 ：基於上述結果取出TOP3：
需求分析：
第一步 ：這個需求同樣要用到兩張表 user_refund 和 user_info。我們先把每個退款用戶的退款金額和手機品牌取出來，並用窗口函數進行切片排序，25%就是分成4片：
注意 ：這里之所以要加 WHERE dt is not null 是因為 user_refund 是一個分區表，分區表要對分區欄位進行限制，否則 hive 會報錯。
第二步 ：選擇前25%，也就是第一片：
最後補充一個從 hive 導出結果數據的命令：
以上就是這節課的全部內容了。做完整個練習，真的半條命都沒了。窗口函數果然很難，不過掌握方法、多多練習，學會拆解需求，一步一步來做，就能明顯降低難度。希望以後有機會能用到這么復雜的技能，哈哈~！
4. HiveSQL核心技能之常用函數
目標：
1、掌握hive基礎語法、常用函數及其組合使用
2、掌握一些基本業務指標的分析思路與實現技巧
1）某次經營活動中，商家發起了「異性拼團購」，試著針對某個地區的用戶進行推廣，找出匹配用戶。
注意：如果該表是一個分區表，則where條件中必須對分區欄位進行限制
2）選出在2018年12月31日，購買的商品品類是food的用戶名、購買數量、支付金額
3）試著對本公司2019年第一季度商品的熱度與價值度進行分析。
"2019年一月到三月，每個品類有多少人購買，累計金額是多少"
GROUP BY ... HAVING（分類匯總過濾）
4）找出在2019年4月支付金額超過5萬元的用戶，給這些VIP用戶贈送優惠券
5）2019年4月，支付金額最多的TOP5用戶
查看Hive中的函數：show functions；
查看具體函數的用法：1）desc function 函數名；2）desc function extended函數名；
6）
7）用戶的首次激活時間，與2019年5月1日的日期間隔。
datediff(string enddate,string startdate):結束日期減去開始日期的天數
拓展:日期增加函數（增加天數）、減少函數（減少天數） —— date_add、date_sub（類型要是string類型的）
date_add（string startdate,int days)、date_sub（string startdate,int days)
case when 函數
8）統計以下四個年齡段20歲以下、20-30歲、30-40歲、40歲以上的用戶數
case when 的時候不用加group by，只有在聚合函數的時候才需要group by
if 函數
9）統計每個性別的用戶等級高低分布情況（假設level大於5為高級，注意列名不能使用中文的）
10）分析每個月都拉新情況
substring(stringA,INT start,int len),substr(stringA,INT start,int len)，截取起始位置和截取長度
extra1需要解析json欄位，然後用$.key取出想要獲取的value;
extra2使用的是中括弧加引號的方式進行欄位提取和分組；
兩種寫法要看欄位類型的不同採取不同的方式
11）求不同手機品牌的用戶數
12）ELLA用戶的2018年的平均每次支付金額，以及2018年最大的支付日期和最小的支付日期的間隔
13）2018年購買的商品品類在兩個以上的用戶數
步驟總結：
1、先求出每個人購買的商品品類書
2、篩選出購買商品品類書大於2的用戶
3、統計符合條件的用戶有多少個
14）用戶激活時間在2018年，年齡段在20-30歲和30-40歲的婚姻情況分布
步驟總結：
1、先選出激活時間在2018年的用戶，並把他們所在的年齡段計算好，並提取出婚姻狀況；
如何select後面沒有進行聚合，則可以不用使用group by，直接使用where進行過濾就可以；
2、取出年齡段在20-30歲和30-40歲的用戶，把他們的婚姻狀況轉義成可理解的說明；
3、聚合計算，針對年齡段，婚姻狀況的聚合
5. SQL中只要用到聚合函數就一定要用到group by 嗎
SQL中只要用到聚合函數就不一定要用到group by。聚合函數是對一組值執行計算，並返回單個值，也被稱為組函數。 聚合函數可以應用於SELECT 查詢語句的 GROUP BY 子句的HAVING子句中，但不可用於WHERE語句中，因為WHERE是對逐條的行記錄進行篩選。
(5)hivesql聚合函數擴展閱讀：
Transact-SQL編程語言提供的聚合函數有：
1、AVG：返回指定組中的平均值，空值被忽略。例：select prd_no,avg(qty) from sales group by prd_no.
2、COUNT：返回指定組中項目的數量。例：select count(prd_no) from sales.
3、COUNT_BIG：返回指定組中的項目數量，與COUNT函數不同的是COUNT_BIG返回bigint值，而COUNT返回的是int值。例：select count_big(prd_no) from sales.
4、MIN：返回指定數據的最小值。例：select prd_no,min(qty) from sales group by prd_no.
5、MAX：返回指定數據的最大值。例：select prd_no,max(qty) from sales group by prd_no.