Напредни извештаи од базата (SQL, складирани процедури и релациона алгебра)

---

1. ABC Анализа на производи (ABC Product Analysis)

Овој извештај ги дели производите во сегменти A (80%), B (15%) и C (5%) според нивниот придонес во вкупниот промет, користејќи ги овие проценти за да се прикаже кои производи се најважни за приходот и да се олесни управувањето со залихите.

WITH ProductRevenue AS (
    SELECT 
        p.name AS product_name, 
        SUM(si.quantity * si.unit_price_at_sale) AS total_revenue
    FROM product p
    JOIN sale_item si ON p.product_id = si.product_id
    GROUP BY p.name
),
RevenueCalculations AS (
    SELECT 
        product_name, 
        total_revenue,
        SUM(total_revenue) OVER (ORDER BY total_revenue DESC) / SUM(total_revenue) OVER () AS cumulative_share
    FROM ProductRevenue
)
SELECT 
    product_name, 
    ROUND(total_revenue, 2) AS revenue_mkd,
    CASE 
        WHEN cumulative_share <= 0.80 THEN 'A (High Value)'
        WHEN cumulative_share <= 0.95 THEN 'B (Medium Value)'
        ELSE 'C (Low Value)' 
    END AS abc_classification
FROM RevenueCalculations;

Релациона алгебра:

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)

---

2. Месечен тренд на продажба (Monthly Sales Growth Trend)

Ги споредува приходите од тековниот месец со претходниот за да го пресмета процентот на раст или пад на бизнисот.

WITH MonthlySales AS (
    SELECT 
        DATE_TRUNC('month', date_time) AS sales_month, 
        SUM(total_amount) AS revenue
    FROM sale 
    GROUP BY 1
)
SELECT 
    sales_month, 
    revenue AS current_month_revenue,
    LAG(revenue) OVER (ORDER BY sales_month) AS previous_month_revenue,
    ROUND(((revenue - LAG(revenue) OVER (ORDER BY sales_month)) / 
           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))

---

3. Состојба на залихи и критични точки (Inventory Health & Reorder Levels)

Ги идентификува производите што се под минимумот во магацините и дава аларм за итна набавка.

SELECT 
    p.name AS product_name, 
    w.name AS warehouse_location, 
    ws.quantity_on_hand, 
    p.reorder_level,
    CASE 
        WHEN ws.quantity_on_hand <= p.reorder_level THEN 'CRITICAL: REORDER'
        WHEN ws.quantity_on_hand <= p.reorder_level * 1.5 THEN 'WARNING: LOW'
        ELSE 'HEALTHY' 
    END AS stock_status
FROM product p
JOIN warehouse_stock ws ON p.product_id = ws.product_id
JOIN warehouse w ON ws.warehouse_id = w.warehouse_id;

Релациона алгебра:

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)

---

4. Перформанси на добавувачите (Supplier Performance Matrix)

Го мери доцнењето на испораките од добавувачите и ја пресметува нивната доверливост во исполнување на роковите.

SELECT 
    s.name AS supplier_name,
    COUNT(po.po_id) AS total_orders,
    AVG(po.actual_delivery_date - po.expected_delivery_date) AS average_delay_days,
    ROUND(COUNT(CASE WHEN po.actual_delivery_date <= po.expected_delivery_date THEN 1 END) * 100.0 / COUNT(*), 2) AS on_time_delivery_rate
FROM supplier s
JOIN purchase_order po ON s.supplier_id = po.supplier_id
WHERE po.status = 'DELIVERED'
GROUP BY s.name;

Релациона алгебра:

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)

Ги сегментира купувачите според тоа кога последен пат купувале, колку често се враќаат и колку пари потрошиле.

SELECT 
    c.name AS customer_name,
    EXTRACT(DAY FROM CURRENT_TIMESTAMP - MAX(s.date_time)) AS recency_days,
    COUNT(s.sale_id) AS frequency_count,
    SUM(s.total_amount) AS monetary_total,
    NTILE(5) OVER (ORDER BY MAX(s.date_time)) AS r_score,
    NTILE(5) OVER (ORDER BY COUNT(s.sale_id)) AS f_score,
    NTILE(5) OVER (ORDER BY SUM(s.total_amount)) AS m_score
FROM customer c
JOIN sale s USING (customer_id)
GROUP BY c.customer_id, c.name;

Релациона алгебра:

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)

---

6. Искористеност на капацитетот на магацините (Warehouse Utilization Rate)

Пресметува колкав процент од физичкиот простор во секој магацин е моментално пополнет.

SELECT 
    w.name AS warehouse_name, 
    w.capacity AS total_unit_capacity,
    SUM(ws.quantity_on_hand) AS units_in_stock,
    ROUND((SUM(ws.quantity_on_hand)::NUMERIC / w.capacity) * 100, 2) AS occupancy_percentage
FROM warehouse w
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)

---

7. Анализа на застојни производи

Ги прикажува производите што не се продале во последните 90 дена и само зафаќаат простор и капитал.

SELECT 
    p.name AS product_name, 
    p.sku, 
    ws.quantity_on_hand, 
    COALESCE(MAX(s.date_time)::TEXT, 'NO SALES RECORDED') AS last_sold_date
FROM product p
JOIN warehouse_stock ws ON p.product_id = ws.product_id
LEFT JOIN sale_item si ON p.product_id = si.product_id
LEFT JOIN sale s ON si.sale_id = s.sale_id
GROUP BY p.product_id, p.name, p.sku, ws.quantity_on_hand
HAVING MAX(s.date_time) < CURRENT_DATE - INTERVAL '90 days' OR MAX(s.date_time) IS NULL;

Релациона алгебра:

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))

---

8. Маржа на профит по категорија (Category Profit Margin)

Ја пресметува разликата помеѓу продажната и набавната цена за секоја категорија производи.

WITH ProductCosts AS (
    SELECT product_id, AVG(unit_cost) AS average_cost FROM purchase_order_item GROUP BY 1
)
SELECT 
    c.name AS category_name,
    SUM(si.quantity * si.unit_price_at_sale) AS gross_revenue,
    SUM(si.quantity * pc.average_cost) AS total_cost_of_goods,
    ROUND(((SUM(si.quantity * si.unit_price_at_sale) - SUM(si.quantity * pc.average_cost)) / 
           NULLIF(SUM(si.quantity * si.unit_price_at_sale), 0)) * 100, 2) AS profit_margin_pct
FROM category c
JOIN product p USING (category_id)
JOIN sale_item si USING (product_id)
JOIN ProductCosts pc USING (product_id)
GROUP BY c.name;

Релациона алгебра:

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)

---

9. Анализа на „пазарна кошничка" (Product Affinity / Market Basket)

Открива кои производи купувачите најчесто ги купуваат заедно во иста трансакција.

SELECT 
    p1.name AS product_a, 
    p2.name AS product_b, 
    COUNT(*) AS pair_occurrence_count
FROM sale_item si1
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
GROUP BY 1, 2
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))

---

10. Финансиска вредност на залихите (Stock Asset Valuation)

Дава точен преглед на вкупната парична вредност на производите во магацините врз основа на последните набавни цени.

SELECT 
    w.name AS warehouse_location,
    SUM(ws.quantity_on_hand * (
        SELECT unit_cost FROM purchase_order_item poi 
        WHERE poi.product_id = ws.product_id 
        ORDER BY po_id DESC LIMIT 1
    )) AS total_inventory_valuation_mkd
FROM warehouse w
JOIN warehouse_stock ws USING (warehouse_id)
GROUP BY w.name;

Релациона алгебра:

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)