Changes between Version 1 and Version 2 of AdvancedReports

Timestamp:

01/15/26 07:25:19 (12 days ago)

Author:

221181

Comment:

--

AdvancedReports

@@ -33 +33 @@
 }}}
 
+'''Релациона алгебра:'''
+{{{
+ProductRevenue ← γ name; SUM(quantity × unit_price_at_sale) → total_revenue (product ⨝ product_id sale_item)
+
+RevenueCalculations ← π product_name, total_revenue, 
+                        (RunningSum(total_revenue) / TotalSum(total_revenue)) → cumulative_share 
+                        (τ total_revenue DESC (ProductRevenue))
+
+Result ← π product_name, 
+           ROUND(total_revenue, 2) → revenue_mkd,
+           CASE(cumulative_share ≤ 0.80 → 'A', 
+                cumulative_share ≤ 0.95 → 'B', 
+                ELSE → 'C') → abc_classification
+           (RevenueCalculations)
+}}}
+
 ----
 
@@ -54 +70 @@
            NULLIF(LAG(revenue) OVER (ORDER BY sales_month), 0)) * 100, 2) AS growth_percentage
 FROM MonthlySales;
+}}}
+
+'''Релациона алгебра:'''
+{{{
+MonthlySales ← γ DATE_TRUNC('month', date_time) → sales_month; 
+                 SUM(total_amount) → revenue (sale)
+
+Result ← π sales_month, 
+           revenue → current_month_revenue,
+           LAG(revenue) → previous_month_revenue,
+           ROUND(((revenue - LAG(revenue)) / NULLIF(LAG(revenue), 0)) × 100, 2) → growth_percentage
+           (τ sales_month ASC (MonthlySales))
 }}}
 
@@ -78 +106 @@
 }}}
 
+'''Релациона алгебра:'''
+{{{
+Temp1 ← product ⨝ product_id=product_id warehouse_stock
+
+Temp2 ← Temp1 ⨝ warehouse_id=warehouse_id warehouse
+
+Result ← π product.name → product_name,
+           warehouse.name → warehouse_location,
+           quantity_on_hand,
+           reorder_level,
+           CASE(quantity_on_hand ≤ reorder_level → 'CRITICAL: REORDER',
+                quantity_on_hand ≤ reorder_level × 1.5 → 'WARNING: LOW',
+                ELSE → 'HEALTHY') → stock_status
+           (Temp2)
+}}}
+
 ----
 
@@ -96 +140 @@
 }}}
 
+'''Релациона алгебра:'''
+{{{
+Temp1 ← supplier ⨝ supplier_id=supplier_id purchase_order
+
+Temp2 ← σ status='DELIVERED' (Temp1)
+
+Result ← γ supplier.name → supplier_name;
+           COUNT(po_id) → total_orders,
+           AVG(actual_delivery_date - expected_delivery_date) → average_delay_days,
+           ROUND(COUNT(actual_delivery_date ≤ expected_delivery_date) × 100.0 / COUNT(*), 2) → on_time_delivery_rate
+           (Temp2)
+}}}
+
 ----
 
 == 5. RFM Анализа на клиенти (Customer RFM Segmentation) ==
 
-Ги сегментира купувачите според тоа кога последен пат купувале, колку често се враќаат и колку пари потрошиле.
+Ги сегментира купувачите volgens тоа кога последен пат купувале, колку често се враќаат и колку пари потрошиле.
 
 {{{#!sql
@@ -116 +173 @@
 }}}
 
+'''Релациона алгебра:'''
+{{{
+Temp1 ← customer ⨝ customer_id=customer_id sale
+
+Result ← γ customer_id, name → customer_name;
+           EXTRACT(DAY FROM CURRENT_TIMESTAMP - MAX(date_time)) → recency_days,
+           COUNT(sale_id) → frequency_count,
+           SUM(total_amount) → monetary_total,
+           NTILE(5, MAX(date_time)) → r_score,
+           NTILE(5, COUNT(sale_id)) → f_score,
+           NTILE(5, SUM(total_amount)) → m_score
+           (Temp1)
+}}}
+
 ----
 
@@ -131 +202 @@
 LEFT JOIN warehouse_stock ws ON w.warehouse_id = ws.warehouse_id
 GROUP BY w.warehouse_id, w.name, w.capacity;
