როგორ მუშაობს CTE-ები და რეკურსიული შეკითხვები PostgreSQL-ში?

Question

Accepted Answer

**CTE** (Common Table Expression), რომელიც განისაზღვრება **`WITH`**-ით, არის დასახელებული დროებითი შედეგების ნაკრები, რომელიც რთული შეკითხვებს წაკითხვადი ხდის. PostgreSQL სრულად მხარს უჭერს CTE-ებს, მათ შორის **რეკურსიულ** CTE-ებს (`WITH RECURSIVE`) იერარქიული/ხის მონაცემების გასავლელად.

## ძირითადი CTE-ები — წაკითხვადი შეკითხვების აგება

```sql
WITH active_users AS (
  SELECT id, name FROM users WHERE active = true
),
order_counts AS (
  SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id
)
SELECT u.name, oc.cnt
FROM active_users u
JOIN order_counts oc ON u.id = oc.user_id;   -- compose named steps cleanly
```

CTE-ები რთულ შეკითხვას დამატებით ეტაპებად ყოფენ (ჯაჭვიანი, თითოეული წინა ელემენტებზე ორიენტირებული) — გაცილებით უფრო ნათელი, ვიდრე ჩასმული ქვეშეკითხვები.

## რეკურსიული CTE-ები — იერარქიების გასავლელი

```sql
-- find all subordinates under employee 1 (an org-chart hierarchy)
WITH RECURSIVE subordinates AS (
  -- BASE CASE: the starting point
  SELECT id, name, manager_id FROM employees WHERE id = 1
  UNION ALL
  -- RECURSIVE STEP: find employees whose manager is in the result so far
  SELECT e.id, e.name, e.manager_id
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;          -- the entire tree under employee 1
```

**რეკურსიული CTE** აქვს საბაზო შემთხვევა (საწყისი რიგები) და რეკურსიული ეტაპი (`UNION ALL`, რომელიც თავის თავზე უბრუნდება CTE-ზე), რომელიც იტერირდება, სანამ ახალი რიგები არ გამოჩნდება — იდეალური **ხეებისა და იერარქიებისთვის** (ორგანიზაციული სქემები, კატეგორიის ხეები, მასალის ბილი, გრაფიკული ბილი).

## შენიშვნა CTE მატერიალიზაციის შესახებ (Postgres-სპეციფიკური)

```sql
-- Postgres 12+ may INLINE CTEs (optimize them) by default; force behavior if needed:
WITH x AS MATERIALIZED (...)      -- force materialization (compute once, store)
WITH x AS NOT MATERIALIZED (...)  -- force inlining (optimize into the main query)
```

Postgres 12-მდე, CTE-ები ყოველთვის იყო "ოპტიმიზაციის შედეგი" (მატერიალიზებული); ახლა ისინი ხაზის გასაკვეთად შეიძლება საჯარო პერფორმანსისთვის — `MATERIALIZED`/`NOT MATERIALIZED` აკონტროლებს ამას.

## რატომ არის მნიშვნელოვანი

CTE-ები და რეკურსიული შეკითხვები ღირებული PostgreSQL ფუნქციებია **წაკითხვადობისა და იერარქიული მონაცემებისთვის**, ამიტომ მათი გაგება სასარგებლოა რთული შেკითხვების პატიოსანი წერისთვის და ხე-სტრუქტურირებული მონაცემების დამუშავებისთვის.

ყოველდღიური სარგებელი არის **წაკითხვადობა**: რთული შეკითხვები მრავალი ქვეშეკითხვის დონეებით რთული გაცნობიერებისთვის, ხოლო CTE-ები საშუალებას გაძლევთ მათი დაშლა დასახელებულ, თანმიმდევრულ ლოგიკურ ეტაპებად (წაკითხვა ზემოდან ქვემოთ), რაც მკაფიოდ აუმჯობესებს სიცხადეს — რაც ხდის CTE-ებს მნიშვნელოვან ინსტრუმენტს პატიოსანი SQL-ის წერისთვის.

გამოჩენილი შესაძლებლობა არის **რეკურსიული CTE-ები** (`WITH RECURSIVE`), რომელიც აძლევს **იერარქიული და ხე-სტრუქტურირებული მონაცემების** შეკითხვას (ორგანიზაციული სქემები, კატეგორიის ხეები, მასალის ბილი, ძაფიანი კომენტარები, გრაფიკული გავლა) — შეკითხვების კლასი, რომელიც სხვაგვარად SQL-ში ძალიან რთულია, რაც ხდის რეკურსიულ CTE-ებს იერარქიული გავლის სტანდარტულ გადაწყვეტილებას.

რეკურსიული CTE სტრუქტურის გაგება (საბაზო შემთხვევა + რეკურსიული ეტაპი, რომელიც თავის თავზე უბრუნდება) არის მნიშვნელოვანი ცოდნა ამ საერთო საჭიროებისთვის.

ხელმისაწვდომი Postgres-სპეციფიკური დეტალი არის **CTE მატერიალიზაციის** ქცევა (Postgres 12+ შეიძლება CTE-ები ხაზის გასაკვეთად უკეთესი პერფორმანსისთვის, კონტროლირებული `MATERIALIZED`/`NOT MATERIALIZED`-ით) — რელევანტური ოპტიმიზაციისთვის.

რთული შეკითხვები ხშირი არის (და წაკითხვადობა მნიშვნელოვანია ტენიანობისთვის), და რადგან იერარქიული მონაცემები (რომელიც რეკურსიული CTE-ები ცალსახად კარგად ამუშავებენ) ხშირად გვხვდება (ორგანიზაციული სტრუქტურები, ჩასმული კატეგორიები), CTE-ებისა და რეკურსიული შეკითხვების გაგება ღირებული, პრაქტიკულად რელევანტური PostgreSQL ცოდნა, რომელიც აუმჯობესებს შეკითხვის ტენიანობას და ხსნის იერარქიული შეკითხვას — ხშირად რელევანტური შესაძლებლობა სუფთა, მოწინავე SQL-ისთვის და საერთო თემა, განსაკუთრებით რეკურსიული ფორმა ხე-სტრუქტურირებული მონაცემების შეკითხვის განმეორებადი საჭიროებისთვის.