Window функцүүд

Window функц бол PostgreSQL-н хамгийн хүчирхэг боломжуудын нэг юм. GROUP BY-аас ялгаатай нь window функц мөрүүдийг нэгтгэн нэг үр дүн буцаадаггүй — мөр тус бүрийг хэвээр нь үлдээж, зэрэгцээ мөрүүдийн утгыг харгалзан тооцоо хийдэг. Эхэндээ ойлгоход хэцүү санагдаж болно, гэхдээ жишээнүүдийг дагаж явбал тодорхой болно.

OVER() — window функцийн үндэс

OVER() түлхүүр үг нь нэгтгэх функцийг window функц болгон хувиргадаг. Хаалт дотор юу ч бичихгүй бол бүх мөрүүдийн дээгүүр нэг "цонх" тавина:

-- products хүснэгтийн мөр бүрд дундаж үнийг харуулах
SELECT
    name,
    price,
    ROUND(AVG(price) OVER (), 0) AS нийт_дундаж,
    price - ROUND(AVG(price) OVER (), 0) AS зөрүү
FROM products;

GROUP BY ашиглавал бүлэглэгдэж нэг мөр болох байсан — гэвч OVER() бол эх мөрүүдийг хэвээр нь хадгалж бүх баганыг харуулна.

PARTITION BY — бүлгийн дотор тооцоо

PARTITION BY нь GROUP BY-тай ижил бүлэглэлт хийдэг ч мөрүүдийг устгахгүй:

-- Категори тус бүрт дундаж үнийг мөр бүрд харуулах
SELECT
    name,
    category,
    price,
    ROUND(AVG(price) OVER (PARTITION BY category), 0) AS категорийн_дундаж
FROM products
ORDER BY category, price;

Үр дүн: 'электроник' бараануудад нэг дундаж, 'хувцас' бараануудад өөр дундаж харагдана — гэхдээ бүх мөр хэвээр байна.

ORDER BY OVER() — дараалсан тооцоо

OVER() дотор ORDER BY нэмбэл тухайн мөр хүртэлх дүнгийн нийлбэр (running total) гэх мэт тооцоо хийж болно:

-- Бараануудыг үнээр эрэмбэлж, дүнгийн нийлбэрийг харуулах
SELECT
    name,
    price,
    SUM(price) OVER (ORDER BY price) AS хуримтлагдсан_дүн
FROM products
ORDER BY price;

ROW_NUMBER, RANK, DENSE_RANK

Эдгээр нь мөр тус бүрт дугаар буюу эрэмбэ өгдөг тусгай window функцүүд юм:

SELECT
    name,
    category,
    price,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS дугаар,
    RANK()       OVER (PARTITION BY category ORDER BY price DESC) AS зэрэглэл,
    DENSE_RANK() OVER (PARTITION BY category ORDER BY price DESC) AS нягт_зэрэглэл
FROM products;

Гурвын ялгаа:

• ROW_NUMBER() — ижил утга байсан ч давтагдахгүй дараалсан дугаар өгнө
• RANK() — ижил утга тэнцүү зэрэглэл авах ба дараагийн зэрэглэл алгасна (1, 1, 3...)
• DENSE_RANK() — ижил утга тэнцүү зэрэглэл авах ч алгасахгүй (1, 1, 2...)

Категори тус бүрээс хамгийн үнэтэй барааг олоход маш хялбар:

-- Категори тус бүрийн хамгийн үнэтэй барааг олох
WITH ranked AS (
    SELECT
        name,
        category,
        price,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS n
    FROM products
)
SELECT name, category, price
FROM ranked
WHERE n = 1;

LAG ба LEAD — өмнөх, дараагийн мөрийн утга

LAG() нь өмнөх мөрийн, LEAD() нь дараагийн мөрийн утгыг авна:

SELECT
    name,
    price,
    LAG(price)  OVER (ORDER BY price) AS өмнөх_үнэ,
    LEAD(price) OVER (ORDER BY price) AS дараагийн_үнэ,
    price - LAG(price) OVER (ORDER BY price) AS өөрчлөлт
FROM products
ORDER BY price;

Энэ нь цаг цувааны өгөгдөл шинжлэхэд — жишээлбэл өдөр тус бүрийн борлуулалтын өсөлт, бууралтыг тооцоход — маш хэрэгтэй.

Дараагийн хичээлд:

Асуулгыг хурдасгадаг индекс-ийн үндсийг судалж, яагаад их өгөгдөл дээр индексгүй асуулга удаан болдгийг ойлгоно.