PostgreSQL 時區感知時間戳記火車行程分析

– 10 分鐘閱讀

在應用系統中，處理跨時區的時間資料是常見的需求，尤其在交通運輸領域。本文以模擬的火車行程資料函式庫為例，示範如何利用 PostgreSQL 的時區感知時間戳記型別 (timestamptz) 進行精確的時間計算和分析。文章首先介紹如何建立包含出發和抵達時間的火車行程資料表，並利用 timestamptz 確保時區資訊的完整性。接著，示範如何計算每段行程的持續時間，以及如何使用視窗函式計算累計行程時間。為了提升時間間隔的可讀性，文章也介紹了 justify_interval() 函式的用法，將時間間隔轉換為更易理解的格式。除了基本的時間計算，文章還探討了子查詢在資料分析中的應用，包含在 WHERE 子句中使用子查詢進行資料篩選、在 DELETE 陳述式中使用子查詢刪除特定資料、以及如何利用子查詢建立和連線派生表，以進行更複雜的資料分析。

利用 PostgreSQL 管理時區感知時間戳記進行火車行程分析

在處理跨時區的時間資料時，正確使用時區感知時間戳記（timestamptz）是至關重要的。本篇文章將透過一個模擬的火車行程資料函式庫案例，展示如何使用 PostgreSQL 進行時區轉換和時間間隔計算。

建立火車行程資料表

首先，我們建立一個名為 train_rides 的資料表來儲存火車行程的相關資訊。特別的是，departure 和 arrival 欄位被定義為 timestamptz 型別，以便能夠正確處理不同時區的時間資料。

CREATE TABLE train_rides (
    trip_id SERIAL PRIMARY KEY,
    segment VARCHAR(50),
    departure TIMESTAMPTZ,
    arrival TIMESTAMPTZ
);

INSERT INTO train_rides (segment, departure, arrival)
VALUES
('Chicago to New York', '2020-11-13 21:30 CST', '2020-11-14 18:23 EST'),
('New York to New Orleans', '2020-11-15 14:15 EST', '2020-11-16 19:32 CST'),
('New Orleans to Los Angeles', '2020-11-17 13:45 CST', '2020-11-18 9:00 PST'),
('Los Angeles to San Francisco', '2020-11-19 10:10 PST', '2020-11-19 21:24 PST'),
('San Francisco to Denver', '2020-11-20 9:10 PST', '2020-11-21 18:38 MST'),
('Denver to Chicago', '2020-11-22 19:10 MST', '2020-11-23 14:50 CST');

SET TIME ZONE 'US/Central';

SELECT * FROM train_rides;

內容解密：

  1. CREATE TABLE 陳述式：定義了 train_rides 資料表結構，其中 departure 和 arrival 欄位使用 TIMESTAMPTZ 型別，以儲存帶有時區資訊的時間戳記。
  2. INSERT INTO 陳述式：插入了六段火車行程的資料。每段行程的出發和抵達時間都帶有相應的時區資訊，例如 CST（美國中部標準時間）、EST（美國東部標準時間）等。
  3. SET TIME ZONE 'US/Central'; 陳述式：將當前資料函式庫連線的時區設定為美國中部時間（Central Time），以便統一檢視時間資料。
  4. SELECT * FROM train_rides; 陳述式：查詢 train_rides 資料表中的所有資料。由於之前設定了時區，因此查詢結果中的所有時間資料都會轉換為美國中部時間。

計算每段行程的持續時間

接下來，我們需要計算每段火車行程的持續時間。這可以透過對 arrival 和 departure 時間戳記進行減法運算來實作。

SELECT 
    segment,
    to_char(departure, 'YYYY-MM-DD HH12:MI a.m. TZ') AS departure,
    arrival - departure AS segment_duration
FROM train_rides;

內容解密：

  1. to_char(departure, 'YYYY-MM-DD HH12:MI a.m. TZ')：將 departure 時間戳記格式化為易讀的字串格式，包括日期、12 小時制的時間、上下午標示以及時區。
  2. arrival - departure：計算每段行程的持續時間。結果是一個 interval 型別的值，表示從出發到抵達的時間間隔。
  3. 當持續時間少於 24 小時時，PostgreSQL 以 HH:MM:SS 的格式顯示結果；當持續時間超過 24 小時時，則以 X day HH:MM:SS 的格式顯示。

