คำนวณจากประวัติศาสตร์ จัดเก็บอย่างมีเจตนา และส่งมอบโดยไม่ทำงานราคาแพงซ้ำทุกครั้ง

WITH daily AS (
 SELECT instrument_key, trading_date,
        MAX(adjusted_close) AS close,
        SUM(volume) AS volume,
        AVG((ask-bid)/NULLIF((ask+bid)/2,0)) AS relative_spread
 FROM analytics.fact_market_observation
 WHERE available_at <= trading_date + INTERVAL '1 day'
 GROUP BY instrument_key, trading_date
), returns AS (
 SELECT *, LN(close/NULLIF(LAG(close) OVER
   (PARTITION BY instrument_key ORDER BY trading_date),0)) AS return_1d
 FROM daily
)
SELECT instrument_key, trading_date AS feature_date,
       volume, relative_spread, return_1d,
       AVG(volume) OVER (PARTITION BY instrument_key ORDER BY trading_date
         ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS avg_volume_20obs,
       STDDEV_SAMP(return_1d) OVER (PARTITION BY instrument_key ORDER BY trading_date
         ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS volatility_20obs
FROM returns;

CREATE TABLE IF NOT EXISTS features.customer_activity_daily (
  customer_id        BIGINT NOT NULL,
  feature_date       DATE NOT NULL,
  orders_30d         INTEGER NOT NULL,
  spend_30d          DECIMAL(14,2) NOT NULL,
  days_since_order   INTEGER,
  feature_version    VARCHAR(30) NOT NULL,
  computed_at        TIMESTAMP NOT NULL,
  PRIMARY KEY (customer_id, feature_date, feature_version)
);

-- Build only the affected feature_date partition.
INSERT INTO features.customer_activity_daily
SELECT
  c.customer_id,
  :feature_date,
  COUNT(o.order_id),
  COALESCE(SUM(o.net_amount), 0),
  DATE_PART('day', :feature_date - MAX(o.order_date)),
  'customer_activity_v1',
  CURRENT_TIMESTAMP
FROM analytics.dim_customer c
LEFT JOIN analytics.fact_order o
  ON o.customer_id = c.customer_id
 AND o.order_date >= :feature_date - INTERVAL '30 days'
 AND o.order_date <  :feature_date
 AND o.is_completed = TRUE
GROUP BY c.customer_id
ON CONFLICT (customer_id, feature_date, feature_version)
DO UPDATE SET
  orders_30d=EXCLUDED.orders_30d,
  spend_30d=EXCLUDED.spend_30d,
  days_since_order=EXCLUDED.days_since_order,
  computed_at=EXCLUDED.computed_at;