1.寫(xiě)作目的說(shuō)明

hive sql是從事數(shù)據(jù)分析的同學(xué)的基本功盖奈。無(wú)論是秋招混坞、春招或者是實(shí)習(xí)，sql都是面試官考察的重點(diǎn)钢坦，拿剛剛過(guò)去的19秋招來(lái)說(shuō)究孕，搜狐、網(wǎng)易爹凹、京東等在數(shù)據(jù)分析師崗位面試時(shí)都考了sql厨诸，而拼多多在數(shù)據(jù)分析筆試時(shí)就安排了四到五道復(fù)雜的sql題，雖然實(shí)習(xí)的難度會(huì)比秋招要小禾酱，可是sql仍然是重頭戲微酬。因此可以說(shuō)數(shù)據(jù)分析的敲門(mén)磚之一就是sql
在工作中，也有人戲稱(chēng)數(shù)據(jù)分析師是sql提數(shù)機(jī)颤陶，也有職場(chǎng)前輩說(shuō)數(shù)據(jù)分析師不應(yīng)該甘心成為一個(gè)提數(shù)機(jī)颗管，但是在沉淀業(yè)務(wù)理解之前，快速準(zhǔn)確的提數(shù)也是數(shù)據(jù)分析師必經(jīng)的階段滓走。
下面進(jìn)入正題

2.hive簡(jiǎn)介

hive是基于hadoop構(gòu)建的一套數(shù)據(jù)倉(cāng)庫(kù)查詢(xún)系統(tǒng)垦江，支持使用sql語(yǔ)句對(duì)存儲(chǔ)在hadoop里面的分布式文件系統(tǒng)進(jìn)行分析，將結(jié)構(gòu)化的數(shù)據(jù)映射成一張表搅方，通過(guò)將sql語(yǔ)句轉(zhuǎn)化成mapreduce任務(wù)進(jìn)行運(yùn)行比吭，從而實(shí)現(xiàn)查詢(xún)分析的功能

3.簡(jiǎn)單常用函數(shù)

其中3.1/3.2/3.3需要了解绽族，3.4的內(nèi)容是必須掌握

3.1 創(chuàng)建臨時(shí)表

CREATE  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], ...)] 
  [STORED AS file_format] 

3.2 刪除新表

DELETE TABLE IF EXISTS table_name;

3.3 插入新字段

ALTER TABLE table_name ADD COLUMNS 
[(col_name data_type [COMMENT col_comment]，...)]

3.4 查詢(xún)字段

查詢(xún)語(yǔ)句是面試中的重頭戲梗逮，需要面試者將查詢(xún)函數(shù)組合在一起實(shí)現(xiàn)某個(gè)需求项秉，比如給出查詢(xún)?nèi)嗯鷶?shù)學(xué)成績(jī)前五名的學(xué)生姓名這個(gè)問(wèn)題，應(yīng)該怎么分析呢慷彤？
在解答上面的問(wèn)題之前娄蔼，需要牢記hive sql語(yǔ)句的執(zhí)行順序，從前到后依次是：

• from + 表名
• on +字段
• （left/right）join +表名
• where +條件
• group by +字段
• having + 條件
• select +字段
• distinct +字段
• union +結(jié)果集
• order by +字段
• limit +數(shù)量
  我的習(xí)慣是執(zhí)行順序就是我思考分析的順序底哗，但是代碼最終的呈現(xiàn)順序和執(zhí)行順序略有不同岁诉，對(duì)上面這個(gè)問(wèn)題來(lái)說(shuō)，首先在問(wèn)題中可以知道最終選出的字段是學(xué)生姓名跋选，那么就明確了select的字段只有一個(gè)涕癣，就是name
  接著想到執(zhí)行順序第一個(gè)是from，那么一定有一個(gè)表前标，無(wú)論是中間表還是原始表坠韩，一定可以接著寫(xiě)下這樣的語(yǔ)句

FROM scores //假設(shè)scores是表名

接著執(zhí)行順序到了on和join,這時(shí)候就要考慮是不是需要連表的問(wèn)題，假設(shè)在這個(gè)問(wèn)題中需要連表炼列，因?yàn)闀?huì)有一張表basic記錄了全校學(xué)生的性別只搁，那么這時(shí)候就需要把score和basic連在一起，接下來(lái)就可以這么寫(xiě)

FROM scores // 這是之前寫(xiě)過(guò)的
LEFT JOIN basic
ON scores.name = basic.name

接著執(zhí)行順序到了where,where的限定條件有三個(gè)俭尖，分別是三班氢惋，數(shù)學(xué)，女生稽犁，那么寫(xiě)下來(lái)就是

FROM scores
LEFT JOIN basic
ON scores.name=basic.name
where scores.class=3 and subject = 'math' and basic.sex = 'female'

接著執(zhí)行順序到了group by ,having 焰望，這個(gè)問(wèn)題不需要這個(gè)函數(shù)，那么接下來(lái)執(zhí)行順序到了select,如之前所說(shuō)已亥，代碼的書(shū)寫(xiě)順序和執(zhí)行順序熊赖，所以select語(yǔ)句要寫(xiě)在前面

