2024年04月03日玄貓（BlackCat）

PostgreSQL 溫度資料處理與轉換技巧

本文介紹如何使用 PostgreSQL 處理和轉換溫度資料，包含建立表格、匯入 CSV 資料、使用交叉表進行資料透視、使用 CASE 陳述式和 CTE 進行資料分類別與分析，以及常用的字串處理函式，有效提升資料分析效率。

資料函式庫資料分析

PostgreSQL 資料處理交叉表 CASE陳述式 CTE 字串函式

PostgreSQL 提供豐富的工具和函式，方便資料工程師進行資料處理和轉換。本文示範如何利用 PostgreSQL 內建功能，將原始溫度資料轉換成易於分析的格式。首先，建立資料表並匯入 CSV 格式的溫度資料，接著使用交叉表函式將資料轉換為矩陣形式，方便比較不同測站的溫度變化趨勢。為了更精細地分析溫度資料，我們使用 CASE 陳述式將溫度劃分為不同等級，並結合 CTE 計算各個溫度等級的出現次數，進一步分析不同地區的氣候特徵。此外，文章也介紹了 PostgreSQL 常用的字串處理函式，包含大小寫轉換、計算字串長度、移除或替換特定字元等功能，這些函式在資料清理和轉換過程中扮演重要角色，提升資料分析的效率和準確性。

使用 PostgreSQL 進行資料分析：溫度資料的處理與轉換

在資料分析過程中，我們經常需要對原始資料進行整理和轉換，以便更好地理解和分析資料。本文將介紹如何使用 PostgreSQL 對溫度資料進行處理和轉換，包括建立表格、匯入資料、使用交叉表（Crosstab）進行資料轉換，以及使用 CASE 陳述式對資料進行重新分類別。

建立溫度資料表格

首先，我們需要建立一個表格來儲存溫度資料。根據提供的 CSV 檔案，我們可以建立一個名為 temperature_readings 的表格，包含四個欄位：station_name、observation_date、max_temp 和 min_temp。

CREATE TABLE temperature_readings (
    station_name text,
    observation_date date,
    max_temp integer,
    min_temp integer,
    CONSTRAINT temp_key PRIMARY KEY (station_name, observation_date)
);

內容解密：

CREATE TABLE temperature_readings：建立一個名為 temperature_readings 的表格。
station_name text、observation_date date、max_temp integer、min_temp integer：定義表格的四個欄位，分別儲存測站名稱、觀測日期、最高溫度和最低溫度。
CONSTRAINT temp_key PRIMARY KEY (station_name, observation_date)：設定主鍵為 station_name 和 observation_date 的組合，以確保每筆資料的唯一性。

匯入 CSV 資料

接下來，我們可以使用 COPY 命令將 CSV 檔案中的資料匯入到 temperature_readings 表格中。

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

內容解密：

COPY temperature_readings：指定要匯入資料的表格。
FROM 'C:\YourDirectory\temperature_readings.csv'：指定 CSV 檔案的路徑。
WITH (FORMAT CSV, HEADER)：指定匯入資料的格式為 CSV，並且第一行包含欄位名稱。

使用交叉表進行資料轉換

為了更好地比較不同測站的溫度資料，我們可以使用交叉表（Crosstab）將資料轉換成矩陣形式。

SELECT *
FROM crosstab(
    'SELECT station_name, date_part(''month'', observation_date), percentile_cont(.5) WITHIN GROUP (ORDER BY max_temp)
     FROM temperature_readings
     GROUP BY station_name, date_part(''month'', observation_date)
     ORDER BY station_name',
    'SELECT month FROM generate_series(1,12) month'
)
AS (
    station text,
    jan numeric(3,0), feb numeric(3,0), mar numeric(3,0), apr numeric(3,0),
    may numeric(3,0), jun numeric(3,0), jul numeric(3,0), aug numeric(3,0),
    sep numeric(3,0), oct numeric(3,0), nov numeric(3,0), dec numeric(3,0)
);

內容解密：

SELECT * FROM crosstab()：使用交叉表函式進行資料轉換。
第一個子查詢：計算每個測站每個月的最高溫度中位數。
- date_part(''month'', observation_date)：提取觀測日期的月份。
- percentile_cont(.5) WITHIN GROUP (ORDER BY max_temp)：計算最高溫度的中位數。
第二個子查詢：生成 1 到 12 的月份列表。
AS 子句：定義輸出欄位的名稱和資料型別。

使用 CASE 陳述式重新分類別溫度資料

最後，我們可以使用 CASE 陳述式對溫度資料進行重新分類別，將最高溫度分成不同的類別。