累計行程時間的計算

要計算整個火車行程的累計時間，我們可以使用視窗函式（Window Function）來對每段行程的持續時間進行累加。

SELECT 
    segment,
    arrival - departure AS segment_duration,
    sum(arrival - departure) OVER (ORDER BY trip_id) AS cume_duration
FROM train_rides;

內容解密：

  1. sum(arrival - departure) OVER (ORDER BY trip_id)：使用視窗函式對每段行程的持續時間進行累加，按照 trip_id 的順序進行排序。
  2. 累計結果以 interval 型別表示，但當總時間超過一天時，PostgreSQL 的顯示方式可能會比較難讀，例如 2 days 85:47:00。

進階查詢技巧：子查詢的應用

在進行資料分析時，我們常常需要使用一些進階的 SQL 技巧，這些技巧能夠幫助我們回答更複雜的問題。在本章中，我們將介紹一些進階查詢技巧，包括使用子查詢和將數值重新分類別後再進行計數。

使用子查詢

子查詢是一種巢狀在另一個查詢中的查詢，通常用於執行計算、邏輯測試或生成要傳遞給主查詢的行。子查詢是標準 ANSI SQL 的一部分，語法並不特殊，只需將查詢用括號括起來即可。

在 WHERE 子句中使用子查詢進行篩選

WHERE 子句允許根據提供的條件篩選查詢結果，例如 WHERE quantity > 1000。但是，這需要事先知道用於比較的值。如果不知道怎麼辦？這時，子查詢就派上用場了：它允許編寫一個查詢來生成一個或多個值，用於 WHERE 子句中的表示式。

生成查詢表示式的值

假設您想編寫一個查詢，顯示哪些美國縣份的人口達到或超過第 90 個百分位（即前 10%）。您可以使用子查詢作為 WHERE 子句的一部分，一次完成這兩個任務，如清單 13-1 所示。

SELECT county_name,
       state_name,
       pop_est_2019
FROM us_counties_pop_est_2019
WHERE pop_est_2019 >= (
    SELECT percentile_cont(.9) WITHIN GROUP (ORDER BY pop_est_2019)
    FROM us_counties_pop_est_2019
)
ORDER BY pop_est_2019 DESC;

內容解密：

  1. 主查詢從 us_counties_pop_est_2019 表中選擇 county_name、state_name 和 pop_est_2019。
  2. 子查詢計算 pop_est_2019 的第 90 個百分位數，用於篩選主查詢的結果。
  3. percentile_cont(.9) 函式計算連續的第 90 個百分位數。
  4. WITHIN GROUP (ORDER BY pop_est_2019) 指定計算百分位數的順序。
  5. 主查詢篩選出 pop_est_2019 大於或等於第 90 個百分位數的縣份，並按 pop_est_2019 降序排列結果。

日期與時間的處理

在 SQL 資料函式庫中處理日期和時間為您的分析增加了一個有趣的維度，讓您能夠回答有關事件發生的時間以及資料中的其他時間相關問題。透過對日期和時間格式、時區以及分解時間戳元件的函式有深入的瞭解，您幾乎可以分析任何您遇到的資料集。

使用 justify_interval() 函式改善累積行程時間的格式

在計算累積行程時間時，我們可以使用 justify_interval() 函式來標準化間隔計算的輸出，使其更容易理解。

SELECT segment,
       arrival - departure AS segment_duration,
       justify_interval(sum(arrival - departure) OVER (ORDER BY trip_id)) AS cume_duration
FROM train_rides;

內容解密：

  1. justify_interval() 函式將間隔計算的結果標準化，使其更容易閱讀。
  2. 該函式將 24 小時滾動到天，將 30 天滾動到月，從而使輸出更加直觀。
  3. 例如，原本的累積時間可能是 2 天 85:47:00，使用 justify_interval() 後變為 5 天 13:47:00。

