004.加入新的資料表

剛剛看了一下有哪些資訊要加進資料庫，看起來財務報表蠻適合的，這樣可以將兩個表格整理在網頁資訊內。就請GPT解析JSON並且上工寫程式。

Q: example是這樣，幫我產生資料表，一樣將公司代號當作PRIMARY KEY。

A:

根據提供的 JSON 範例，以下是適合存儲這些數據的 PostgreSQL 資料表結構，並將 公司代號 (stock_id) 作為主鍵。

資料表建置 SQL

sql
複製程式碼CREATE TABLE financial_reports (
    report_date DATE,                                -- 出表日期
    year VARCHAR(4),                                -- 年度
    quarter VARCHAR(10),                            -- 季別
    stock_id VARCHAR(10) PRIMARY KEY,               -- 公司代號 (主鍵)
    company_name VARCHAR(255),                      -- 公司名稱
    revenue_million NUMERIC(20, 2),                 -- 營業收入 (百萬元)
    gross_margin_percent NUMERIC(10, 2),            -- 毛利率 (%)
    operating_profit_margin_percent NUMERIC(10, 2), -- 營業利益率 (%)
    pretax_profit_margin_percent NUMERIC(10, 2),    -- 稅前純益率 (%)
    net_profit_margin_percent NUMERIC(10, 2),       -- 稅後純益率 (%)
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP  -- 創建時間
);

欄位設計說明

1. 主鍵 (PRIMARY KEY)：
2. • 使用 stock_id 作為主鍵，保證數據的唯一性。
2. 數據類型選擇：
3. • DATE：report_date (出表日期)，用於存儲日期型數據。
   • VARCHAR：year, quarter, stock_id, company_name，用於文本型數據。
   • NUMERIC(20, 2)：
   • • 適合存儲財務數據，如營業收入和百分比率，並支持高精度。
3. 新增時間戳欄位：
4. • created_at 記錄數據插入的時間。

測試資料插入

以下是測試用的 INSERT 語句，驗證資料表是否正常運行：

INSERT INTO financial_reports (
    report_date, year, quarter, stock_id, company_name,
    revenue_million, gross_margin_percent, operating_profit_margin_percent,
    pretax_profit_margin_percent, net_profit_margin_percent
) VALUES (
    '2024-11-25', '2024', 'Q3', '2330', '台灣積體電路製造股份有限公司',
    100000.50, 45.30, 25.20, 20.10, 15.60
);

查詢資料

插入數據後，使用以下查詢檢查表中內容：

SELECT * FROM financial_reports;

後續擴展建議

1. 索引：
2. • 如果經常按年度或季別查詢，可以添加索引以提升查詢效能：

     CREATE INDEX idx_year_quarter ON financial_reports(year, quarter);
     

2. 分區表 (Partitioning)：
3. • 如果數據量龐大，可基於年度或季度進行分區存儲。
3. 外鍵關係：
4. • 若 stock_id 與其他資料表（如公司基本信息表）有關聯，可設置外鍵：

     ALTER TABLE financial_reports
     ADD CONSTRAINT fk_stock_id FOREIGN KEY (stock_id)
     REFERENCES twse_listed_companies(stock_id);
     

這樣的資料表結構設計能高效支持你的財務數據存儲需求，並便於後續擴展分析功能。

還不錯，這樣就有基本步驟了，又換我上工把東西整合再一起了。

經過一番剪剪貼貼後，財報分析頁就出來了。