数据仓库建模Warehouse Modeling

CREATE TABLE dim_asset AS
SELECT row_number() OVER () AS asset_key, symbol, asset_class
FROM (SELECT DISTINCT symbol, asset_class FROM stage_trades);
CREATE TABLE fact_transactions AS
SELECT t.tx_id, a.asset_key, t.account_id, t.trade_date,
       t.quantity, t.price, t.quantity * t.price AS gross_amount
FROM stage_trades t JOIN dim_asset a USING(symbol);
-- 先核对粒度，再汇总
SELECT asset_class, sum(gross_amount) AS turnover
FROM fact_transactions f JOIN dim_asset a USING(asset_key)
GROUP BY asset_class;