Changes between Initial Version and Version 1 of ContractsRevenueView

Timestamp:

05/10/26 14:33:19 (2 weeks ago)

Author:

185022

Comment:

--

ContractsRevenueView

@@ +1 @@
+= Преглед: 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 Дефиниција ==
+{{{
+#!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;
+}}}
+
+== Тестирање на перформанси ==
+
+=== Препорачано тест прашање ===
+{{{
+#!sql
+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 овде (пред индексирање)
+}}}
+
+=== Применети индекси ===
+{{{
+#!sql
+-- 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 || ___ % ||