花費暴衝異常 · 偵測

最新一天的花費明顯高於平常水準就提醒你,只通知不動手。

編輯 SQL

提醒

campaign_id	name	spend_today	avg_spend	z_score
4	露營折疊椅・季節	4414	1552	4.75

SQL

WITH spend AS (
  SELECT campaign_id, ts, value FROM observations WHERE metric='spend'
),
stats AS (   -- 基準排除「今天」,暴衝那天才不會稀釋自己的標準差
  SELECT s.campaign_id, AVG(s.value) AS mu,
         SQRT(AVG(s.value*s.value) - AVG(s.value)*AVG(s.value)) AS sigma
  FROM spend s
  WHERE s.ts < (SELECT MAX(ts) FROM spend WHERE campaign_id = s.campaign_id)
  GROUP BY s.campaign_id
),
latest AS (
  SELECT s.campaign_id, s.value AS today
  FROM spend s
  WHERE s.ts = (SELECT MAX(ts) FROM spend WHERE campaign_id = s.campaign_id)
)
SELECT c.id AS campaign_id, c.name,
       ROUND(l.today,0) AS spend_today, ROUND(st.mu,0) AS avg_spend,
       ROUND((l.today - st.mu)/st.sigma, 2) AS z_score
FROM latest l
JOIN stats st    ON st.campaign_id = l.campaign_id
JOIN campaigns c ON c.id = l.campaign_id
WHERE (l.today - st.mu)/st.sigma > 2.5
ORDER BY z_score DESC;