Co to jest CTE?

Problem: Jak napisać zapytanie SQL w przejrzysty sposób. Będzie ono czytelne dla innych. Dodatkowo, gdy wrócisz do niego po kilku miesiącach też będzie dla Ciebie zrozumiałe.
W przypadku zmiany wymagań da się łatwo dostosować do nowych potrzeb. Będzie łatwe w utrzymaniu i rozbudowie.

Rozwiązanie: użyj Common Table Expressions (CTE), żeby napisać czytelny kod.

Co to jest CTE?

Możesz rozumieć CTE jako widok na dane, który istnieje tymczasowo w pamięci. Jest on pomocny przy tworzeniu dużych zapytań, które dzięki CTE możesz podzielić na mniejsze, bardziej czytelne elementy.

CTE znajdziesz między innymi w:

• SQL Server,
  *Oracle,
• Netezza,
• Impala.

CTE będzie pomocne, gdy przeprowadzasz analizę w hurtowni danych.

CTE Podstawy

Pierwsze Common Table Expression zacznij od słowa WITH nadaj znaczącą nazwę i słowo kluczowe AS, otocz klaulę select nawiasami i gotowe:

WITH  latest_snapshot AS (
SELECT  * FROM d_snapshot
WHERE latest = 1
)
SELECT * FROM latest_snapshot;

Twój kod SQL może zawierać więcej niż jedno CTE. Kolejne dodajesz po przecinku, definiujesz nazwę i dodajesz AS.

WITH latest_snapshot AS (
SELECT  snapshot_date FROM d_snapshot
WHERE latest = 1
), invoiced_orders AS (
SELECT * FROM f_order
WHERE status = 'Invoiced'
) SELECT * FROM invoiced_orders a
INNER JOIN latest_snapshot b ON (a.snapshot_date = b.snapshot_date)

CTE Użycie

Zdefiniowane CTE może być użyte wewnątrz kolejnego CTE.

Więc powyższe zapytanie można też napisać inaczej.

WITH latest_snapshot AS (
SELECT  snapshot_date FROM d_snapshot
WHERE latest = 1
), invoiced_orders AS (
SELECT * FROM f_order
INNER JOIN latest_snapshot b ON (a.snapshot_date = b.snapshot_date)
WHERE status = 'Invoiced'
) SELECT * FROM invoiced_orders;

Użycie jednego CTE w wielu miejscach

Warto wspomnieć, że jedno CTE może być użyte w wielu miejscach w kodzie. Ważne jest, żeby takie CTE było zdefiniowane przed blokiem w którym będziesz chciał go użyć.

WITH latest_snapshot AS (
SELECT  snapshot_date FROM d_snapshot
WHERE latest = 1
), invoiced_orders AS (
SELECT * FROM f_order
INNER JOIN latest_snapshot b ON (a.snapshot_date = b.snapshot_date)
WHERE status = 'Invoiced'
), invoice_details AS (
SELECT invoice_number, invoice_date FROM f_invoice a
INNER JOIN latest_snapshot b ON (a.snapshot_date = b.snapshot_date)
)
SELECT b.*, a.* FROM invoiced_orders a
LEFT JOIN invoice_datails b ON (a.snapshot_date = b.snapshot_date and a.order_id = b.order_id)

Jak widzisz latest_snapshot jest użyty wewnątrz invoiced_orders i invoiced_details.

CTE i zmiana wymagań

Teraz wyobraź sobie, że dostajesz do implementacji zmianę wymagań. Dodatkowo do ostatniego snapshotu masz dostarczać też ostatni dzień kalendarzowy miesiąca.

