SQL VIEW va MATERIALIZED VIEW

View — saqlangan so'rov bo'lib, jadval kabi ishlatiladi.

Oddiy VIEW

-- View yaratish
CREATE VIEW yuqori_ballilar AS
SELECT
    t.ism,
    t.email,
    k.nom AS kurs,
    t.ball
FROM talabalar t
JOIN kurslar k ON t.kurs_id = k.id
WHERE t.ball >= 80;

-- View ni ishlatish
SELECT * FROM yuqori_ballilar;
SELECT * FROM yuqori_ballilar WHERE kurs = 'Python';

View ni yangilash

CREATE OR REPLACE VIEW yuqori_ballilar AS
SELECT
    t.ism,
    t.email,
    k.nom AS kurs,
    t.ball,
    t.shahar  -- yangi ustun qo'shildi
FROM talabalar t
JOIN kurslar k ON t.kurs_id = k.id
WHERE t.ball >= 80;

VIEW orqali ma'lumot kiritish

-- Oddiy (bir jadvalga) VIEW larga INSERT/UPDATE mumkin
CREATE VIEW toshkent_talabalari AS
SELECT * FROM talabalar WHERE shahar = 'Toshkent';

INSERT INTO toshkent_talabalari (ism, email, shahar)
VALUES ('Sherali', 'sh@mail.uz', 'Toshkent');

MATERIALIZED VIEW

Oddiy view har safar hisoblaydi, materialized view natijani saqlaydi.

-- Yaratish
CREATE MATERIALIZED VIEW oylik_statistika AS
SELECT
    DATE_TRUNC('month', yaratildi) AS oy,
    COUNT(*) AS yangi_talabalar,
    AVG(ball) AS o'rta_ball
FROM talabalar
GROUP BY DATE_TRUNC('month', yaratildi);

-- Yangilash (qo'lda)
REFRESH MATERIALIZED VIEW oylik_statistika;

-- Yangilash (blokirovkasiz)
REFRESH MATERIALIZED VIEW CONCURRENTLY oylik_statistika;

View ni o'chirish

DROP VIEW yuqori_ballilar;
DROP VIEW IF EXISTS yuqori_ballilar CASCADE;
DROP MATERIALIZED VIEW oylik_statistika;

Amaliy foydalanish

-- Murakkab hisobot uchun view
CREATE VIEW moliyaviy_hisobot AS
SELECT
    k.nom AS kurs,
    COUNT(t.id) AS talabalar_soni,
    SUM(t.to'lov) AS umumiy_daromad,
    AVG(t.to'lov) AS o'rta_to'lov,
    MIN(t.yaratildi) AS birinchi_yozilish
FROM kurslar k
LEFT JOIN talabalar t ON k.id = t.kurs_id
GROUP BY k.id, k.nom;