PostgreSQL正規表示式與全文檢索應用

– 8 分鐘閱讀

PostgreSQL 提供強大的正規表示式和全文檢索功能，能有效處理和分析大量文字資料。正規表示式允許精確提取所需資訊，例如日期、時間、案件編號等，並將其更新到資料函式庫的對應欄位，實作資料結構化。全文檢索功能則能快速搜尋大量文字，支援多種語言和複雜查詢，有效提升資料分析效率。本文提供的程式碼範例涵蓋了日期時間處理、地址資訊提取、特殊情況處理等常見應用場景，並探討了全文檢索的設定、索引建立和實際應用，包括多關鍵字搜尋、相鄰詞搜尋和搜尋結果位置顯示等進階用法，適合資料函式庫開發和資料分析人員參考。

資料函式庫更新與正規表示式應用

在處理犯罪報告資料時，我們經常需要將非結構化的文字資料轉換成結構化的表格資料，以便進行進一步的分析。在本篇文章中，我們將介紹如何使用 PostgreSQL 的正規表示式函式 regexp_match() 來提取文字資料中的特定資訊，並將其更新到資料函式庫表格中。

從文字資料中提取資訊

首先，我們需要了解 regexp_match() 函式的用法。該函式可以根據指定的正規表示式模式，從文字資料中提取匹配的內容。傳回的結果是一個陣列型別，包含了所有匹配的元素。

SELECT 
    crime_id,
    (regexp_match(original_text, '(?:C0|SO)[0-9]+'))[1] AS case_number
FROM crime_reports
ORDER BY crime_id;

內容解密：

  1. regexp_match(original_text, '(?:C0|SO)[0-9]+')：使用正規表示式模式 (?:C0|SO)[0-9]+ 來匹配以 C0 或 SO 開頭，後面跟著數字字元的內容。
  2. (regexp_match(...))[1]：提取陣列中的第一個元素，即匹配的案件編號。
  3. AS case_number：將提取的結果命名為 case_number。

更新資料函式庫表格

提取出所需的資訊後，我們需要將其更新到資料函式庫表格中。首先，我們將提取的日期和時間組合成一個單一的時間戳值。

UPDATE crime_reports
SET date_1 = (
    (regexp_match(original_text, '\d{1,2}\/\d{1,2}\/\d{2}'))[1]
    || ' ' ||
    (regexp_match(original_text, '\/\d{2}\n(\d{4})'))[1]
    || ' US/Eastern'
)::timestamptz
RETURNING crime_id, date_1, original_text;

內容解密：

  1. (regexp_match(original_text, '\d{1,2}\/\d{1,2}\/\d{2}'))[1]：提取日期部分。
  2. (regexp_match(original_text, '\/\d{2}\n(\d{4})'))[1]：提取時間部分。
  3. || ' ' ||：將日期和時間部分用空格連線起來。
  4. || ' US/Eastern'：新增時區資訊。
  5. ::timestamptz：將組成的字串轉換為帶時區的時間戳型別。

處理特殊情況

在更新資料函式庫表格時，我們可能會遇到一些特殊情況，例如某些欄位的值不存在或不一致。這時，我們可以使用 CASE 陳述式來處理這些特殊情況。

UPDATE crime_reports
SET 
    date_11 = (
        (regexp_match(original_text, '\d{1,2}\/\d{1,2}\/\d{2}'))[1]
        || ' ' ||
        (regexp_match(original_text, '\/\d{2}\n(\d{4})'))[1]
        || ' US/Eastern'
    )::timestamptz,
    date_22 = 
    CASE
        WHEN (SELECT regexp_match(original_text, '-(\d{1,2}\/\d{1,2}\/\d{2})') IS NULL)
        AND (SELECT regexp_match(original_text, '\/\d{2}\n\d{4}-(\d{4})') IS NOT NULL)
        THEN (
            (regexp_match(original_text, '\d{1,2}\/\d{1,2}\/\d{2}'))[1]
            || ' ' ||
            (regexp_match(original_text, '\/\d{2}\n\d{4}-(\d{4})'))[1]
            || ' US/Eastern'
        )::timestamptz
        ELSE NULL
    END
