Преглед: v_contracts_revenue

Датотека	views/05_contracts_revenue_view.sql
Шема	kbnteam
Категорија	Договори, Фактурирање и Верност
Поврзани индекси	indexes/v_contracts_revenue_index.sql
Сложеност	Висока — 2 CTE со UNION + агрегации

Опис

Резимира приход по компанија и ресторан под активни договори. Го користи CTE order_restaurants за да определи кој ресторан го послужил секој редослед преку UNION на order_meal и order_drink. Вториот CTE contract_metrics агрегира броеви на нарачки и вкупен приход. Важен преглед за финансиско известување.

Зависности

Табела	Тип на употреба
kbnteam.contract	Главна табела
kbnteam.company	JOIN — компанија
kbnteam.restaurant	JOIN — ресторан
kbnteam.contract_status	JOIN — статус на договор
kbnteam.customer_order	JOIN (CTE) — нарачки
kbnteam.company_order	JOIN (CTE) — компаниски нарачки
kbnteam.order_meal	JOIN (CTE, UNION) — оброци во нарачки
kbnteam.meal	JOIN (CTE) — ресторан на оброкот
kbnteam.order_drink	JOIN (CTE, UNION) — пијачи во нарачки
kbnteam.drink	JOIN (CTE) — ресторан на пијачот

SQL Дефиниција

CREATE OR REPLACE VIEW kbnteam.v_contracts_revenue AS
WITH order_restaurants AS (
    SELECT DISTINCT
        om.order_id,
        m.rest_id
    FROM kbnteam.order_meal om
    JOIN kbnteam.meal m ON m.meal_id = om.meal_id

    UNION

    SELECT DISTINCT
        od.order_id,
        d.rest_id
    FROM kbnteam.order_drink od
    JOIN kbnteam.drink d ON d.drink_id = od.drink_id
),
contract_metrics AS (
    SELECT
        co.company_id,
        orr.rest_id,
        COUNT(DISTINCT co.comp_order_id) AS company_order_count,
        COUNT(DISTINCT o.order_id) AS customer_order_count,
        COALESCE(SUM(o.order_total), 0)::numeric(14,2) AS total_revenue
    FROM kbnteam.customer_order o
    JOIN kbnteam.company_order co ON co.comp_order_id = o.comp_order_id
    JOIN order_restaurants orr ON orr.order_id = o.order_id
    GROUP BY co.company_id, orr.rest_id
)
SELECT
    ct.contract_id,
    cmp.company_id,
    cmp.company_name,
    r.rest_id,
    r.rest_name,
    cs.contract_status_name,
    ct.contract_start_date,
    ct.contract_end_date,
    COALESCE(cm.company_order_count, 0) AS company_order_count,
    COALESCE(cm.customer_order_count, 0) AS customer_order_count,
    COALESCE(cm.total_revenue, 0)::numeric(14,2) AS total_revenue
FROM kbnteam.contract ct
JOIN kbnteam.company cmp ON cmp.company_id = ct.company_id
JOIN kbnteam.restaurant r ON r.rest_id = ct.rest_id
JOIN kbnteam.contract_status cs ON cs.contract_status_id = ct.contract_status_id
LEFT JOIN contract_metrics cm
    ON cm.company_id = ct.company_id
   AND cm.rest_id = ct.rest_id;

Тестирање на перформанси

Препорачано тест прашање

SET search_path TO kbnteam;
SET statement_timeout = '60s';

-- Тест 1: по компанија (тестира idx на contract)
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM kbnteam.v_contracts_revenue
WHERE company_id = 1;

-- Тест 2: по ресторан
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM kbnteam.v_contracts_revenue
WHERE rest_id = 1;

Резултати пред индексирање

Метрика	Тест 1 (по company_id)	Тест 2 (по rest_id)
Planning Time	_ ms	_ ms
Execution Time	_ ms	_ ms
Rows Returned	_	_
contract scan	_	_
order_meal scan	_	_
customer_order scan	_	_

-- Излезот од EXPLAIN ANALYZE овде (пред индексирање)

Применети индекси

-- indexes/v_contracts_revenue_index.sql
CREATE INDEX IF NOT EXISTS idx_order_meal_order_id_meal_id
ON kbnteam.order_meal (order_id, meal_id);

CREATE INDEX IF NOT EXISTS idx_order_drink_order_id_drink_id
ON kbnteam.order_drink (order_id, drink_id);

CREATE INDEX IF NOT EXISTS idx_customer_order_comp_order_id
ON kbnteam.customer_order (comp_order_id);

CREATE INDEX IF NOT EXISTS idx_contract_company_id_rest_id
ON kbnteam.contract (company_id, rest_id);

Резултати по индексирање

Метрика	Тест 1 (по company_id)	Тест 2 (по rest_id)
Planning Time	_ ms	_ ms
Execution Time	_ ms	_ ms
Rows Returned	_	_
contract scan	_	_
order_meal scan	_	_
customer_order scan	_	_

-- Излезот од EXPLAIN ANALYZE овде (по индексирање)

Анализа на подобрување

Индекс	Помага на	Очекувана промена
idx_contract_company_id_rest_id	JOIN-от во contract_metrics и главниот SELECT	Seq Scan → Index Scan
idx_customer_order_comp_order_id	JOIN со company_order во CTE	Seq Scan → Index Scan
idx_order_meal_order_id_meal_id	CTE order_restaurants UNION	Seq Scan → Index Scan
idx_order_drink_order_id_drink_id	CTE order_restaurants UNION	Seq Scan → Index Scan

Метрика	Пред	По	Δ Подобрување
Execution Time (Тест 1)	_ ms	_ ms	_ %
Execution Time (Тест 2)	_ ms	_ ms	_ %