Common Table Expressions - WITH Clause

Giriş

Açıklaması şöyle. Yani bir select sonucu gelen tablo üzerinde daha fazla select yapılabilir. Böylece iç içe sorgulardan kaçabiliriz. Yani subquery'nin üste yazılmış hali gibi düşünülebilir.

Common table expressions (CTEs) are also known as 'WITH' queries. They're a nice way to avoid deeply nested subqueries.

Söz dizimi şöyle. CTE aynı zamanda WITH clause olarak ta bilinir

WITH cte_name AS (cte_body)

"Common Table Expressions" genellikle "Analytic Functions / Window Functions" ile birlikte kullanılırlar

1. Common Table Expressions Neden Lazım

Çünkü nested veya subquery sorgular çok fazla iç içe geçebiliyor. CTE ile bu yapı düzleştiriliyor ve okuması kolaylaşıyor

Örnek

CTE ile şöyle yaparız

WITH engineers AS(
 SELECT * FROM employees WHERE
  dept="Engineering"
),
eu_engineers AS (
  SELECT * FROM engineers 
  WHERE country IN ("NL",...)
)
SELECT * FROM eu_engineers WHERE ...

Subquery  ile şöyle yaparız

SELECT * FROM (
 SELECT * FROM (SELECT * employees WHERE
  dept="Engineering") AS engineers
  WHERE country IN ("NL",...))
WHERE ...

2. CTE İsmi Zaten Varsa - Existing Table Name

Soru MySQL ile ilgili ancak cevap aslında aynı. Eğer elimizde mevcut bir tablo varsa ve CTE içinde de bu tablo ismini kullanırsak ne olur? Kural şöyle

derived tables > CTEs (table defined in a WITH block) > everything else

3. Kullanım

Örnek

Şöyle yaparız

WITH my_expression AS (
  SELECT customer AS name FROM my_table
)
SELECT name FROM my_expression

Örnek

Şöyle yaparız. Burada tablo T olarak seçiliyor, daha sonra ilk ve son satırına erişiliyor.

WITH T AS (
   SELECT id, coins_id, first_coin, second_coin, price, `time`
   FROM hist_all
   WHERE `time` BETWEEN (NOW() - interval 120 minute) AND NOW()
     AND (second_coin = 'USD' OR second_coin = 'USDT')
     AND first_coin = 'LSK'
) 
(SELECT * FROM T ORDER BY time LIMIT 1)
UNION ALL
(SELECT * FROM T ORDER BY time DESC LIMIT 1);

Örnek

Şöyle yaparız

WITH idtempp as (
  SELECT id as id
  FROM id 
  WHERE country = "US"
  AND status = "Y"
)

SELECT *
FROM bill
WHERE id in (SELECT id from idtempp)

4. Dikkat Edilmesi Gereken Hususlar

CTE ile geçici bir tablo yaratılır. Bu tablo bir kere yaratılır ve tekrar tekrar kullanılır.  Açıklaması şöyle

A useful property of WITH queries is that they are evaluated only once per execution of the parent query, even if they are referred to more than once by the parent query or sibling WITH queries. Thus, expensive calculations that are needed in multiple places can be placed within a WITH query to avoid redundant work. Another possible application is to prevent unwanted multiple evaluations of functions with side-effects

Ancak bazen ana tablodaki index'ler CTE tablosuna aktarılamıyor. Açıklaması şöyle

However, the other side of this coin is that the optimizer is less able to push restrictions from the parent query down into a WITH query than an ordinary subquery.

Örnek

Şöyle yaparız. Arada çok fazla süre farkı var. Aslında bu yeni PostgreSQL ile düzeltilmiş ama sadece örnek olsun diye aldım.

> CREATE TABLE foo (id INT, padding TEXT);
> INSERT INTO foo (id, padding) SELECT id, md5(random()::text) FROM
  generate_series(1, 1000000) AS id ORDER BY random();
> CREATE INDEX foo_id_ix ON foo (id);

> SELECT * FROM foo WHERE id = 500000;
...
Time: 0.619 ms

> WITH CTE AS (SELECT * FROM foo) SELECT * FROM cte WHERE id = 500000;
...
Time: 227.675 ms

Sebebini görmek için şöyle yaparız Index Scan yerine CTE Scan yapılıyor

