SQLHAVING子句篩選聚合查詢

在資料函式庫查詢中，我們經常需要對資料進行分組彙總，並根據彙總結果篩選出符合特定條件的資料。HAVING 子句正是用於篩選聚合函式結果的利器。例如，我們可以利用 HAVING 子句篩選出特定年份圖書館到訪人次超過一定數量的州或地區，以便進行更精細的分析。這在處理大量資料時尤其有用，可以有效縮小結果集，提高查詢效率。

SELECT pls18.stabr,
       sum(pls18.visits) AS visits_2018,
       sum(pls17.visits) AS visits_2017,
       sum(pls16.visits) AS visits_2016,
       round((sum(pls18.visits::numeric) - sum(pls17.visits)) / sum(pls17.visits) * 100, 1) AS chg_2018_17,
       round((sum(pls17.visits::numeric) - sum(pls16.visits)) / sum(pls16.visits) * 100, 1) AS chg_2017_16
FROM pls_fy2018_libraries pls18
JOIN pls_fy2017_libraries pls17 ON pls18.fscskey = pls17.fscskey
JOIN pls_fy2016_libraries pls16 ON pls18.fscskey = pls16.fscskey
WHERE pls18.visits >= 0
  AND pls17.visits >= 0
  AND pls16.visits >= 0
GROUP BY pls18.stabr
HAVING sum(pls18.visits) > 50000000
ORDER BY chg_2018_17 DESC;

使用HAVING篩選聚合查詢

為了細化分析，我們可以檢視具有相似特徵的州和領地的子集。根據造訪次數的百分比變化，將大州和小州區分開來是有意義的。在羅德島州這樣的小州，單一圖書館因裝修而關閉六個月可能會產生重大影響。但在加利福尼亞州這樣的大州，單一圖書館的關閉可能在全州範圍內的統計中幾乎不會被注意到。為了檢視造訪次數相似的州，我們可以按照造訪次數欄位對結果進行排序，但透過篩選查詢來獲得較小的結果集會更為簡潔。

要篩選聚合函式的結果，我們需要使用標準ANSI SQL中的HAVING子句。您已經熟悉使用WHERE進行篩選，但聚合函式（如sum()）不能在WHERE子句中使用，因為它們在行層級上運作，而聚合函式則跨多行運作。HAVING子句對聚合產生的群組設定條件。清單9-14中的程式碼透過在GROUP BY之後插入HAVING子句來修改清單9-13中的查詢。

SELECT pls18.stabr,
       sum(pls18.visits) AS visits_2018,
       sum(pls17.visits) AS visits_2017,
       sum(pls16.visits) AS visits_2016,
       round((sum(pls18.visits::numeric) - sum(pls17.visits)) / sum(pls17.visits) * 100, 1) AS chg_2018_17,
       round((sum(pls17.visits::numeric) - sum(pls16.visits)) / sum(pls16.visits) * 100, 1) AS chg_2017_16
FROM pls_fy2018_libraries pls18
JOIN pls_fy2017_libraries pls17 ON pls18.fscskey = pls17.fscskey
JOIN pls_fy2016_libraries pls16 ON pls18.fscskey = pls16.fscskey
WHERE pls18.visits >= 0
  AND pls17.visits >= 0
  AND pls16.visits >= 0
GROUP BY pls18.stabr
HAVING sum(pls18.visits) > 50000000
ORDER BY chg_2018_17 DESC;

內容解密：

查詢目標：此查詢旨在找出2018年造訪次數總和超過5000萬的州，並計算各州在2016年至2018年間的造訪次數變化百分比。
資料來源：查詢涉及三個表格，分別是pls_fy2018_libraries、pls_fy2017_libraries和pls_fy2016_libraries，這些表格儲存了不同年份的圖書館調查資料。
WHERE條件：確保所選取的資料中，造訪次數為非負值，避免因負值資料導致計算錯誤。
GROUP BY與HAVING：按照州別(stabr)進行分組，並使用HAVING篩選出2018年造訪次數總和超過5000萬的州。
ORDER BY：按照2017年至2018年的造訪次數變化百分比(chg_2018_17)進行降序排序。

自行練習

