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
bilinirWITH cte_name AS (cte_body)
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_engineersWITH engineers AS(
SELECT * FROM employees WHERE
dept="Engineering"
),
eu_engineers AS (
SELECT * FROM engineers
WHERE country IN ("NL",...)
)
SELECT * FROM eu_engineers WHERE ...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
ÖrnekWITH 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
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;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ı
şöyleA 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ı
şöyleHowever, 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 msEğ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
