SQL 子查詢與交叉報表應用技巧

SQL 子查詢在資料函式庫操作中扮演著重要的角色，可以有效地處理複雜的資料分析任務。本文將介紹子查詢的不同型別，包括相關子查詢和非相關子查詢，並以計算每千人企業數量、比較人口差異等實際案例說明其應用。此外，LATERAL 連線和 CTE 的使用也能簡化 SQL 查詢，提高程式碼可讀性。LATERAL 連線允許子查詢參照外部查詢的欄位，實作更靈活的資料處理。CTE 則可以定義臨時結果集，減少冗餘計算，並使複雜查詢更容易理解和維護。最後，文章還將介紹如何使用 PostgreSQL 的 crosstab() 函式建立交叉報表，用於更直觀地展現資料的關聯性，例如統計不同辦公室員工的冰淇淋口味偏好。

使用子查詢進行複雜資料分析

在SQL查詢中，子查詢是一種強大的工具，能夠幫助我們處理複雜的資料分析任務。本文將探討子查詢的使用方法，並透過具體範例展示其在實際應用中的價值。

子查詢的基本概念

子查詢是指巢狀在另一個查詢中的查詢陳述式。它可以用於生成臨時結果集，或者用於篩選資料。子查詢可以分為相關子查詢和無相關子查詢兩種。無相關子查詢是指子查詢可以獨立執行，而相關子查詢則依賴於外部查詢的結果。

使用子查詢計算每千人企業數量

在某些情況下，我們需要計算每個州的每千人企業數量。這涉及到兩個步驟：首先計算每個州的企業總數，然後將其與該州的人口數進行比較。以下是一個範例查詢：

SELECT 
    st, 
    pop_est_2018, 
    establishment_count, 
    (establishment_count / (pop_est_2018::numeric / 1000)) AS estabs_per_thousand
FROM 
(
    SELECT 
        state_name AS st, 
        SUM(pop_est_2018) AS pop_est_2018
    FROM 
        census_data
    GROUP BY 
        state_name
) AS census
JOIN 
(
    SELECT 
        st, 
        SUM(establishments) AS establishment_count
    FROM 
        business_data
    GROUP BY 
        st
) AS est
ON census.st = est.st
ORDER BY 
    estabs_per_thousand DESC;

內容解密：

1. 內部子查詢：首先，我們使用兩個子查詢分別計算每個州的人口數和企業數量。這兩個子查詢分別被賦予別名census和est。
2. JOIN操作：然後，我們根據州名將這兩個結果集合並起來。
3. 計算每千人企業數量：接著，我們計算每千人企業數量，並將結果按降序排列。
4. 結果輸出：最終結果顯示了每個州的每千人企業數量，從最高到最低排序。

在列清單中使用子查詢

除了在WHERE子句中使用子查詢外，我們還可以在SELECT列清單中使用子查詢來生成額外的列。例如，以下查詢計算每個縣的人口與全國縣級人口中位數的差異：

SELECT 
    county_name, 
    state_name AS st, 
    pop_est_2019, 
    pop_est_2019 - (SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY pop_est_2019) FROM us_counties_pop_est_2019) AS diff_from_median
FROM 
    us_counties_pop_est_2019
WHERE 
    (pop_est_2019 - (SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY pop_est_2019) FROM us_counties_pop_est_2019))
    BETWEEN -1000 AND 1000;

內容解密：

1. 子查詢計算中位數：首先，子查詢計算全國縣級人口的中位數。
2. 計算人口差異：然後，主查詢計算每個縣的人口與該中位數的差異，並將結果儲存在diff_from_median列中。
3. 篩選結果：最後，WHERE子句篩選出人口與中位數差異在-1000到1000之間的縣。

使用子查詢表示式進行資料篩選

子查詢表示式是另一種強大的工具，可以用於根據另一個表的資料篩選行。例如，IN運算子可以用於檢查某個值是否存在於子查詢傳回的結果集中。

SELECT 
    first_name, 
    last_name