+}}}
+
+'''Релациона алгебра:'''
+{{{
+Temp1 ← warehouse ⟕ warehouse_id=warehouse_id warehouse_stock
+
+Result ← γ warehouse_id, name → warehouse_name, capacity → total_unit_capacity;
+           SUM(quantity_on_hand) → units_in_stock,
+           ROUND((SUM(quantity_on_hand) / capacity) × 100, 2) → occupancy_percentage
+           (Temp1)
 }}}
 
@@ -153 +234 @@
 }}}
 
+'''Релациона алгебра:'''
+{{{
+Temp1 ← product ⨝ product_id=product_id warehouse_stock
+
+Temp2 ← Temp1 ⟕ product_id=product_id sale_item
+
+Temp3 ← Temp2 ⟕ sale_id=sale_id sale
+
+Temp4 ← γ product_id, name, sku, quantity_on_hand; 
+          MAX(date_time) → last_sold_date (Temp3)
+
+Result ← π name → product_name, sku, quantity_on_hand,
+           COALESCE(last_sold_date::TEXT, 'NO SALES RECORDED') → last_sold_date
+           (σ last_sold_date < CURRENT_DATE - 90 ∨ last_sold_date IS NULL (Temp4))
+}}}
+
 ----
 
@@ -176 +273 @@
 }}}
 
+'''Релациона алгебра:'''
+{{{
+ProductCosts ← γ product_id; AVG(unit_cost) → average_cost (purchase_order_item)
+
+Temp1 ← category ⨝ category_id=category_id product
+
+Temp2 ← Temp1 ⨝ product_id=product_id sale_item
+
+Temp3 ← Temp2 ⨝ product_id=product_id ProductCosts
+
+Result ← γ category.name → category_name;
+           SUM(quantity × unit_price_at_sale) → gross_revenue,
+           SUM(quantity × average_cost) → total_cost_of_goods,
+           ROUND(((SUM(quantity × unit_price_at_sale) - SUM(quantity × average_cost)) / 
+                  NULLIF(SUM(quantity × unit_price_at_sale), 0)) × 100, 2) → profit_margin_pct
+           (Temp3)
+}}}
+
 ----
 
@@ -188 +303 @@
     COUNT(*) AS pair_occurrence_count
 FROM sale_item si1
-JOIN sale_item si2 ON si1.sale_id = si2.sale_id AND si1.product_id < си2.product_id
+JOIN sale_item si2 ON si1.sale_id = si2.sale_id AND si1.product_id < si2.product_id
 JOIN product p1 ON si1.product_id = p1.product_id
 JOIN product p2 ON si2.product_id = p2.product_id
@@ -194 +309 @@
 ORDER BY pair_occurrence_count DESC 
 LIMIT 10;
+}}}
+
+'''Релациона алгебра:'''
+{{{
+Temp1 ← ρ si1 (sale_item)
+
+Temp2 ← ρ si2 (sale_item)
+
+Temp3 ← σ si1.sale_id = si2.sale_id ∧ si1.product_id < si2.product_id (Temp1 × Temp2)
+
+Temp4 ← Temp3 ⨝ si1.product_id = p1.product_id (ρ p1 (product))
+
+Temp5 ← Temp4 ⨝ si2.product_id = p2.product_id (ρ p2 (product))
+
+Temp6 ← γ p1.name → product_a, p2.name → product_b; 
+          COUNT(*) → pair_occurrence_count (Temp5)
+
+Result ← δ 10 (τ pair_occurrence_count DESC (Temp6))
 }}}
 
@@ -215 +348 @@
 }}}
 
-----
+'''Релациона алгебра:'''
+{{{
+LatestCost(product_id) ← π product_id, unit_cost 
+                         (δ 1 (τ po_id DESC (purchase_order_item)))
+
+Temp1 ← warehouse ⨝ warehouse_id=warehouse_id warehouse_stock
+
+Temp2 ← Temp1 ⨝ product_id=product_id LatestCost
+
+Result ← γ warehouse.name → warehouse_location;
+           SUM(quantity_on_hand × unit_cost) → total_inventory_valuation_mkd
+           (Temp2)
+}}}
+
+----
+
+'''Символи и операции:'''
+* π - проекција (SELECT)
+* σ - селекција (WHERE)
+* ⨝ - природен спој (JOIN)
+* ⟕ - left outer join (LEFT JOIN)
+* × - Декартов производ (CROSS JOIN)
+* γ - групирање (GROUP BY)
+* τ - сортирање (ORDER BY)
+* δ - лимит (LIMIT)
+* ρ - преименување (AS / alias)
+* ∧ - логичко И (AND)
+* ∨ - логичко ИЛИ (OR)