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

Açıklaması şöyle

Postgres' SELECT DISTINCT doesn't scale. Here's why and how to fix it.  👇 

Let's say you need to query distinct values from a large table, for example, a "device_id" column with a few possible values but many rows associated to each device.

The table has a B-tree index on the device_id column, i.e., the index entries are sorted by device_id.

SELECT DISTINCT should cost only as many lookups as there are devices. That's what I expected from a sorted index: find the first device, jump past it, find the next one, and so on.

Postgres has no plan node for "jump past it". The planner picks an Index Only Scan, reads all the index entries in order, then pick the one you're looking for with a "Unique" node that throws away all the rows you don't care for, potentially millions of them.

The work is proportional to the number of rows, not to the number of distinct values.

MySQL knows how to handle this: it calls this operation a "loose index scan". (PG 18 ships a new "skip scan" that does a similar thing for non-leading column.)

The fix is to build the loose scan out of two pieces Postgres does have: seeks and recursive CTEs.

Burada problem kullanılan B-tree yapısının sadece iki şeyi iyi yapabilmesi.

1. İleri yürüme

2. İstenilen değeri bulma - seek

Eğer tablolar çok büyükse index scan yapsa bile uzun sürüyor. Çünkü tüm indeksi dolaşıyor ve daha sonra distinct işlemine sokuyor.  Yani Postgres şunu yapamıyor. 

I'm on A. Jump directly to the first value greater than A

Bu durumda 1 seek per distinct value haline getirmek lazım. 

Elimizde şöyle bir sorgu olsun

WITH RECURSIVE devices AS (
    -- Anchor
    (
        SELECT device_id
        FROM tasks
        ORDER BY device_id
        LIMIT 1
    )

    UNION ALL

    -- Recursive step
    SELECT (
        SELECT device_id
        FROM tasks
        WHERE device_id > devices.device_id
        ORDER BY device_id
        LIMIT 1
    )
    FROM devices
    WHERE devices.device_id IS NOT NULL
)
SELECT device_id
FROM devices
WHERE device_id IS NOT NULL;

Bu sorgu B-tree seek haline getirir.

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"

NOW() metodu

Giriş

MySQL için açıklaması şöyle

The time returned by NOW(), and other date time functions, is derived from the start time of the query. 

Açıklaması şöyle

Default Timestamps

It is always better to have the two timestamptz fields created_at and updated_at columns with default now() .... Storing what time a record was created or modified would be very useful in the future when going over some analytics or reporting. Audit logs might be required and timestamps are key.

Örnek

Görmek için şöyle yaparız. Burada sleep() olmasına rağmen now() tek select için aynı sonucu veriyor.

MariaDB [test]> select now(),sleep(10),now();
+---------------------+-----------+---------------------+
| now()               | sleep(10) | now()               |
+---------------------+-----------+---------------------+
| 2021-03-22 14:17:05 |         0 | 2021-03-22 14:17:05 |
+---------------------+-----------+---------------------+

FULL OUTER JOIN - İki Tablonun Union'ı Gibidir

Giriş

Join tiplerini görsel olarak gösteren resimler burada.

Şeklen şöyle

Bir başka şekil şöyle

İki çeşit FULL OUTER JOIN var.

1.  FULL OUTER JOIN

2.  FULL OUTER JOIN (IF NULL)

Not : Bazı veri tabanları Full Outer Join'i desteklemez. Örneğin MySQL. Açıklaması şöyle

Unlike SQL Server, MySQL does not have a distinct JOIN type for FULL OUTER JOIN. You may, however, combine LEFT OUTER JOIN and RIGHT OUTER JOIN to get the same effects as FULL OUTER JOIN.

Bu durumda şöyle yaparız

SELECT *  FROM tableA 
LEFT JOIN tableB 
  ON tableA.id = tableB.id

UNION

SELECT * FROM tableA
RIGHT JOIN tableB 
  ON tableA.id = tableB.id

1.  FULL OUTER JOIN
Örnek

Şöyle yaparız

SELECT FROM tableA a FULL OUTER JOIN tableB b ON a.key = b.key

Örnek

Şöyle yaparız

-- Retrieve all employees and department names, including those 
-- without a department and departments without employees
SELECT employees.employee_name, departments.department_name
FROM employees
FULL JOIN departments ON employees.department_id = departments.department_id;

2. RIGHT JOIN (IF NULL)

İki tablonun kesişimi olmayan satırları gösterir.

Örnek

Şöyle yaparız

SELECT FROM tableA a FULL OUTER JOIN tableB b on a.key = b.key
 WHERE a.key IS NULL OR b.key IS NULL