毫不負責任的說，你和數據科學家最大的鴻溝，就差一個SQL語言：)入門后，后面的事情就簡單了

為了幫大家盡快入門Hive SQL、學會提數和分析，實現在大數據領域大干一場的愿望，幫你準備好了數據，準備好了SQL樣例，照著學，邊學邊練，主要是解決學一門新手藝起點高入手難的問題。

這個自編的課程分成如下的幾個部分，歡迎大家選讀選練。

1. 特別說明

2. 兩張示例表

3. SQL示例

• 簡單select查詢

• 使用where語句按條件篩選

• 使用distinct語句排重

• 使用order by語句對查詢結果排序

• 使用sum/count/avg計算總和/計數/平均值等

• 使用group by按照分組計算

• 子查詢

• 使用join從多張表中查詢數據

• 使用union all拼接多份數據

• 使用case when控制列輸出

SQL高級教程(待續)

1. 特別說明

本文中使用的數據集是從網上下來的測試數據，數據地址：https://github.com/markgrover/cloudcon-hive

2. 兩張示例表

為了方便練習SQL語法，特地準備了兩張hive表，ide_test.flights和 ide_test.airports， 表結構如下面的建表語句。另外兩張表中各有數行測試數據，可以在練習SQL時直接使用。

Flights表

CREATE TABLE `ide_test.flights`(`year` string COMMENT 'year',`month` string COMMENT 'month',`day` string COMMENT 'day',`dep_time` string COMMENT '起飛時間',`crs_dep_time` string COMMENT '計劃起飛時間',`arr_time` string COMMENT '到達時間',`crs_arr_time` string COMMENT '計劃到達時間',`carrier_code` string COMMENT '航空公司代號',`flight_num` string COMMENT '航班號',`actual_elapsed_time` string COMMENT '實際耗時',`crs_elapsed_time` string COMMENT '計劃耗時',`air_time` string COMMENT '飛行時間',`arr_delay` string COMMENT '到達延遲',`dep_delay` string COMMENT '起飛延遲',`origin` string COMMENT '起飛機場',`dest` string COMMENT '目標機場',`distance` string COMMENT '距離')COMMENT '測試數據-航班信息'

Airports表?

CREATE TABLE `ide_test.airports`(`name` string COMMENT 'name',`country` string COMMENT 'country',`area_code` string COMMENT 'area_code',`code` string COMMENT 'code')COMMENT '測試數據-機場信息'

3. SQL示例

3.1 簡單select查詢

使用select語句可以查看表里的數據

select用法

--查詢ide_test.airports表中的name列，返回5行select name from ide_test.airports limit 5--使用 '*'代表表中所有列select * from ide_test.airports limit 5

特別提示：限制SQL返回的行數是一個好習慣，如上面的 limit 5, 表示最多返回5行數據

3.2 使用where語句按條件篩選

使用where語句，可以根據指定的條件刷選數據，例如查看航班號為335的航班信息

where用法?

select     *   -- 返回表里所有列from     ide_test.flightswhere     flight_num= '335' --返回flight_num列為'335'的記錄limit 5

 Tips: where后面跟的是一個邏輯表達式，多個邏輯表達式用 AND、OR來連接

3.3 使用distinct語句排重

某一列(或多列)時常會有重復的值，有時候需要去重之后再查看，可以使用distinct語句來完成這個功能。例如查看ide_test.flights表中包含了那幾年的數據，可以使用下面的SQL語句來完成：

distinct 用法

selectdistinct year  -- distinct會對year列的值去重之后，再返回from     ide_test.flightslimit 10

Tips：查看某列包含了哪些值，有更好的寫法，這里只是展示下distinct語句的用法

3.4 使用order by語句對查詢結果排序

找出起飛時間最晚的5個航班

order by 用法

select     origin, dest, flight_num, dep_timefrom     ide_test.flightsorder by dep_time desc-- 按照起飛時間倒序排列， desc表示由大到小排序， -- asc(或者是省略不寫)表示由小到大排序 limit 5

3.5 使用sum/count/avg計算總和/計數/平均值等

使用count()函數可以計數，比如要看一下ide_test.flights表里一共有多少個航班信息，則可以這么寫:

count 用法?

selectcount(flight_num) as cnt -- count函數表示計數from     ide_test.flightslimit 10

