CTE - COMMON TABLE EXPRESSION

Давайте рассмотрим эту тему сразу же на примере в контексте прошлой темы, чтобы было понятнее.

Задача:

Нам нужно вывести топ-5 игроков команды, у которой лучший средний рейтинг среди всех представленных команд в таблице.

Эта задача легко решается через подзапросы, а логика следующая:

1. В подзапросе для каждой команды считаем средний рейтинг.
2. Получившийся в подзапросе результат объединяем с нашей таблицей.

Давайте подробно разберем запрос:

select
    club_rating.club,
    avg_overall,
    short_name,
    overall
from
    (	
    select
        club,
        avg(overall) as avg_overall
    from
        fifa_players_2018
    group by
        club
    order by
        avg_overall desc
    limit 1
        ) as club_rating
join
    fifa_players_2018 as players 
on club_rating.club = players.club
order by
    overall desc
limit 5

Как вы видите, эту задачу нельзя решить одним запросом, ведь нам нужно агрегированную информацию (средний рейтинг) по команде соединить с не агрегированными данными (список игроков и рейтинг). Поэтому мы применяем подзапросы. 

Однако текущее решение написано не совсем оптимально, можно проще и быстрее.

В этом нам поможет конструкция CTE (common table expression) - это, по сути, изначальное представление таблицы, а потом ее использование в запросе 

Конструкция CTE работает так:

with
    cte as ( select * from table ) – Инициализируем таблицу через with
select
    *
from
    cte; – Используем таблицу в запросе

На примере нашей задачи CTE можно использовать вот так:

with cte as (
select
    club,
    avg(overall) as avg_overall
from
    fifa_players_2018
group by
    club
order by
    avg_overall desc
limit 1
    )
select
    players.club,
    short_name,
    avg_overall,
    overall
from
    fifa_players_2018 as players
join cte on players.club = cte.club
order by
    overall desc
limit 5

По сути CTE - это даже не физическая таблица, а представление (то есть, написанный заранее cte выполнятся прямо во время запроса), а главное его преимущество - это возможность использовать CTE несколько раз в запросе, а не писать повторяющиеся подзапросы.

Так, если подзапрос нужно переиспользовать несколько раз, то CTE - это незаменимый помощник!