55 / 100

CTE

Umumiy jadval ifodalari

Kirish: Ushbu qo'llanmada siz PostgreSQL Common Table Expression (CTE — umumiy jadval ifodalari) yordamida ma'lumotlar bazasidan ma'lumotlarni qulay va sodda usulda so'rab olishni o'rganasiz.

PostgreSQL CTE ga kirish

CTE (Common Table Expression) — bu bitta SQL so'rovi (masalan, SELECT, INSERT, UPDATE yoki DELETE) doirasida amal qiluvchi vaqtinchalik nomlangan natijalar to'plamidir. Uni faqat shu so'rov davomida mavjud bo'ladigan virtual yoki vaqtinchalik jadval deb tasavvur qilish mumkin.

CTE ni e'lon qilishning umumiy sintaksisi:

query.sql

WITH cte_name (column_list) AS (
    -- CTE so'rovi
    SELECT ...
)
-- Asosiy so'rov
SELECT select_list
FROM cte_name;

Ushbu sintaksisda:

WITH kalit so'zi CTE yaratilishini bildiradi.
cte_name — siz yaratayotgan vaqtinchalik jadvalning nomi. Asosiy so'rovda ushbu nomga oddiy jadval kabi murojaat qilasiz.
column_list — CTE ustunlari ro'yxati (ixtiyoriy). Agar ko'rsatilmasa, CTE ichki so'rov qaytargan ustun nomlarini oladi.
CTE so'rovi — CTE tarkibini hosil qiluvchi so'rov (SELECT, INSERT, UPDATE, DELETE va hokazo).
Asosiy so'rov — CTE dan foydalanuvchi tashqi so'rov.

Oddiy PostgreSQL CTE ga misol

Quyidagi misolda barcha omborlar bo'yicha eng yuqori umumiy tovar qiymatini topish uchun CTE dan foydalaniladi:

query.sql

WITH warehouse_inventories (warehouse_name, amount) AS (
    SELECT
      warehouse_name,
      SUM(quantity * price)
    FROM
      inventories
      INNER JOIN products USING (product_id)
      INNER JOIN warehouses USING (warehouse_id)
    GROUP BY
      warehouse_name
)
SELECT
  MAX(amount)
FROM
  warehouse_inventories;

Natija:

matn

    max
------------
 2419483.10

So'rov qanday ishlaydi?

Dastlab warehouse_inventories nomli CTE yaratiladi. U uchta jadvalni (inventories, products va warehouses) birlashtirib, har bir ombor nomi va undagi tovarlarning umumiy summasini hisoblaydi:
query.sql
```
SELECT
  warehouse_name,
  SUM(quantity * price)
FROM
  inventories
  INNER JOIN products USING (product_id)
  INNER JOIN warehouses USING (warehouse_id)
GROUP BY
  warehouse_name;
```
Asosiy so'rov esa ushbu hosil bo'lgan warehouse_inventories natijasidan MAX(amount) ni tanlab oladi.

DELETE va INSERT amallarida CTE dan foydalanish

CTE nafaqat ma'lumotlarni o'qishda, balki o'chirilgan ma'lumotlarni arxivlashda ham juda qo'l keladi.

Dastlab o'chirilgan mahsulotlar tarixini saqlovchi product_logs jadvalini yaratamiz:

query.sql

CREATE TABLE product_logs (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_id INT NOT NULL,
  product_name VARCHAR(100) NOT NULL,
  price DECIMAL(11, 2),
  brand_id INT NOT NULL,
  category_id INT NOT NULL,
  deleted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Endi products jadvalidan identifikatori 3 dan kichik yoki teng bo'lgan mahsulotlarni o'chiramiz va shu zahotiyoq ularni product_logs jadvaliga joylaymiz:

query.sql

WITH deleted_products AS (
    DELETE FROM products
    WHERE product_id <= 3 
    RETURNING *
)
INSERT INTO
  product_logs (product_id, product_name, price, brand_id, category_id)
SELECT
  product_id, product_name, price, brand_id, category_id 
FROM
  deleted_products;

Bu yerda:

deleted_products CTE si DELETE buyrug'ini bajaradi va RETURNING * orqali o'chirilgan qatorlarni qaytaradi.
Asosiy INSERT INTO so'rovi esa shu o'chirilgan ma'lumotlarni arxiv jadvaliga yozadi. Bularning barchasi bitta atomik so'rovda bajariladi!

Xulosa

Vaqtinchalik natijalar to'plamini aniqlash uchun WITH kalit so'zi bilan CTE dan foydalaning.
CTE murakkab ichma-ich so'rovlarni o'qilishi oson va tushunarli bo'laklarga ajratishga yordam beradi.
CTE ichida RETURNING bilan birga DELETE, UPDATE yoki INSERT buyruqlarini ham bajarish mumkin.

Oldingi

Keyingi