Најпрометни часови во денот

Опис

Извештајот го прикажува бројот на нарачки и вкупниот приход групирани по час од денот. Овозможува идентификување на најпрометните периоди.

SQL решение

CREATE OR REPLACE FUNCTION get_busiest_hours()
RETURNS TABLE (
    hour_of_day NUMERIC,
    order_count BIGINT,
    total_items BIGINT,
    total_revenue NUMERIC,
    avg_order_value NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        EXTRACT(HOUR FROM o.created_at) AS hour_of_day,
        COUNT(DISTINCT o.order_id) AS order_count,
        SUM(oi.quantity) AS total_items,
        SUM(oi.quantity * oi.unit_price) AS total_revenue,
        ROUND(AVG(oi.quantity * oi.unit_price), 2) AS avg_order_value
    FROM project.orders o
    JOIN project.order_item oi ON oi.order_id = o.order_id
    WHERE o.status = 'ПЛАТЕНА'
    GROUP BY EXTRACT(HOUR FROM o.created_at)
    ORDER BY total_revenue DESC;
END;
$$;

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

O(order_id, created_at, status)
OI(order_id, quantity, unit_price)

J1 ← O ⨝ O.order_id = OI.order_id OI

F1 ← σ status='ПЛАТЕНА' (J1)

G ← γ hour_of_day;
     COUNT(DISTINCT order_id) → order_count,
     SUM(quantity) → total_items,
     SUM(quantity * unit_price) → total_revenue,
     AVG(quantity * unit_price) → avg_order_value (F1)

R ← π hour_of_day, order_count, total_items, total_revenue, avg_order_value (G)

R_final ← τ total_revenue DESC (R)