SELECT max_temp,
       CASE
           WHEN max_temp >= 90 THEN 'Hot'
           WHEN max_temp >= 70 AND max_temp < 90 THEN 'Warm'
           WHEN max_temp >= 50 AND max_temp < 70 THEN 'Pleasant'
           WHEN max_temp >= 33 AND max_temp < 50 THEN 'Cold'
           WHEN max_temp >= 20 AND max_temp < 33 THEN 'Frigid'
           WHEN max_temp < 20 THEN 'Inhumane'
           ELSE 'No reading'
       END AS temperature_group
FROM temperature_readings
ORDER BY station_name, observation_date;

內容解密：

CASE 陳述式：根據條件對最高溫度進行分類別。
各個 WHEN 子句：定義不同的溫度範圍及其對應的類別標籤。
ELSE 'No reading'：處理不符合任何條件的資料。
ORDER BY station_name, observation_date：按測站名稱和觀測日期排序輸出結果。

透過上述步驟，我們可以有效地對溫度資料進行處理和轉換，從而更好地理解和分析資料。這些技術不僅適用於溫度資料，還可以廣泛應用於其他型別的資料分析任務中。

在通用表表達式中使用 CASE 陳述式重新分類別溫度資料

在前一節中，我們使用 CASE 陳述式對溫度資料進行了分類別，現在讓我們使用這些分類別來比較三個城市的氣候。

使用 CTE 進行溫度資料分析

列表 13-21 顯示瞭如何使用 CASE 陳述式重新分類別每日最高溫度，並將結果存入一個名為 temps_collapsed 的 CTE 中，然後對該 CTE 進行查詢以分析資料。

WITH temps_collapsed (station_name, max_temperature_group) AS (
  SELECT station_name,
    CASE 
      WHEN max_temp >= 90 THEN 'Hot'
      WHEN max_temp >= 70 AND max_temp < 90 THEN 'Warm'
      WHEN max_temp >= 50 AND max_temp < 70 THEN 'Pleasant'
      WHEN max_temp >= 33 AND max_temp < 50 THEN 'Cold'
      WHEN max_temp >= 20 AND max_temp < 33 THEN 'Frigid'
      WHEN max_temp < 20 THEN 'Inhumane'
      ELSE 'No reading'
    END
  FROM temperature_readings
)
SELECT station_name, max_temperature_group, count(*)
FROM temps_collapsed
GROUP BY station_name, max_temperature_group
ORDER BY station_name, count(*) DESC;

內容解密：

CTE 定義：使用 WITH 陳述式定義了一個名為 temps_collapsed 的 CTE，包含兩個欄位：station_name 和 max_temperature_group。
CASE 陳述式：根據 max_temp 的值，將溫度分為不同的類別，如 ‘Hot’、‘Warm’、‘Pleasant’ 等。
查詢 CTE：對 temps_collapsed CTE 進行查詢，計算每個城市在每個溫度類別中的天數，並按城市和計數結果降序排序。

查詢結果如下：

| station_name | max_temperature_group | count | |

–|

|

| | CHICAGO NORTHERLY ISLAND IL US | Warm | 133 | | CHICAGO NORTHERLY ISLAND IL US | Cold | 92 | | CHICAGO NORTHERLY ISLAND IL US | Pleasant | 91 | | CHICAGO NORTHERLY ISLAND IL US | Frigid | 30 | | CHICAGO NORTHERLY ISLAND IL US | Inhumane | 8 | | CHICAGO NORTHERLY ISLAND IL US | Hot | 8 | | SEATTLE BOEING FIELD WA US | Pleasant | 198 | | SEATTLE BOEING FIELD WA US | Warm | 98 | | SEATTLE BOEING FIELD WA US | Cold | 50 | | SEATTLE BOEING FIELD WA US | Hot | 3 | | WAIKIKI 717.2 HI US | Warm | 361 | | WAIKIKI 717.2 HI US | Hot | 5 |

分析結果

從結果中可以看出，Waikiki 的氣候非常穩定，361 天的最高溫度為 ‘Warm’，使其成為一個受歡迎的旅遊勝地。Seattle 的氣候也相對宜人，有近 300 天的最高溫度為 ‘Pleasant’ 或 ‘Warm’。Chicago 的氣候則較為極端，有 30 天的最高溫度為 ‘Frigid’，8 天為 ‘Inhumane’。

練習

修改列表 13-21 中的程式碼，以深入分析 Waikiki 的最高溫度。將 temps_collapsed 表限制為 Waikiki 的每日最高溫度觀測值，然後使用 CASE 陳述式將溫度重新分類別為七個組。
修改列表 13-17 中的冰淇淋調查交叉表，將表格翻轉，即將 flavor 作為行，將 office 作為列。哪些查詢元素需要更改？計數結果是否不同？

字串處理函式：文字操作的強大工具

