# 11. 進階查詢語法結構
## 11.1 將兩個查詢結果聯集的 UNION
使用UNION 關鍵字可以將兩個查詢的結果做聯集,只要兩者的欄位資料型別、欄位數與欄位排列順序相同即可。
### 用 UNION 聯集只保留不重複的列資料
聯集的意思就是將兩個集合中的所有元素放在一起,並將重複的元素只保留一個。
我們用兩個SELECT 語句分別查出市集年份是2019與2020年的首次市集日期,然後將這兩個查詢的輸出結果合併起來。因為第一個查詢與第二個查詢的輸出不重複,都會被放進聯集的輸出中:
```sql=
SELECT market_year, MIN(market_date) AS first_market_date
FROM farmers_market.market_date_info
WHERE market_year = '2019'
UNION
SELECT market_year, MIN(market_date) AS first_market_date
FROM farmers_market.market_date_info
WHERE market_year = '2020'
```
![CleanShot 2024-03-27 at 15.24.26@2x](https://hackmd.io/_uploads/H1gchKrWJA.jpg)
💡你可能會想,這麼簡單的查詢只要用 GROUP BY 與IN 關鍵字(如下程式)就能得到相同答案,有需要分成兩個查詢再用 UNION 合併嗎?
```sql=
SELECT market_year, MIN(market_date) AS first_market_date
FROM farmers_market.market_date_info
WHERE market_year IN ('2019','2020')
GROUP BY market_year
```
SQL 可以用不同的寫法達到相同的目的,多學一種寫法在將來遇到複雜的查詢時就可以派上用場。
### 用 UNION ALL 直接合併
「UNON 的聯集運算需要花時間檢查兩個結果集中重複的列資料並排除」
如果我們事先能確定兩個結果集不會有重複的列資料,那可以用UNIONALL 關缝字直接合併,就不用額外時間檢查重複的列資料了。
### 打造一份能展示每次市集供應量最大的產品:包括以個數計算的產品以及以重量計算的產品。
1. 將 vendor_inventory(供應商存貨)表格 LEFT JOIN product 表格,以product_id 欄位連結。其目的是將 product 表格中的 product_name、product_qty_type(包括 unit、lbs)等欄位連結進來,如同打造一個自訂資料集,並用WITH 語句寫成 CTE,取名為product_quantity_by_date 供後續查詢使用。若單獨執行此 CTE 的輸出如下:
```sql=
WITH
product_quantity_by_date AS (
SELECT
vi.market_date,
vi.product_id,
p.product_name,
SUM(vi.quantity) AS total_quantity_available,
p.product_qty_type
FROM
farmers_market.vendor_inventory AS vi
LEFT JOIN farmers_market.product AS p ON vi.product_id = p.product_id
GROUP BY
vi.market_date,
vi.product_id,
p.product_name,
p.product_qty_type
)
```
![CleanShot 2024-03-27 at 15.53.56@2x](https://hackmd.io/_uploads/HyPje8by0.jpg)
2. 接下來要用兩個 SELECT 語句從 product_quantity_by_date CTE 結果集分別對 product_qty_type 欄位中的 unit 與 lbs 做查詢。這兩個查詢都用 RANK 窗口函數作用在 market_date 的分區,並以 totall_quantity_avallable 欄位做降冪排序,也就是讓數字最大的排在第一位,再用 RANK 函數賦予排名編號填入 quantity_rank 欄位。
3. 由於是分別對 unit 與Ibs 做查詢,兩個查詢的輸出本來就不會有交集,可直接用 UNION ALL 合併。
```sql=
WITH
product_quantity_by_date AS (
SELECT
vi.market_date,
vi.product_id,
p.product_name,
SUM(vi.quantity) AS total_quantity_available,
p.product_qty_type
FROM
farmers_market.vendor_inventory AS vi
LEFT JOIN farmers_market.product AS p ON vi.product_id = p.product_id
GROUP BY
vi.market_date,
vi.product_id,
p.product_name,
p.product_qty_type
)
SELECT
market_date,
product_id,
product_name,
total_quantity_available,
product_qty_type,
RANK() OVER (
PARTITION BY
market_date
ORDER BY
total_quantity_available DESC
) AS quantity_rank
FROM
product_quantity_by_date
WHERE
product_qty_type = 'unit'
UNION ALL
SELECT
market_date,
product_id,
product_name,
total_quantity_available,
product_qty_type,
RANK() OVER (
PARTITION BY
market_date
ORDER BY
total_quantity_available DESC
) AS quantity_rank
FROM
product_quantity_by_date
WHERE
product_qty_type = 'lbs'
```
![CleanShot 2024-03-27 at 16.44.01@2x](https://hackmd.io/_uploads/r1IvhIZyA.jpg)
4. 由於我們想知道的是每個市集日期供貨量最大的產品(包括單位是 unit 與 lbs),因此只需要將所有 quantity_rank = 1 的產品篩選出來即可。為此,我們將兩個 SELECT 查詢以及 UNION ALL 改為子查詢,並取別名為 x。然後在外部查詢中用 WHERE 子句指定 x.quantity_rank = 1 的篩選條件,即可得到最後的輸出
```sql=
WITH
product_quantity_by_date AS (
SELECT
vi.market_date,
vi.product_id,
p.product_name,
SUM(vi.quantity) AS total_quantity_available,
p.product_qty_type
FROM
farmers_market.vendor_inventory AS vi
LEFT JOIN farmers_market.product AS p ON vi.product_id = p.product_id
GROUP BY
vi.market_date,
vi.product_id,
p.product_name,
p.product_qty_type
)
SELECT
*
FROM
(
SELECT
market_date,
product_id,
product_name,
total_quantity_available,
product_qty_type,
RANK() OVER (
PARTITION BY
market_date
ORDER BY
total_quantity_available DESC
) AS quantity_rank
FROM
product_quantity_by_date
WHERE
product_qty_type = 'unit'
UNION ALL
SELECT
market_date,
product_id,
product_name,
total_quantity_available,
product_qty_type,
RANK() OVER (
PARTITION BY
market_date
ORDER BY
total_quantity_available DESC
) AS quantity_rank
FROM
product_quantity_by_date
WHERE
product_qty_type = 'lbs'
) AS x
WHERE
x.quantity_rank = 1
ORDER BY
market_date
```
![CleanShot 2024-03-27 at 16.49.04@2x](https://hackmd.io/_uploads/rylcp8bk0.jpg)
### 用 UNION的時機
例如:
1. 當你擁有事件日誌(如網站流量日誌)分存於多個文件檔,當每個文件檔被加載到資料庫中需要合併資料時,就會有這種需求。
2. 這些表格可能是來自不同時間點的同一動態資料集的靜態快照(比如說股票交易是動態進行,但我們每整點抓取所有股票當時的股價,這就是整點的靜態快照)。
3. 資料可能是從一個系統遷移到另一個系統,你需要從兩個不同系統的表格中提取資料,並將其合併到一個視圖中,以查看記錄的整個歷史。
## 11.2 自我連結(Self-Join)找出最大值
自我連結 (Self-Join) 是讓表格與自己做連結,可藉此比較自身列資料間的異同。SQL 寫法就限一般 JOIN 一模一樣,差別只在於是同一個表格與自己(如同副本)連結。為了區別兩個表格,可以各自取一個表格別名。
我們試著用農夫市集資料庫示範,**找出任一市集日期的總銷售額是否是當時的歷史新高**。在這個案例中,我們想知道每個市集日期之前有哪一天營業額最高,這就可以利用自我連結來進行比較。
### 將某一市集日期銷售額與之前銷售額並列
首先,我們需要透過 market_date 欄位來彙總 customer_purchases 表格中每日的總銷售額,將其寫入 WITH 語旬中並命名為 sales_per_market_date
```sql=
WITH
sales_per_market_date AS (
SELECT
market_date,
ROUND(SUM(quantity * cost_to_customer_per_qty), 2) AS sales
FROM
farmers_market.customer_purchases
GROUP BY
market_date
ORDER BY
market_date
)
```
![CleanShot 2024-03-27 at 17.04.35@2x](https://hackmd.io/_uploads/SkdVZvZJ0.jpg)
假設我們要以 2019-04-13 這一個市集日期為準,比較此日期與之前各市集日期的總銷售額,我們可以利用自我連結的方式,將副本中小於 2019-04-13 的日期與總營業額合併到 2019-04-13列資料的後面
這裡要注意的是:
1. 我們要從已經彙總銷售額的 sales_per_market_date CTE(別名cm) 連結自己的副本(別名 pm),連結條件是 "pm.market_date < cm.market_date"
2. WHERE 子句中指定的比較基準日是 2019-04-13,因此 ON 條件中只有早於 2019-04-13 的日期才會被連結到 cm 的後面。
```sql=
WITH
sales_per_market_date AS (
SELECT
market_date,
ROUND(SUM(quantity * cost_to_customer_per_qty), 2) AS sales
FROM
customer_purchases
GROUP BY
market_date
ORDER BY
market_date
)
SELECT
cm.market_date,
cm.sales,
pm.market_date,
pm.sales
FROM
sales_per_market_date AS cm
LEFT JOIN sales_per_market_date AS pm ON pm.market_date < cm.market_date
WHERE
cm.market_date = '2019-04-13'
```
#### sales_per_market_date:
![CleanShot 2024-03-27 at 17.04.35@2x](https://hackmd.io/_uploads/SkdVZvZJ0.jpg)
#### result:
![CleanShot 2024-03-27 at 17.17.08@2x](https://hackmd.io/_uploads/ByXXND-JC.jpg)
出可看出只有三個市集日期早於 2019-04-13,所以會將該三個日期與總銷售額一一連結到 2019-04-13 欄位的後面。
### 算出過去市集日期的最高銷售額
再進一步,我們希望跟在每個市集日期之後的是當日營業額與之前的歷史新高,不需要把過去每一日的營業額都一一列出,那就可以為 pm.sales 欄位用 MAX 函數取得之前的最高銷售額,取欄位別名 previous_max_sales,同時用 GROUP BY 以 cm.market_date、cm.sales 分組
```sql=
WITH
sales_per_market_date AS (
SELECT
market_date,
ROUND(SUM(quantity * cost_to_customer_per_qty), 2) AS sales
FROM
farmers_market.customer_purchases
GROUP BY
market_date
ORDER BY
market_date
)
SELECT
cm.market_date,
cm.sales,
MAX(pm.sales) AS previous_max_sales
FROM
sales_per_market_date AS cm
LEFT JOIN sales_per_market_date AS pm ON pm.market_date < cm.market_date
WHERE
cm.market_date = '2019-04-13'
GROUP BY
cm.market_date,
cm.sales
```
![CleanShot 2024-03-27 at 17.38.12@2x](https://hackmd.io/_uploads/B1ufKPb10.jpg)
### 設定是否創歷史新高的指標
此外,為了更清楚識別某市集日期的銷售額是否打破歷史最高記錄,我們可以用 CASE WHEN 子句判斷本次銷售額是否大於歷史記錄,並建一個識別用的指標欄位(sales_record_set),當創下歷史新高時,此欄位的值會標示「YES」,否則標示「NO」
```sql=
WITH
sales_per_market_date AS (
SELECT
market_date,
ROUND(SUM(quantity * cost_to_customer_per_qty), 2) AS sales
FROM
farmers_market.customer_purchases
GROUP BY
market_date
ORDER BY
market_date
)
SELECT
cm.market_date,
cm.sales,
MAX(pm.sales) AS previous_max_sales,
CASE
WHEN cm.sales > MAX(pm.sales) THEN "YES"
ELSE "NO"
END sales_record_set
FROM
sales_per_market_date AS cm
LEFT JOIN sales_per_market_date AS pm ON pm.market_date < cm.market_date
GROUP BY
cm.market_date,
cm.sales
```
![CleanShot 2024-03-27 at 17.40.39@2x](https://hackmd.io/_uploads/rJdsYv-y0.jpg)
1. 第一次市集日期 2019-04-03 因為沒有前一次的銷售額,故在 previous_max_sales 欄位是 NULL,也因為 NULL 無法比較,故 WHEN 子句條件判斷為 FALSE,因此在 sales_record_set 欄位填入『NO』。
2. 第二次市集日期 2019-04-06 銷售額 549.50 大於過去歷史記錄 475.00,因此在 sales_record_set 欄位填入『YES』,依此類推
## 11.3 統計每週的新顧客與回頭客
在製作報表時,一種常見的要求是為某個時間範圍內的顧客做摘要總結。
假設農夫市集經理想了解每週造訪市集的顧客人數,以及其中有多少是首次消費的新顧客。
提醒!在農夫市集資料庫中只有曾經買過產品的顧客才有交易記錄,我們無法得知並追蹤來參觀卻沒有交易的訪客有多少
### 將顧客每次購買日期與首次購買日期並列
怎麼區別新顧客與回頭客呢?
有個方法是比較每位顧客的最早購買日期。如果顧客的最早購買日期是『今天』,那麼這位顧客就是今天的新顧客。
我們來彙總每次市集日期與顧客資料,這段查詢是用 MIN 函數作為窗口函數,以 customer_id 分區找到該顧客首次購買日期(最早的日期),取名為 first_purchase_date 欄位
```sql=
SELECT DISTINCT
customer_id,
market_date,
MIN(market_date) OVER (
PARTITION BY
cp.customer_id
) AS first_purchase_date
FROM
farmers_market.customer_purchases cp
```
![CleanShot 2024-03-27 at 23.13.08@2x](https://hackmd.io/_uploads/HJcow3-1R.jpg)
我們的目的是統計每週的新顧客與回頭客,所以還需要連結 market_date_info 表格將每個市集日期的 market_year 與 market_week 欄位納入。以下是此查詢的幾個重點:
1. 將 customer_markets_attended(取別名cma)CTE 做為左表格,用 LEFT JOIN 將 market_date_info(取別名 md)表格在 ON 條件中以 market_date 欄位連結。
2. 因為要得到每週的顧客數,需要用 GROUP BY 將 md 的 market_year、market_week 欄位做每年每週的分組
3. 如果一位顧客在該週兩個市集日期都有購買,那麼這兩次都會被分到同一個每年每週分組。所以我們要增加兩種類型的計數欄位:
- customer_visit_count:在當週兩個市集日期的顧客數加總。若一位顧客在當週兩個市集都有購買就計數為 2,只在其中一個市集購買就計數為 1。用COUNT(customer_id)函數計數。
- distinct_customer_count:在當週兩個市集日期不重複的顧客數加總。用 COUNT(DISTINCT customer_id)函數計數
```sql=
WITH
customer_markets_attended AS (
SELECT DISTINCT
customer_id,
market_date,
MIN(market_date) OVER (
PARTITION BY
cp.customer_id
) AS first_purchase_date
FROM
farmers_market.customer_purchases cp
)
SELECT
md.market_year,
md.market_week,
COUNT(customer_id) AS customer_visit_count,
COUNT(DISTINCT customer_id) AS distinct_customer_count
FROM
customer_markets_attended AS cma
LEFT JOIN farmers_market.market_date_info AS md ON cma.market_date = md.market_date
GROUP BY
md.market_year,
md.market_week
ORDER BY
md.market_year,
md.market_week
```
![CleanShot 2024-03-27 at 23.24.26@2x](https://hackmd.io/_uploads/HJsN9h-k0.jpg)
### 找出新顧客的人數與佔比
現在我們想要找出每週有多少首次購買的新顧客,以及他們佔所有不重複顧客的比例有多少
- new_customer_count: 每週新顧客數。
- new_customer_percent: 每週新顧客數佔比。用每週新顧客數除以該週不重複顧客數
```sql=
WITH
customer_markets_attended AS (
SELECT DISTINCT
customer_id,
market_date,
MIN(market_date) OVER (
PARTITION BY
cp.customer_id
) AS first_purchase_date
FROM
farmers_market.customer_purchases cp
)
SELECT
md.market_year,
md.market_week,
COUNT(customer_id) AS customer_visit_count,
COUNT(DISTINCT customer_id) AS distinct_customer_count,
COUNT(
DISTINCT CASE
WHEN cma.market_date = cma.first_purchase_date THEN customer_id
ELSE NULL
END
) AS new_customer_count,
COUNT(
DISTINCT CASE
WHEN cma.market_date = cma.first_purchase_date THEN customer_id
ELSE NULL
END
) / COUNT(DISTINCT customer_id) AS new_customer_percent
FROM
customer_markets_attended AS cma
LEFT JOIN farmers_market.market_date_info AS md ON cma.market_date = md.market_date
GROUP BY
md.market_year,
md.market_week
ORDER BY
md.market_year,
md.market_week
```
![CleanShot 2024-03-27 at 23.42.27@2x](https://hackmd.io/_uploads/S1gfK0nbJC.jpg)