PostgreSQL / CTE (WITH хуваарилагч)

CTE (WITH хуваарилагч)

Subquery-г FROM дотор ашиглах үед асуулга урт, уншихад хэцүү болдог. CTE (Common Table Expression) бол subquery-г асуулгын эхэнд нэрлэн тодорхойлж, дараа нь нэрлэж дуудах боломж олгодог. Код нь хамаагүй цэвэр, ойлгоход хялбар болдог.

CTE-г ямар үед ашиглах вэ?

Доорх байдлаар асуулга ажиллагааны хувьд адилхан ч CTE нь уншихад илүү тод:

sql

-- Subquery хувилбар (уншихад хэцүү)
SELECT name, price
FROM (
    SELECT name, price, AVG(price) OVER () AS avg_p
    FROM products
) AS sub
WHERE price > avg_p;

-- CTE хувилбар (уншихад хялбар)
WITH avg_price AS (
    SELECT AVG(price) AS avg_p FROM products
)
SELECT name, price
FROM products, avg_price
WHERE price > avg_p;

WITH синтакс

CTE-ийн бүтэц:

sql

WITH cte_нэр AS (
    -- Энд дотоод SELECT бичнэ
    SELECT ...
)
SELECT *
FROM cte_нэр;

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

sql

WITH category_max AS (
    SELECT category, MAX(price) AS max_price
    FROM products
    GROUP BY category
)
SELECT p.name, p.price, p.category
FROM products p
JOIN category_max cm
  ON p.category = cm.category AND p.price = cm.max_price
ORDER BY p.price DESC;

CTE (category_max) нь эхлээд гүйцэтгэгдэж, дараа нь үндсэн асуулгад хүснэгт шиг JOIN хийгдэнэ.

Олон CTE-г дараалан ашиглах

Нэг асуулгад таслалаар тусгаарлаж хэд хэдэн CTE тодорхойлж болно:

sql

WITH
-- 1. Категори тус бүрийн нийт борлуулалт
category_sales AS (
    SELECT
        p.category,
        SUM(o.quantity * p.price) AS нийт_орлого
    FROM orders o
    JOIN products p ON o.product_id = p.id
    GROUP BY p.category
),
-- 2. Нийт орлогыг тооц
total AS (
    SELECT SUM(нийт_орлого) AS нийт FROM category_sales
)
-- 3. Хувь хэмжээг харуулах
SELECT
    cs.category,
    cs.нийт_орлого,
    ROUND(cs.нийт_орлого * 100.0 / t.нийт, 1) AS хувь
FROM category_sales cs, total t
ORDER BY хувь DESC;

Дараагийн CTE нь өмнөх CTE-г нэрээр нь ашиглаж болно — энэ нь нарийн тооцооны алхмуудыг нэг нэгээр нь задлан бичихэд тустай.

Recursive CTE — давтагдах бүтэц

CTE нь өөрийгөө дуудах (recursive) боломжтой. Энэ нь мод, шаталсан бүтэц гэх мэт өгөгдлийг боловсруулахад хэрэгтэй:

sql

-- 1-ээс 10 хүртэлх тоо үүсгэх
WITH RECURSIVE цуваа AS (
    SELECT 1 AS n           -- Эхлэх утга
    UNION ALL
    SELECT n + 1            -- Давтагдах хэсэг
    FROM цуваа
    WHERE n < 10            -- Зогсоох нөхцөл
)
SELECT n FROM цуваа;

Recursive CTE нь гурван хэсгээс бүрдэнэ: эхлэх утга → UNION ALL → давтагдах хэсэг. WHERE нөхцөлгүйгээр төгсгөлгүй давтагдах тул заавал зогсоох нөхцөл бичих ёстой.

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

GROUP BY-с огт өөр аргаар мөр тус бүр дээр тооцоо хийдэг window function-уудыг сурна — нэгтгэх функцүүдийн хамгийн хүчирхэг хувилбар.

← Өмнөх хичээлДэд асуулга (Subquery)Дараах хичээл →Window функцүүд