sum：表示按照指定的列求和，比如計算一下335號航班一共飛行了多少公里

sum 用法

selectsum(cast(distance as int)) as total_distance --sum函數表示求和from     ide_test.flightswhere     flight_num = '335'limit  10

Tips: count/sum這種函數叫聚合函數，還有其他聚合函數，比如avg/max/min分別用戶求平均值、最大值和最小值

3.6 使用group by按照分組計算

group by語句的用處很大，它可以先對數據分組、然后再計算，看個例子就明白了。比如，要計算每個航班飛行的里程數，并按照總數由大到小取前10個，寫法如下：

group by 用法

select     flight_num, sum(distance) as total_distance--除了sum/count/avg/min/max這些聚合函數之外，-- 明確寫在select之后的列，必須同時出現在group by的后面from     ide_test.flightsgroup by flight_num  --按照航班號分組，對每一組的里程數求和order by total_distance desclimit  10

還有一種情況，查詢總飛行里程數大于3000的航班號，這時having語句就排上用場了

having 語法

select     flight_num --寫在select之后的列，必須同時出現在group by的后面from     ide_test.flightsgroup by flight_numhaving sum(distance) > 3000--刷選出飛行總里程大于3000的航班limit  5

小思考：為什么這種情況下要使用having來作為篩選條件，而不是使用where？

資料：深入理解Hive Group by語法

3.7 子查詢

要計算飛行總里程大于3000的航班數量，SQL可以這么寫

子查詢用法

select count(1) as cntfrom (select flight_numfrom  ide_test.flightsgroup by flight_numhaving sum(distance) > 3000 ) t

Tips: 子查詢的用法和普通hive表是一樣的，一般為了清晰，會使用括號'()'把子查詢包含起來

3.8 使用join從多張表中查詢數據

假如，我們計劃查詢每個航班的飛行里程和起始機場名稱，因為這兩個信息分別存儲在兩張不同的hive表中，因此需要同時查詢兩張hive表才能得到結果，寫法如下

join 用法?

select     p.flight_num, -- 從flights表取出航班號     p.distance, --從flights表取出飛行里程     q.name  --從airports表取出出發的機場名稱from     ide_test.flights pleft join ide_test.airports qon p.origin = q.codelimit 10

Tips：

• left join：左表關聯右表，當坐標中存在某值而右表不存在時，則右表對應信息使用null代替

• inner join：只有左表和右表都存在對應值的時候，才出現的結果中

• right join：與left join相反

• full outer join：當左表或右表不存在相應值時，使用null代替

延伸閱讀，做做思維操：

注意：如果A中有m條記錄，B中有n條記錄，A join B的時候，則最多會產生m*n條記錄，想想為什么？

3.9 使用union all拼接多份數據

UNION 語句符用于合并兩個或多個 SELECT 語句的結果集。

選出飛行里程大于1000或小于100的航班，SQL寫法如下：

注意：這里只是為了展示union用法，顯然應該有更好的寫法，自己思考下：)

union 用法?

select flight_numfrom ide_test.flightswhere distance > 1000unionselect flight_numfrom ide_test.flightswhere distance < 100

Tips: 請注意，UNION 內部的 SELECT 語句必須擁有相同數量的列。列也必須擁有相似的數據類型。同時，每條 SELECT 語句中的列的順序必須相同

3.10 使用case when控制列輸出

查詢航班的飛行距離，按照距離分成“長、中、短”三個級別，可以借助case when語句來實現，示例寫法：

case when 用法?

select flight_num, distance,case when distance > 3000 then '長距離航班'when distance > 1000 then '中距離航班'else '近距離航班'end as flight_levelfrom ide_test.flightslimit 10

Tips: 使用case when可以靈活自定義查詢結果。另外case when還可以和count/sum等聚合函數使用，來實現有條件的計數/匯總

4. SQL高級教程(待續)

• window函數

• 處理Json格式數據

• 處理XML格式數據

• 行列轉換

• 使用with語法

• 數據抽樣

/完

其他文章：

• 通過在阿里的實踐，談一下中臺建設的Why、When與How

• CTO內參 | Gartner報告，近年十大戰略性技術趨勢：自主設備、增強分析、AI驅動的開發、數字雙生、邊緣計算等

• 從黨的成功經驗看企業中臺建設

*每次「在看」，都是鼓勵* ?