Откривање на аномалии: нарачки со невообичаено високи вредности

Опис

Извештајот ги прикажува нарачките чија вредност е повеќе од 2 стандардни отстапувања над просекот за тој келнер. Користи статистички функции (STDDEV) и корелационен потпрашалник за споредба со глобалниот просек.

SQL решение

CREATE OR REPLACE FUNCTION project.get_anomalous_orders()
RETURNS TABLE (
    order_id INT,
    waiter_name TEXT,
    order_total NUMERIC,
    waiter_avg NUMERIC,
    waiter_stddev NUMERIC,
    z_score NUMERIC,
    deviation_level TEXT
)
LANGUAGE sql
AS $$
    WITH order_totals AS (
        SELECT 
            o.order_id AS oid,
            o.user_id AS uid,
            SUM(oi.quantity * oi.unit_price)::numeric AS total
        FROM project.orders o
        JOIN project.order_item oi ON oi.order_id = o.order_id
        WHERE o.status = 'ПЛАТЕНА'
        GROUP BY o.order_id, o.user_id
    ),
    waiter_stats AS (
        SELECT 
            ot.uid,
            AVG(ot.total)::numeric AS avg_total,
            STDDEV(ot.total)::numeric AS stddev_total,
            COUNT(*) AS num_orders
        FROM order_totals ot
        GROUP BY ot.uid
    )
    SELECT 
        ot.oid,
        (u.first_name || ' ' || u.last_name)::TEXT,
        ot.total,
        ROUND(ws.avg_total, 2),
        ROUND(ws.stddev_total, 2),
        ROUND(((ot.total - ws.avg_total) / NULLIF(ws.stddev_total, 0))::numeric, 2),
        CASE 
            WHEN (ot.total - ws.avg_total) / NULLIF(ws.stddev_total, 0) > 3 
                 THEN 'ЕКСТРЕМНО ВИСОКА'
            WHEN (ot.total - ws.avg_total) / NULLIF(ws.stddev_total, 0) > 2 
                 THEN 'ВИСОКА'
            ELSE 'НОРМАЛНА'
        END::TEXT
    FROM order_totals ot
    JOIN waiter_stats ws ON ws.uid = ot.uid
    JOIN project.app_user u ON u.user_id = ot.uid
    WHERE ws.num_orders >= 1
      AND (ot.total - ws.avg_total) / NULLIF(ws.stddev_total, 0) > 1
    ORDER BY 6 DESC;
$$;

Релациона алгебра

O(order_id, user_id, status)
OI(order_id, quantity, unit_price)
U(user_id, first_name, last_name)

J1 ← O ⨝ O.order_id = OI.order_id OI
F1 ← σ status='ПЛАТЕНА' (J1)
G1 ← γ order_id, user_id; SUM(quantity * unit_price) → order_total (F1)

G2 ← γ user_id; AVG(order_total) → avg_total,
                  STDDEV(order_total) → stddev_total,
                  COUNT(*) → num_orders (G1)

J2 ← G1 ⨝ G1.user_id = G2.user_id G2
J3 ← J2 ⨝ U
F2 ← σ num_orders ≥ 1 ∧ (order_total - avg_total)/stddev_total > 1 (J3)

R ← π order_id, waiter_name, order_total, waiter_avg,
      waiter_stddev, z_score, deviation_level (F2)

R_final ← τ z_score DESC (R)