練習題

  1. 使用紐約市計程車資料，計算每次行程的時間長度，並按行程時間從長到短排序。你是否注意到最長或最短的行程有什麼特別之處，可能需要向市政府官員詢問？
  2. 使用 AT TIME ZONE 關鍵字，編寫一個查詢，顯示當紐約市迎來 2100 年 1 月 1 日時，倫敦、約翰內斯堡、莫斯科和墨爾本的日期和時間。使用清單 12-5 中的程式碼查詢時區名稱。
  3. 作為額外的挑戰，使用第 11 章中的統計函式，計算紐約市計程車資料中行程時間與總金額之間的相關係數和 R 平方值。對行程距離與總金額做同樣的計算。將查詢限制在行程時間不超過三小時的行程。

在WHERE子句中使用子查詢

WHERE子句1用於過濾總人口欄位（pop_est_2019），但它並不像平常那樣包含一個特定的值。相反，在>=比較運算子之後，我們提供了一個用括號括起來的子查詢。這個子查詢使用percentile_cont()函式來生成一個值：pop_est_2019欄位中第90百分位數的截止點。

注意：使用percentile_cont()進行過濾的子查詢只有在傳入單一輸入時才有效，如所示。如果傳入一個陣列，如第6章第90頁的清單6-12所示，percentile_cont()會傳回一個陣列，而查詢將無法對陣列型別進行>=的評估。

這是一個不相關子查詢的例子。它不依賴於外部查詢中的任何值，並且只會執行一次以生成所需的值。如果您只執行子查詢部分，透過在pgAdmin中突出顯示它，它將執行，並且您應該看到結果為213707.3。但是，當您執行清單13-1中的整個查詢時，您不會看到這個數字，因為子查詢的結果直接傳遞給外部查詢的WHERE子句。

整個查詢應該傳回315行，或us_counties_pop_est_2019表中3142行的約10%。 county_name state_name pop_est_2019

Los Angeles County California 10039107 Cook County Illinois 5150233 Harris County Texas 4713325 Maricopa County Arizona 4485414 San Diego County California 3338330 –snip– Cabarrus County North Carolina 216453 Yuma County Arizona 213787

結果包括所有人口大於或等於213707.3的縣，這是子查詢生成的值。

使用子查詢識別要刪除的行

我們可以在DELETE陳述式中使用相同的子查詢來指定要從表中刪除的內容。在清單13-2中，我們使用第10章學到的方法複製了census表，然後從該備份中刪除了除人口最多的前10%的315個縣以外的所有內容。

CREATE TABLE us_counties_2019_top10 AS
SELECT * FROM us_counties_pop_est_2019;

DELETE FROM us_counties_2019_top10
WHERE pop_est_2019 < (
    SELECT percentile_cont(.9) WITHIN GROUP (ORDER BY pop_est_2019)
    FROM us_counties_2019_top10
);

清單13-2：在WHERE子句中使用子查詢與DELETE

執行清單13-2中的程式碼，然後執行SELECT count(*) FROM us_counties_2019_top10;來計算剩餘的行數。結果應該是315行，即原始的3142行減去低於子查詢識別的值的2827行。

使用子查詢建立派生表

如果您的子查詢傳回行和列，您可以將其放在FROM子句中，以建立一個新的表，稱為派生表，您可以像查詢或連線其他表一樣對其進行查詢。這是不相關子查詢的另一個例子。

讓我們看一個簡單的例子。在第6章中，您瞭解了平均值和中位數之間的區別。中位數通常更好地指示資料集的中心值，因為幾個非常大或小的異常值可能會扭曲平均值。因此，我經常比較兩者。如果它們接近，則資料更可能落在正態分佈（熟悉的鐘形曲線）中，並且平均值是中心值的良好表示。如果平均值和中位數相差甚遠，則可能有一些異常值正在產生影響，或者分佈是偏態的，而不是正態的。

