使用 SQL 查詢一卡通消費記錄：從網頁資料擷取到結構化分析實戰

案例背景

遺失使用兩年的一卡通 (SFZ 號: 10007510104887822)，欲在掛失前查詢歷史消費記錄。目標網站為北京一卡通查詢系統，回傳 HTML 表格資料。

技術方案：外部表映射 HTML 表格

使用類 SQL 引擎 (如 Sqlhub) 定義外部結構描述，將網頁表格直接映射為可查詢資料表。

步驟 1：定義 HTML 外部結構描述

CREATE TABLE Html.YKT
URL 'https://www.bjsuperpass.com/pagecontrol.do?object=ecard&action=query&card_id=10007510104887822'
CHARSET 'gb2312'
ROW_XPATH '/html/body/table/tr' (
    P1 VARCHAR ELEMENT 'td[1]',  -- 交易日期
    P2 VARCHAR ELEMENT 'td[2]',  -- 交易時間
    P3 VARCHAR ELEMENT 'td[3]',  -- 交易類型
    P4 VARCHAR ELEMENT 'td[4]',  -- 金額
    P5 VARCHAR ELEMENT 'td[5]',  -- 餘額
    P6 VARCHAR ELEMENT 'td[6]'   -- 商戶/地點
);

• ROW_XPATH：定位每筆記錄的列節點 (這裡假設表格結構為 <table>><tr>><td>)
• ELEMENT：CSS 選擇器提取欄位文字
• CHARSET：網頁編碼 gb2312

步驟 2：建立參數化查詢程序

CREATE OR REPLACE PROCEDURE ykt(p_card_id VARCHAR) RETURN ROWSET AS
BEGIN
    EXECUTE IMMEDIATE '
        ALTER TABLE Html.YKT SET
        URL ''https://www.bjsuperpass.com/pagecontrol.do?object=ecard&action=query&card_id='' || p_card_id;
    SELECT * FROM Html.YKT;
END;

步驟 3：執行查詢

SELECT * FROM ykt('10007510104887822');

結果資料結構範例

進階分析查詢

統計月消費總額

SELECT 
    SUBSTR(P1, 1, 7) AS 年月,
    SUM(CASE WHEN CAST(P4 AS DECIMAL(10,2)) < 0 THEN CAST(P4 AS DECIMAL(10,2)) ELSE 0 END) AS 消費支出,
    SUM(CASE WHEN CAST(P4 AS DECIMAL(10,2)) > 0 THEN CAST(P4 AS DECIMAL(10,2)) ELSE 0 END) AS 圈存收入,
    COUNT(*) AS 筆數
FROM ykt('10007510104887822')
GROUP BY SUBSTR(P1, 1, 7)
ORDER BY 年月 DESC;

高頻消費地點 TOP 10

SELECT P6 AS 地點, COUNT(*) AS 次數, SUM(CASE WHEN CAST(P4 AS DECIMAL(10,2)) < 0 THEN ABS(CAST(P4 AS DECIMAL(10,2))) ELSE 0 END) AS 總金額
FROM ykt('10007510104887822')
WHERE CAST(P4 AS DECIMAL(10,2)) < 0
GROUP BY P6
ORDER BY 次數 DESC
LIMIT 10;

每日餘額變化趨勢

SELECT P1 AS 日期, MAX(CAST(P5 AS DECIMAL(10,2))) AS 日末餘額
FROM ykt('10007510104887822')
GROUP BY P1
ORDER BY 日期;

技術架構說明

此方案採用 Wrapper/Wrapper Generator 模式：

1. Wrapper 定義：宣告式描述外部資料源 (URL、編碼、解析規則)
2. Wrapper 執行引擎：HTTP 取得內容 → 文字/HTML 解析 → 結構化轉換 → 關聯式結果集
3. SQL 查詢介面：標準 SQL 查詢虛擬表，支援 WHERE、JOIN、聚合等完整關聯運算

注意事項與風險控管

• 法律合規：確認網站 robots.txt、服務條款、個資保護法規，僅查詢自有卡號資料
• 反爬蟲機制：高頻查詢可能觸發驗證碼、IP 封鎖，建議快取結果、降低頻率
• 頁面結構變更：網站改版導致 XPath/CSS 選擇器失效，需建立監控告警
• 編碼問題：錯誤 CHARSET 導致中文亂碼，實測確認後再定義
• 替代方案：優先尋找官方 API、APP 匯出功能、郵件帳單、客服查詢，穩定性遠優於網頁解析

擴展應用場景

• 多卡號批次查詢：建立卡號表，迴圈呼叫 ykt() 彙總家庭/團體消費
• 異常交易偵測：單筆金額異常、短時間高頻、異地交易等規則
• 預算追蹤：設定月預算，自動比對實際消費發送提醒
• 資料倉庫整合：定期抽取載入本地資料庫，建立個人消費數據資產

工具選擇建議