在資料處理和分析中，字串操作是一項基本且重要的技能。PostgreSQL 提供了一系列強大的函式來處理字串資料，從簡單的大小寫轉換到複雜的正規表示式匹配，這些函式讓我們能夠高效地清理、轉換和分析文字資料。

大小寫轉換

在處理文字資料時，大小寫的一致性是非常重要的。PostgreSQL 提供了多個函式來處理大小寫轉換：

lower(string) 函式將字串中的所有字母轉換為小寫，而非字母字元保持不變。例如，lower('Randy') 傳回 randy。
initcap(string) 函式將字串中每個單詞的第一個字母大寫，其餘字母小寫。例如，initcap('at the end of the day') 傳回 At The End Of The Day。這個函式對於格式化書名或電影標題很有用，但需要注意它不認識縮寫詞，如 initcap('Practical SQL') 傳回 Practical Sql。
upper(string) 函式與 lower(string) 相反，將字串中的所有字母轉換為大寫。

其中，upper() 和 lower() 是 ANSI SQL 標準命令，而 initcap() 是 PostgreSQL 特有的。

字元資訊

除了大小寫轉換外，瞭解字串的特性也是非常重要的。有幾個函式可以用來取得字串的資訊：

char_length(string) 函式傳回字串中的字元數，包括空格。例如，char_length(' Pat ') 傳回 5，因為 ‘Pat’ 三個字母加上前後的空格總共有五個字元。
position(substring in string) 函式傳回子字串在主字串中的位置。例如，position(', ' in 'Tan, Bella') 傳回 4，因為子字串 ‘, ’ 位於主字串 ‘Tan, Bella’ 的第四個索引位置。

移除字元

有時候，我們需要移除字串中的某些字元。PostgreSQL 提供了多種方法來實作這一點：

trim(characters from string) 函式可以移除字串開頭和結尾指定的字元。例如，trim('s' from 'socks') 傳回 ock。如果不指定字元，則預設移除空格。
ltrim(string, characters) 和 rtrim(string, characters) 是 PostgreSQL 特有的函式，分別用於移除字串左側和右側的指定字元。

程式碼範例

SELECT 
    char_length(trim(' Pat ')),  -- 傳回 3
    trim('s' from 'socks'),      -- 傳回 'ock'
    ltrim('socks', 's'),         -- 傳回 'ocks'
    rtrim('socks', 's');         -- 傳回 'sock'

內容解密：

char_length(trim(' Pat '))：首先使用 trim(' Pat ') 移除 ’ Pat ’ 前後的空格，得到 ‘Pat’，然後使用 char_length() 取得 ‘Pat’ 的長度，即 3。
trim('s' from 'socks')：移除 ‘socks’ 開頭和結尾的 ’s’，得到 ‘ock’。
ltrim('socks', 's')：移除 ‘socks’ 左側的 ’s’，得到 ‘ocks’。
rtrim('socks', 's')：移除 ‘socks’ 右側的 ’s’，得到 ‘sock’。

提取和替換字元

除了移除字元外，我們還可以提取或替換字串中的特定部分：

left(string, number) 和 right(string, number) 函式分別用於從字串的左側或右側提取指定數量的字元。例如，left('703-555-1212', 3) 傳回 703，而 right('703-555-1212', 8) 傳回 555-1212。
replace(string, from, to) 函式用於替換字串中的指定字元或子字串。例如，replace('bat', 'b', 'c') 傳回 cat。

程式碼範例

SELECT 
    left('703-555-1212', 3),   -- 傳回 '703'
    right('703-555-1212', 8),  -- 傳回 '555-1212'
    replace('bat', 'b', 'c');  -- 傳回 'cat'

內容解密：

left('703-555-1212', 3)：從左側開始提取 ‘703-555-1212’ 的前 3 個字元，即 ‘703’。
right('703-555-1212', 8)：從右側開始提取 ‘703-555-1212’ 的前 8 個字元，即 ‘555-1212’。
replace('bat', 'b', 'c')：將 ‘bat’ 中的 ‘b’ 替換為 ‘c’，得到 ‘cat’。

使用正規表示式匹配文字模式

正規表示式（regex）是一種強大的文字模式匹配工具，可以用來過濾或提取具有特定模式的文字資料。PostgreSQL 支援正規表示式，使得處理複雜文字資料變得更加靈活高效。

程式碼範例

SELECT * FROM table_name WHERE column_name ~ '^[0-9]{3}-[0-9]{3}-[0-9]{4}$';

內容解密：

此查詢使用正規表示式來匹配符合電話號碼格式（XXX-XXX-XXXX）的資料。其中：

^ 表示字串的開始。
[0-9]{3} 表示恰好三個數字。
- 表示連字元。
$ 表示字串的結束。

透過掌握這些字串處理函式和正規表示式的使用，我們可以更加高效地處理和分析文字資料，為資料分析和挖掘提供有力的支援。