51 lines
1.9 KiB
SQL
51 lines
1.9 KiB
SQL
-- =============================================================================
|
||
-- Migration 022: 競品價格歷史快照表
|
||
-- MOMO PRO — Dashboard PChome competitor history
|
||
-- 2026-05-01 台北
|
||
-- =============================================================================
|
||
-- 說明:
|
||
-- competitor_prices 只保存每個 SKU + source 的最新快取。
|
||
-- competitor_price_history 採 append-only 快照，保存每次 PChome feeder 實際抓到的價格、
|
||
-- 對應商品 ID、商品名稱、比對分數，以及當下 MOMO 最新價格，供週/月/季/年歷史圖表使用。
|
||
-- =============================================================================
|
||
|
||
CREATE TABLE IF NOT EXISTS competitor_price_history (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
|
||
-- MOMO 側商品識別
|
||
sku VARCHAR(50) NOT NULL,
|
||
source VARCHAR(30) NOT NULL DEFAULT 'pchome',
|
||
momo_product_id INTEGER,
|
||
momo_price NUMERIC(10,2),
|
||
|
||
-- 競品側價格快照
|
||
price NUMERIC(10,2) NOT NULL,
|
||
original_price NUMERIC(10,2),
|
||
discount_pct INTEGER,
|
||
|
||
-- 競品側商品識別
|
||
competitor_product_id VARCHAR(100),
|
||
competitor_product_name TEXT,
|
||
|
||
-- 比對品質與語意標籤
|
||
match_score NUMERIC(4,3),
|
||
tags JSONB DEFAULT '[]'::jsonb,
|
||
|
||
-- 每次 feeder 寫入一筆，保留完整歷史
|
||
crawled_at TIMESTAMP NOT NULL DEFAULT NOW()
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_comp_price_history_sku_source_time
|
||
ON competitor_price_history (sku, source, crawled_at DESC);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_comp_price_history_competitor_id
|
||
ON competitor_price_history (competitor_product_id);
|
||
|
||
GRANT ALL PRIVILEGES ON competitor_price_history TO momo;
|
||
GRANT USAGE, SELECT ON SEQUENCE competitor_price_history_id_seq TO momo;
|
||
|
||
DO $$
|
||
BEGIN
|
||
RAISE NOTICE '✅ Migration 022 完成 — competitor_price_history 歷史快照表已建立';
|
||
END $$;
|