➡️ Wstęp

Jeżeli piszesz SQL i zastanawiasz się co to jest CTE – to jesteś w dobrym miejscu.

W ogóle CTE, Common Table Expression, co za debil wymyślił tak nieintuicyjne nazwy 🤦‍♂️? No dobra, pomińmy nazwy – co to jest to CTE.

W gruncie rzeczy jest to prosta rzecz. Spójrz na tego SQLa:

select 
     DATE_FORMAT(order_date, '%Y-%m-01') as order_month
     ,count(distinct customer_id)        as customers
     ,sum(sales)                         as sales
 from orders 
 where ship_mode = 'First Class'
 group by 1;

Aha, a jeśli nie wiesz o co chodzi z tym group by 1 to ogarnij sobie artykuł o group by. I teraz wyobraź sobie, że chciałbyś użyć wyników, które z tego wychodzą w następnej kwerendzie, w nastepnym zapytaniu SELECT. Dlaczego?

Ano, chciałbym po prostu uzyskać raport miesięczny, który wygląda tak:

SQL with - co to jest CTE - przykład raportu 1

Mam kilka opcji:

  • stworzyć z tego tabelę albo widok
  • zagnieździć to w FROM (czyli zrobić tzw. nested query)
  • albo…

Albo właśnie użyć Common Table Expression, a krócej: CTE.

📺 Wersja do oglądania

➡️ SQL WITH – czyli co to jest CTE i jak je zbudować

CTE zaczynamy od budowy słówka kluczowego with, a potem, w nawiasie umieszczamy tymczasowy nazwany select.

with
first_class as
    (
    select
        date_format(order_date, '%Y-%m-01') as order_month
        ,count(distinct customer_id)        as customers
        ,sum(sales)                         as sales
    from orders
    where ship_mode = 'First Class'
    group by 1
    )
-- To nie koniec… 

Jeżeli takich selectów będzie kilka, odzielamy kolejne nawiasy przecinkami. Poza ostatnim nawiasem.

with
first_class as
    (
    select
        date_format(order_date, '%Y-%m-01') as order_month
        ,count(distinct customer_id)        as customers
        ,sum(sales)                         as sales
    from orders
    where ship_mode = 'First Class'
    group by 1
    ),
second_class as
    (
    select
        date_format(order_date, '%Y-%m-01') as order_month
        ,count(distinct customer_id)        as customers
        ,sum(sales)                         as sales
    from orders
    where ship_mode = 'Second Class'
    group by 1
    ),
months as
    (
    select distinct date_format(order_date, '%Y-%m-01') as order_month
    from orders
    )
-- To nie koniec…    

Po ostatnim nawiasie dajemy zwykły SELECT i korzystamy z wcześniej skonstruowanych tabel tymczasowych tak, jakby istniały w bazie danych. SQL idąc od góry, zaczynając od słówka with, będzie zapamiętywał stworzone przez nas CTE.

Dzięki temu będziemy mogli ich użyć po słowie FROM czy w joinach.

with
first_class as
    (
    select
        date_format(order_date, '%Y-%m-01') as order_month
        ,count(distinct customer_id)        as customers
        ,sum(sales)                         as sales
    from orders
    where ship_mode = 'First Class'
    group by 1
    ),
second_class as
    (
    select
        date_format(order_date, '%Y-%m-01') as order_month
        ,count(distinct customer_id)        as customers
        ,sum(sales)                         as sales
    from orders
    where ship_mode = 'Second Class'
    group by 1
    ),
months as
    (
    select distinct date_format(order_date, '%Y-%m-01') as order_month
    from orders
     )
select
    m.order_month   as order_month
    ,fc.customers   as first_class_customers
    ,sc.customers   as second_class_customers
    ,fc.sales       as first_class_sales
    ,sc.sales       as second_class_sales
from months m
left join first_class    fc on fc.order_month = m.order_month
left join second_class    sc on sc.order_month = m.order_month
order by 1 asc;

Rezultat? Jak na obrazku:

SQL with - co to jest CTE - result

➡️ Tabela tymczasowa może czytać wcześniejszą tabelę tymczasową

Nic nie stoi na przeszkodzie by tabela tymczasowa, którą definiujemy sobie w nawiasie, przeczytała inną, którą zdefiniowaliśmy wcześniej.

with
 first_class as
     (
     select
         date_format(order_date, '%Y-%m-01') as order_month
         ,count(distinct customer_id)        as customers
         ,sum(sales)                         as sales
     from orders
     where ship_mode = 'First Class'
     group by 1
     ),
 fc_best_month as
     (
     select max(sales)   as best_sales
     from first_class
     ),
 second_class as
     (
     select
         date_format(order_date, '%Y-%m-01') as order_month
         ,count(distinct customer_id)        as customers
         ,sum(sales)                         as sales
     from orders
     where ship_mode = 'Second Class'
     group by 1
     ),
 sc_best_month as
     (
     select max(sales)   as best_sales
     from second_class
     )
 select
     fc.best_sales   as fc_best_sales
     ,sc.best_sales  as sc_best_sales
 from fc_best_month fc
 left join sc_best_month sc on 1 = 1

Jak widać w linijkach 15 i 30 korzystam ze wcześniej zdefiniowanych first_class i second_class.

I na tym polega magia tego “SQL with“, czy też CTE – nie musisz mieć stworzonych wcześniej tabel, by robić wieloetapową analizę danych w SQL.

Może zastanawiasz się co się wydarzyło w ostatniej linijce z tym 1=1? Chodzi o to, że łączymy wszystko ze wszystkim. Możemy tak zrobić bo każda z tych tabel ma tylko jeden rezultat, więc to i tak da nam tylko jedną kombinację.

Moglibyśmy wpisać everything. Ale SQL nie rozumie everything 😉 SQL rozumie 1=1.

Wynik:

common table expression reading previous select example

Najlepsza miesieczna sprzedaż z first_class obok najlepszej z second_class.


😎 Darmowe pliki do pobrania

Przykład kodu SQL na github.

↪️ Podsumowanie

Jeśli kojarzysz coś takiego jak sql with – to tak naprawdę chodzi Ci o CTE czyli Common Table Expression

CTE pozwalają na stworzenie tymczasowych tabel (tymczasowych selectów) w pamięci SQLa, tak abyś mógł je wykorzystać później w finalnej fazie select, oraz w joinach. Dzięki temu możliwa jest wieloetapowa analiza danych w jednym zapytaniu

Tymczasowe zapytania w CTE mogą korzystać ze wcześniej nazwanych zapytań – wszystko po prostu musi się odbywać chronologicznie.

Mam nadzieję, że googlowanie fraz co to jest cte i sql with nie będzie już potrzebne 😉

To tyle w tym temacie. Analizujcie w pokoju!  📊📈

Podobał Ci się ten artykuł?
Podziel się nim w Social Mediach:
>>> udostępnij go na LinkedIn i pokaż, że codziennie uczysz się czegoś nowego 💻
>>> wrzuć go na Facebooka, to się może przydać któremuś z Twoich znajomych 💗
>>> Przypnij sobie tą stronkę to zakładek, może się przydać w przyszłości 💡

Wolisz oglądać 📺 niż czytać – nie ma problemu 😉
>>> Obserwuj i oglądaj KajoData na YouTube

A jeśli chcesz się dowiedzieć czegoś więcej o SQL – zajrzyj tutaj.

Leave a Reply