FROM 
    employees
WHERE 
    emp_id IN (SELECT id FROM retirees)
ORDER BY 
    emp_id;

內容解密：

1. 子查詢生成ID列表：首先，子查詢從retirees表中生成一列ID。
2. 篩選員薪水料：然後，主查詢根據emp_id是否在該ID列表中篩選員薪水料。
3. 結果輸出：最終結果顯示了同時出現在employees表和retirees表中的員工姓名。

子查詢表示式：EXISTS 與 NOT EXISTS

在SQL查詢中，子查詢是一種強大的工具，用於處理複雜的資料檢索需求。其中，EXISTS 和 NOT EXISTS 子查詢表示式尤其有用於判斷某個條件是否存在或不存在於資料函式庫中。

EXISTS 子查詢表示式

EXISTS 用於檢查子查詢是否至少傳回一行資料。如果子查詢傳回至少一行，則 EXISTS 評估為 true；否則，評估為 false。在 Listing 13-9 中，我們看到一個相關子查詢的例子，它在 WHERE 子句中包含了一個需要外部查詢資料的表示式。

SELECT first_name, last_name
FROM employees
WHERE EXISTS (
    SELECT id
    FROM retirees
    WHERE id = employees.emp_id
);

這個查詢會傳回 employees 表中 emp_id 在 retirees 表中存在對應 id 的員工姓名。由於子查詢是相關的，它將為外部查詢傳回的每一行執行一次，檢查 retirees 中是否有匹配的 id。

NOT EXISTS 子查詢表示式

與 EXISTS 相反，NOT EXISTS 用於找出在另一個表中沒有對應記錄的行。在 Listing 13-10 中，我們使用 NOT EXISTS 找出 employees 表中沒有在 retirees 表中對應記錄的員工。

SELECT first_name, last_name
FROM employees
WHERE NOT EXISTS (
    SELECT id
    FROM retirees
    WHERE id = employees.emp_id
);

這個查詢傳回了 employees 表中那些 emp_id 在 retirees 表中沒有匹配 id 的員工姓名。結果可能如下：

first_name  last_name
---
-
---
---
  
---
-
---
--
Julia       Reyes
Arthur      Pappas

使用 NOT EXISTS 是找出缺失值或評估資料集完整性的有用技巧。

使用 LATERAL 子查詢

在 FROM 子句中使用 LATERAL 關鍵字可以簡化複雜的查詢，提供更多的功能。

LATERAL 與 FROM

首先，放在 LATERAL 後的子查詢可以參照出現在它之前的表或其他子查詢，這減少了冗餘程式碼，使得計算的重用變得容易。在 Listing 13-11 中，我們計算了縣人口從 2018 年到 2019 年的變化，包括數字變化和百分比變化。

SELECT county_name,
       state_name,
       pop_est_2018,
       pop_est_2019,
       raw_chg,
       round(pct_chg * 100, 2) AS pct_chg
FROM us_counties_pop_est_2019,
     LATERAL (SELECT pop_est_2019 - pop_est_2018 AS raw_chg) rc,
     LATERAL (SELECT raw_chg / pop_est_2018::numeric AS pct_chg) pc
ORDER BY pct_chg DESC;

在這個例子中，第一個 LATERAL 子查詢計算了人口的變化，並將結果別名為 raw_chg。第二個 LATERAL 子查詢計算了百分比變化，直接參照了前一個子查詢的結果 raw_chg。

LATERAL 與 JOIN

結合 LATERAL 和 JOIN 可以建立類別似於程式語言中的迴圈功能：對於在 LATERAL 連線之前的查詢傳回的每一行，在 LATERAL 連線之後的子查詢或函式將被評估一次。在 Listing 13-12 中，我們找出每位教師最近兩次存取實驗室的時間。

SELECT t.first_name, t.last_name, a.access_time, a.lab_name
FROM teachers t
LEFT JOIN LATERAL (SELECT *
                   FROM teachers_lab_access
                   WHERE teacher_id = t.id
                   ORDER BY access_time DESC
                   LIMIT 2) a