EXPLAIN (ANALYZE ON, TIMING ON) SELECT * FROM foo WHERE id = 500000;
QUERY PLAN
— — — — — — — — — — — — — — — 
Index Scan using foo_id_ix on foo (cost=0.42..8.44 rows=1 width=37) (actual time=0.026..0.028 rows=1 loops=1)
    Index Cond: (id = 500000)
Execution time: 0.060 ms

EXPLAIN (ANALYZE ON, TIMING ON) WITH CTE AS (SELECT * FROM foo) 
SELECT * FROM CTE WHERE id = 500000;
QUERY PLAN
------------------------------
CTE Scan on cte  (cost=18334.00..40834.00 rows=5000 width=36) (actual time=3.243..269.290 rows=1 loops=1)
  Filter: (id = 500000)
  Rows Removed by Filter: 999999
  CTE cte
    ->  Seq Scan on foo  (cost=0.00..18334.00 rows=1000000 width=37) (actual time=0.029..77.078 rows=1000000 loops=1)
Execution time: 276.625 ms

Eğer subquery kullanırsak çıktı şöyle. Yine Index Scan kullanılıyor

EEXPLAIN (ANALYZE ON, TIMING ON) SELECT * FROM (SELECT * FROM foo) AS subquery WHERE id = 500000;
QUERY PLAN
------------------------------
Index Scan using foo_id_ix on foo  (cost=0.42..8.44 rows=1 width=37) (actual time=0.028..0.031 rows=1 loops=1)
  Index Cond: (id = 500000)
Execution time: 0.066 ms

WITH RECURSIVE

Örnek

Elimizde şöyle bir tablo olsunn

-------------------------------------------------
|  id  | description         | parent_id   |  cost
--------------------------------------------------
| 1    |  Radiology         |       NULL  | 0.00
| 2    |  Lab Tests         |       NULL  | 0.00
| 3    |  Normal Radiology  |         1   | 0.00
| 4    |  Resonance         |         1   | 100.00
| 1100 |  Cerebral Resonance|         4   | 200.00
| 1900 |  Blood Tests       |         2   | 10.00
| 2044 |  Calcium           |         2   | 50.00

---------------------------------------------------

Çıktı olarak şunu isteyelim

Radiology
   -->Normal Radiology
   -->Resonance
      -->Cerebral Resonance with contrast
Lab Test
    --> Blood Test
    --> Calcium

Şöyle yaparız

WITH RECURSIVE hierarchy AS (
    SELECT  id, 1 AS rown, CAST(description AS TEXT) AS parent_list, id as parent
    FROM    orders
    WHERE   parent_id is null
    UNION  
    SELECT  c.id
    ,rown + 1 as rown
    ,CAST(repeat('    ', rown) || ' --> ' || c.description as text) as parent_list
    ,parent
    FROM orders c
    INNER JOIN hierarchy c2 ON CAST(c.parent_id AS INTEGER) = c2.id )
SELECT id,parent_list FROM hierarchy
ORDER BY parent DESC,rown

Çıktı olarak şunu alırız

  id | parent_list                    
---: | :------------------------------
   2 | Lab Tests                      
1900 |      --> Blood Tests           
2044 |      --> Calcium               
   1 | Radiology                      
   4 |      --> Resonance             
   3 |      --> Normal Radiology      
1100 |          --> Cerebral Resonance

Şöyle yaparız

WITH RECURSIVE hierarchy AS (
    SELECT  id, CAST(description AS TEXT) AS parent_list, 1 AS rown, id as parent
    FROM    orders
    WHERE   parent_id is null
    UNION  
    SELECT  c.id
    ,CAST(c2.parent_list || ' --> ' || c.description as text) as parent_list
    ,rown + 1 as rwon
    ,parent
    FROM orders c
    INNER JOIN hierarchy c2 ON CAST(c.parent_id AS INTEGER) = c2.id )
SELECT  id, parent_list
FROM    hierarchy
GROUP BY id, parent_list
ORDER BY parent_list;

Çıktı olarak şunu alırız

  id | parent_list                                   
---: | :---------------------------------------------
   2 | Lab Tests                                     
1900 | Lab Tests --> Blood Tests                     
2044 | Lab Tests --> Calcium                         
   1 | Radiology                                     
   3 | Radiology --> Normal Radiology                
   4 | Radiology --> Resonance                       
1100 | Radiology --> Resonance --> Cerebral Resonance