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. Burada 2 tane CTE tablosu oluşturuluyor. İsimleri engineers ve eu_engineers

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 CTE tablo ismi T, 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)

Örnek

Tablonun ilk hali şöyle. Yani subquery kullanıyor.

SELECT
    users.id,
    users.name,
    COUNT(DISTINCT orders.id) AS order_count,
    SUM(orders.amount) AS total_spent,
    MAX(logins.timestamp) AS last_login
FROM users
LEFT JOIN orders ON users.id = orders.user_id
LEFT JOIN logins ON users.id = logins.user_id
WHERE users.created_at >= '2023-01-01'
GROUP BY users.id;

CTE kullanarak şöyle yaparız

WITH recent_users AS (
    SELECT id, name
    FROM users
    WHERE created_at >= '2023-01-01'
),
order_stats AS (
    SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total_spent
    FROM orders
    GROUP BY user_id
),
last_logins AS (
    SELECT user_id, MAX(timestamp) AS last_login
    FROM logins
    GROUP BY user_id
)
SELECT
    u.id,
    u.name,
    o.order_count,
    o.total_spent,
    l.last_login
FROM recent_users u
LEFT JOIN order_stats o ON u.id = o.user_id
LEFT JOIN last_logins l ON u.id = l.user_id;

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 - Graph Sorgular İçindir

Ö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

Örnek

Şöyle yaparız

WITH RECURSIVE user_network AS (
  -- Base case: direct friends of user
    SELECT friend_id FROM friends WHERE user_id = $1

    UNION

    -- Recursive step: get friends of those friends
    SELECT f.friend_id
    FROM friends f
    JOIN user_network un ON f.user_id = un.friend_id
)
--  selects purchases made by all users in the friend network.
SELECT p.*
FROM purchases p
JOIN user_network un ON p.user_id = un.friend_id
-- Filters the purchases to include only those for items the original user has purchased.
WHERE p.item_id IN (
    SELECT item_id FROM purchases WHERE user_id = $1
);

pg_createcluster komutu

Giriş

Açıklaması şöyle

Ubuntu/Debian packages for Postgres have their own layer on top of initdb and pg_ctl to control multiple instances and the integration with systemd.

The command that may be used to create an instance with specific options in Debian/Ubuntu is pg_createcluster. 

use pg_lsclusters to see the list of already existing clusters. 

Söz dizimi şöyle

pg_createcluster [options] version name [-- initdb options]

Örnek

Şöyle yaparız

$ pg_lsclusters
$ sudo pg_dropcluster --stop 13 main
$ sudo pg_createcluster 13 main -- --wal-segsize=256
$ sudo pg_ctlcluster 13 main start

CREATE DOMAIN

Örnek

Şöyle yaparız

CREATE DOMAIN mydomain AS int;

CREATE TABLE foo(bar) AS SELECT 42::mydomain;

SELECT f1.bar AS f1, f2.bar AS f2, pg_typeof(f1.bar), pg_typeof(f2.bar)
FROM foo AS f1
LEFT JOIN foo AS f2
  ON false;

Çıktı olarak şunu alırız. Yani Domain NULL değere sahip olablir.

 f1 | f2 | pg_typeof | pg_typeof 
----+----+-----------+-----------
 42 |    | mydomain  | mydomain

CREATE TYPE

Örnek - ENUM

Elimizde şöyle bir PostgreSQL tablosu olsun. Burada order_status isimli yeni bir type yarattık.

CREATE TYPE order_status AS ENUM(
  'Ordered', 
  'Baking', 
  'Delivering', 
  'YummyInMyTummy');

CREATE TABLE pizza_order (
  id INT PRIMARY KEY,
  status order_status NOT NULL,
  order_time TIMESTAMP NOT NULL DEFAULT now()
);

Şu SQL çalışır, çünkü status tipi olarak CREATE TYPE ile belirtilen bir string verdik

> INSERT INTO pizza_order (id, status, order_time) 
VALUES (1, 'Ordered', now());

VARCAHR ve ENUM arasında dönüşüm için bir cast yaratırız. 

CREATE CAST (varchar AS order_status) WITH INOUT AS IMPLICIT;

Örnek

Şöyle yaparız

CREATE TYPE address AS (
  city TEXT,
  address_line TEXT,
  zip_code INT
);

Bu type'tan başka bir şey üretmek için şöyle yaparız

CREATE DOMAIN address_domain AS address 
check (
  (value).city is not null and 
  (value).address_line is not null and
  (value).zip_code is not null
);

Kullanmak için şöyle yaparız

> CREATE TABLE test_address_domain (a address_domain);
CREATE TABLE
> INSERT INTO test_address_domain VALUES (('foo', 'bar', 11));
INSERT 0 1
> INSERT INTO test_address_domain VALUES (('foo', 'bar', null)); -- fails
ERROR:  value for domain address_domain violates check constraint "address_domain_check"