# 11. 進階查詢語法結構

## 11.1 將兩個查詢結果聯集的 UNION

使用UNION 關鍵字可以將兩個查詢的結果做聯集，只要兩者的欄位資料型別、欄位數與欄位排列順序相同即可。

### 用 UNION 聯集只保留不重複的列資料

聯集的意思就是將兩個集合中的所有元素放在一起，並將重複的元素只保留一個。

我們用兩個SELECT 語句分別查出市集年份是2019與2020年的首次市集日期，然後將這兩個查詢的輸出結果合併起來。因為第一個查詢與第二個查詢的輸出不重複，都會被放進聯集的輸出中：

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'


💡你可能會想，這麼簡單的查詢只要用 GROUP BY 與IN 關鍵字（如下程式）就能得到相同答案，有需要分成兩個查詢再用 UNION 合併嗎？

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 的輸出如下：

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
  )


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 合併。

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'


4. 由於我們想知道的是每個市集日期供貨量最大的產品（包括單位是 unit 與 lbs），因此只需要將所有 quantity_rank = 1 的產品篩選出來即可。為此，我們將兩個 SELECT 查詢以及 UNION ALL 改為子查詢，並取別名為 x。然後在外部查詢中用 WHERE 子句指定 x.quantity_rank = 1 的篩選條件，即可得到最後的輸出

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


### 用 UNION的時機

例如：
1. 當你擁有事件日誌（如網站流量日誌）分存於多個文件檔，當每個文件檔被加載到資料庫中需要合併資料時，就會有這種需求。
2. 這些表格可能是來自不同時間點的同一動態資料集的靜態快照（比如說股票交易是動態進行，但我們每整點抓取所有股票當時的股價，這就是整點的靜態快照）。
3. 資料可能是從一個系統遷移到另一個系統，你需要從兩個不同系統的表格中提取資料，並將其合併到一個視圖中，以查看記錄的整個歷史。

## 11.2 自我連結（Self-Join）找出最大值

自我連結 (Self-Join) 是讓表格與自己做連結，可藉此比較自身列資料間的異同。SQL 寫法就限一般 JOIN 一模一樣，差別只在於是同一個表格與自己（如同副本）連結。為了區別兩個表格，可以各自取一個表格別名。

我們試著用農夫市集資料庫示範，**找出任一市集日期的總銷售額是否是當時的歷史新高**。在這個案例中，我們想知道每個市集日期之前有哪一天營業額最高，這就可以利用自我連結來進行比較。

### 將某一市集日期銷售額與之前銷售額並列

首先，我們需要透過 market_date 欄位來彙總 customer_purchases 表格中每日的總銷售額，將其寫入 WITH 語旬中並命名為 sales_per_market_date

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
  )


假設我們要以 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 的後面。

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:

#### result:

出可看出只有三個市集日期早於 2019-04-13，所以會將該三個日期與總銷售額一一連結到 2019-04-13 欄位的後面。

### 算出過去市集日期的最高銷售額

再進一步，我們希望跟在每個市集日期之後的是當日營業額與之前的歷史新高，不需要把過去每一日的營業額都一一列出，那就可以為 pm.sales 欄位用 MAX 函數取得之前的最高銷售額，取欄位別名 previous_max_sales，同時用 GROUP BY 以 cm.market_date、cm.sales 分組

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


### 設定是否創歷史新高的指標

此外，為了更清楚識別某市集日期的銷售額是否打破歷史最高記錄，我們可以用 CASE WHEN 子句判斷本次銷售額是否大於歷史記錄，並建一個識別用的指標欄位（sales_record_set），當創下歷史新高時，此欄位的值會標示「YES」，否則標示「NO」

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


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 欄位

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


我們的目的是統計每週的新顧客與回頭客，所以還需要連結 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）函數計數

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


### 找出新顧客的人數與佔比

現在我們想要找出每週有多少首次購買的新顧客，以及他們佔所有不重複顧客的比例有多少

- new_customer_count: 每週新顧客數。
- new_customer_percent: 每週新顧客數佔比。用每週新顧客數除以該週不重複顧客數

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