Напредни извештаи од базата

Извршување на релациска алгебра во RelaX

За да ги извршите изразите на релациска алгебра прикажани во овој документ:

1. Отворете го RelaX онлајн алатката: ​https://dbis-uibk.github.io/relax/calc/local/uibk/local/0
2. Кликнете на табот Group Editor
3. Избришете го целиот текст внатре и залепете ја содржината на дадениот dataset.txt​.
4. Кликнете на копчето Preview
5. Ако табелите се појават на левиот панел, спремно е.
6. Преминете на табот Relational Algebra и извршете ги барањата

1. Тековен залиха по складиште

Цел: Прикажува вкупниот број производи и вредност на залихата по складишта.

SELECT
    w.warehouse_id,
    w.name AS warehouse_name,
    SUM(ws.quantity_on_hand) AS total_units,
    SUM(ws.quantity_on_hand * p.unit_price) AS total_stock_value
FROM warehouse_stock ws
JOIN warehouse w ON ws.warehouse_id = w.warehouse_id
JOIN product p ON ws.product_id = p.product_id
GROUP BY w.warehouse_id, w.name
ORDER BY total_stock_value DESC;

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

τ total_stock_value (
  γ warehouse_id, warehouse_name ; SUM(quantity_on_hand)->total_units, SUM(stock_value)->total_stock_value (
    π warehouse_id, warehouse_name, quantity_on_hand, quantity_on_hand * unit_price -> stock_value (
      (warehouse_stock ⋈ ρ name->warehouse_name (warehouse)) ⋈ ρ name->product_name (product)
    )
  )
)

2. Приход од продажба по производ

Цел: Прикажува кој производ носи најмногу приход.

SELECT
    p.product_id,
    p.name AS product_name,
    SUM(si.quantity) AS total_units_sold,
    SUM(si.quantity * si.unit_price_at_sale) AS total_revenue
FROM sale_item si
JOIN product p ON si.product_id = p.product_id
GROUP BY p.product_id, p.name
ORDER BY total_revenue DESC;

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

τ total_revenue (
  γ product_id, product_name ; SUM(quantity)->total_units_sold, SUM(revenue)->total_revenue (
    π product_id, product_name, quantity, quantity * unit_price_at_sale -> revenue (
      sale_item ⋈ ρ name->product_name (product)
    )
  )
)

3. Статус на нарачки за набавка

Цел: Прикажува колку е нарачано и колку примено од секој производ.

SELECT
    po.po_id,
    po.status,
    p.name AS product_name,
    poi.quantity AS ordered_quantity,
    poi.received_quantity,
    (poi.quantity - poi.received_quantity) AS pending_quantity
FROM purchase_order po
JOIN purchase_order_item poi ON po.po_id = poi.po_id
JOIN product p ON poi.product_id = p.product_id
ORDER BY po.po_id;

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

τ po_id (
  π po_id, status, product_name, quantity->ordered_quantity, received_quantity, quantity - received_quantity -> pending_quantity (
    purchase_order ⋈ purchase_order_item ⋈ ρ name->product_name (product)
  )
)

4. Продажба по категорија

Цел: Прикажува кои категории носат најголем приход.

SELECT
    c.category_id,
    c.name AS category_name,
    SUM(si.quantity * si.unit_price_at_sale) AS total_category_revenue
FROM sale_item si
JOIN product p ON si.product_id = p.product_id
JOIN category c ON p.category_id = c.category_id
GROUP BY c.category_id, c.name
ORDER BY total_category_revenue DESC;

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

τ total_category_revenue (
  γ category_id, category_name ; SUM(revenue)->total_category_revenue (
    π category_id, category_name, quantity * unit_price_at_sale -> revenue (
      sale_item ⋈ ρ name->product_name, description->product_desc (product) ⋈ ρ name->category_name, description->category_desc (category)
    )
  )
)

5. Искористеност на капацитетот на магацините

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

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;

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

τ occupancy_percentage (
  π warehouse_name, capacity->total_unit_capacity, units_in_stock, (units_in_stock / capacity) * 100 -> occupancy_percentage (
    γ warehouse_id, warehouse_name, capacity ; SUM(quantity_on_hand)->units_in_stock (
      ρ name->warehouse_name (warehouse) ⟕ warehouse_stock
    )
  )
)

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

Цел: Ги прикажува производите што не се продале во последните 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;

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

τ product_name (
  π product_name, sku, quantity_on_hand (
    (ρ name->product_name (product) ⋈ warehouse_stock)
    -
    π product_name, sku, quantity_on_hand (
      ρ name->product_name (product) ⋈ warehouse_stock ⋈ sale_item
    )
  )
)

7. Анализа на тренд на продажба и доволност на залиха

Цел: Да се анализира продажбата во последните 60 дена и врз основа на просечната дневна продажба да се пресмета дали моменталната залиха е доволна за наредните 30 дена.