WITH latest_snapshot_eom AS (
SELECT  a.snapshot_date FROM d_snapshot a
LEFT JOIN d_callendar b ON (a.snapshot_date = b.day)
WHERE a.latest = 1 OR b.is_last_day_of_the_month = 1
), invoiced_orders AS (
SELECT * FROM f_order
INNER JOIN  latest_snapshot_eom b ON (a.snapshot_date = b.snapshot_date)
WHERE order_status = 'Invoiced'
), invoice_details AS (
SELECT invoice_number, invoice_date FROM f_invoice a
INNER JOIN latest_snapshot_eom b ON (a.snapshot_date = b.snapshot_date)
WHERE invoice_status = 'Invoiced'
)
SELECT b.*, a.* FROM invoiced_orders a
LEFT JOIN invoice_datails b ON (a.snapshot_date = b.snapshot_date and a.order_id = b.order_id)

Prosta zmiana w jednym miejscu w kodzie. W pierwszym CTE został dodany left join. Nie ma potrzeby zmiany w innych miejscach w zapytaniu.

CTE dodatkowe opcje

Definiując CTE możesz także dodać nazwy zwracanych kolumn. Po nazwie CTE wystarczy w nawiasie wstawić nazwę kolumny:

WITH latest_snapshot (max_snapshot) AS (
SELECT  a.snapshot_date FROM d_snapshot a
LEFT JOIN d_callendar b ON (a.snapshot_date = b.day)
WHERE a.latest = 1 OR b.is_last_business_day_of_the_month = 1
) SELECT max_snapshot FROM latest_snapshot;

CTE dla zaawansowanych - rekurencja

Wyobraź sobie, że chcesz wygenerować d_snapshot, żeby zawierała ostatnie 30 dni. Dzisiejszy dzień ma być oznaczony jako latest. Tabela będzie generowana codziennie.

Przykładowy kod gdzie używasz rekurencji, żeby to zrobić.

WITH aux_d_snapshot(n, snapshot_date, is_latest) AS (
    SELECT 0, getdate(), 'Y' as is_latest
    UNION ALL
    SELECT n + 1, DATEADD(dd, -1, snapshot_date), 'N' as is_latest
    FROM aux_d_snapshot WHERE n < 30
)
SELECT cast(format(snapshot_date, 'yyyyMMdd') as int) as snapshot_date, is_latest
FROM aux_d_snapshot;

Impala to jeden z tych silników, które nie wspiera rekursji ale znajdziesz ją na przykład w SQL Server.

Jak wyglądałby kod bez CTE?

SELECT * FROM f_order latest_order
INNER JOIN (SELECT a.snapshot_date FROM d_snapshot snapshot
LEFT JOIN d_callendar calendar ON (snapshot.snapshot_date = calendar.day)
WHERE snapshot.latest = 1 OR calendar.is_last_day_of_the_month = 1) latest_snapshot 
    ON (latest_order.snapshot_date = latest_snapshot.snapshot_date)
LEFT JOIN f_invoice latest_invoice 
    ON (latest_order.snapshot_date = latest_invoice.snapshot_date 
        AND latest_order.order_id = latest_invoice.order_id)
WHERE latest_order.order_status = 'Invoiced'  
    AND (latest_invoiced.invoice_status = 'Invoice' 
        OR latest_invoice.invoice_status IS NULL)

Albo

SELECT * FROM f_order latest_order
INNER JOIN (SELECT a.snapshot_date FROM d_snapshot snapshot
LEFT JOIN d_callendar calendar ON (snapshot.snapshot_date = calendar.day)
WHERE snapshot.latest = 1 OR calendar.is_last_day_of_the_month = 1) latest_snapshot 
    ON (latest_order.snapshot_date = latest_snapshot.snapshot_date)
LEFT JOIN f_invoice latest_invoice
    ON  (latest_order.snapshot_date = latest_invoice.snapshot_date 
        AND latest_order.order_id = latest_invoice.order_id 
        AND latest_invoice.invoice_status = 'Invoice')
WHERE latest_order.order_status = 'Invoiced' ;

Możesz powiedzieć, że ten kod jest bardziej zwięzły ale czy przez to jest bardziej czytelny?

Pytania na koniec

1. Do czego może być przydatne CTE?
2. Czy Twoim zdaniem CTE sprzyja w tworzeniu czytelnego kodu?