為了幫大家儘快實現學習Hive SQL、提數，在大資料領域大幹一場的願望，幫你**準備好了資料，準備好了SQL樣例**，照著學，邊學邊練，主要是解決學一門新手藝起點高入手難的問題。

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

- 特別說明
- 準備環境
- 兩張示例表
- SQL示例
- - 簡單select查詢
  - 使用where語句按條件篩選
  - 使用distinct語句排重
  - 使用order by語句對查詢結果排序
  - 使用sum/count/avg計算總和/計數/平均值等
  - 使用group by按照分組計算
  - 子查詢
  - 使用join從多張表中查詢資料
  - 使用union all拼接多份資料
  - 使用case when控制列輸出
- SQL高階教程（待續）
- - window函式
  - 處理Json格式資料
  - 處理XML格式資料
  - 行列轉換
  - 使用with語法
  - 資料抽樣

# 特別說明

------

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

# 兩張示例表

------

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

**Flights表**

```javascript
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表**

```javascript
CREATE TABLE `ide_test.airports`(
  `name` string COMMENT 'name', 
  `country` string COMMENT 'country', 
  `area_code` string COMMENT 'area_code', 
  `code` string COMMENT 'code')
COMMENT '測試資料-機場資訊'
```

# SQL示例

------

## 簡單select查詢

使用select語句可以檢視錶裡的資料

**select用法**

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

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

**資料**：[深入理解Hive Select語法](https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select)

## 使用where語句按條件篩選

使用where語句，可以根據指定的條件刷選資料，例如檢視航班號為335的航班資訊

**where用法**

```javascript
select
    *   -- 返回表裡所有列
from
    ide_test.flights
where
    flight_num= '335' --返回flight_num列為'335'的記錄
limit 5
```

Tips: where後面跟的是一個邏輯表示式，多個邏輯表示式用 AND、OR來連線

## 使用distinct語句排重

某一列（或多列）時常會有重複的值，有時候需要去重之後再檢視，可以使用distinct語句來完成這個功能。例如檢視ide_test.flights表中包含了那幾年的資料，可以使用下面的SQL語句來完成：

**distinct 用法**

```javascript
select
    distinct year  -- distinct會對year列的值去重之後，再返回
from
    ide_test.flights
limit 10  
```

**Tips****：**檢視某列包含了哪些值，有更好的寫法，這裡只是展示下distinct語句的用法

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

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

**order by 用法**

```javascript
select
    origin, dest, flight_num, dep_time
from
    ide_test.flights
order by dep_time desc    -- 按照起飛時間倒序排列， desc表示由大到小排序， asc（或者是省略不寫）表示由小到大排序 
limit 5
```

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

使用count()函式可以計數，比如要看一下ide_test.flights表裡一共有多少個航班資訊，則可以這麼寫:

**count 用法**

```javascript
select
    count(flight_num) as cnt -- count函式表示計數
from
    ide_test.flights
limit 10
```

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

**sum 用法**

```javascript
select
    sum(cast(distance as int)) as total_distance --sum函式表示求和
from
    ide_test.flights
where
    flight_num = '335'
limit  10
```

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

**資料**：[深入理解Hive函式](https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF)

## 使用group by按照分組計算

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

**group by 用法**

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

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

**having 語法**

```javascript
select
    flight_num --寫在select之後的列，必須同時出現在group by的後面
from
    ide_test.flights
group by flight_num
having sum(distance) > 3000 --刷選出飛行總里程大於3000的航班
limit  5
```

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

**資料**：[深入理解Hive Group by語法](https://cwiki.apache.org/confluence/display/Hive/LanguageManual+GroupBy)

## 子查詢

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

**子查詢用法**

```javascript
select count(1) as cnt 
from
(
    select flight_num
    from  ide_test.flights
    group by flight_num
    having sum(distance) > 3000
) t 
```

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

**資料：**[深入理解Hive 子查詢](https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SubQueries)

## 使用join從多張表中查詢資料

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

**join 用法**

```javascript
select
    p.flight_num, -- 從flights表取出航班號
    p.distance, --從flights表取出飛行里程
    q.name  --從airports表取出出發的機場名稱
from
    ide_test.flights p
left join ide_test.airports q 
on p.origin = q.code
limit 10
```

**Tips****：**

**left join****：**左表關聯右表，當座標中存在某值而右表不存在時，則右表對應資訊使用null代替

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

**right join****：**與left join相反

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

**注意：**

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

**資料**

[深入理解Hive Join語法](https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Joins)

## 使用union all拼接多份資料

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

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

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

**union 用法**

```javascript
select flight_num
from ide_test.flights
where distance > 1000 
union 
select flight_num
from ide_test.flights
where distance < 100 
```

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

**資料：**[深入理解Hive Union語法](https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Union)

## 使用case when控制列輸出

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

**case when 用法**

```javascript
select flight_num,
distance,
case when distance > 3000 then '長距離航班'
    when distance > 1000 then '中距離航班'
    else '近距離航班'
end as flight_level
from ide_test.flights
limit 10
```

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

作者：張彥龍（源自本人公眾號：資料老鐵匠）

- [滴滴雲全線標準型雲伺服器限時特惠,註冊即送新手大禮包](https://www.didiyun.com/?channel=14816 )
- [新購雲服務1月5折 3月4折 6月低至3折](https://www.didiyun.com/?channel=14816 "新購雲服務1月5折 3月4折 6月低至3折")
- [滴滴雲使者招募，推薦最高返傭50%](https://z.didi.cn/3tr7J)

如何1小時快速上手SQL？

相關文章