ON true
ORDER BY t.id;

在這個查詢中，對於 teachers 表中的每一行，子查詢都會執行，傳回該教師最近兩次存取實驗室的記錄。使用 LEFT JOIN 保證了所有教師都會被列出，即使他們沒有存取實驗室的記錄。

內容解密：

1. 使用LATERAL簡化查詢：透過使用LATERAL，我們可以在FROM子句中參照前面的表或子查詢的結果，減少程式碼冗餘。
2. LATERAL與JOIN結合：這種組合提供了類別似於程式語言中的迴圈的功能，對每一行輸入執行一次子查詢或函式。
3. EXISTS和NOT EXISTS：這兩個表示式用於檢查子查詢是否傳回至少一行或沒有行，分別用於找出存在或缺失的資料。
4. 實際應用：這些技術可以用於多種場景，如資料驗證、找出缺失值、以及複雜的資料分析任務。

使用子查詢與臨時表的進階技術

在處理複雜的資料查詢時，SQL提供了多種強大的工具，如子查詢（Subquery）、LATERAL連線（LATERAL Join）以及公用表表達式（Common Table Expressions, CTE）。本篇將探討這些技術，並透過例項展示其應用。

LATERAL 連線

LATERAL連線是一種特殊的JOIN語法，允許子查詢參照外部查詢中的欄位。這種語法在需要根據外部查詢的結果動態生成子查詢結果時非常有用。

SELECT teachers.first_name, teachers.last_name, 
       labs.access_time, labs.lab_name
FROM teachers
LEFT JOIN LATERAL (
  SELECT access_time, lab_name
  FROM teachers_lab_access
  WHERE teachers_lab_access.teacher_id = teachers.teacher_id
  ORDER BY access_time DESC
  LIMIT 2
) labs ON true;

內容解密：

1. LEFT JOIN LATERAL 用於將 teachers 表與一個依賴於 teachers 表的子查詢進行連線。
2. 子查詢內部使用 WHERE 子句過濾出與外部 teachers 表中 teacher_id 相匹配的紀錄。
3. ORDER BY access_time DESC LIMIT 2 確保只取最近的兩筆存取紀錄。
4. ON true 表示無需特定的連線條件，因為LATERAL子查詢已經與外部查詢建立了關聯。

公用表表達式（CTE）

CTE是一種定義臨時表的語法，可以在主查詢中重複使用。CTE使得複雜的查詢變得更易讀，並且減少了冗餘程式碼。

WITH large_counties (county_name, state_name, pop_est_2019) AS (
  SELECT county_name, state_name, pop_est_2019
  FROM us_counties_pop_est_2019
  WHERE pop_est_2019 >= 100000
)
SELECT state_name, count(*)
FROM large_counties
GROUP BY state_name
ORDER BY count(*) DESC;

內容解密：

1. WITH large_counties ... AS 定義了一個名為 large_counties 的CTE。
2. CTE內部選擇了人口超過100,000的縣份資料。
3. 主查詢對 large_counties 進行分組統計，計算每個州有多少個縣的人口超過100,000。

使用CTE簡化複雜查詢

CTE不僅提高了可讀性，還能減少重複程式碼。以下示例展示瞭如何使用CTE重寫複雜的JOIN查詢。

WITH counties (st, pop_est_2018) AS (
  SELECT state_name, sum(pop_est_2018)
  FROM us_counties_pop_est_2019
  GROUP BY state_name
),
establishments (st, establishment_count) AS (
  SELECT st, sum(establishments) AS establishment_count
  FROM cbp_naics_72_establishments
  GROUP BY st
)
SELECT counties.st, pop_est_2018, establishment_count,
       round((establishments.establishment_count / counties.pop_est_2018::numeric(10,1)) * 1000, 1)
       AS estabs_per_thousand
FROM counties JOIN establishments ON counties.st = establishments.st
ORDER BY estabs_per_thousand DESC;

內容解密：