SELECT scores.name
FROM scores
LEFT JOIN basic
ON scores.name=basic.name
where scores.class=3 and subject = 'math' and basic.sex = 'female'

可是我們并沒(méi)有實(shí)現(xiàn)前五名的查找，我們目前只是查到了所有人的名字虑椎，所以需要根據(jù)成績(jī)來(lái)對(duì)名字進(jìn)行排序秫舌，select之后的執(zhí)行順序是distinct和union這里我們并不需要，因此要用order by 進(jìn)行排序

(SELECT scores.name,scores.score // 增加了一個(gè)scores.score字段
FROM scores
LEFT JOIN basic
ON scores.name=basic.name
where scores.class=3 and subject = 'math' and basic.sex = 'female'
ORDER BY scores.score desc)a

你會(huì)有疑問(wèn)為什么要加上score字段绣檬，因?yàn)閛rder by是在select 之后執(zhí)行足陨，他需要在select出來(lái)的那些字段里進(jìn)行排序，如果我們沒(méi)有加上score,那么order by就找不到要排序的字段了娇未，因此我們要在a表的基礎(chǔ)上再進(jìn)行一次select才可以墨缘，只把name字段選擇出來(lái)，并且通過(guò)limit把前5名選出來(lái),

SELECT a.name
FROM
(SELECT scores.name,scores.score // 增加了一個(gè)scores.score字段
FROM scores
LEFT JOIN basic
ON scores.name=basic.name
where scores.class=3 and subject = 'math' and basic.sex = 'female'
ORDER BY scores.score desc)a
LIMIT 5

剛才的分析問(wèn)題解答完畢。

4.復(fù)雜窗口函數(shù)

在sql中有聚合函數(shù)镊讼，它的作用是把多行數(shù)據(jù)聚成一行宽涌，比如count,sum,avg,max,min就是常見(jiàn)的聚合函數(shù)，但是當(dāng)我們既想要保留聚合前的數(shù)據(jù)又想要看到聚合后新字段的數(shù)據(jù)時(shí)蝶棋，就需要用到窗口函數(shù)卸亮，那么窗口函數(shù)的執(zhí)行順序是怎樣的呢？窗口函數(shù)的執(zhí)行順序靠后玩裙，在order by之前兼贸。
窗口函數(shù)有以下幾種形式

4.1 普通型

1. 聚合函數(shù)+over()
2. 聚合函數(shù)+over(partition by ... )
3. 聚合函數(shù)+over(partition by ... order by ...)
4. 聚合函數(shù)+over(partition by ... order by ...) rows between A and B，A/B

可選擇的如下：

- UNBOUNDED PRECEDING 起點(diǎn)
- UNBOUNDED FOLLOWING 終點(diǎn)
- CURRENT ROW 當(dāng)前行
- 數(shù)字+PRECEDING 前幾行
- 數(shù)字+FOLLOWING 后幾行

4.2 排序型

- dense_rank over(partition by ... order by ...)
//eg:1,2,3,3,4 
- rank over(partition by ... order by ...)
//eg:1,2,3,3,5
- row_number over (partition by ... order by ...)
//eg:1,2,3,4,5

4.3 前后型

- lag(colname,num,default) over(partition by ... order by ...) :前幾行
- lead(colname,num,default) over (partition by ... order by ...):后幾行

4.4 分組排序后

- first_value(colname) over(partition by ... order by ...):分組排序后第一行
- last_value(colname) over(partition by ... order by ...):分組排序后最后一行

4.5 切片型

- ntile(3) over() :對(duì)全局?jǐn)?shù)據(jù)切片
- ntile(3) over(partition by ...):對(duì)分組之后的數(shù)據(jù)切片
- ntile(3) over(order by ...):對(duì)數(shù)據(jù)按照升序后切片
- ntile(3) over(partition by ... order by ...):對(duì)數(shù)據(jù)分組并按照升序后切片

4.6 百分型

1.小于等于當(dāng)前值的行數(shù)/分組內(nèi)總行數(shù)

- CUME_DIST over (order by ...)
- CUME_DIST over (partition by ... order by ...)

2.分組內(nèi)當(dāng)前行的排名-1/分組內(nèi)總行數(shù)-1

- PERCENT_RANK over(order by ...)
- PERCENT_RANK over(partition by ... order by ...)

5.專(zhuān)題--日期函數(shù)

- to_date:日期時(shí)間轉(zhuǎn)成日期函數(shù)
-from_unixtime:時(shí)間戳轉(zhuǎn)成制定格式的日期
-unix_timestamp:日期轉(zhuǎn)化成時(shí)間戳
-year/month/day/hour/minute/second
-weekofyear
-datediff
-date_sub
-date_add
-from_unixtime+unix_timestamp

6.參考資料

[hive詳解]
（https://blog.csdn.net/hguisu/article/details/7256833）
[hive官方手冊(cè)]（https://cwiki.apache.org/confluence/display/Hive/LanguageManual）
[hive窗口函數(shù)]
（https://blog.csdn.net/qq_26937525/article/details/54925827）
[hive常用函數(shù)大全（二）]
（https://blog.csdn.net/scgaliguodong123_/article/details/60135385）
[hive日期函數(shù)]
（https://blog.csdn.net/u013421629/article/details/80450047）