Hive解题思路
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? Hive解題思路
1 相關知識講解
1.1 HQL語句的語法
sql語句的語法：
select ..... from .... join ..... where .....group by ... having...order by|sort by|cluster by|distribute by ?....
（1）group by：按照某些字段的值進行分組，相同的一組。注意：限制了select字段，發生在select之前，不可以使用select別名，select后面非聚合列，必須出現在group by中 select后面除了普通列就是一些聚合操作 group by后面也可以跟表達式。
（2）where和having：都是限定返回的數據集。
where：對查詢結果進行分組前，將不符合where條件的行去掉，即在分組之前過濾數據，條件中不能包含聚組函數，使用where條件顯示特定的行。
having：在分組group by后，將分組后的不符合條件的數據過濾掉，
查詢語句的聚合函數只能出現在select?和group?by?,having?子句。
（3）四個排序關鍵字
①order by：對輸入做全局排序，所以只有一個reduce，當數據量大時，導致效率低；
②sort by：局部排序，不是全局排序，其在數據進入 reducer 前完成排序。如果用 sort by 進行排序，并且設置 mapred.reduce.tasks>1，那么 sort by只保證每個 reducer 的輸出有序，不能保證全局有序；
③distribute by：根據指定的字段將數據分到不同的 reducer，分發算法是 hash 散列；
④cluster by：如果distribute by后面的字段和sort by后面的字段一樣，cluster by = distribute by + sort by
1.2 窗口函數over
聚合函數可以將多行數據按照規則聚集為一行，一般來講聚集后的行數是要少于聚集前的行數的，但是有時我們想要既顯示聚集前的數據，又要顯示聚集后的數據，這時我們便引入了窗口函數。窗口函數的執行時間且僅位于Order by字句之前。
分析函數 ?over（開窗依據distribute by |partition by 每一個窗口的排序規則 sort by |order by ?） 子句 ?窗口函數 ? 開窗函數
distribute by與 sort by搭配，partition by與order by 。
over子句不可單獨使用，有兩種搭配方式：
（1）與聚合函數一起使用，求的是每一個窗口內的聚合
①聚合函數 ?+ over(distribute by ) ?整個窗口的整體聚合
select max(sumpv) over(distribute by userid) from test;
②聚合函數 ?+ over(distribute by ?sort by ) 截止到排序數據之前的，即當前。sort by子句會讓輸入的數據強制排序
執行順序：distribute by 整個窗口 + sort by一條 + max?
select userid,month,sumpv,max(sumpv) over(distribute by userid sort by sumpv) from test;
③如果已經通過使用partition by子句將數據進行了分組的處理，如果我們想要更細粒度的劃分，我們就要引入window子句了
window子句：?- PRECEDING：往前? ? ? ? ? ? ??- FOLLOWING：往后? ? ? ? ? ? ? ??- CURRENT ROW：當前行? ? ? ? ?
- UNBOUNDED：起點? ? ? ? ? UNBOUNDED PRECEDING 從前面的起點? ? ?UNBOUNDED FOLLOWING：到后面的終點
| select? ? sum(sumpv) over()? as sumpvv1?? ?#所有行相加 ? sum(sumpv) over(partition by userid) as sumpvv2?? #按userid分組，組內數據相加 ? sum(sumpv) over(partition by userid order by month) as sumpvv3?? #按userid分組，組內數據累加 ? sum(sumpv) over(partition by userid order by month rows between UNBOUNDED PRECEDING and current row) as sumpvv4?? #按userid分組，組內數據累加 ? sum(sumpv) over(partition by userid order by month rows between 1 PRECEDING and current row) as sumpvv5? #當前行和前面一行做聚合 ? sum(sumpv) over(partition by userid order by month rows between 1 PRECEDING and? 1 FOLLOWING) as sumpvv6? #當前行和前邊一行及后面一行 ? sum(sumpv) over(partition by userid order by month rows between current row and UNBOUNDED FOLLOWING) as sumpvv? 7 #當前行及后面所有行 ? from test; |
④over(partition by)與group by的主要區別為，帶上group by的hql語句只能顯示與分組聚合相關的字段，而帶上over(partition by ......)的hql語句能顯示所有字段.。
（2）與序列函數使用
序列函數是不支持window子句的
①NTILE(n)，用于將分組數據按照順序切分成n片，返回當前切片值，如果切片不均勻，默認增加第一個切片的分布
使用場景：假如我們想要每位顧客購買數量前1/5的交易記錄,我們便可以使用這個函數.
| select?userid,month,sumpv, ?nitle(5) over()? #全局數據切片 ??nitle(5) over(partition by userid)??#按照userid進行分組，在分組內切片 ??nitle(5) over(order by month)?#全局按照月份升序排列，數據切分成5份 ??nitle(5) over(partition by userid order by month) #按照userid進行分組，在分組內按照月份升序排列，數據切分成5份 from test; |
②row_number()、rank()、dense_rank()
row_number()：生成分組內記錄的序列,從1開始，按照順序,row_number()的值不會存在重復,當排序的值相同時,按照表中記錄的順序進行排列；
RANK()：生成數據項在分組中的排名，排名相等會在名次中留下空位 ;
DENSE_RANK()：生成數據項在分組中的排名，排名相等會在名次中不會留下空位。
③lag和lead
LAG ?(scalar_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)
LEAD (scalar_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause);
這兩個函數為常用的窗口函數,可以返回上下數據行的數據.
使用場景：如要查看顧客上次的購買時間可以這樣去查詢
| select?userid,month,sumpv, ??lag(month,1,'1900-01-01')?over(partition by userid order by month) #按照userid進行分組，在分組內按照月份升序排列，取上一行數據的值。當沒有上一行時取1900-01-01' ?lag(month,2')?over(partition by userid order by month) #按照userid進行分組，在分組內按照月份升序排列，取上面2行的數據的值，當沒有上兩行時取NULL ?lead(month,1,'1900-01-01')?over(partition by userid order by month) #按照userid進行分組，在分組內按照月份升序排列，取上下行數據的值 from test; |
當lag函數未設置行數值時,默認為1行.未設定取不到時的默認值時,取null值.
④first_value和last_value
first_value取分組內排序后，截止到當前行，第一個值 ；last_value取分組內排序后，截止到當前行，最后一個值。
| select?userid,month,sumpv, ??first_value(month,1,'1900-01-01')?over(partition by userid order by month) #按照userid進行分組，在分組內按照月份升序排列，截止到當前行的第一個值 ?last_value(month,2')?over(partition by userid order by month) #按照userid進行分組，在分組內按照月份升序排列，截止到當前行的最后一個值，即當前行 ?lead(month,1,'1900-01-01')?over(partition by userid order by month) #按照userid進行分組，在分組內按照月份升序排列，取上下行數據的值 from test; |
2 使用窗口分析函數
| 現有這么一批數據 三個字段的意思： A,2015-01,5 現要求出：如每個用戶截止到每月為止的最大單月訪問次數和累計到該月的總訪問次數 |
分析：求每個用戶截止到每月為止的最大單月訪問次數和累計到該月的總訪問次數
既顯示聚集前的數據，又要顯示聚集后的數據，并且如果單是求最大單月訪問次數的話直接按照用戶+月份分組就行，但是還要求累計到該月，就應該聯想當窗口函數，窗口函數有很多，比如：聚合函數 ?+ over() ---整個窗口的整體聚合? ?first_value()?over--分組后截止到當前行第一個值等。因為是要求累計到當前月，所以前面的數據是全部都要的。那么就可以使用 sum(字段名)? + over(distribute by 字段名?sort by 字段名) ---------截止到排序數據之前的即當前 或?sum(字段名) over(partition by 字段名 order by 字段名 rows between UNBOUNDED PRECEDING and current row)
| select??userid, ?MONTH, ?sumpv, ?max(sumpv) over ( ?? ?PARTITION BY userid ?? ?ORDER BY ?? ??? ?MONTH ) maxpv, ?sum(sumpv) over ( ?? ?PARTITION BY userid ?? ?ORDER BY ?? ??? ?MONTH ) sumpvv ?? FROM ?? ?test; |
當然這題還有其他的解決方案，比如自連接
| SELECT ?? ?buserid, ?? ?bmonth, ?? ?bsumpv, ?? ?max(asumpv) maxpv, ?? ?sum(asumpv) sumpv FROM ?? ?( ?? ??? ?SELECT ?? ??? ??? ?* ?? ??? ?FROM ?? ??? ??? ?( ?? ??? ??? ??? ?SELECT ?? ??? ??? ??? ??? ?a.userid auserid, ?? ??? ??? ??? ??? ?a. MONTH amonth, ?? ??? ??? ??? ??? ?a.sumpv asumpv, ?? ??? ??? ??? ??? ?b.userid buserid, ?? ??? ??? ??? ??? ?b. MONTH bmonth, ?? ??? ??? ??? ??? ?b.sumpv bsumpv ?? ??? ??? ??? ?FROM ?? ??? ??? ??? ??? ?test a ?? ??? ??? ??? ?JOIN test b ON a.userid = b.userid ?? ??? ??? ?) c ?? ??? ?WHERE ?? ??? ??? ?amonth <= bmonth ?? ?) d GROUP BY ?? ?buserid, ?? ?bmonth, ?? ?bsumpv; |
總結：當題目要求既顯示聚集前的數據，又要顯示聚集后的數據，并且還要對數據進行累加的時候要想到用窗口函數。
?
3 行列轉換
（1）行轉列
數據如下：
求：所有數學課程成績 大于 語文課程成績的學生的學號
思路：要求的是數學成績>語文成績的學生的學號，目前的數據一行是每個學生每一科的成績，我們查詢出來的數據是一條一條的，并且可能每個學生的科目數量有可能不一樣，那么要比較不同的行的數據是非常困難的。而我們知道當數據在同一行時，這一行的數據是非常好進行比較的，那么我們就可以將行轉換成列，那么要比較的就在同一行了，處理起來非常容易。
那么接下來就是怎樣將列轉換成行，我們知道查詢出來的結果的列是根據select后面的字段名的個數而定的，select后有多少個字段，查詢的結果就有多少個列。因為科目是固定的，那么列就加上所有的科目，所以我們就可以原表中的每一行查詢出來后進行進行判斷，并對這條記錄中的科目放到對應的select后字段下。那么就可以使用case when條件判斷。
| case when? switch(字段){ 語法2：相當于if else? ? |
使用條件判斷語句后有如下結果
| SELECT ?? ?sid, ?? ?CASE WHEN course = "yuwen" THEN ?? ?score ELSE ?? ?0 END yuwen, ?CASE WHEN course = "shuxue" THEN ?? ?score ELSE ?? ?0 END shuxue FROM ?? ?course; |
接下來就要合并相同id的數據，最終語句如下
| SELECT ? |
?
需求如下：
思路：因為要顯示所有的課程，所以首先要使用collect_set（）函數---將分組中的某列轉為一個數組返回，獲取到所有課程，然后獲取到每一個學員所選的科目，判斷有哪些科目，這里可以用if判斷語句。所以把查詢出一個所有課程作為一個表，每一個學員所選的科目作為另一個表，因為所有課程的表只有一條數據，所以可以用笛卡爾積。
hive 處于安全考慮 ? 沒有開啟笛卡爾積，所以要手動開啟，設置如下
set hive.strict.checks.cartesian.product=false;
set hive.mapred.mode=nonstrict;
| select? ? ? )c; |
總結：行轉列即是把行轉列前的分行條件，分別作為行轉列后的各列的篩選條件?？梢越柚鷆ase when，if等判斷函數，與collect_set(),collect_list()函數
（2）列轉行在下面的TopN例子中一起講解
4 group by相關問題
| 現有如下數據 2001010310 需求：求出每一年的最高溫度是哪一天（日期， 最高溫度） |
這題根據題意可知需要求每一年...所以對年進行分組，相同年份的為一組，分組之后使用max函數就可知最高溫度是哪一天。因為最高溫度那一天只有一條，所以可以和原表進行笛卡爾積連接，即可得知那一天的的日期和最高溫度等。
| select? ?? ?a.year year,a.max_temp max_temp,substr(b.data,1,8) day? from? ?? ?(select? ?? ??? ?substr(data,1,4) year,max(cast(substr(data,-2) as int)) max_temp? ?? ?from test04? ?? ?group by substr(data,1,4)) a? ?? ??? ?join test04 b? ?? ??? ?on a.year=substr(b.data,1,4) and a.max_temp=cast(substr(b.data,-2) as int); |
總結：當我們遇到每一，每一個這種字眼時，就需要使用到分組，分組的字段就是跟在這些字眼后面的字段。
5 求TopN，及列轉行相關問題
| 現在有這樣一份數據： 字段的意義： 每一條記錄中的愛好有多個值，以"-"分隔 需求：求出每種愛好中，年齡最大的兩個人（愛好，年齡，姓名） |
總體思路：由求出每種愛好中可知要對愛好進行分組，根據年齡最大的兩個人可知分組之后對年齡進行倒敘排序，然后取前兩個。
因為表中的愛好以a-c-d-f方式存在，這樣無法對每種愛好進行分組，我們要想對每種愛好進行分組，那么就必須一行只有一種愛好我們才能求得。所以這里我們就需要列轉換成行，就需要用到explode（）函數，explode只能有兩種類型的參數，如下：
explode(ARRAY)： 列表中的每個元素生成一行
explode(MAP)：map中每個key-value對，生成一行，key為一列，value為一列
注意：explode()是有很多限制的，限制如下
| 1、No other expressions are allowed in SELECT? #SELECT中不允許有其他表達式 如：SELECT id, explode(adid_list) AS aa... is not supported ? 2、UDTF's can't be nested? ?#UDTF不能嵌套 如：SELECT explode(explode(adid_list)) AS aaa... is not supported ? 3、GROUP BY / CLUSTER BY / DISTRIBUTE BY / SORT BY is not supported? #不支持分組排序等 如：SELECT explode(adid_list) AS aaa?... GROUP BY aaa?is not supported |
，所以經常和lateral view搭配，除以上限制?LATERAL VIEW explode(expression) tableAlias AS columnAlias (',' columnAlias)
這里將愛好explode（）炸裂之后，有幾個愛好對應就生成幾行，那么就需要將原表與炸裂后的愛好進行連接，那么怎么連接呢？原表的數據全都需要所以用原表做連接炸裂后的愛好表，因為炸裂后的就得出一個愛好的字段，無法用其他字段進行關聯，那就用array_contains（）函數，判斷炸裂出來的愛好是否在原始的a-c-d-f中，如果在的話，那么就關聯起來。然后再對姓名，年齡，愛好分組，起去重作用。到這就將炸裂后的字段關聯上了，因為需求中要保留原表的愛好，年齡，姓名等信息，那么就可以使用窗口函數row_number() over(partition by hobc order by agec desc)對愛好排序，對年齡分組，生成一個新的序列字段。因為窗口函數是除了order外最后執行的，所以我們需要在生成窗戶函數的這個表外進行過濾。最終代碼如下：
| select? ?? ?d.hobc,d.agec,d.namec? from? ?? ?(select? ?? ??? ?hobc,agec,namec, row_number() over (partition by hobc order by agec desc) as seqs? ?? ?from ?? ??? ?(select? ?? ??? ??? ?b.name as namec,b.age as agec,a.hob as hobc? ?? ??? ?from test05 b? ?? ??? ??? ?left join ?? ??? ??? ?(select? ?? ??? ??? ??? ?explode(hobby) as hob? ?? ??? ??? ?from test05 ) a ? ?? ??? ??? ?on array_contains(hobby,hob) ?? ??? ?group by b.name,b.age,a.hob) c ?? ?)d where d.seqs <=2; |
總結：當遇到需求中有最...的幾個，前幾個這種字眼眼時，就可以用求解TopN的思路去分析。當列中字段的內容是以組合的形式存在，而我們又要對該列中的內中進行分組等操作，就需要使用explode()函數，將列炸裂成行，由于explode()有諸多限制，所以經常會和lateral view一起使用。
6 內置函數的自定義UDF
| 統計每個月每天出現的情況，比如下面兩串數字 現在是想把這兩串數字拼起來，比如第一天，通話標識為0，流量標識為1，我需要的數字是兩個標識同一天有1的就置為1，兩個1也置為1。結果為 1101010101010101010100101010101 數據： |
建表語句：create table test06(day1 string,day2 string) row format delimited fields terminated by "," ;
（1）內置函數實現
思路：將通話記錄和流量記錄分別用兩個表取出來，并使用explode()函數炸裂，并不對炸裂后的結果使用row_number() over() ，生成行號，然后將這兩個表根據行號進行左連接查詢（有為空的就可以過濾掉），最后再用concat_ws()行數將列轉換成行。
最終實現如下：
| select? ?? ?concat_ws('',collect_list(cast(dyd as string)))? from ?? ?(select c.seqs,cast(c.dyc as int)|cast(b.dyb as int) as dyd? ?? ?from? ?? ??? ?(select? ?? ??? ??? ?row_number() over() as seqs,a.dy as dyc ?? ??? ?from? ?? ??? ??? ?(select explode(split(day2,"")) as dy? ?? ??? ??? ?from test06) a ?? ??? ?where a.dy!='' ?? ??? ?order by seqs desc ?? ??? ?) c ?? ??? ?left join ?? ??? ?(select row_number() over () as seqs,a.dy as dyb ?? ??? ?from? ?? ??? ??? ?(select explode(split(day1,"")) as dy? ?? ??? ??? ?from test06) a ?? ??? ?where a.dy!='' ?? ??? ?order by seqs desc ?? ??? ?) b ?? ??? ?on c.seqs=b.seqs order by c.seqs desc) d; |
（2）自定義UDF臨時函數步驟：
①繼承 org.apache.hadoop.hive.ql.exec.UDF，重載 evaluate 方法
②打成 jar 包上傳到服務器
③將 jar 包添加到 hive 的 classpath? ? ? ? ? ??hive>add JAR “jar包在服務器的路徑”
④查看jar包是否加入成功，hive> list jar
⑤創建臨時函數與開發好的 class 關聯起來
hive>create temporary function tolowercase as '類名全路徑';
⑥show functions;就可以看多多了一個函數，不過這個為臨時函數。
//思路將兩串數字遍歷一遍，每一為進行或運算，再拼接起來 public class MyUDF extends UDF {public String evaluate(String str1,String str2)throws HiveException {if(str1.length()!=str2.length()) {throw new UDFArgumentTypeException(0,"The lengths of the arguments are inconsistent");}String[] s1=str1.split("");String[] s2=str2.split("");StringBuffer str = new StringBuffer();for(int i=0;i<s1.length;i++) {if(!s1[i].equals("")) {Integer or=Integer.parseInt(s1[i])|Integer.parseInt(s2[i]);String temp=or.toString();if(temp!=null) {str.append(temp);}}}return str.toString();} }?
總結
- 上一篇： 微信小程序 地图组件使用
- 下一篇： android 打开移动开关,教你一个让