RETURNING crime_id, date_11, date_22;

內容解密：

  1. CASE 陳述式用於檢查某些條件是否滿足，如果滿足則執行特定的操作。
  2. WHEN 子句檢查兩個條件：第一個正規表示式匹配是否為空，第二個正規表示式匹配是否非空。
  3. 如果條件滿足，則提取並組合成新的時間戳值，否則傳回 NULL。

資料解析與轉換：犯罪報告處理例項

在資料分析過程中，原始文字資料的解析與結構化是至關重要的步驟。本文將以犯罪報告資料集為例，展示如何利用正規表示式和SQL查詢來更新資料表欄位，並最終實作資料的結構化與可用性提升。

利用正規表示式更新犯罪報告資料表

首先，我們面對的是包含豐富但非結構化的文字資料的crime_reports資料表。我們的目標是從original_text欄位中提取關鍵資訊，如日期、地點、犯罪型別等，並將其填充到相應的欄位中。

SQL查詢解析

UPDATE crime_reports
SET 
    date_1 = (regexp_match(original_text, '\d{1,2}\/\d{1,2}\/\d{4} \d{1,2}:\d{2}'))[1]::timestamptz,
    date_2 = CASE
        WHEN (SELECT regexp_match(original_text, '-\d{1,2}\/\d{1,2}\/\d{2}') IS NULL)
             AND (SELECT regexp_match(original_text, '\d{2}\n\d{4}-(\d{4})') IS NOT NULL)
        THEN ((regexp_match(original_text, '\d{1,2}\/\d{1,2}\/\d{2}'))[1]
              || ' ' ||
              (regexp_match(original_text, '\/\d{2}\n\d{4}-(\d{4})'))[1]
              ||' US/Eastern')::timestamptz
        WHEN (SELECT regexp_match(original_text, '-\d{1,2}\/\d{1,2}\/\d{2}') IS NOT NULL)
             AND (SELECT regexp_match(original_text, '\/\d{2}\n\d{4}-(\d{4})') IS NOT NULL)
        THEN ((regexp_match(original_text, '-(\d{1,2}\/\d{1,2}\/\d{1,2})'))[1]
              || ' ' ||
              (regexp_match(original_text, '\/\d{2}\n\d{4}-(\d{4})'))[1]
              ||' US/Eastern')::timestamptz
    END,
    street = (regexp_match(original_text, 'hrs.\n(\d+ .+(?:Sq.|Plz.|Dr.|Ter.|Rd.))'))[1],
    city = (regexp_match(original_text, '(?:Sq.|Plz.|Dr.|Ter.|Rd.)\n(\w+ \w+|\w+)\n'))[1],
    crime_type = (regexp_match(original_text, '\n(?:\w+ \w+|\w+)\n(.*):'))[1],
    description = (regexp_match(original_text, ':\s(.+)(?:C0|SO)'))[1],
    case_number = (regexp_match(original_text, '(?:C0|SO)[0-9]+'))[1];

內容解密：

  1. 日期處理：使用regexp_match函式從original_text中提取日期和時間資訊，並將其轉換為timestamp with time zone格式。CASE陳述式用於處理第二日期存在與否的不同情況。

    • 第一個WHEN條件檢查是否只有第二時間而無第二日期，若成立，則將第一日期與第二時間結合。
    • 第二個WHEN條件檢查是否同時存在第二日期和第二時間，若成立，則將第二日期與第二時間結合。

  2. 地址、地點、犯罪型別等資訊提取：利用正規表示式匹配相應的模式，從文字中提取街道地址、城市名稱、犯罪型別等。

  3. 描述和案件編號：同樣透過正規表示式提取案件描述和編號。

資料查詢與呈現

更新完成後，我們可以透過簡單的SQL查詢來檢視結構化後的資料。

SELECT date_1, street, city, crime_type
FROM crime_reports
ORDER BY crime_id;

內容解密：

  • 查詢結果將呈現結構化的犯罪報告資料，包括日期、街道、城市和犯罪型別等。
  • 資料現在以清晰、結構化的形式呈現，便於進一步的分析和挖掘。