圖書館就業趨勢分析：修改清單9-13和9-14中的程式碼，計算各州在不同年份的圖書館員工總數(totstaff)變化百分比，並篩選出造訪次數較高的州進行比較。
按地區分析造訪次數變化：使用obereg欄位對資料進行分組，計算不同地區的造訪次數變化百分比，並與相關檔案對照以瞭解地區程式碼的含義。額外挑戰：建立一個包含地區名稱的表格，並與匯總查詢結合，以地區名稱而非程式碼進行分組。
探索不同型別的連線：回顧第7章中學習的不同型別的連線，寫出一個查詢，使用適當的連線型別顯示所有表格中的所有列，包括那些沒有匹配的列。並在WHERE子句中使用IS NULL篩選條件，找出未包含在一個或多個表格中的機構。

第10章：檢查與修改資料

現實中，資料分析師經常需要處理結構不完善或錯誤百出的資料。本章將學習如何使用SQL來清理和維護資料，使其更適合分析。資料品質問題可能源於多種原因，包括資料轉換錯誤、人為輸入錯誤等。掌握檢查和修改資料的方法，不僅能幫助我們解決現有的問題，也能為未來的資料更新和擴充提供便利。

資料匯入與初步分析

在進行資料分析之前，首先需要將資料匯入資料函式庫中。以下範例使用的是美國肉類別、家禽和蛋製品生產商的目錄，該資料由美國農業部的食品安全與檢驗服務（FSIS）機構編制和更新。

匯入肉類別、家禽和蛋製品生產商資料

首先，我們需要建立一個名為 meat_poultry_egg_establishments 的表格，並使用 COPY 命令將 CSV 檔案匯入該表格。

CREATE TABLE meat_poultry_egg_establishments (
    establishment_number text CONSTRAINT est_number_key PRIMARY KEY,
    company text,
    street text,
    city text,
    st text,
    zip text,
    phone text,
    grant_date date,
    activities text,
    dbas text
);

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

CREATE INDEX company_idx ON meat_poultry_egg_establishments (company);

內容解密：

建立 meat_poultry_egg_establishments 表格，包含 10 個欄位，其中 establishment_number 設為主要鍵。
使用 COPY 命令將 CSV 檔案匯入表格中，指定 CSV 格式並包含標題列。
在 company 欄位上建立索引，以加快查詢速度。

匯入資料後，我們可以使用 count() 聚合函式檢查表格中的資料列數。

SELECT count(*) FROM meat_poultry_egg_establishments;

結果應顯示 6,287 列。接下來，我們需要了解資料的內容，並判斷是否需要對資料進行修改。

資料初步分析

在進行資料分析時，首先需要了解資料的結構和內容。以下範例使用聚合查詢來檢查資料中是否存在重複的公司地址。

SELECT company, street, city, st, count(*) AS address_count
FROM meat_poultry_egg_establishments
GROUP BY company, street, city, st
HAVING count(*) > 1
ORDER BY company, street, city, st;

內容解密：

將公司按照地址（company、street、city 和 st）進行分組。
使用 count(*) 統計每個分組中的資料列數，並將結果命名為 address_count。
使用 HAVING 子句篩選出 address_count 大於 1 的結果，即重複的地址。
將結果按照公司和地址進行排序。

查詢結果顯示，有 23 列資料存在重複的公司地址。這可能表明資料存在輸入錯誤或其他問題，需要進一步調查。

檢查缺失值

接下來，我們需要檢查資料中是否存在缺失值，例如某些列的資料缺失。以下範例檢查每個州的肉類別、家禽和蛋製品加工公司的數量。

-- 待續，請參考 Listing 10-3

內容解密：

（待續，請參考原來的 Listing 10-3）

在進行資料分析時，瞭解資料的結構和內容至關重要。透過聚合查詢和其他分析技術，可以發現資料中的問題和潛在的洞察。

資料清理與檢查：以肉品、家禽和雞蛋機構資料為例

在進行資料分析時，資料的品質直接影響分析結果的準確性和可靠性。本文以 meat_poultry_egg_establishments 表格為例，介紹如何檢查和清理資料中的問題，包括缺失值、不一致的資料值和格式錯誤等。

檢查缺失值

首先，我們檢查 st（州程式碼）欄位中的缺失值。以下 SQL 查詢用於計算每個州程式碼的出現次數：

SELECT st,
       count(*) AS st_count
FROM meat_poultry_egg_establishments
GROUP BY st
ORDER BY st;

結果顯示共有 57 行資料，包括美國 50 個州以及其他領地，如波多黎各、關島和美屬薩摩亞。其中，阿拉斯加州（AK）有 17 個機構登記在冊。然而，結果底部有一行的 st 欄位為 NULL，且 st_count 為 3，表示有三行資料的州程式碼缺失。

