Материјализирани погледи и индексирање

Број на одобрени набавки (продажби) по производи и година

CREATE MATERIALIZED VIEW brojac_na_nabavki_godisno AS
SELECT 
  EXTRACT(YEAR FROM p.ProcurementDate) AS godina,
  pr.Model AS proizvod,
  COUNT(*) AS broj_nabavki
FROM procurement p
JOIN product pr ON p.ProductID = pr.ProductID
WHERE p.Status = 'Approved'
GROUP BY godina, proizvod
ORDER BY godina, proizvod DESC;

CREATE INDEX idx_brojac_na_nabavki ON brojac_na_nabavki_godisno(godina, proizvod);

Вкупен приход по модел и месец

CREATE TABLE prihod_po_model_mesec AS
SELECT 
  YEAR(p.ProcurementDate) AS godina,
  MONTH(p.ProcurementDate) AS mesec,
  pr.Model AS model,
  SUM(t.TotalPrice) AS vkupen_prihod
FROM procurement p
JOIN product pr ON p.ProductID = pr.ProductID
JOIN t_type t ON p.TransactionID = t.TransactionID
WHERE p.Status = 'Approved'
GROUP BY godina, mesec, model;