資料轉換的價值

將原始文字轉換為結構化資料具有重要的價值。它不僅能夠提高資料的可讀性和可用性，還能夠支援後續的資料分析和挖掘工作。此外，自行構建和整理的資料集對於特定的研究和分析需求具有獨特的優勢。

PostgreSQL全文檢索功能簡介

PostgreSQL提供了強大的全文檢索功能，能夠對大量文字資料進行高效搜尋。本文將簡要介紹如何設定和使用PostgreSQL的全文檢索功能。

文字搜尋資料型別

PostgreSQL支援兩種特殊的資料型別用於全文檢索：tsvector和tsquery。

  • tsvector代表待搜尋的文字，並以規範化的形式儲存。
  • tsquery代表搜尋查詢詞和運算子。

使用tsvector儲存文字

tsvector資料型別將文字簡化為一組排序的詞根（lexemes），並記錄詞彙在原始文字中的位置，同時移除無關緊要的停用詞（如“the”、“it”等）。

SELECT to_tsvector('I am washing my clothes');

內容解密：

  • to_tsvector函式將輸入的文字轉換為tsvector格式，規範化文字內容以便於搜尋。

全文檢索技術在PostgreSQL中的應用

全文檢索是資料函式庫查詢中一個非常有用的功能，特別是在處理大量文字資料時。PostgreSQL提供了強大的全文檢索功能，支援多種語言和複雜的查詢操作。

將文字轉換為tsvector資料型別

在進行全文檢索之前，需要將文字資料轉換為tsvector資料型別。tsvector是一種特殊的資料型別，用於儲存經過最佳化的文字資料，以支援高效的全文檢索。

SELECT to_tsvector('english', 'I am walking across the sitting room to sit with you.');

執行上述查詢後，將傳回以下結果：

'across':4 'room':7 'sit':6,9 'walk':3

to_tsvector()函式將原始文字中的單詞進行了簡化和轉換，去除了無意義的停用詞（如“I”、“am”、“the”等），並將單詞還原為其基本形式（例如“walking”變為“walk”，“sitting”變為“sit”）。數字代表單詞在原始文字中的位置。

建立搜尋條件使用tsquery

tsquery是另一種特殊的資料型別，用於表示全文檢索的查詢條件。它支援多種運算子，如&（AND）、|（OR）和!（NOT），以及用於搜尋相鄰單詞的<->運算子。

SELECT to_tsquery('english', 'walking & sitting');

執行後，結果為：

'walk' & 'sit'

這表示查詢條件為同時包含“walk”和“sit”兩個詞的文字。

使用@@匹配運算子進行搜尋

當文字和查詢條件都轉換為相應的資料型別後，可以使用@@匹配運算子來檢查查詢條件是否匹配文字。

SELECT to_tsvector('english', 'I am walking across the sitting room') @@ to_tsquery('english', 'walking & sitting');
SELECT to_tsvector('english', 'I am walking across the sitting room') @@ to_tsquery('english', 'walking & running');

第一個查詢傳回true，因為文字中同時包含“walking”和“sitting”的詞幹。第二個查詢傳回false，因為文字中不包含“running”。

建立全文檢索表

為了示範全文檢索的應用，建立一個名為president_speeches的表，用於儲存美國總統演講的文字資料。

CREATE TABLE president_speeches (
    president text NOT NULL,
    title text NOT NULL,
    speech_date date NOT NULL,
    speech_text text NOT NULL,
    search_speech_text tsvector,
    CONSTRAINT speech_key PRIMARY KEY (president, speech_date)
);

COPY president_speeches (president, title, speech_date, speech_text)
FROM 'C:\YourDirectory\president_speeches.csv'
WITH (FORMAT CSV, DELIMITER '|', HEADER OFF, QUOTE '@');

將文字轉換為tsvector並建立索引

將s contentionText欄位中的文字資料轉換為tsvector型別，並儲存在search_speech_text欄位中。

UPDATE president_speeches
SET search_speech_text = to_tsvector('english', speech_text);

