『壹』 hive 分頁sql語句
分頁實現的方式比較多了 下面舉個例子 比如 獲取前10條數據
註：同時需要記錄這10條中最大的id為preId，作為下一頁的條件。
select * from table order by id asc limit 10;
select * from table where id >preId order by id asc limit 10;
『貳』 Hive中Order by和Sort by的區別是什麼
Hive基於HADOOP來執行分布式程序的，和普通單機程序不同的一個特點就是最終的數據會產生多個子文件，每個recer節點都會處理partition給自己的那份數據產生結果文件，這導致了在HADOOP環境下很難對數據進行全局排序，如果在HADOOP上進行order by全排序，會導致所有的數據集中在一台recer節點上，然後進行排序，這樣很可能會超過單個節點的磁碟和內存存儲能力導致任務失敗。
一種替代的方案則是放棄全局有序，而是分組有序，比如不求全網路最高的點擊詞排序，而是求每種產品線的最高點擊詞排序。
使用order by會引發全局排序
select * from _click order by click desc;
使用distribute和sort進行分組排序
select * from _click distribute by proct_line sort by click desc;
distribute by + sort by就是該替代方案，被distribute by設定的欄位為KEY，數據會被HASH分發到不同的recer機器上，然後sort by會對同一個recer機器上的每組數據進行局部排序。
order by是全局有序而distribute+sort是分組有序
distribute+sort的結果是按組有序而全局無序的，輸入數據經過了以下兩個步驟的處理：
1) 根據KEY欄位被HASH，相同組的數據被分發到相同的recer節點；
2) 對每個組內部做排序
由於每組數據是按KEY進行HASH後的存儲並且組內有序，其還可以有兩種用途：
1) 直接作為HBASE的輸入源，導入到HBASE；
2) 在distribute+sort後再進行orderby階段，實現間接的全局排序；
不過即使是先distribute by然後sort by這樣的操作，如果某個分組數據太大也會超出rece節點的存儲限制，常常會出現137內存溢出的錯誤，對大數據量的排序都是應該避免的。
『叄』 Hive sql下的Order by和Sort by的區別
使用order
by會引發全局排序
select
*
from
_click order
by click
desc;
使用distribute和sort進行分組排序
select
*
from
_click distribute
by proct_line sort
by click
desc;
distribute
by
+
sort
by就是該替代方案，被distribute
by設定的欄位為KEY，數據會被HASH分發到不同的recer機器上，然後sort
by會對同一個recer機器上的每組數據進行局部排序。
『肆』 hive order by是升序還是降序
Hive基於HADOOP來執行分布式程序的，和普通單機程序不同的一個特點就是最終的數據會產生多個子文件，每個recer節點都會處理partition給自己的那份數據產生結果文件，這導致了在HADOOP環境下很難對數據進行全局排序，如果在HADOOP上進行order by全排序，會導致所有的數據集中在一台recer節點上，然後進行排序，這樣很可能會超過單個節點的磁碟和內存存儲能力導致任務失敗。一種替代的方案則是放棄全局有序，而是分組有序，比如不求全網路最高的點擊詞排序，而是求每種產品線的最高點擊詞排序。使用order by會引發全局排序select * from _click order by click desc;
使用distribute和sort進行分組排序
select * from _click distribute by proct_line sort by click desc;
distribute by + sort by就是該替代方案，被distribute by設定的欄位為KEY，數據會被HASH分發到不同的recer機器上，然後sort by會對同一個recer機器上的每組數據進行局部排序。
『伍』 hive中order by，distribute by，sort by和cluster by的區別和聯系
1、order
by
order
by
會對數據進行全局排序,和oracle和mysql等資料庫中的order
by
效果一樣，它只在一個rece中進行所以數據量特別大的時候效率非常低。而且當設置
：set
hive.
mapred.
mode
=strict的時候不指定limit，執行select會報錯，如下：LIMIT
must
also
be
specified.
2、sort
by
sort
by
是單獨在各自的rece中進行排序，所以並不能保證全局有序，一般和distribute
by
一起執行，而且distribute
by
要寫在sort
by前面。
如果mapred.rece.tasks=1和order
by效果一樣，如果大於1會分成幾個文件輸出每個文件會按照指定的欄位排序，而不保證全局有序。
sort
by
不受
hive.mapred.mode
是否為strict
,nostrict
的影響
3、distribute
by
用distribute
by
會對指定的欄位按照hashCode值對rece的個數取模，然後將任務分配到對應的rece中去執行，就是在maprece程序中的patition分區過程，默認根據指定key.hashCode()&Integer.MAX_VALUE%numRece
確定處理該任務的rece。
4、cluster
By
distribute
by
和
sort
by
合用就相當於cluster
by，但是cluster
by
不能指定排序為asc或
desc
的規則，只能是desc倒序排列。
『陸』 hive order by 和group by可以為同一個欄位嗎
不可以
order by
會對輸入做全局排序，因此只有一個
recer
（多個recer無法保證全局有序）
只有一個recer，會導致當輸入規模較大時，需要較長的計算時間。
set hive.mapred.mode=nonstrict; (default value /
默認值) set hive.mapred.mode=strict; order by 和資料庫中的Order by 功能一致，按照某一項
&
幾項排序輸出。
『柒』 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、對分組後的表進行條件篩選提取
『捌』 數據分析課程筆記 - 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 導出結果數據的命令：
以上就是這節課的全部內容了。做完整個練習，真的半條命都沒了。窗口函數果然很難，不過掌握方法、多多練習，學會拆解需求，一步一步來做，就能明顯降低難度。希望以後有機會能用到這么復雜的技能，哈哈~！
『玖』 hive sql 中 怎麼用變數
分頁實現的方式比較多了
下面舉個例子
比如
獲取前10條數據
註：同時需要記錄這10條中最大的id為preid，作為下一頁的條件。
select
*
from
table
order
by
id
asc
limit
10;
select
*
from
table
where
id
>preid
order
by
id
asc
limit
10;
『拾』 hive sql里，幫我描述一個簡單的sql的原理
select a.id,a.info,b.num from a join b on a.id=b.id and where b.num>=10
兩個表做關聯，首先where會過濾掉不需要的數據。
至於表怎麼做map和rece操作，在hive里的表是虛擬的，其實還是對hdfs文件進行操作，你可以在hdfs:///user/hive/warehouse路徑下找到以表名來命名的文件，裡面就是表的內容，可以執行-cat命令查看。所以，它的map操作很簡單，就是按行讀文件，然後會根據hive的默認分隔符\001對每行進行切分。切分完成後就會按照你SQL指定的邏輯進行合並，最後再輸出成hdfs文件，只不過在hive裡面看它是以表的形式展現的。
job數會在你執行sql語句之後緊接著有相應的日誌記錄，
Total MapRece jobs = 2
Launching Job 1 out of 2
Number of rece tasks not specified. Estimated from input data size: 2
In order to change the average load for a recer (in bytes):
set hive.exec.recers.bytes.per.recer=<number>
In order to limit the maximum number of recers:
set hive.exec.recers.max=<number>
In order to set a constant number of recers:
這樣就是有兩個job，正在執行第一個job。
Hadoop job information for Stage-1: number of mappers: 5; number of recers: 2
而這個就會告訴你有多少個mapper和recer。
像你寫的這個sql有join操作，而且是hiveSQL裡面最普通的join，那麼一定會有recer參與，如果數據量很大，比如上千萬條記錄，join就會特別慢，job進度就會一直卡在rece操作。可以改成mapjoin或者sort merge bucket mapjoin。
其實hive效率不高，不適合實時查詢，即使一個表為空，用hive進行查詢也會很耗時，因為它要把sql語句翻譯成MR任務。雖然簡化了分布式編程，但是效率上就會付出代價。
你的這句sql應該會翻譯成一個JOB來執行，就是簡單地map和rece。
maprece就是按行讀文件，然後切分，合並，輸出成文件。