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. DDL 操作
DDL操作類型
1.2 建表：
建表
•CREATE TABLE 創建一個指定名字的表。如果相同名字的表已經存在，則拋出異常；用戶可以用 IF NOT EXIST 選項來忽略這個異常
•EXTERNAL 關鍵字可以讓用戶創建一個外部表，在建表的同時指定一個指向實際數據的路徑（LOCATION）
•LIKE 允許用戶復制現有的表結構，但是不復制數據
•COMMENT可以為表與欄位增加描述
•ROW FORMAT
DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]
用戶在建表的時候可以自定義 SerDe 或者使用自帶的 SerDe。如果沒有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，將會使用自帶的 SerDe。在建表的時候，用戶還需要為表指定列，用戶在指定表的列的同時也會指定自定義的 SerDe，Hive 通過 SerDe 確定表的具體的列的數據。
•STORED AS
SEQUENCEFILE
| TEXTFILE
| RCFILE
| INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname
如果文件數據是純文本，可以使用 STORED AS TEXTFILE。如果數據需要壓縮，使用 STORED AS SEQUENCE 。
1.3 創建簡單表：
hive> CREATE TABLE pokes (foo INT, bar STRING);
1.4 創建外部表：
創建外部表
1.5 建分區表
分區表
1.6 建Bucket表
創建Bucket表
1.7 創建表並創建索引欄位ds
hive> CREATE TABLE invites (foo INT, bar STRING) PARTITIONED BY (ds STRING);
1.8 復制一個空表
CREATE TABLE empty_key_value_store
LIKE key_value_store;
例子
例子
導入數據表的數據格式是：欄位之間是tab鍵分割，行之間是斷行。
及要我們的文件內容格式：
100636 100890 c5c86f4cddc15eb7 yyyvybtvt
100612 100865 97cc70d411c18b6f gyvcycy
100078 100087 ecd6026a15ffddf5 qa000100
1.9 顯示所有表：
hive> SHOW TABLES;
1.10 按正條件（正則表達式）顯示表，
hive> SHOW TABLES '.*s';
DDL類型-修改表結構
1.21 表添加一列：
hive> ALTER TABLE pokes ADD COLUMNS (new_col INT);
1.22 添加一列並增加列欄位注釋
hive> ALTER TABLE invites ADD COLUMNS (new_col2 INT COMMENT 'a comment');
1.23 更改表名：
hive> ALTER TABLE events RENAME TO 3koobecaf;
1.24 刪除列：
hive> DROP TABLE pokes;
1.25 增加、刪除分區
•增加
ALTER TABLE table_name ADD [IF NOT EXISTS] partition_spec [ LOCATION 'location1' ] partition_spec [ LOCATION 'location2' ] ...
partition_spec:
: PARTITION (partition_col = partition_col_value, partition_col = partiton_col_value, ...)
•刪除
ALTER TABLE table_name DROP partition_spec, partition_spec,...
1.26 重命名表
ALTER TABLE table_name RENAME TO new_table_name
1.27 修改列的名字、類型、位置、注釋：
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]
這個命令可以允許改變列名、數據類型、注釋、列位置或者它們的任意組合
1.28 表添加一列：
hive> ALTER TABLE pokes ADD COLUMNS (new_col INT);
1.29 添加一列並增加列欄位注釋
hive> ALTER TABLE invites ADD COLUMNS (new_col2 INT COMMENT 'a comment');
1.30 增加/更新列
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)
• ADD是代表新增一欄位，欄位位置在所有列後面(partition列前)
REPLACE則是表示替換表中所有欄位。
1.31 增加表的元數據信息
ALTER TABLE table_name SET TBLPROPERTIES table_properties table_properties:
:[property_name = property_value…..]
•用戶可以用這個命令向表中增加metadata
1.31改變表文件格式與組織
ALTER TABLE table_name SET FILEFORMAT file_format
ALTER TABLE table_name CLUSTERED BY(userid) SORTED BY(viewTime) INTO num_buckets BUCKETS
這個命令修改了表的物理存儲屬性
1.4 創建／刪除視圖
CREATE VIEW [IF NOT EXISTS] view_name [ (column_name [COMMENT column_comment], ...) ][COMMENT view_comment][TBLPROPERTIES (property_name = property_value, ...)] AS SELECT
•增加視圖
•如果沒有提供表名，視圖列的名字將由定義的SELECT表達式自動生成
•如果修改基本表的屬性，視圖中不會體現，無效查詢將會失敗
•視圖是只讀的，不能用LOAD/INSERT/ALTER
•DROP VIEW view_name
•刪除視圖
1.5 創建資料庫
CREATE DATABASE name
1.6 顯示命令
3. hive sql的語法幫助在哪
Hive 是基於Hadoop 構建的一套數據倉庫分析系統，它提供了豐富的SQL查詢方式來分析存儲在Hadoop 分布式文件系統中的數據，可以將結構化的數據文件映射為一張資料庫表，並提供完整的SQL查詢功能，可以將SQL語句轉換為MapRece任務進行運行，通過自己的SQL 去查詢分析需要的內容，這套SQL 簡稱Hive SQL，使不熟悉maprece 的用戶很方便的利用SQL 語言查詢，匯總，分析數據。而maprece開發人員可以把己寫的mapper 和recer 作為插件來支持Hive 做更復雜的數據分析。
它與關系型資料庫的SQL 略有不同，但支持了絕大多數的語句如DDL、DML 以及常見的聚合函數、連接查詢、條件查詢。HIVE不適合用於聯機online)事務處理，也不提供實時查詢功能。它最適合應用在基於大量不可變數據的批處理作業。
HIVE的特點：可伸縮（在Hadoop的集群上動態的添加設備），可擴展，容錯，輸入格式的鬆散耦合。
Hive 的官方文檔中對查詢語言有了很詳細的描述，請參考：http://wiki.apache.org/hadoop/Hive/LanguageManual ，本文的內容大部分翻譯自該頁面，期間加入了一些在使用過程中需要注意到的事項。
1. DDL 操作
DDL
?建表
?刪除表
?修改表結構
?創建／刪除視圖
?創建資料庫
?顯示命令
建表：
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
[(col_name data_type [COMMENT col_comment], ...)]
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
[CLUSTERED BY (col_name, col_name, ...)
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION hdfs_path]
?CREATE TABLE 創建一個指定名字的表。如果相同名字的表已經存在，則拋出異常；用戶可以用 IF NOT EXIST 選項來忽略這個異常
?EXTERNAL 關鍵字可以讓用戶創建一個外部表，在建表的同時指定一個指向實際數據的路徑（LOCATION）
?LIKE 允許用戶復制現有的表結構，但是不復制數據
?COMMENT可以為表與欄位增加描述
?ROW FORMAT
DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]
用戶在建表的時候可以自定義 SerDe 或者使用自帶的 SerDe。如果沒有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，將會使用自帶的 SerDe。在建表的時候，用戶還需要為表指定列，用戶在指定表的列的同時也會指定自定義的 SerDe，Hive 通過 SerDe 確定表的具體的列的數據。
?STORED AS
SEQUENCEFILE
| TEXTFILE
| RCFILE
| INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname
如果文件數據是純文本，可以使用 STORED AS TEXTFILE。如果數據需要壓縮，使用 STORED AS SEQUENCE 。
創建簡單表：
hive> CREATE TABLE pokes (foo INT, bar STRING);
創建外部表：
CREATE EXTERNAL TABLE page_view(viewTime INT, userid BIGINT,
page_url STRING, referrer_url STRING,
ip STRING COMMENT 'IP Address of the User',
country STRING COMMENT 'country of origination')
COMMENT 'This is the staging page view table'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\054'
STORED AS TEXTFILE
LOCATION '';
建分區表
CREATE TABLE par_table(viewTime INT, userid BIGINT,
page_url STRING, referrer_url STRING,
ip STRING COMMENT 'IP Address of the User')
COMMENT 'This is the page view table'
PARTITIONED BY(date STRING, pos STRING)
ROW FORMAT DELIMITED 『\t』
FIELDS TERMINATED BY '\n'
STORED AS SEQUENCEFILE;
建Bucket表
CREATE TABLE par_table(viewTime INT, userid BIGINT,
page_url STRING, referrer_url STRING,
ip STRING COMMENT 'IP Address of the User')
COMMENT 'This is the page view table'
PARTITIONED BY(date STRING, pos STRING)
CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS
ROW FORMAT DELIMITED 『\t』
FIELDS TERMINATED BY '\n'
STORED AS SEQUENCEFILE;
創建表並創建索引欄位ds
hive> CREATE TABLE invites (foo INT, bar STRING) PARTITIONED BY (ds STRING);
復制一個空表
CREATE TABLE empty_key_value_store
LIKE key_value_store;
例子
create table user_info (user_id int, cid string, ckid string, username string)
row format delimited
fields terminated by '\t'
lines terminated by '\n';
導入數據表的數據格式是：欄位之間是tab鍵分割，行之間是斷行。
及要我們的文件內容格式：
100636 100890 c5c86f4cddc15eb7 yyyvybtvt
100612 100865 97cc70d411c18b6f gyvcycy
100078 100087 ecd6026a15ffddf5 qa000100
顯示所有表：
hive> SHOW TABLES;
按正條件（正則表達式）顯示表，
hive> SHOW TABLES '.*s';
修改表結構
?增加分區、刪除分區
?重命名表
?修改列的名字、類型、位置、注釋
?增加/更新列
?增加表的元數據信息
表添加一列 ：
hive> ALTER TABLE pokes ADD COLUMNS (new_col INT);
添加一列並增加列欄位注釋
hive> ALTER TABLE invites ADD COLUMNS (new_col2 INT COMMENT 'a comment');
更改表名：
hive> ALTER TABLE events RENAME TO 3koobecaf;
刪除列：
hive> DROP TABLE pokes;
增加、刪除分區
?增加
ALTER TABLE table_name ADD [IF NOT EXISTS] partition_spec [ LOCATION 'location1' ] partition_spec [ LOCATION 'location2' ] ...
partition_spec:
: PARTITION (partition_col = partition_col_value, partition_col = partiton_col_value, ...)
?刪除
ALTER TABLE table_name DROP partition_spec, partition_spec,...
重命名表
?ALTER TABLE table_name RENAME TO new_table_name
修改列的名字、類型、位置、注釋：
?ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]
?這個命令可以允許改變列名、數據類型、注釋、列位置或者它們的任意組合
表添加一列 ：
hive> ALTER TABLE pokes ADD COLUMNS (new_col INT);
添加一列並增加列欄位注釋
hive> ALTER TABLE invites ADD COLUMNS (new_col2 INT COMMENT 'a comment');
增加/更新列
?ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)
? ADD是代表新增一欄位，欄位位置在所有列後面(partition列前)
REPLACE則是表示替換表中所有欄位。
增加表的元數據信息
?ALTER TABLE table_name SET TBLPROPERTIES table_properties table_properties:
:[property_name = property_value…..]
?用戶可以用這個命令向表中增加metadata
改變表文件格式與組織
?ALTER TABLE table_name SET FILEFORMAT file_format
?ALTER TABLE table_name CLUSTERED BY(userid) SORTED BY(viewTime) INTO num_buckets BUCKETS
附上出處鏈接：http://blog.itpub.net/26613085/viewspace-1224043/
4. hive 怎麼添加 表注釋語法
要添加註釋，只需要用單引號』作為注釋文字的開頭。注釋符告訴Visual Basic，忽略這個符號後面的內容，這些內容就是代碼段中的注釋部分，在代碼編輯器中以綠色字元顯示。
注釋可以和語句在同一行，寫在語句的後面，也可占據一整行。
例如：
'在文本框中放歡迎詞。
Private Sub Command1_Click()
Text1. Text="Hello." '把文本框Text1的屬性設置為Hello。
End Sub
注意，不能在同一行上把注釋接在續行符後面。
5. Hive一行拆多行、多行拼一行
舉例說明：
有兩張表差燃 sen_tbl(敏感欄位定義表) 、 msg_tbl(消息表) 。虛陪虛其中消息表的消息是一個json字元串，最多有兩層json嵌套。而json消息中的某些value是含有敏感信息的，key-value是否敏感的定義存放在 sen_tbl 中。
現在要做的是將json消息中的敏感value加密，然後分成不含敏感的json，和只含敏感的json。所以需要分為兩步亂櫻：
SQL執行結果數據在中沒有對齊，在其他編輯器入notepad++中是可以對齊的
6. 2021-05-26-Hive-20(格式化SQL多行變成一行)
好記性不如爛筆頭,記錄一下
7. hive如何同時執行多段sql
hive sql放到文件當中，之後 hive -f filename執行
8. Hive SQL語句執行順序
Hive 中 sql 語句的執行順序如下：
from .. where .. join .. on .. select .. group by .. select .. having .. distinct .. order by .. limit .. union/union all
下面我們通過一個 sql 語句分析下：
上面這條 sql 語句是可以成功執行的，我們看下它在 MR 中的執行順序：
Map 階段 ：
Rece 階段 ：
上面這個執行順序到底對不對呢，我們可以通過 explain 執行計劃來看下，內容過多，我們分階段來看。
我們看到 Stage-5 是根，也就是最先執行 Stage-5，Stage-2 依賴 Stage-5，Stage-0 依賴 Stage-2。
圖中標 ① 處是表掃描操作，注意先掃描的 b 表，也就是 left join 後面的表，然後進行過濾操作（圖中標 ② 處），我們 sql 語句中是對 a 表進行的過濾，但是 Hive 也會自動對 b 表進行相同的過濾操作，這樣可以減少關聯的數據量。
先掃描 a 表（圖中標 ① 處）；接下來進行過濾操作 idno > �'（圖中標 ② 處）；然後進行 left join，關聯的 key 是 idno（圖中標 ③ 處）；執行完關聯操作之後會進行輸出操作，輸出的是三個欄位，包括 select 的兩個欄位加 group by 的一個欄位（圖中標 ④ 處）；然後進行 group by 操作，分組方式是 hash（圖中標 ⑤ 處）；然後進行排序操作，按照 idno 進行正向排序（圖中標 ⑥ 處）。
首先進行 group by 操作，注意此時的分組方式是 mergepartial 合並分組（圖中標 ① 處）；然後進行 select 操作，此時輸出的欄位只有兩個了，輸出的行數是 30304 行（圖中標 ② 處）；接下來執行 having 的過濾操作，過濾出 count_user>1 的欄位，輸出的行數是 10101 行（圖中標 ③ 處）；然後進行 limit 限制輸出的行數（圖中標 ④ 處）；圖中標 ⑤ 處表示是否對文件壓縮，false 不壓縮。
限制最終輸出的行數為 10 行。
通過上面對 SQL 執行計劃的分析，總結以下幾點：
9. Hive SQL執行計劃深度解析
Hive SQL執行計劃深度解析 - An342647823的專欄 - 博客頻道 - CSDN.NET
http://blog.csdn.net/an342647823/article/details/36385479
美團網技術陳純大作，值得擁有。
Hive是基於Hadoop的一個數據倉庫系統，在各大公司都有廣泛的應用。美團數據倉庫也是基於Hive搭建，每天執行近萬次的Hive ETL計算流程，負責每天數百GB的數據存儲和分析。Hive的穩定性和性能對我們的數據分析非常關鍵。
在幾次升級Hive的過程中，我們遇到了一些大大小小的問題。通過向社區的咨詢和自己的努力，在解決這些問題的同時我們對Hive將SQL編譯為MapRece的過程有了比較深入的理解。對這一過程的理解不僅幫助我們解決了一些Hive的bug，也有利於我們優化Hive SQL，提升我們對Hive的掌控力，同時有能力去定製一些需要的功能。
10. Hive sql及窗口函數
hive函數：
1、根據指定條件返回結果：case when then else end as
2、基本類型轉換：CAST（）
3、nvl：處理空欄位：三個str時，是否為空可以指定返回不同的值
4、sql通配符： https://www.w3school.com.cn/sql/sql_wildcards.asp
5、count(1)與COUNT(*)：返回行數
如果表沒有主鍵，那麼count（1）比count（*）快；
如果有主鍵，那麼count（主鍵，聯合主鍵）比count（*）快；
count(1)跟count(主鍵)一樣，只掃描主鍵。count(*)跟count(非主鍵)一樣，掃描整個表。明顯前者更快一些。
性能問題：
1.任何情況下SELECT COUNT(*) FROM tablename是最優選擇,(指沒有where的情況）；
2.盡量減少SELECT COUNT(*) FROM tablename WHERE COL = 『value』 這種查詢；
3.杜絕SELECT COUNT(COL) FROM tablename WHERE COL2 = 『value』 的出現。
count(expression)：查詢 is_reply=0 的數量： SELECT COUNT(IF(is_reply=0,1,NULL)) count FROM t_iov_help_feedback;
6、distinct與group by
distinct去重所有distinct之後所有的欄位，如果有一個欄位值不一致就不作為一條
group by是根據某一欄位分組，然後查詢出該條數據的所需欄位，可以搭配 where max(time)或者Row_Number函數使用，求出最大的一條數據
7、使用with 臨時表名 as() 的形式,簡單的臨時表直接嵌套進sql中，復雜的和需要復用的表寫到臨時表中，關聯的時候先找到關聯欄位，過濾條件最好在臨時表中先過濾後關聯
處理json的函數：
split(json_array_string(schools), '\\|\\|') AS schools
get_json_object(school, '$.id') AS school_id,
字元串函數：
1、instr（』源字元串』 , 『目標字元串』 ,』開始位置』,』第幾次出現』）
instr(sourceString,destString,start,appearPosition)
1.sourceString代表源字元串； destString代表要從源字元串中查找的子串；
2.start代表查找的開始位置，這個參數可選的，默認為1；
3.appearPosition代表想從源字元中查找出第幾次出現的destString，這個參數也是可選的， 默認為1
4.如果start的值為負數，則代表從右往左進行查找，但是位置數據仍然從左向右計算。
5.返回值為：查找到的字元串的位置。如果沒有查找到，返回0。
最簡單例子： 在abcd中查找a的位置，從第一個字母開始查，查找第一次出現時的位置
select instr(『abcd』,』a』,1,1) from al; —1
應用於模糊查詢：instr(欄位名/列名, 『查找欄位』)
select code,name,dept,occupation from staff where instr(code, 『001』)> 0;
等同於 select code, name, dept, occupation from staff where code like 『%001%』 ;
應用於判斷包含關系：
select ccn,mas_loc from mas_loc where instr(『FH,FHH,FHM』,ccn)>0;
等同於 select ccn,mas_loc from mas_loc where ccn in (『FH』,』FHH』,』FHM』);
2、substr（string A，int start，int len）和 substring（string A，int start，int len），用法一樣
substr(time,1,8) 表示將time從第1位開始截取，截取的長度為8位
第一種用法：
substr（string A，int start）和 substring（string A，int start），用法一樣
功效：返回字元串A從下標start位置到結尾的字元串
第二種用法：
substr（string A，int start，int len）和 substring（string A，int start，int len），用法一樣
功效：返回字元串A從下標start位置開始，長度為len的字元串
3、get_json_object(form_data,'$.學生姓名') as student_name
json_tuple 函數的作用：用來解析json字元串中的多個欄位
4、split(full_name, '\\.') [5] AS zq; 取的是數組里的第六個
日期(時間)函數：
1、to_date(event_time) 返回日期部分
2、date_sub：返回當前日期的相對時間
當前日期：select curdate()
當前日期前一天：select date_sub(curdate(),interval 1 day)
當前日期後一天：select date_sub(curdate(),interval -1 day)
date_sub(from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss'), 14) 將現在的時間總秒數轉為標准格式時間，返回14天之前的時間
時間戳>>>>日期：
from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') 將現在的時間總秒數轉為標准格式時間
from_unixtime(get_json_object(get_json_object(form_data,'$.挽單時間'),'$.$date')/1000) as retain_time
unix_timestamp('2019-08-15 16:40:00','yyyy-MM-dd HH:mm:ss') --1565858400
日期>>>>時間戳：unix_timestamp()
date_format：yyyy-MM-dd HH:mm:ss 時間轉格式化時間
select date_format('2019-10-07 13:24:20', 'yyyyMMdd000000')-- 20191007000000select date_format('2019-10-07', 'yyyyMMdd000000')-- 20191007000000
1.日期比較函數: datediff語法: datediff(string enddate,string startdate)
返回值: int
說明: 返回結束日期減去開始日期的天數。
舉例： hive> select datediff('2016-12-30','2016-12-29'); 1
2.日期增加函數: date_add語法: date_add(string startdate, intdays)
返回值: string
說明: 返回開始日期startdate增加days天後的日期。
舉例： hive>select date_add('2016-12-29',10); 2017-01-08
3.日期減少函數: date_sub語法: date_sub (string startdate,int days)
返回值: string
說明: 返回開始日期startdate減少days天後的日期。
舉例： hive>select date_sub('2016-12-29',10); 2016-12-19
4.查詢近30天的數據
select * from table where datediff(current_timestamp,create_time)<=30；
create_time 為table里的欄位，current_timestamp 返回當前時間 2018-06-01 11:00:00
3、trunc()函數的用法：當前日期的各種第一天,或者對數字進行不四捨五入的截取
日期：
1.select trunc(sysdate) from al --2011-3-18 今天的日期為2011-3-18
2.select trunc(sysdate, 'mm') from al --2011-3-1 返回當月第一天.
上月1號 trunc(add_months(current_date(),-1),'MM')
3.select trunc(sysdate,'yy') from al --2011-1-1 返回當年第一天
4.select trunc(sysdate,'dd') from al --2011-3-18 返回當前年月日
5.select trunc(sysdate,'yyyy') from al --2011-1-1 返回當年第一天
6.select trunc(sysdate,'d') from al --2011-3-13 (星期天)返回當前星期的第一天
7.select trunc(sysdate, 'hh') from al --2011-3-18 14:00:00 當前時間為14:41
8.select trunc(sysdate, 'mi') from al --2011-3-18 14:41:00 TRUNC()函數沒有秒的精確
數字：TRUNC（number,num_digits） Number 需要截尾取整的數字。Num_digits 的默認值為 0。TRUNC()函數截取時不進行四捨五入
11.select trunc(123.458,1) from al --123.4
12.select trunc(123.458,-1) from al --120
4、round()：四捨五入：
select round(1.455, 2) #結果是：1.46，即四捨五入到十分位，也就是保留兩位小數
select round(1.5) #默認四捨五入到個位，結果是：2
select round(255, -1) #結果是：260，即四捨五入到十位，此時個位是5會進位
floor()：地板數
ceil()天花板數
5、
6.日期轉年函數: year語法: year(string date)
返回值: int
說明: 返回日期中的年。
舉例：
hive> select year('2011-12-08 10:03:01') from al;
2011
hive> select year('2012-12-08') fromal;
2012
7.日期轉月函數: month語法: month (string date)
返回值: int
說明: 返回日期中的月份。
舉例：
hive> select month('2011-12-08 10:03:01') from al;
12
hive> select month('2011-08-08') fromal;
8
8.日期轉天函數: day語法: day (string date)
返回值: int
說明: 返回日期中的天。
舉例：
hive> select day('2011-12-08 10:03:01') from al;
8
hive> select day('2011-12-24') fromal;
24
9.日期轉小時函數: hour語法: hour (string date)
返回值: int
說明: 返回日期中的小時。
舉例：
hive> select hour('2011-12-08 10:03:01') from al;
10
10.日期轉分鍾函數: minute語法: minute (string date)
返回值: int
說明: 返回日期中的分鍾。
舉例：
hive> select minute('2011-12-08 10:03:01') from al;
3
11.日期轉秒函數: second語法: second (string date)
返回值: int
說明: 返回日期中的秒。
舉例：
hive> select second('2011-12-08 10:03:01') from al;
1
12.日期轉周函數: weekofyear語法: weekofyear (string date)
返回值: int
說明: 返回日期在當前的周數。
舉例：
hive> select weekofyear('2011-12-08 10:03:01') from al;
49
查看hive表在hdfs中的位置：show create table 表名;
在hive中hive2hive，hive2hdfs：
HDFS、本地、hive -----> Hive：使用 insert into | overwrite、loaddata local inpath "" into table student;
Hive ----> Hdfs、本地：使用：insert overwrite | local
網站訪問量統計：
uv：每用戶訪問次數
ip：每ip(可能很多人)訪問次數
PV:是指頁面的瀏覽次數
VV:是指你訪問網站的次數
sql：
基本函數：
count、max、min、sum、avg、like、rlike（'2%'、'_2%'、%2%'、'[2]'）（java正則）
and、or、not、in
where、group by、having、{ join on 、full join} 、order by（desc降序）
sort by需要與distribut by集合結合使用：
hive (default)> set maprece.job.reces=3; //先設置rece的數量
insert overwrite local directory '/opt/mole/datas/distribute-by'
row format delimited fields terminated by '\t'
先按照部門編號分區，再按照員工編號降序排序。
select * from emp distribute by deptno sort by empno desc;
外部表 create external table if not exists dept
分區表：create table dept_partition ( deptno int, dname string, loc string ) partitioned by ( month string )
load data local inpath '/opt/mole/datas/dept.txt' into table default.dept_partition partition(month='201809');
alter table dept_partition add/drop partition(month='201805') ,partition(month='201804');
多分區聯合查詢：union
select * from dept_partition2 where month='201809' and day='10';
show partitions dept_partition;
desc formatted dept_partition;
二級分區表：create table dept_partition2 ( deptno int, dname string, loc string ) partitioned by (month string, day string) row format delimited fields terminated by '\t';
分桶抽樣查詢：分區針對的是數據的存儲路徑；分桶針對的是數據文件
create table stu_buck(id int, name string) clustered by(id) into 4 bucketsrow format delimited fields terminated by '\t';
設置開啟分桶與rece為1：
set hive.enforce.bucketing=true;
set maprece.job.reces=-1;
分桶抽樣：select * from stu_bucktablesample(bucket x out of y on id);
抽取，桶數/y，x是從哪個桶開始抽取，y越大 抽樣數越少，y與抽樣數成反比，x必須小於y
給空欄位賦值：
如果員工的comm為NULL，則用-1代替或用其他欄位代替 ：select nvl(comm,-1) from emp;
case when:如何符合記為1，用於統計、分組統計
select dept_id, sum(case sex when '男' then 1 else 0 end) man , sum(case sex when '女' then 1 else 0 end) woman from emp_sex group by dept_id;
用於組合歸類匯總(行轉列)：UDAF：多轉一
concat：拼接查詢結果
collect_set(col)：去重匯總，產生array類型欄位，類似於distinct
select t.base, concat_ws('|',collect_set(t.name)) from (select concat_ws(',',xingzuo,blood_type) base,name from person_info) t group by t.base;
解釋：先第一次查詢得到一張沒有按照（星座血型）分組的表，然後分組，使用collect_set將名字組合成數組，然後使用concat將數組變成字元串
用於拆分數據：(列轉行)：UDTF：一轉多
explode(col)：將hive一列中復雜的array或者map結構拆分成多行。
lateral view 側面顯示：用於和UDTF一對多函數搭配使用
用法：lateral view udtf(expression) tablealias as cate
cate：炸開之後的列別名
temptable ：臨時表表名
解釋：用於和split, explode等UDTF一起使用，它能夠將一列數據拆成多行數據，在此基礎上可以對拆分後的數據進行聚合。
開窗函數：
Row_Number,Rank，Dense_Rank over：針對統計查詢使用
Row_Number：返回從1開始的序列
Rank:生成分組中的排名序號，會在名詞s中留下空位。3 3 5
dense_rank：生成分組中的排名序號，不會在名詞中留下空位。3 3 4
over：主要是分組排序，搭配窗口函數使用
結果：
SUM、AVG、MIN、MAX、count
preceding：往前
following：往後
current row：當前行
unbounded：unbounded preceding 從前面的起點， unbounded following：到後面的終點
sum：直接使用sum是總的求和，結合over使用可統計至每一行的結果、總的結果、當前行+之前多少行/之後多少行、當前行到往後所有行的求和。
over(rowsbetween 3/current ) 當前行到往後所有行的求和
ntile：分片，結合over使用，可以給數據分片，返回分片號
使用場景：統計出排名前百分之或n分之一的數據。
lead,lag,FIRST_VALUE,LAST_VALUE
lag與lead函數可以返回上下行的數據
lead(col,n,dafault) 用於統計窗口內往下第n行值
第一個參數為列名，第二個參數為往下第n行（可選，默認為1），第三個參數為默認值（當往下第n行為NULL時候，取默認值，如不指定，則為NULL）
LAG(col,n,DEFAULT) 用於統計窗口內往上第n行值
第一個參數為列名，第二個參數為往上第n行（可選，默認為1），第三個參數為默認值（當往上第n行為NULL時候，取默認值，如不指定，則為NULL）
使用場景：通常用於統計某用戶在某個網頁上的停留時間
FIRST_VALUE:取分組內排序後，截止到當前行，第一個值
LAST_VALUE:取分組內排序後，截止到當前行,最後一個值
范圍內求和: https://blog.csdn.net/happyrocking/article/details/105369558
cume_dist，percent_rank
–CUME_DIST :小於等於當前值的 行數 / 分組內總行數
–比如，統計小於等於當前薪水的人數，占總人數的比例
percent_rank:分組內當前行的RANK值-1/分組內總行數-1
總結：
在Spark中使用spark sql與hql一致，也可以直接使用sparkAPI實現。
HiveSql窗口函數主要應用於求TopN，分組排序TopN、TopN求和，前多少名前百分之幾。
與Flink窗口函數不同。
Flink中的窗口是用於將無線數據流切分為有限塊處理的手段。
window分類：
CountWindow：按照指定的數據條數生成一個 Window，與時間無關。
TimeWindow：按照時間生成 Window。
1. 滾動窗口（Tumbling Windows）：時間對齊，窗口長度固定，不重疊：：常用於時間段內的聚合計算
2.滑動窗口（Sliding Windows）：時間對齊，窗口長度固定，可以有重疊：：適用於一段時間內的統計（某介面最近 5min 的失敗率來報警）
3. 會話窗口（Session Windows）無時間對齊，無長度，不重疊：：設置session間隔，超過時間間隔則窗口關閉。