Feature Engineering มีต้นทุนสูง และการสร้างความจริงเดิมซ้ำไปซ้ำมายิ่งแพงกว่า

CREATE TABLE ml.training_events (
  customer_id INTEGER NOT NULL,
  prediction_time TIMESTAMP NOT NULL,
  label INTEGER
);

-- One row represents one customer at one prediction time.
CREATE VIEW ml.v_customer_features_training AS
SELECT
  t.customer_id,
  t.prediction_time,
  t.label,
  COUNT(o.order_id) FILTER (
    WHERE o.status = 'completed'
  ) AS completed_orders_30d,
  COALESCE(SUM(o.amount) FILTER (
    WHERE o.status = 'completed'
  ), 0) AS completed_value_30d,
  MAX(o.ordered_at) AS latest_known_order_time
FROM ml.training_events AS t
LEFT JOIN raw.orders AS o
  ON o.customer_id = t.customer_id
 AND o.ordered_at >= t.prediction_time - INTERVAL '30 days'
 AND o.ordered_at <  t.prediction_time
GROUP BY t.customer_id, t.prediction_time, t.label;

CREATE TABLE ml.customer_features_online (
  customer_id INTEGER PRIMARY KEY,
  completed_orders_30d INTEGER NOT NULL,
  completed_value_30d DECIMAL(14,2) NOT NULL,
  feature_timestamp TIMESTAMP NOT NULL,
  feature_version VARCHAR(20) NOT NULL
);

INSERT INTO ml.customer_features_online
SELECT
  c.customer_id,
  COUNT(o.order_id) FILTER (WHERE o.status='completed'),
  COALESCE(SUM(o.amount) FILTER (WHERE o.status='completed'), 0),
  CURRENT_TIMESTAMP,
  'customer_activity_v1'
FROM raw.customers c
LEFT JOIN raw.orders o
  ON o.customer_id=c.customer_id
 AND o.ordered_at >= CURRENT_TIMESTAMP - INTERVAL '30 days'
GROUP BY c.customer_id
ON CONFLICT (customer_id) DO UPDATE SET
  completed_orders_30d=EXCLUDED.completed_orders_30d,
  completed_value_30d=EXCLUDED.completed_value_30d,
  feature_timestamp=EXCLUDED.feature_timestamp,
  feature_version=EXCLUDED.feature_version;