查詢美國縣的人口平均值和中位數，以及它們之間的差異，是一個兩步過程。我們需要計算平均值和中位數，然後相減。我們可以使用FROM子句中的子查詢一步完成這兩個操作，如清單13-3所示。

SELECT 
    round(calcs.average, 0) AS average,
    calcs.median,
    round(calcs.average - calcs.median, 0) AS median_average_diff
FROM (
    SELECT 
        avg(pop_est_2019) AS average,
        percentile_cont(.5) WITHIN GROUP (ORDER BY pop_est_2019)::numeric AS median
    FROM us_counties_pop_est_2019
) AS calcs;

清單13-3：在FROM子句中使用子查詢作為派生表

生成派生表的子查詢1很簡單。我們使用avg()和percentile_cont()函式來查詢census表的pop_est_2019欄位的平均值和中位數，並為每個欄位命名別名。然後，我們將派生表命名為calcs2，以便在主查詢中參照它。

在主查詢中，我們從平均值中減去中位數，兩者都由子查詢傳回。結果四捨五入並標記為median_average_diff別名。執行查詢，結果應該是： average median median_average_diff

104468 25726 78742

中位數和平均值之間的差異78742幾乎是中位數的三倍。這表明我們有一些高人口縣拉高了平均值。

連線派生表

連線多個派生表使您能夠在主查詢中進行最終計算之前執行多個預處理步驟。例如，在第11章中，我們計算了每個縣的旅遊相關企業佔人口的比率。假設我們想在州級別進行相同的計算。在計算該比率之前，我們需要知道每個州的旅遊企業數量和每個州的人口。清單13-4顯示瞭如何為這兩個任務編寫子查詢並連線它們以計算整體比率。

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

清單13-4：連線兩個派生表

您在第11章中學習瞭如何計算比率，因此用於查詢estabs_per_thousand1的外部查詢中的數學和語法應該很熟悉。我們將企業數量除以人口，然後乘以1000以獲得每千人企業數量。#### 內容解密： 此段落主要闡述了使用SQL子查詢來進行資料分析的方法。首先，我們看到了如何在WHERE子句中使用子查詢來過濾資料，特別是透過使用percentile_cont()函式來找出第90百分位的資料，並將其作為篩選條件。這種方法可以有效地找出資料集中位於前10%的記錄。

接著，文章展示瞭如何利用相同的技術在DELETE陳述式中刪除不需要的資料，從而保留最重要的記錄。這種技術對於資料清理和保留關鍵資訊非常有用。

然後，文章介紹瞭如何使用子查詢建立派生表。這種技術允許我們對資料進行更複雜的操作，例如計算平均值和中位數，並比較它們之間的差異。這對於理解資料的分佈特徵非常重要。

最後，文章展示瞭如何連線多個派生表來進行更複雜的資料分析。這種方法允許我們整合來自不同資料來源的資訊，並進行綜合分析。

關鍵概念：

  1. 在WHERE子句中使用子查詢：利用子查詢動態生成篩選條件，例如找出前10%的人口大縣。
  2. 使用子查詢進行資料刪除：透過DELETE陳述式結合子查詢，刪除不重要的資料，保留關鍵記錄。
  3. 建立派生表：利用子查詢生成臨時表，用於進一步的資料分析和操作。
  4. 連線派生表：透過JOIN操作連線多個派生表，以整合不同資料來源的資訊。

技術要點：

  1. percentile_cont()函式：用於計算連續百分位數，在資料分析中非常有用。
  2. AVG()和percentile_cont()：分別用於計算平均值和中位數，有助於理解資料的分佈特性。
  3. JOIN操作：用於合併來自不同表的資料，是進行綜合資料分析的重要手段。
  4. 資料清理和預處理：透過DELETE陳述式和子查詢結合，可以有效地清理和預處理資料。

內容作用與邏輯：

本文透過逐步講解SQL中的子查詢技術，從基本的WHERE子句使用到建立派生表及連線多個派生表，不僅展示了SQL語言的強大功能，也提供了實際操作的，有助於讀者深入理解並應用這些技術進行複雜的資料分析工作。

玄貓 BlackCat

玄貓 BlackCat

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