➡️ 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:
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.
➡️ 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:
➡️ 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 = 1Jak 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:
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
A może warto by przeskoczyć do Pythona? Jakiś prosty temat na początek, np. manipulacja listą, hm?
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
Inne ciekawe artykuły:
- MoM YoY Growth Tableau – czyli jak stworzyć formułę z wzrostem procentowym.
- SQL WHERE | SQL GROUP BY – filtrowanie i grupowanie w bazie danych.
- Jak zacząć pracę w IT jako Analityk Danych?
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.
