Changes between Version 1 and Version 2 of ContractsRevenueView
- Timestamp:
- 09/14/26 00:42:07 (2 weeks ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
ContractsRevenueView
v1 v2 1 1 = Преглед: v_contracts_revenue = 2 2 3 ||= Датотека ||= `views/05_contracts_revenue_view.sql` || 4 ||= Шема ||= `kbnteam` || 5 ||= Категорија ||= Договори, Фактурирање и Верност || 6 ||= Поврзани индекси ||= `indexes/v_contracts_revenue_index.sql` || 7 ||= Сложеност ||= Висока — 2 CTE со UNION + агрегации || 3 ||= Својство ||= Вредност || 4 || Шема || `kbnteam` || 5 || Категорија || Договори и приходи || 6 || Поврзани индекси || [wiki:ContractsRevenueIndex Индекси за договори и приходи] || 8 7 9 8 == Опис == 10 Резимира приход по компанија и ресторан под активни договори. Го користи CTE `order_restaurants` за да определи кој ресторан го послужил секој редослед преку `UNION` на `order_meal` и `order_drink`. Вториот CTE `contract_metrics` агрегира броеви на нарачки и вкупен приход. Важен преглед за финансиско известување. 9 10 За секој договор ги прикажува бројот на компаниски и клиентски нарачки и вкупниот приход за парот компанија–ресторан. 11 12 Статистиката ги опфаќа сите нарачки, независно од датумот и статусот на договорот или нарачката. Целиот износ на нарачката се брои кај секој вклучен ресторан. Договорите за истиот пар компанија–ресторан ја прикажуваат истата статистика. 13 14 Еден ред за секој договор. Нарачките без јадења и пијалоци немаат поврзан ресторан и не влегуваат во статистиката. Договорите без соодветни нарачки имаат нулти броеви и приход. 11 15 12 16 == Зависности == 13 ||= Табела ||= Тип на употреба || 14 || `kbnteam.contract` || Главна табела || 15 || `kbnteam.company` || JOIN — компанија || 16 || `kbnteam.restaurant` || JOIN — ресторан || 17 || `kbnteam.contract_status` || JOIN — статус на договор || 18 || `kbnteam.customer_order` || JOIN (CTE) — нарачки || 19 || `kbnteam.company_order` || JOIN (CTE) — компаниски нарачки || 20 || `kbnteam.order_meal` || JOIN (CTE, UNION) — оброци во нарачки || 21 || `kbnteam.meal` || JOIN (CTE) — ресторан на оброкот || 22 || `kbnteam.order_drink` || JOIN (CTE, UNION) — пијачи во нарачки || 23 || `kbnteam.drink` || JOIN (CTE) — ресторан на пијачот || 24 25 == SQL Дефиниција == 17 18 ||= Табела / преглед ||= Употреба || 19 || `kbnteam.order_meal` || Јадења вклучени во нарачките. || 20 || `kbnteam.meal` || Јадења, цени и поврзаност со категорија и ресторан. || 21 || `kbnteam.order_drink` || Пијалоци вклучени во нарачките. || 22 || `kbnteam.drink` || Пијалоци, количини, цени и ресторан. || 23 || `kbnteam.customer_order` || Клиентски нарачки, датуми, статуси и износи. || 24 || `kbnteam.company_order` || Поврзување на компаниските нарачки со компанијата и доставата. || 25 || `kbnteam.contract` || Договори меѓу компаниите и рестораните. || 26 || `kbnteam.company` || Податоци за компаниите. || 27 || `kbnteam.restaurant` || Податоци за рестораните. || 28 || `kbnteam.contract_status` || Називи на статусите на договорите. || 29 30 == Излезни колони == 31 32 ||= Колона ||= Извор / пресметка ||= Опис || 33 || `contract_id` || `contract.contract_id` || Идентификатор на договорот. || 34 || `company_id` || `company.company_id` || Идентификатор на компанијата. || 35 || `company_name` || `company.company_name` || Назив на компанијата. || 36 || `rest_id` || `restaurant.rest_id` || Идентификатор на ресторанот. || 37 || `rest_name` || `restaurant.rest_name` || Назив на ресторанот. || 38 || `contract_status_name` || `contract_status.contract_status_name` || Статус на договорот. || 39 || `contract_start_date` || `contract.contract_start_date` || Почетен датум на договорот. || 40 || `contract_end_date` || `contract.contract_end_date` || Краен датум на договорот. || 41 || `company_order_count` || `COALESCE(contract_metrics.company_order_count, 0)` || Број на различни компаниски нарачки. || 42 || `customer_order_count` || `COALESCE(contract_metrics.customer_order_count, 0)` || Број на клиентски нарачки. || 43 || `total_revenue` || `COALESCE(contract_metrics.total_revenue, 0)::numeric(14,2)` || Збир на износите за парот компанија–ресторан. || 44 45 == SQL дефиниција == 46 26 47 {{{ 27 48 #!sql 28 49 CREATE OR REPLACE VIEW kbnteam.v_contracts_revenue AS 29 50 WITH order_restaurants AS ( 30 SELECT DISTINCT51 SELECT 31 52 om.order_id, 32 53 m.rest_id … … 36 57 UNION 37 58 38 SELECT DISTINCT59 SELECT 39 60 od.order_id, 40 61 d.rest_id … … 47 68 orr.rest_id, 48 69 COUNT(DISTINCT co.comp_order_id) AS company_order_count, 49 COUNT( DISTINCT o.order_id) AS customer_order_count,50 COALESCE(SUM(o.order_total), 0)::numeric(14,2) AS total_revenue70 COUNT(*) AS customer_order_count, 71 SUM(o.order_total)::numeric(14,2) AS total_revenue 51 72 FROM kbnteam.customer_order o 52 73 JOIN kbnteam.company_order co ON co.comp_order_id = o.comp_order_id … … 77 98 == Тестирање на перформанси == 78 99 79 === Препорачано тест прашање === 80 {{{ 81 #!sql 82 SET search_path TO kbnteam; 83 SET statement_timeout = '60s'; 84 85 -- Тест 1: по компанија (тестира idx на contract) 100 Примерите користат илустративни идентификатори и датуми. За споредливи мерења се избираат вредности што постојат во базата и се задржуваат истите податоци, филтри, услови за кеширање и број на повторувања. Статистиките за табелите треба да бидат ажурирани во двете состојби. 101 102 === Тест прашалници === 103 104 {{{ 105 #!sql 106 BEGIN READ ONLY; 107 SET LOCAL statement_timeout = '60s'; 108 109 -- Тест 1: по компанија 86 110 EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 87 111 SELECT * FROM kbnteam.v_contracts_revenue … … 92 116 SELECT * FROM kbnteam.v_contracts_revenue 93 117 WHERE rest_id = 1; 118 119 ROLLBACK; 94 120 }}} 95 121 96 122 === Резултати пред индексирање === 97 ||= Метрика ||= Тест 1 (по company_id) ||= Тест 2 (по rest_id) || 123 124 Основната состојба ги задржува индексите на примарните клучеви и уникатните ограничувања. Останатите присутни индекси се евидентираат одделно. При споредба на заеднички индекси се користи истата почетна состојба за секој преглед. 125 126 ||= Метрика ||= Тест 1 (по компанија) ||= Тест 2 (по ресторан) || 98 127 || Planning Time || ___ ms || ___ ms || 99 128 || Execution Time || ___ ms || ___ ms || 100 129 || Rows Returned || ___ || ___ || 101 || contract scan || ___ || ___ || 102 || order_meal scan || ___ || ___ || 103 || customer_order scan || ___ || ___ || 104 105 {{{ 106 -- Излезот од EXPLAIN ANALYZE овде (пред индексирање) 107 }}} 108 109 === Применети индекси === 110 {{{ 111 #!sql 112 -- indexes/v_contracts_revenue_index.sql 130 || Начин на читање по табела || ___ || ___ || 131 || Shared Hit Blocks || ___ || ___ || 132 || Shared Read Blocks || ___ || ___ || 133 134 {{{ 135 -- Планови и резултати пред дополнителното индексирање: 136 137 }}} 138 139 === Дополнителни и заеднички индекси === 140 141 `contract(company_id, rest_id)` поддржува и услов само по company_id. Индексот со rest_id како прва колона ја поддржува другата насока на пребарување. 142 143 ||= Индекс ||= Табела и колони ||= Намена || 144 || `idx_company_order_company_id` || `company_order(company_id)` || Пронаоѓање компаниски нарачки по компанија. || 145 || `idx_customer_order_comp_order_id` || `customer_order(comp_order_id)` || Пронаоѓање клиентски нарачки по компаниска нарачка. || 146 || `idx_order_meal_order_id_meal_id` || `order_meal(order_id, meal_id)` || Пристап до јадењата на нарачката и групирање по order_id. || 147 || `idx_order_drink_order_id_drink_id` || `order_drink(order_id, drink_id)` || Пристап до пијалоците на нарачката и групирање по order_id. || 148 || `idx_contract_company_id_rest_id` || `contract(company_id, rest_id)` || Пребарување договори по компанија и ресторан и групирање по компанија. || 149 || `idx_contract_rest_id_company_id` || `contract(rest_id, company_id)` || Пронаоѓање договори почнувајќи од ресторан. || 150 151 {{{ 152 #!sql 153 CREATE INDEX IF NOT EXISTS idx_company_order_company_id 154 ON kbnteam.company_order (company_id); 155 156 CREATE INDEX IF NOT EXISTS idx_customer_order_comp_order_id 157 ON kbnteam.customer_order (comp_order_id); 158 113 159 CREATE INDEX IF NOT EXISTS idx_order_meal_order_id_meal_id 114 160 ON kbnteam.order_meal (order_id, meal_id); … … 117 163 ON kbnteam.order_drink (order_id, drink_id); 118 164 119 CREATE INDEX IF NOT EXISTS idx_customer_order_comp_order_id120 ON kbnteam.customer_order (comp_order_id);121 122 165 CREATE INDEX IF NOT EXISTS idx_contract_company_id_rest_id 123 166 ON kbnteam.contract (company_id, rest_id); 167 168 CREATE INDEX IF NOT EXISTS idx_contract_rest_id_company_id 169 ON kbnteam.contract (rest_id, company_id); 170 }}} 171 172 === Проверка на индексите === 173 174 {{{ 175 #!sql 176 SELECT tablename, indexname, indexdef 177 FROM pg_indexes 178 WHERE schemaname = 'kbnteam' 179 AND indexname IN ( 180 'idx_company_order_company_id', 181 'idx_customer_order_comp_order_id', 182 'idx_order_meal_order_id_meal_id', 183 'idx_order_drink_order_id_drink_id', 184 'idx_contract_company_id_rest_id', 185 'idx_contract_rest_id_company_id' 186 ) 187 ORDER BY tablename, indexname; 124 188 }}} 125 189 126 190 === Резултати по индексирање === 127 ||= Метрика ||= Тест 1 (по company_id) ||= Тест 2 (по rest_id) || 191 192 Мерењето ги користи истите тест прашалници и услови како почетното мерење. 193 194 ||= Метрика ||= Тест 1 (по компанија) ||= Тест 2 (по ресторан) || 128 195 || Planning Time || ___ ms || ___ ms || 129 196 || Execution Time || ___ ms || ___ ms || 130 197 || Rows Returned || ___ || ___ || 131 || contract scan || ___ || ___ || 132 || order_meal scan || ___ || ___ || 133 || customer_order scan || ___ || ___ || 134 135 {{{ 136 -- Излезот од EXPLAIN ANALYZE овде (по индексирање) 198 || Начин на читање по табела || ___ || ___ || 199 || Shared Hit Blocks || ___ || ___ || 200 || Shared Read Blocks || ___ || ___ || 201 202 {{{ 203 -- Планови и резултати по дополнителното индексирање: 204 137 205 }}} 138 206 139 207 === Анализа на подобрување === 140 ||= Индекс ||= Помага на ||= Очекувана промена || 141 || `idx_contract_company_id_rest_id` || JOIN-от во `contract_metrics` и главниот SELECT || Seq Scan → Index Scan || 142 || `idx_customer_order_comp_order_id` || JOIN со `company_order` во CTE || Seq Scan → Index Scan || 143 || `idx_order_meal_order_id_meal_id` || CTE `order_restaurants` UNION || Seq Scan → Index Scan || 144 || `idx_order_drink_order_id_drink_id` || CTE `order_restaurants` UNION || Seq Scan → Index Scan || 145 146 ||= Метрика ||= Пред ||= По ||= Δ Подобрување || 208 209 ||= Метрика ||= Пред ||= По ||= Промена (%) || 147 210 || Execution Time (Тест 1) || ___ ms || ___ ms || ___ % || 148 211 || Execution Time (Тест 2) || ___ ms || ___ ms || ___ % || 212 213 Промената се пресметува како 100 × (време пред − време по) / време пред, кога почетното време е поголемо од нула. Негативна вредност означува побавно извршување. 214 215 Изборот меѓу секвенцијално читање, индексно читање и други планови зависи од обемот на податоците, селективноста на филтрите и статистиките. Индексите не гарантираат забрзување или отстранување на сортирањата и спојувањата. Плановите и времињата се утврдуваат со мерење.