為了加速搜尋，建立一個GIN索引在search_speech_text欄位上。

CREATE INDEX search_idx ON president_speeches USING gin(search_speech_text);

內容解密：

  1. 更新陳述式的作用：將s contentionText欄位中的原始文字轉換為tsvector型別，以便進行高效的全文檢索。
  2. 語言引數的重要性：選擇正確的語言引數（如“english”）對於正確處理停用詞和詞幹提取至關重要。
  3. GIN索引的作用：GIN索引能夠顯著提高全文檢索的效率，特別是在大型資料集上。

搜尋演講文字

現在，可以使用全文檢索功能來查詢包含特定關鍵字的總統演講。

SELECT president, speech_date
FROM president_speeches
WHERE search_speech_text @@ to_tsquery('english', 'Vietnam')
ORDER BY speech_date;

這個查詢傳回所有提到“Vietnam”的總統演講，按演講日期排序。

內容解密：

  1. 查詢邏輯：使用@@運算子檢查s contentionText欄位是否匹配查詢條件“Vietnam”。
  2. 結果排序：按演講日期對結果進行排序，以便按時間順序檢視演講。
  3. 全文檢索的優勢：能夠快速、準確地找到包含特定內容的文字資料，是分析和研究文字資料的重要工具。

全文檢索與搜尋功能實作

在前面的章節中，我們已經瞭解如何使用 PostgreSQL 的全文檢索功能來搜尋資料函式庫中的文字資料。現在，我們將進一步探討如何使用 ts_headline() 函式來顯示搜尋結果的位置，以及如何使用多個搜尋詞和相鄰詞搜尋。

顯示搜尋結果位置

要顯示搜尋詞在文字中的位置，我們可以使用 ts_headline() 函式。該函式會突出顯示搜尋詞，並顯示其周圍的文字。以下是一個例子：

SELECT president,
       speech_date,
       ts_headline(speech_text, to_tsquery('english', 'tax'),
                   'StartSel = <, StopSel = >, MinWords=5, MaxWords=7, MaxFragments=1')
FROM president_speeches
WHERE search_speech_text @@ to_tsquery('english', 'tax')
ORDER BY speech_date;

內容解密：

  1. ts_headline() 函式的第一個引數是原始的 speech_text 列，而不是用於搜尋的 tsvector 列。
  2. 第二個引數是 to_tsquery() 函式，用於指定要搜尋的詞。
  3. 第三個引數是可選的格式化引數，用於設定突出顯示的起始和結束字元、顯示的最小和最大字數等。

使用多個搜尋詞

我們可以使用 & 和 ! 運算子來組合多個搜尋詞。例如，以下查詢會找出包含 “transportation” 但不包含 “roads” 的演講：

SELECT president,
       speech_date,
       ts_headline(speech_text, to_tsquery('english', 'transportation & !roads'),
                   'StartSel = <, StopSel = >, MinWords=5, MaxWords=7, MaxFragments=1')
FROM president_speeches
WHERE search_speech_text @@ to_tsquery('english', 'transportation & !roads')
ORDER BY speech_date;

內容解密：

  1. 在 to_tsquery() 函式中，我們使用 & 運算子來組合多個搜尋詞。
  2. 使用 ! 運算子來排除特定的詞。

相鄰詞搜尋

我們可以使用 <-> 運算子來搜尋相鄰的詞。例如，以下查詢會找出包含 “military” 後面緊跟著 “defense” 的演講：

SELECT president,
       speech_date,
       ts_headline(speech_text, to_tsquery('english', 'military <-> defense'),
                   'StartSel = <, StopSel = >, MinWords=5, MaxWords=7, MaxFragments=1')
FROM president_speeches
WHERE search_speech_text @@ to_tsquery('english', 'military <-> defense')
ORDER BY speech_date;

內容解密：

  1. 在 to_tsquery() 函式中，我們使用 <-> 運算子來指定相鄰的詞。
  2. 這種查詢可以用於找出特定的片語或表示式。
玄貓 BlackCat

玄貓 BlackCat

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