SQL 匯總函式是資料函式庫分析的利器，能有效地從大量資料中提取關鍵資訊。本文以美國公共圖書館調查資料為例，示範如何運用 SQL 匯總函式，例如 COUNT(), SUM(), MAX(), MIN()，搭配 GROUP BY 子句進行分組彙總，計算各州圖書館數量、總訪客數、無線網路使用次數等指標。此外，文章也說明如何合併多個年度的資料表，分析圖書館訪客數量的變化趨勢，以及如何計算訪客數量的百分比變化，進而瞭解不同地區的圖書館使用趨勢。透過這些技巧，我們可以更深入地瞭解圖書館的營運狀況和服務效能。
使用SQL匯總函式分析圖書館調查資料
本章節將延續前面的SQL基礎，探討如何使用匯總函式和分組來分析資料。我們將以美國公共圖書館調查資料為例，展示如何透過SQL查詢來提取有價值的資訊。
建立圖書館調查資料表
首先，我們需要建立三個資料表來儲存2018年、2017年和2016年的圖書館調查資料。這些資料表將用於後續的分析和比較。
建立2018年圖書館資料表
CREATE TABLE pls_fy2018_libraries (
stabr text NOT NULL,
fscskey text CONSTRAINT fscskey_2018_pkey PRIMARY KEY,
libid text NOT NULL,
libname text NOT NULL,
address text NOT NULL,
city text NOT NULL,
zip text NOT NULL,
--snip--
longitude numeric(10,7) NOT NULL,
latitude numeric(10,7) NOT NULL
);
COPY pls_fy2018_libraries
FROM 'C:\YourDirectory\pls_fy2018_libraries.csv'
WITH (FORMAT CSV, HEADER);
CREATE INDEX libname_2018_idx ON pls_fy2018_libraries (libname);
內容解密：
- CREATE TABLE 陳述式用於建立
pls_fy2018_libraries資料表，包含相關欄位以儲存圖書館調查資料。 - COPY 命令用於從CSV檔案中匯入資料到
pls_fy2018_libraries資料表。 - CREATE INDEX 陳述式用於在
libname欄位上建立索引，以提高查詢效率。
分析圖書館調查資料
在建立資料表並匯入資料後，我們可以使用SQL匯總函式來分析圖書館調查資料，例如計算每年的圖書館數量、藏書量等指標。
修改CREATE TABLE陳述式以新增主鍵和外部索引鍵約束
根據提供的 albums 和 songs 表格，我們需要修改 CREATE TABLE 陳述式以新增主鍵和外部索引鍵約束。
CREATE TABLE albums (
album_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
catalog_code text,
title text NOT NULL,
artist text NOT NULL,
release_date date,
genre text,
description text
);
CREATE TABLE songs (
song_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title text NOT NULL,
composers text,
album_id bigint NOT NULL,
CONSTRAINT fk_album FOREIGN KEY (album_id) REFERENCES albums(album_id)
);
內容解密：
- 在
albums表格中，album_id被設定為主鍵。 - 在
songs表格中，song_id被設定為主鍵，album_id被設定為外部索引鍵，參考albums表格的album_id。
選擇自然鍵或代理鍵
在 albums 表格中，catalog_code 可能是一個有用的自然鍵，但需要進一步檢查其唯一性和非空性。
建立索引
在 albums 和 songs 表格中，title 和 artist 欄位可能是建立索引的好候選者，以提高查詢效率。
建立與填充公共圖書館調查資料表
在前面的範例中，我們使用 CREATE TABLE 陳述式建立了一個名為 pls_fy2018_libraries 的資料表，並指定 fscskey 欄位作為主鍵。接著，我們使用 COPY 陳述式將 CSV 檔案中的資料匯入到該資料表中。最後，我們在 libname 欄位上建立了一個索引，以提高查詢效能。
建立 2017 和 2016 年圖書館資料表
建立 2017 和 2016 年圖書館調查資料表的步驟與前述類別似。以下為相關的 SQL 程式碼：
CREATE TABLE pls_fy2017_libraries (
stabr text NOT NULL,
fscskey text CONSTRAINT fscskey_17_pkey PRIMARY KEY,
libid text NOT NULL,
libname text NOT NULL,
address text NOT NULL,
city text NOT NULL,
zip text NOT NULL,
-- 省略其他欄位定義
longitude numeric(10,7) NOT NULL,
latitude numeric(10,7) NOT NULL
);
CREATE TABLE pls_fy2016_libraries (
stabr text NOT NULL,
fscskey text CONSTRAINT fscskey_16_pkey PRIMARY KEY,
libid text NOT NULL,
libname text NOT NULL,
address text NOT NULL,
city text NOT NULL,
zip text NOT NULL,
-- 省略其他欄位定義
longitude numeric(10,7) NOT NULL,
latitude numeric(10,7) NOT NULL
);
COPY pls_fy2017_libraries
FROM 'C:\YourDirectory\pls_fy2017_libraries.csv'
WITH (FORMAT CSV, HEADER);
COPY pls_fy2016_libraries
FROM 'C:\YourDirectory\pls_fy2016_libraries.csv'
WITH (FORMAT CSV, HEADER);
CREATE INDEX libname_2017_idx ON pls_fy2017_libraries (libname);
CREATE INDEX libname_2016_idx ON pls_fy2016_libraries (libname);
內容解密：
CREATE TABLE陳述式：用於建立名為pls_fy2017_libraries和pls_fy2016_libraries的資料表，並定義各欄位的資料型別和約束條件。fscskey欄位：被指定為主鍵，以確保每筆記錄的唯一性。COPY陳述式：用於將 CSV 檔案中的資料匯入到對應的資料表中。CREATE INDEX陳述式：在libname欄位上建立索引，以提高查詢效能。
使用聚合函式探索圖書館資料
聚合函式可以對多個列的值進行計算，並傳回單一結果。在本文中，我們將使用聚合函式來探索圖書館資料。
使用 count() 函式計算列數
首先，我們可以使用 count() 函式來計算每個資料表的列數。以下是相關的 SQL 程式碼：
SELECT count(*)
FROM pls_fy2018_libraries;
SELECT count(*)
FROM pls_fy2017_libraries;
SELECT count(*)
FROM pls_fy2016_libraries;
內容解密：
count(*)函式：傳回資料表的列數，包括含有 NULL 值的列。- 查詢結果：預計結果應與 IMLS 檔案中記載的列數相符，分別為 9261、9245 和 9252。
執行上述查詢後，我們可以驗證資料表的列數是否正確。如果結果與預期相符，則表示資料匯入正確。否則，可能需要檢查匯入過程或資料來源是否有誤。
瞭解SQL中的聚合函式
在SQL中，聚合函式用於對資料進行匯總和分析，例如計算總和、平均值、最大值和最小值等。這些函式對於理解資料的整體特性和趨勢至關重要。
計算列中的非NULL值數量
使用count()函式可以計算指定列中非NULL值的數量。例如，在pls_fy2018_libraries表中計算phone列的非NULL值數量，如下所示：
SELECT count(phone)
FROM pls_fy2018_libraries;
內容解密：
count(phone)：計算phone列中非NULL值的數量。FROM pls_fy2018_libraries：指定要查詢的表。 結果顯示共有9,261行具有phone值，這與表的總行數相同，表明每個行都有一個phone值。
計算列中的唯一值數量
使用DISTINCT關鍵字可以傳回指定列中的唯一值。若將其與count()函式結合，可以計算列中的唯一值數量。例如，計算libname列中的唯一值數量：
SELECT count(libname)
FROM pls_fy2018_libraries;
SELECT count(DISTINCT libname)
FROM pls_fy2018_libraries;
內容解密：
- 第一個查詢傳回表中的總行數，即9,261。
- 第二個查詢透過使用
DISTINCT關鍵字，傳回libname列中的唯一值數量，即8,478。 這表明有526個圖書館機構與其他機構分享名稱。
使用max()和min()函式查詢最大值和最小值
max()和min()函式分別用於查詢指定列中的最大值和最小值。例如，在visits列上使用這些函式：
SELECT max(visits), min(visits)
FROM pls_fy2018_libraries;
內容解密：
max(visits)：傳回visits列中的最大值。min(visits)：傳回visits列中的最小值。 結果顯示最大值為16,686,945，而最小值為-3。負值表示特定的條件，例如-1表示未回應，-3表示不適用。
使用GROUP BY子句匯總資料
GROUP BY子句與聚合函式結合使用，可以根據一個或多個列的值對結果進行分組。例如，根據stabr列（州縮寫）對結果進行分組：
SELECT stabr
FROM pls_fy2018_libraries
GROUP BY stabr
ORDER BY stabr;
內容解密：
GROUP BY stabr：根據stabr列的值對結果進行分組。ORDER BY stabr：按字母順序排列結果。 這將傳回55個唯一的州縮寫，包括50個州、華盛頓特區和幾個美國領土。
結合GROUP BY與count()函式
透過將GROUP BY與count()函式結合，可以獲得更具描述性的資訊。例如，計算每個州的圖書館機構數量：
SELECT stabr, count(*)
FROM pls_fy2018_libraries
GROUP BY stabr
ORDER BY count(*) DESC;
內容解密：
count(*)：計算每個分組中的行數。GROUP BY stabr：根據stabr列的值對結果進行分組。ORDER BY count(*) DESC：按計數降序排列結果。 這將顯示每個州的圖書館機構數量，並按數量排序。
使用GROUP BY與聚合函式分析圖書館資料
在分析圖書館資料時，我們經常需要使用SQL的GROUP BY子句與聚合函式來匯總和比較資料。本篇文章將介紹如何使用這些技術來分析2018年圖書館資料，並探討如何結合多個表格進行趨勢分析。
使用GROUP BY與count()函式
首先，我們來看看如何使用GROUP BY與count()函式來統計每個州的圖書館機構數量。以下是SQL查詢陳述式：
SELECT stabr, count(*)
FROM pls_fy2018_libraries
GROUP BY stabr
ORDER BY count(*) DESC;
內容解密：
SELECT stabr, count(*)：選擇stabr欄位並計算每組的資料筆數。FROM pls_fy2018_libraries：指定資料來源表格。GROUP BY stabr：根據stabr欄位進行分組。ORDER BY count(*) DESC：按照計數結果降序排序。
執行上述查詢後，我們發現紐約州、伊利諾伊州和德克薩斯州擁有最多的圖書館機構。
使用GROUP BY於多個欄位
我們還可以結合GROUP BY與多個欄位來取得更多資訊。例如，統計每個州中圖書館機構地址變更的情況：
SELECT stabr, stataddr, count(*)
FROM pls_fy2018_libraries
GROUP BY stabr, stataddr
ORDER BY stabr, stataddr;
內容解密：
SELECT stabr, stataddr, count(*)：選擇stabr和stataddr欄位並計算每組的資料筆數。GROUP BY stabr, stataddr：根據stabr和stataddr兩個欄位進行分組。ORDER BY stabr, stataddr：按照州別和地址狀態程式碼升序排序。
查詢結果顯示，大多數圖書館機構的地址沒有變更，這與我們的預期相符。
使用sum()函式分析圖書館活動趨勢
為了分析圖書館的年度參觀次數趨勢，我們需要使用sum()聚合函式。首先，我們分別計算2018年、2017年和2016年的總參觀次數：
SELECT sum(visits) AS visits_2018
FROM pls_fy2018_libraries
WHERE visits >= 0;
SELECT sum(visits) AS visits_2017
FROM pls_fy2017_libraries
WHERE visits >= 0;
SELECT sum(visits) AS visits_2016
FROM pls_fy2016_libraries
WHERE visits >= 0;
內容解密：
SELECT sum(visits) AS visits_年份：計算visits欄位的總和並賦予別名。WHERE visits >= 0：過濾掉負值（表示不適用或未回應）的資料。
結果顯示，從2016年到2018年，圖書館的年度參觀次數呈現下降趨勢。
結合多表進行趨勢分析
為了進一步分析趨勢，我們需要結合多個表格的資料，篩選出同時存在於三個年份表格中的圖書館機構，並計算它們的參觀次數總和：
SELECT sum(pls18.visits) AS visits_2018,
sum(pls17.visits) AS visits_2017,
sum(pls16.visits) AS visits_2016
FROM pls_fy2018_libraries pls18
JOIN pls_fy2017_libraries pls17 ON pls18.fscskey = pls17.fscskey
JOIN pls_fy2016_libraries pls16 ON pls17.fscskey = pls16.fscskey
WHERE pls18.visits >= 0 AND pls17.visits >= 0 AND pls16.visits >= 0;
內容解密：
JOIN操作：根據共同的fscskey欄位連線三個表格。WHERE子句：確保所有年份的參觀次數都是非負值。
此查詢幫助我們更準確地分析圖書館參觀次數的變化趨勢，因為它排除了因機構開閉或合併導致的資料差異。
分析圖書館使用趨勢：訪客資料的變化
在前面的章節中，我們已經探討瞭如何使用SQL查詢來分析單一表格中的資料。現在，我們將進一步學習如何結合多個表格的資料來進行更全面的分析。
合併多年度圖書館訪客資料
首先，我們來看看如何使用JOIN操作將2018年、2017年和2016年的圖書館訪客資料合併起來，並計算總訪客數。
SELECT
sum(pls18.visits) AS visits_2018,
sum(pls17.visits) AS visits_2017,
sum(pls16.visits) AS visits_2016
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;
內容解密：
- 使用
sum()聚合函式來計算每個表格中的visits欄位的總和。 - 使用
JOIN操作根據fscskey主鍵將三個表格連線起來。這裡使用了標準的JOIN（也稱為INNER JOIN），意味著查詢結果只會包含在所有三個表格中fscskey值都匹配的行。 - 使用
WHERE子句篩選出visits大於或等於0的行，以避免人為的負值影響總和。
執行此查詢後，結果如下：
visits_2018 visits_2017 visits_2016
---
-
---
---
-
---
-
---
---
-
---
-
---
-
---
1278148838 1319325387 1355078384
分析訪客資料的變化趨勢
從結果中可以看到，雖然訪客數量呈下降趨勢，但下降幅度並不如單獨查詢每個表格時那麼明顯。這表明，透過合併多個表格的資料，可以獲得更全面的視角。
為了進一步瞭解圖書館使用趨勢，我們還可以對其他包含績效指標的欄位進行類別似的查詢。例如，使用wifisess欄位來檢視無線網路的使用情況。
SELECT
sum(pls18.wifisess) AS wifi_2018,
sum(pls17.wifisess) AS wifi_2017,
sum(pls16.wifisess) AS wifi_2016
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.wifisess >= 0
AND pls17.wifisess >= 0
AND pls16.wifisess >= 0;
內容解密：
- 將
visits替換為wifisess以分析無線網路使用情況。 - 其他部分與前面的查詢相同。
結果顯示，儘管訪客數量下降，但無線網路的使用量卻大幅增加，這為我們瞭解圖書館角色的變化提供了重要的洞察。
按州別分組的訪客資料變化
為了了解不同地區的訪客資料變化趨勢，我們可以修改前面的查詢，以按州別（使用stabr欄位）進行分組，並計算訪客數量的百分比變化。
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
ORDER BY chg_2018_17 DESC;
內容解密：
- 在
SELECT陳述式中加入stabr欄位，並在GROUP BY子句中使用它來按州別分組。 - 使用百分比變化計算來比較不同年份之間的訪客數量變化。
- 使用
ORDER BY子句按chg_2018_17欄位進行降序排序。
執行此查詢後，我們可以看到不同州別的訪客數量變化情況。結果顯示，一些州（如南達科他州和蒙大拿州）的訪客數量在2018年有所增加，而其他州（如夏威夷和美屬薩摩亞）則出現了顯著下降。