檢視缺失值的詳細資訊

SELECT establishment_number,
       company,
       city,
       st,
       zip
FROM meat_poultry_egg_establishments
WHERE st IS NULL;

該查詢傳回了三行 st 欄位為 NULL 的資料，顯示這些機構的州程式碼缺失。檢查原始資料後發現，這些行的確沒有州程式碼，這是資料本身的問題，而非匯入過程中的錯誤。

檢查不一致的資料值

不一致的資料值會影響分析結果。例如，公司名稱的多種拼寫方式會導致聚合結果不準確。使用以下查詢檢查 company 欄位中的不一致性：

SELECT company,
       count(*) AS company_count
FROM meat_poultry_egg_establishments
GROUP BY company
ORDER BY company ASC;

結果顯示，一些公司有多種不同的名稱拼寫，例如 “Armour-Eckrich Meats” 有多種變體。這種不一致性需要在分析前進行修正，以確保正確的聚合結果。

檢查格式錯誤的值

使用 length() 函式檢查欄位值的長度，可以發現格式錯誤。例如，檢查 zip 欄位的長度：

SELECT length(zip),
       count(*) AS length_count
FROM meat_poultry_egg_establishments
GROUP BY length(zip)
ORDER BY length(zip) ASC;

結果顯示，有 496 個 ZIP 程式碼長度為 4 位，86 個為 3 位，這些可能是由於在轉換資料時丟失了前導零。進一步篩選這些錯誤的 ZIP 程式碼對應的州程式碼：

SELECT st,
       count(*) AS st_count
FROM meat_poultry_egg_establishments
WHERE length(zip) < 5
GROUP BY st
ORDER BY st ASC;

結果表明，這些錯誤的 ZIP 程式碼主要集中在美國東北部地區，這些地區的 ZIP 程式碼通常以零開頭。

ZIP 程式碼格式錯誤的原因與解決方案

在將原始 Excel 檔案轉換為 CSV 檔案時，如果將 ZIP 程式碼儲存為「常規」數字格式而不是文字，就會丟失前導零。解決此問題的方法是在處理資料時，將 ZIP 程式碼視為文字，並確保在匯入資料函式庫前保留前導零。

資料函式庫修正與資料處理的最佳實踐

在處理資料函式庫時，資料的正確性和完整性是至關重要的。當資料存在錯誤或不一致時，我們需要使用SQL來進行修正。以下將介紹如何使用SQL來修改資料表、欄位和資料，以確保資料的品質。

發現並修正資料問題

在分析資料時，我們經常會遇到一些問題，例如缺失值、不一致的資料格式或錯誤的資料型別。這些問題可能會影響我們的分析結果，因此需要及時發現並修正。

常見的資料問題

缺失值：某些欄位可能存在缺失值，需要檢查並決定如何處理這些缺失值。
資料不一致：例如，公司的名稱可能存在不同的拼寫方式，需要統一。
資料轉換錯誤：在從其他格式轉換資料時，可能會出現錯誤，例如ZIP程式碼的錯誤。

使用ALTER TABLE修改資料表結構

ALTER TABLE是SQL中用於修改資料表結構的陳述式，可以新增欄位、修改欄位資料型別或刪除欄位。

新增欄位

ALTER TABLE table_name ADD COLUMN column_name data_type;

刪除欄位

ALTER TABLE table_name DROP COLUMN column_name;

修改欄位資料型別

ALTER TABLE table_name ALTER COLUMN column_name SET DATA TYPE data_type;

新增NOT NULL約束

ALTER TABLE table_name ALTER COLUMN column_name SET NOT NULL;

執行ALTER TABLE陳述式後，如果操作成功，PostgreSQL會傳回ALTER TABLE的訊息。如果操作違反了約束或無法修改欄位資料型別，PostgreSQL會傳回錯誤訊息。

使用UPDATE修改資料

UPDATE陳述式用於修改資料表中的資料，可以更新單一欄位或多個欄位的值。

更新所有列的欄位值

UPDATE table_name SET column_name = value;

更新特定列的欄位值

UPDATE table_name SET column_name = value WHERE condition;

同時更新多個欄位

UPDATE table_name 
SET column_a = value_a, 
    column_b = value_b;

執行UPDATE陳述式後，PostgreSQL會傳回UPDATE的訊息，並顯示受影響的列數。

何時應該放棄使用某個資料集？