WITH recent_sales AS (
    SELECT
        si.product_id,
        SUM(si.quantity) AS sold_last_60_days
    FROM sale_item si
    JOIN sale s ON si.sale_id = s.sale_id
    WHERE s.date_time >= CURRENT_DATE - INTERVAL '60 days'
    GROUP BY si.product_id
),
current_stock AS (
    SELECT
        product_id,
        SUM(quantity_on_hand) AS total_stock
    FROM warehouse_stock
    GROUP BY product_id
)
SELECT
    p.product_id,
    p.name AS product_name,
    COALESCE(rs.sold_last_60_days, 0) AS sold_last_60_days,
    ROUND(COALESCE(rs.sold_last_60_days,0)::numeric / 60, 2) AS avg_daily_sales,
    ROUND((COALESCE(rs.sold_last_60_days,0)::numeric / 60) * 30, 2) AS projected_next_30_days,
    COALESCE(cs.total_stock,0) AS current_total_stock,
    CASE
        WHEN COALESCE(cs.total_stock,0) >= 
             ((COALESCE(rs.sold_last_60_days,0)::numeric / 60) * 30)
        THEN 'SUFFICIENT'
        ELSE 'INSUFFICIENT'
    END AS stock_status
FROM product p
LEFT JOIN recent_sales rs ON p.product_id = rs.product_id
LEFT JOIN current_stock cs ON p.product_id = cs.product_id
ORDER BY stock_status, projected_next_30_days DESC;

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

τ stock_status, projected_next_30_days (
  π product_id, product_name, sold_last_60_days, avg_daily_sales, projected_next_30_days, current_total_stock, 
    (current_total_stock >= projected_next_30_days ? 'SUFFICIENT' : 'INSUFFICIENT') -> stock_status (
    π product_id, product_name, sold_last_60_days, sold_last_60_days / 60 -> avg_daily_sales, 
      (sold_last_60_days / 60) * 30 -> projected_next_30_days, current_total_stock (
      product ⟕ (
        γ product_id ; SUM(quantity)->sold_last_60_days (
          σ date_time >= CURRENT_DATE - 60 (sale_item ⋈ sale)
        )
      ) ⟕ (
        γ product_id ; SUM(quantity_on_hand)->current_total_stock (warehouse_stock)
      )
    )
  )
)

8. Годишен извештај за продажба (последни 12 месеци)

Цел: Прикажува детална анализа на продажбата за последните 12 месеци, групирана по месец, складиште, категорија и добавувач.

SELECT 
    TO_CHAR(date_trunc('month', s.date_time), 'YYYY-MM') AS sales_month,
    w.name AS warehouse_name,
    c.name AS category_name,
    sup.name AS supplier_name,
    COUNT(DISTINCT s.sale_id) AS total_order_count,
    SUM(si.quantity) AS total_units_sold,
    SUM(si.quantity * si.unit_price_at_sale) AS total_gross_revenue
FROM sale s
JOIN sale_item si ON s.sale_id = si.sale_id
JOIN product p ON si.product_id = p.product_id
LEFT JOIN category c ON p.category_id = c.category_id
LEFT JOIN supplier sup ON p.supplier_id = sup.supplier_id
JOIN warehouse w ON s.warehouse_id = w.warehouse_id
WHERE s.date_time >= date_trunc('month', CURRENT_DATE) - INTERVAL '11 months'
GROUP BY sales_month, w.name, c.name, sup.name
ORDER BY sales_month DESC, total_gross_revenue DESC;

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

γ warehouse_name, category_name, supplier_name ; COUNT(sale_id)->total_order_count, SUM(quantity)->total_units_sold, SUM(revenue)->total_gross_revenue (
  π sale_id, warehouse_name, category_name, supplier_name, quantity, quantity * unit_price_at_sale -> revenue (
    (
      (
        sale
        ⋈ sale_item
        ⋈ ρ name->product_name, description->product_desc (product)
      )
      ⟕ ρ name->category_name, description->category_desc (category)
    )
    ⟕ ρ name->supplier_name (supplier)
    ⋈ ρ name->warehouse_name (warehouse)
  )
)

9. Детален извештај за набавни нарачки

Цел: Да се прикаже детална состојба на сите набавни нарачки: статус на нарачката, добавувач, магацин, нарачана количина, примена количина и преостаната количина.

SELECT 
    po.po_id,
    po.status AS order_status,
    po.order_date,
    po.expected_delivery_date,
    po.actual_delivery_date,
    w.name AS warehouse_name,
    sup.name AS supplier_name,
    p.name AS product_name,
    poi.quantity AS ordered_qty,
    poi.received_quantity AS received_qty,
    (poi.quantity - poi.received_quantity) AS remaining_to_receive
FROM purchase_order po
JOIN purchase_order_item poi ON po.po_id = poi.po_id
JOIN product p ON poi.product_id = p.product_id
LEFT JOIN supplier sup ON po.supplier_id = sup.supplier_id
JOIN warehouse w ON po.warehouse_id = w.warehouse_id
ORDER BY po.status, po.expected_delivery_date;

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

τ status (
  π po_id, status, order_date, expected_delivery_date, warehouse_name, supplier_name, product_name, quantity->ordered_qty, received_quantity->received_qty, quantity - received_quantity -> remaining_to_receive (
    (
      (
        purchase_order
        ⋈ purchase_order_item
        ⋈ ρ name->product_name, description->product_desc, supplier_id->product_supplier_id (product)
      )
      ⟕ ρ name->supplier_name (supplier)
    )
    ⋈ ρ name->warehouse_name (warehouse)
  )
)