Changes between Version 1 and Version 2 of CustomerLoyaltyFullView
- Timestamp:
- 09/14/26 00:44:08 (2 weeks ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
CustomerLoyaltyFullView
v1 v2 1 1 = Преглед: v_customer_loyalty_full_v2 = 2 2 3 ||= Датотека ||= `views/06_customer_loyalty_view_v2.sql` || 4 ||= Шема ||= `kbnteam` || 5 ||= Категорија ||= Договори, Фактурирање и Верност || 6 ||= Поврзани индекси ||= `indexes/v_customer_loyalty_full_v2_index.sql` || 7 ||= Статус ||= '''Канонска верзија''' — препорачана за употреба наместо `v_customer_loyalty_full` || 3 ||= Својство ||= Вредност || 4 || Шема || `kbnteam` || 5 || Категорија || Лојалност на клиентите || 6 || Поврзани индекси || [wiki:CustomerLoyaltyFullIndex Индекси за лојалност на клиентите] || 8 7 9 8 == Опис == 10 Прикажува статус на верност, податоци за ниво и статистика на нарачки на купувачи. Користи CTE `order_stats` за агрегирање на вкупен приход, број на нарачки и последна нарачка по купувач. V2 верзијата е почиста и поефикасна од `v_customer_loyalty_full` бидејќи CTE-то е поделено наместо вградено подпрашање. 9 10 Ги прикажува клиентот, компанијата, поените, статусот и нивото на лојалност, бројот на нарачки, вкупната потрошувачка и датумот на последната нарачка. 11 12 Статистиката се пресметува по клиент и ги опфаќа сите статуси на нарачки. Се прикажуваат членовите на програмата за лојалност, вклучувајќи ги и оние без нарачки. 13 14 Еден ред за секој член на програмата за лојалност. Кај член без нарачки бројот и потрошувачката се нула, а последната нарачка е NULL. 11 15 12 16 == Зависности == 13 ||= Табела ||= Тип на употреба ||14 || `kbnteam.customer_loyalty` || Главна табела ||15 || `kbnteam.customer` || JOIN — купувач ||16 || `kbnteam.company` || JOIN — компанија ||17 || `kbnteam.api_user` || JOIN — детали за корисник ||18 || `kbnteam.customer_loyalty_status` || JOIN — статус на верност ||19 || `kbnteam.loyalty_tier` || JOIN — ниво на верност ||20 || `kbnteam.customer_order` || LEFT JOIN (CTE) — статистика на нарачки ||21 17 22 == SQL Дефиниција == 18 ||= Табела / преглед ||= Употреба || 19 || `kbnteam.customer_order` || Клиентски нарачки, датуми, статуси и износи. || 20 || `kbnteam.customer_loyalty` || Членство и поени во програмата за лојалност. || 21 || `kbnteam.customer` || Клиенти и компаниите на кои припаѓаат. || 22 || `kbnteam.company` || Податоци за компаниите. || 23 || `kbnteam.api_user` || Лични и контактни податоци за корисниците. || 24 || `kbnteam.customer_loyalty_status` || Статуси на членството. || 25 || `kbnteam.loyalty_tier` || Нивоа и поволности во програмата за лојалност. || 26 27 == Излезни колони == 28 29 ||= Колона ||= Извор / пресметка ||= Опис || 30 || `cus_loyalty_id` || `customer_loyalty.cus_loyalty_id` || Идентификатор на членството. || 31 || `customer_user_id` || `customer_loyalty.user_id` || Идентификатор на клиентот. || 32 || `company_id` || `customer.company_id` || Идентификатор на компанијата. || 33 || `company_name` || `company.company_name` || Назив на компанијата. || 34 || `user_first_name` || `api_user.user_first_name` || Име на клиентот. || 35 || `user_last_name` || `api_user.user_last_name` || Презиме на клиентот. || 36 || `user_email` || `api_user.user_email` || Е-пошта на клиентот. || 37 || `user_phone_no` || `api_user.user_phone_no` || Телефон на клиентот. || 38 || `cus_loyalty_curr_points` || `customer_loyalty.cus_loyalty_curr_points` || Тековни поени за лојалност. || 39 || `cus_loyalty_joined_at` || `customer_loyalty.cus_loyalty_joined_at` || Датум и време на зачленување. || 40 || `cus_loyalty_status_id` || `customer_loyalty_status.cus_loyalty_status_id` || Идентификатор на статусот на членството. || 41 || `cus_loyalty_status_name` || `customer_loyalty_status.cus_loyalty_status_name` || Назив на статусот на членството. || 42 || `tier_id` || `loyalty_tier.tier_id` || Идентификатор на нивото. || 43 || `tier_name` || `loyalty_tier.tier_name` || Назив на нивото. || 44 || `tier_discount_percentage` || `loyalty_tier.tier_discount_percentage` || Процент на попуст. || 45 || `tier_free_delivery_eligibility` || `loyalty_tier.tier_free_delivery_eligibility` || Право на бесплатна достава. || 46 || `tier_priority_support` || `loyalty_tier.tier_priority_support` || Право на приоритетна поддршка. || 47 || `order_count` || `COALESCE(order_stats.order_count, 0)` || Вкупен број на клиентски нарачки. || 48 || `total_spent` || `COALESCE(order_stats.total_spent, 0)::numeric(14,2)` || Збир на износите на клиентските нарачки. || 49 || `last_order_at` || `order_stats.last_order_at` || Датум и време на последната нарачка. || 50 51 == SQL дефиниција == 52 23 53 {{{ 24 54 #!sql … … 28 58 o.customer_user_id, 29 59 COUNT(*) AS order_count, 30 COALESCE(SUM(o.order_total), 0)::numeric(14,2) AS total_spent,60 SUM(o.order_total)::numeric(14,2) AS total_spent, 31 61 MAX(o.order_datetime) AS last_order_at 32 62 FROM kbnteam.customer_order o … … 55 85 os.last_order_at 56 86 FROM kbnteam.customer_loyalty cl 57 JOIN kbnteam.customer cu 58 ON cu.user_id = cl.user_id 59 JOIN kbnteam.company cmp 60 ON cmp.company_id = cu.company_id 61 JOIN kbnteam.api_user au 62 ON au.user_id = cl.user_id 63 JOIN kbnteam.customer_loyalty_status cls 64 ON cls.cus_loyalty_status_id = cl.cus_loyalty_status_id 65 JOIN kbnteam.loyalty_tier lt 66 ON lt.tier_id = cl.tier_id 67 LEFT JOIN order_stats os 68 ON os.customer_user_id = cl.user_id; 87 JOIN kbnteam.customer cu ON cu.user_id = cl.user_id 88 JOIN kbnteam.company cmp ON cmp.company_id = cu.company_id 89 JOIN kbnteam.api_user au ON au.user_id = cl.user_id 90 JOIN kbnteam.customer_loyalty_status cls ON cls.cus_loyalty_status_id = cl.cus_loyalty_status_id 91 JOIN kbnteam.loyalty_tier lt ON lt.tier_id = cl.tier_id 92 LEFT JOIN order_stats os ON os.customer_user_id = cl.user_id; 69 93 }}} 70 94 71 95 == Тестирање на перформанси == 72 96 73 === Препорачано тест прашање === 97 Примерите користат илустративни идентификатори и датуми. За споредливи мерења се избираат вредности што постојат во базата и се задржуваат истите податоци, филтри, услови за кеширање и број на повторувања. Статистиките за табелите треба да бидат ажурирани во двете состојби. 98 99 === Тест прашалници === 100 74 101 {{{ 75 102 #!sql 76 SET search_path TO kbnteam;77 SET statement_timeout = '60s';103 BEGIN READ ONLY; 104 SET LOCAL statement_timeout = '60s'; 78 105 79 106 -- Тест 1: по компанија … … 82 109 WHERE company_id = 1; 83 110 84 -- Тест 2: по ниво на верност111 -- Тест 2: по ниво 85 112 EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 86 113 SELECT * FROM kbnteam.v_customer_loyalty_full_v2 87 114 WHERE tier_id = 1; 88 115 89 -- Тест 3: по к упувач (директна точка пристап)116 -- Тест 3: по клиент 90 117 EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 91 118 SELECT * FROM kbnteam.v_customer_loyalty_full_v2 92 119 WHERE customer_user_id = 1; 120 121 ROLLBACK; 93 122 }}} 94 123 95 124 === Резултати пред индексирање === 96 ||= Метрика ||= Тест 1 (company) ||= Тест 2 (tier) ||= Тест 3 (customer) || 125 126 Основната состојба ги задржува индексите на примарните клучеви и уникатните ограничувања. Останатите присутни индекси се евидентираат одделно. При споредба на заеднички индекси се користи истата почетна состојба за секој преглед. 127 128 ||= Метрика ||= Тест 1 (по компанија) ||= Тест 2 (по ниво) ||= Тест 3 (по клиент) || 97 129 || Planning Time || ___ ms || ___ ms || ___ ms || 98 130 || Execution Time || ___ ms || ___ ms || ___ ms || 99 131 || Rows Returned || ___ || ___ || ___ || 100 || customer_order scan || ___ || ___ || ___ || 132 || Начин на читање по табела || ___ || ___ || ___ || 133 || Shared Hit Blocks || ___ || ___ || ___ || 134 || Shared Read Blocks || ___ || ___ || ___ || 101 135 102 136 {{{ 103 -- Излезот од EXPLAIN ANALYZE овде (пред индексирање) 137 -- Планови и резултати пред дополнителното индексирање: 138 104 139 }}} 105 140 106 === Применети индекси === 141 === Дополнителни и заеднички индекси === 142 143 `customer_loyalty(user_id)` има уникатен индекс. Индексот по клиент и датум не го содржи order_total, па пресметката на вкупната потрошувачка и натаму бара читање на износите. 144 145 ||= Индекс ||= Табела и колони ||= Намена || 146 || `idx_customer_order_customer_user_id_order_datetime` || `customer_order(customer_user_id, order_datetime DESC)` || Пронаоѓање нарачки по клиент; поддршка за пристап по датум во рамки на клиентот. || 147 || `idx_customer_company_id` || `customer(company_id)` || Пронаоѓање клиенти по компанијата на која припаѓаат. || 148 107 149 {{{ 108 150 #!sql 109 -- indexes/v_customer_loyalty_full_v2_index.sql110 151 CREATE INDEX IF NOT EXISTS idx_customer_order_customer_user_id_order_datetime 111 152 ON kbnteam.customer_order (customer_user_id, order_datetime DESC); 153 154 CREATE INDEX IF NOT EXISTS idx_customer_company_id 155 ON kbnteam.customer (company_id); 156 }}} 157 158 === Проверка на индексите === 159 160 {{{ 161 #!sql 162 SELECT tablename, indexname, indexdef 163 FROM pg_indexes 164 WHERE schemaname = 'kbnteam' 165 AND indexname IN ( 166 'idx_customer_order_customer_user_id_order_datetime', 167 'idx_customer_company_id' 168 ) 169 ORDER BY tablename, indexname; 112 170 }}} 113 171 114 172 === Резултати по индексирање === 115 ||= Метрика ||= Тест 1 (company) ||= Тест 2 (tier) ||= Тест 3 (customer) || 173 174 Мерењето ги користи истите тест прашалници и услови како почетното мерење. 175 176 ||= Метрика ||= Тест 1 (по компанија) ||= Тест 2 (по ниво) ||= Тест 3 (по клиент) || 116 177 || Planning Time || ___ ms || ___ ms || ___ ms || 117 178 || Execution Time || ___ ms || ___ ms || ___ ms || 118 179 || Rows Returned || ___ || ___ || ___ || 119 || customer_order scan || ___ || ___ || ___ || 180 || Начин на читање по табела || ___ || ___ || ___ || 181 || Shared Hit Blocks || ___ || ___ || ___ || 182 || Shared Read Blocks || ___ || ___ || ___ || 120 183 121 184 {{{ 122 -- Излезот од EXPLAIN ANALYZE овде (по индексирање) 185 -- Планови и резултати по дополнителното индексирање: 186 123 187 }}} 124 188 125 189 === Анализа на подобрување === 126 ||= Индекс ||= Помага на ||= Очекувана промена ||127 || `idx_customer_order_customer_user_id_order_datetime` || CTE `order_stats` GROUP BY + MAX(order_datetime) || Seq Scan → Index Scan ||128 190 129 ||= Метрика ||= Пред ||= По ||= Δ Подобрување || 191 ||= Метрика ||= Пред ||= По ||= Промена (%) || 192 || Execution Time (Тест 1) || ___ ms || ___ ms || ___ % || 193 || Execution Time (Тест 2) || ___ ms || ___ ms || ___ % || 130 194 || Execution Time (Тест 3) || ___ ms || ___ ms || ___ % || 131 195 132 '''Напомена:''' Индексот е особено ефективен при Тест 3 (по купувач) бидејќи CTE-то `order_stats` може директно да го скенира по `customer_user_id`. При целосен скен (Тест 1 или 2), подобрувањето зависи од бројот на редови. 196 Промената се пресметува како 100 × (време пред − време по) / време пред, кога почетното време е поголемо од нула. Негативна вредност означува побавно извршување. 197 198 Изборот меѓу секвенцијално читање, индексно читање и други планови зависи од обемот на податоците, селективноста на филтрите и статистиките. Индексите не гарантираат забрзување или отстранување на сортирањата и спојувањата. Плановите и времињата се утврдуваат со мерење.
