Changes between Version 3 and Version 4 of AdvancedAppDevelopment
- Timestamp:
- 09/17/25 19:50:31 (4 weeks ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
AdvancedAppDevelopment
v3 v4 9 9 == Индекси 10 10 11 Заради забрзување на пристап и пребарување низ базата, креирани се индекси на секој надворешен клуч во секој ентитет. 11 === Индекси на надворешни клучеви 12 12 13 13 {{{#!sql … … 41 41 }}} 42 42 43 == Процедури 43 === Индекси за пребарувања кои содржат датуми 44 45 {{{#!sql 46 create index idx_orders_date on orders(ord_date); 47 create index idx_price_art_effdate on price(art_id, price_eff_date); 48 create index idx_orders_date_status on orders(ord_date, o_status_id); 49 }}} 50 51 === Индекси за подобрување на перформанси при join-ови 52 53 {{{#!sql 54 create index idx_unitprice_composite on unit_price(unit_id, price_id); 55 create index idx_article_unit_wh_ord on article_unit(wh_id, ord_id); 56 create index idx_price_art_date_price on price(art_id, price_eff_date, price); 57 }}} 58 59 === Индекси за агрегација и филтрирање 60 61 {{{#!sql 62 create index idx_orders_date_extract on orders(ord_date); 63 create index idx_article_unit_cost on article_unit(unit_cost_price); 64 create index idx_orders_sum_status on orders(ord_sum, o_status_id); 65 }}} 66 67 === Индекси специфични за функции 68 69 {{{#!sql 70 create index idx_orders_week on orders(ord_date); 71 create index idx_article_category on article(ctg_id, art_id); 72 create index idx_article_unit_cost_wh on article_unit(wh_id, unit_cost_price); 73 }}} 74 75 == Функции 44 76 45 77 === Промена на број на продажби на секој артикл при промена на цена 46 78 47 79 {{{#!sql 48 create procedure ArticleSalesChangeOnPriceChange() 49 language plpgsql 50 as $$ 51 begin 80 create or replace function ArticleSalesChangeOnPriceChange() 81 returns table( 82 articleName varchar(128), 83 unitPrice decimal(10,2), 84 priceEffectiveDate timestamp, 85 units_sold bigint, 86 revenue decimal(10,2) 87 ) 88 language plpgsql 89 as $$ 90 begin 91 return query 52 92 with PriceChanges as ( 53 93 select … … 79 119 join FilteredPrice fp on p.art_id = fp.art_id and p.price_eff_date = fp.price_eff_date 80 120 where o.ord_date >= fp.price_eff_date and o.ord_date < coalesce( 81 (select min(fp2.price_eff_date)82 from FilteredPrice fp283 where fp2.art_id = fp.art_id and fp2.price_eff_date > fp.price_eff_date),84 '9999-12-31'121 (select min(fp2.price_eff_date) 122 from FilteredPrice fp2 123 where fp2.art_id = fp.art_id and fp2.price_eff_date > fp.price_eff_date), 124 '9999-12-31' 85 125 ) 86 126 group by p.art_id, fp.price, fp.price_eff_date … … 102 142 103 143 {{{#!sql 104 create procedure SalesComparisonByCategoryAndRegionInQuantityAndIncome() 105 language plpgsql 106 as $$ 107 begin 144 create or replace function SalesComparisonByCategoryAndRegionInQuantityAndIncome() 145 returns table( 146 region varchar(128), 147 category varchar(128), 148 totalUnitsSold bigint, 149 totalIncome decimal(10,2) 150 ) 151 language plpgsql 152 as $$ 153 begin 154 return query 108 155 with ArticleSales as ( 109 156 select a.art_id, … … 124 171 join orders o on au.ord_id = o.ord_id 125 172 where o.ord_date>=p.price_eff_date 126 group by a.art_id, a.ctg_id, w.wh_id, c.city_id, r.region_id, p.price , p.price173 group by a.art_id, a.ctg_id, w.wh_id, c.city_id, r.region_id, p.price 127 174 ) 128 175 select r.region_name as region, … … 142 189 143 190 {{{#!sql 144 create procedure SalesPrognosingOnArticlesAccToSalesInPreviousWeeks() 145 language plpgsql 146 as $$ 147 begin 191 create or replace function SalesPrognosingOnArticlesAccToSalesInPreviousWeeks() 192 returns table( 193 art_id bigint, 194 art_name varchar(128), 195 currentStock bigint, 196 avg_units_sold numeric, 197 remaining_after_forecast numeric 198 ) 199 language plpgsql 200 as $$ 201 begin 202 return query 148 203 with WeeklySales as ( 149 204 select a.art_id, … … 192 247 193 248 {{{#!sql 194 create procedure ArticleSalesInWarehouseByQuarters() 195 language plpgsql 196 as $$ 197 begin 249 create or replace function ArticleSalesInWarehouseByQuarters() 250 returns table( 251 warehouseId bigint, 252 warehouseAddress varchar(128), 253 warehouseCity varchar(128), 254 warehouseRegion varchar(128), 255 "year" double precision, 256 "quarter" double precision, 257 totalArticlesSold bigint, 258 totalSales decimal(10,2) 259 ) 260 language plpgsql 261 as $$ 262 begin 263 return query 198 264 select w.wh_id as warehouseId, 199 265 w.wh_adr as warehouseAddress, … … 218 284 219 285 {{{#!sql 220 create procedure YearlyIncomeProfitAndExpensesByWarehouses() 221 language plpgsql 222 as $$ 223 begin 286 create or replace function YearlyIncomeProfitAndExpensesByWarehouses() 287 returns table( 288 warehouseId bigint, 289 warehouseCity varchar(128), 290 warehouseRegion varchar(128), 291 "year" double precision, 292 totalIncome decimal(10,2), 293 total_profit decimal(10,2) 294 ) 295 language plpgsql 296 as $$ 297 begin 298 return query 224 299 select w.wh_id as warehouseId, 225 300 c.city_name as warehouseCity, … … 233 308 join article_unit au on w.wh_id = au.wh_id 234 309 join orders o on au.ord_id = o.ord_id 235 group by w.wh_id, c.city_name, r.region_name, o.ord_date310 group by w.wh_id, c.city_name, r.region_name, extract(year from o.ord_date) 236 311 order by w.wh_id,"year"; 237 312 end; … … 242 317 243 318 {{{#!sql 244 create procedure AnalyticsOfProductsByIncomeExpensesAndProfit() 245 language plpgsql 246 as $$ 247 begin 319 create or replace function AnalyticsOfProductsByIncomeExpensesAndProfit() 320 returns table( 321 art_id bigint, 322 art_name varchar(128), 323 category varchar(128), 324 totalUnitsSold bigint, 325 totalIncome numeric, 326 totalCost numeric, 327 totalProfit numeric 328 ) 329 language plpgsql 330 as $$ 331 begin 332 return query 248 333 with ProductSales as (select a.art_id, 249 334 a.art_name,