如果在檢查資料時發現大量的缺失值或不合理的資料，可能需要重新評估使用該資料集的合理性。首先，檢查原始資料檔案是否正確匯入，並與原始檔案進行視覺比較。如果問題仍然存在，應聯絡提供資料的機構或公司，尋求解釋和建議。有時，修復資料集所需的努力可能超過其價值，這時就需要做出艱難的判斷，放棄使用該資料集或尋找替代方案。

更新資料與備份表格的實務操作

在進行資料更新時，務必謹慎處理以避免資料遺失或毀損。PostgreSQL 提供多種方式來檢查更新結果並備份重要資料。

使用 RETURNING 檢視更新資料

在 UPDATE 陳述式中加入 RETURNING 子句，可以直接檢視被更新的資料列，無需額外執行查詢陳述式。語法如下：

UPDATE table
SET column_a = value
RETURNING column_a, column_b, column_c;

內容解密：

UPDATE table 指定要更新的表格。
SET column_a = value 設定要更新的欄位及其新值。
RETURNING column_a, column_b, column_c 指定更新後要傳回的欄位，可使用萬用字元 * 傳回所有欄位。

這種方式不僅適用於 UPDATE，還可用於 INSERT 和 DELETE FROM 操作，是 PostgreSQL 的特色功能之一。

建立備份表格

在對重要資料進行修改前，建議建立備份表格以防意外發生。以下範例展示如何使用 CREATE TABLE 陳述式的變體來建立備份表格：

CREATE TABLE meat_poultry_egg_establishments_backup AS
SELECT * FROM meat_poultry_egg_establishments;

內容解密：

CREATE TABLE meat_poultry_egg_establishments_backup AS 建立一個名為 meat_poultry_egg_establishments_backup 的新表格，並將查詢結果作為其初始資料。
SELECT * FROM meat_poultry_egg_establishments 選擇來源表格的所有欄位和資料。

建立備份表格後，可使用以下查詢確認兩表格的資料筆數是否一致：

SELECT 
    (SELECT count(*) FROM meat_poultry_egg_establishments) AS original,
    (SELECT count(*) FROM meat_poultry_egg_establishments_backup) AS backup;

內容解密：

此查詢比較原始表格和備份表格的資料筆數，以驗證備份是否成功。

注意事項

使用 CREATE TABLE 建立備份表格時，不會複製來源表格的索引。若需在備份表格上執行查詢，建議重新建立相應的索引。

修復缺失的欄位資料

假設 meat_poultry_egg_establishments 表格中有部分資料的 st 欄位缺失，需使用 UPDATE 陳述式填補這些缺失值。

建立欄位副本

為避免誤操作導致資料遺失，先建立 st 欄位的副本：

ALTER TABLE meat_poultry_egg_establishments ADD COLUMN st_copy text;
UPDATE meat_poultry_egg_establishments SET st_copy = st;

內容解密：

ALTER TABLE meat_poultry_egg_establishments ADD COLUMN st_copy text; 在表格中新增一個名為 st_copy 的欄位，其資料型別與 st 相同。
UPDATE meat_poultry_egg_establishments SET st_copy = st; 將 st 欄位的資料複製到新建立的 st_copy 欄位。

執行以下查詢可驗證複製結果：

SELECT st, st_copy 
FROM meat_poultry_egg_establishments 
WHERE st IS DISTINCT FROM st_copy 
ORDER BY st;

內容解密：

使用 IS DISTINCT FROM 比較 st 和 st_copy 欄位的值是否不同。正常情況下，此查詢應傳回零筆資料，表示兩欄位完全一致。

更新缺失值

根據實際情況填補缺失的州程式碼：

UPDATE meat_poultry_egg_establishments 
SET st = 'MN' 
WHERE establishment_number = 'V18677A';

UPDATE meat_poultry_egg_establishments 
SET st = 'AL' 
WHERE establishment_number = 'M45319+P45319';

UPDATE meat_poultry_egg_establishments 
SET st = 'WI' 
WHERE establishment_number = 'M263A+P263A+V263A'
RETURNING establishment_number, company, city, st, zip;

內容解密：

透過 establishment_number 識別需要更新的特定資料列，並使用 RETURNING 檢視更新後的結果。

這種逐步更新的方式能夠有效減少錯誤發生的機率，並確保資料的一致性。

玄貓 BlackCat

技術愛好者，專注於分享程式開發、雲端技術與 AI 應用的心得體會。