1. 定義了兩個CTE：counties 和 establishments。
2. counties CTE計算每個州的總人口。
3. establishments CTE計算每個州的相關企業數量。
4. 主查詢JOIN這兩個CTE，並計算每千人中的企業數量。

CTE減少冗餘程式碼

WITH us_median AS (
  SELECT percentile_cont(.5) WITHIN GROUP (ORDER BY pop_est_2019) AS us_median_pop
  FROM us_counties_pop_est_2019
)
SELECT county_name, state_name, pop_est_2019,
       us_median_pop,
       pop_est_2019 - us_median_pop AS diff_from_median
FROM us_counties_pop_est_2019 CROSS JOIN us_median
WHERE (pop_est_2019 - us_median_pop) BETWEEN -1000 AND 1000;

內容解密：

1. 定義了一個CTE us_median 用於計算全國縣份人口的中位數。
2. 主查詢使用 CROSS JOIN 將 us_median 的結果與每個縣份的人口資料結合。
3. 篩選出人口接近中位數的縣份。

交叉報表：資料分析的利器

交叉報表是一種強大的資料分析工具，能夠幫助我們快速地匯總和比較變數之間的關係。在本篇文章中，我們將介紹如何使用 PostgreSQL 的 crosstab() 函式來產生交叉報表。

什麼是交叉報表？

交叉報表是一種表格，用於展示兩個變數之間的關係。行代表一個變數，列代表另一個變數，而每個儲存格中的值則代表了兩個變數之間的交集。例如，在選舉分析中，候選人的名字是一個變數，選區是另一個變數，而每個儲存格中的值則代表了該候選人在該選區的得票數。

安裝 crosstab() 函式

PostgreSQL 的 crosstab() 函式是 tablefunc 模組的一部分。要安裝 tablefunc，請在 pgAdmin 中執行以下命令：

CREATE EXTENSION tablefunc;

安裝完成後，您就可以使用 crosstab() 函式來產生交叉報表了。

使用 crosstab() 函式

讓我們以一個例子來說明如何使用 crosstab() 函式。假設我們有一個名為 ice_cream_survey 的表格，其中包含了員工對冰淇淋口味的調查結果。我們想要知道每個辦公室的員工最喜歡哪種冰淇淋口味。

首先，我們需要建立一個表格並載入資料：

CREATE TABLE ice_cream_survey (
    response_id integer PRIMARY KEY,
    office text,
    flavor text
);

COPY ice_cream_survey
FROM 'C:\YourDirectory\ice_cream_survey.csv'
WITH (FORMAT CSV, HEADER);

接下來，我們可以使用以下程式碼來產生交叉報表：

SELECT *
FROM crosstab(
    'SELECT office, flavor, count(*)
     FROM ice_cream_survey
     GROUP BY office, flavor
     ORDER BY office',
    'SELECT flavor
     FROM ice_cream_survey
     GROUP BY flavor
     ORDER BY flavor'
) AS (office text, chocolate bigint, strawberry bigint, vanilla bigint);

內容解密：

1. crosstab() 函式需要兩個查詢作為引數。第一個查詢產生交叉報表的資料，第二個查詢產生欄位的名稱。
2. 第一個查詢使用 GROUP BY 和 count(*) 來計算每個辦公室的員工對每種冰淇淋口味的喜好程度。
3. 第二個查詢使用 GROUP BY 和 ORDER BY 來取得唯一的冰淇淋口味，並按照字母順序排序。
4. AS 關鍵字用於指定交叉報表的欄位名稱和資料型別。

執行上述程式碼後，我們可以得到以下交叉報表：

 office   | chocolate | strawberry | vanilla
---
-
---
---
+
---
-
---
-
---
+
---
-
---
-
---
-+
---
-
---
--
 Downtown |        23 |         32 |      19
 Midtown  |        41 |            |      
 Uptown   |        22 |         17 |      23

從交叉報表中，我們可以清楚地看到每個辦公室的員工最喜歡的冰淇淋口味。