Глава 10. Соединения - SQL Lab в JupyterLab

import pandas as pd
import sql
import sqlalchemy as sa

pd.set_option('display.max_rows', 20)

%load_ext sql
%config SqlMagic.displaycon = False
%config SqlMagic.autopandas = True

connection_url = sa.engine.URL.create(
    drivername='mysql+pymysql',
    host='localhost',
    port=3306,
    database='sakila',
    username='root',
    password='*UHB5rdx',
)
engine = sa.create_engine(connection_url)

%sql engine

print(f"Pandas ver. {pd.__version__}: порог усечения строк уменьшен до 20")
print(f"SQLAlchemy ver. {sa.__version__}: подключение создано")
print(f"JupySQL ver. {sql.__version__}: подключен через SQLAlchemy Engine")

Pandas ver. 3.0.5: порог усечения строк уменьшен до 20
SQLAlchemy ver. 2.0.52: подключение создано
JupySQL ver. 0.11.1: подключен через SQLAlchemy Engine

К настоящему времени вы уже должны быть хорошо знакомы с концепцией врутреннего соединения, которая была представлена в главе 5.

В этой главе рассматриваются другие способы соединения таблиц, включая внешнее соединение и перекрестное соединение.

Внешние соединения¶

До сих пор во всех примерах, включающих несколько таблиц, нас не волновало, что условия соединения могут не найти совпадений для всех строк таблицы.

Например, таблица inventory содержит строку для каждого фильма, доступного для проката, но из 1000 строк в таблице film только 958 имеют одну или несколько строк в таблице inventory. Остальные 42 фильма для проката недоступны (возможно это новинки, которые должны прибыть в пункты проката со дня на день), поэтому идентификаторы этих фильмов в таблице inventory отсутствуют.

Следующий запрос подсчитывает количество доступных копий каждого фильма с помощью соединения этих двух таблиц:

%%sql
SELECT
    f.film_id,
    f.title,
    COUNT(*) AS num_copies
FROM film f
    INNER JOIN inventory i
        ON f.film_id = i.film_id
GROUP BY
    f.film_id,
    f.title;

Хотя можно было бы ожидать, что будет возвращено 1000 строк (по одной для каждого фильма), запрос возвращает только 958 строк.

Дело в том, что запрос использует внутреннее соединение, которое возвращает только строки, удовлетворяющие условию соединения. Например, фильм Alice Fantasia (film_id равен 14) не отображается в результатах, потому что для него нет строк в таблице inventory.

Если хотите, чтобы запрос возвращал все 1000 фильмов, независимо от того, имеются ли соответствующие строки в таблице inventory, можете использовать внешнее соединение, которое, по сути, делает условие соединения необязательным:

%%sql
SELECT
    f.film_id,
    f.title,
    COUNT(i.inventory_id) AS num_copies
FROM film f
    LEFT OUTER JOIN inventory i
        ON f.film_id = i.film_id
GROUP BY
    f.film_id,
    f.title
ORDER BY num_copies;

Как видите, запрос теперь возвращает все 1000 строк из таблицы film, при этом 42 строки из общего количества строк (включая фильм Alice Fantasia) имеют значение 0 в столбце num_copies, что указывает на отсутствие доступных для проката копий.

Вот описание изменений по сравнению с предыдущей версией запроса.

Определение соединения было изменено с inner на left outer, что указывает серверу на необходимость включения всех строк из таблицы в левой части соединения (в данном случае film) и включения столбцов из таблицы с правой стороны соединения (inventory), если соединение прошло успешно.
Определение столбца num_copies было изменено с count(*) на count(i.inventory_id: это выражение подсчитывает количество значений столбца inventory.inventory_id не равных null.

Давайте удалим предложение group by и отфильтруем большинство строк, чтобы ясно видеть различия между внутренними и внешними соединениями. Вот запрос с использованием внутреннего соединения и условия фильтрации для возврата всего лишь нескольких фильмов:

%%sql
SELECT
    f.film_id,
    f.title,
    i.inventory_id
FROM film f
    INNER JOIN inventory i
        ON f.film_id = i.film_id
WHERE f.film_id BETWEEN 13 AND 15;

Результаты показывают, что в прокате имеется четыре копии Ali Forever и шесть копий Alien Center.

Вот так выглядит тот же запрос. но с использованием внешнего соединения:

%%sql
SELECT
    f.film_id,
    f.title,
    i.inventory_id
FROM film f
    LEFT JOIN inventory i
        ON f.film_id = i.film_id
WHERE f.film_id BETWEEN 13 AND 15;

Результаты для Ali Forever и Alien Center остаются прежними, но появляется одна новая строка для Alice Fantasia со значением null (NaN) в столбце inventory.inventory_id. В этом примере показано, как внешнее соединение добавляет значения столбцов без ограничения на количество строк, возвращаемых запросом. Если условие соединения не выполняется (как в случае Alice Fantasia), то любые столбцы, извлеченные из внешне соединенной таблицы, будут иметь значения null.

Левое и правое внешние соединения¶

В примерах внешнего соединания в предыдущем разделе было указано left outer join. Ключевое слово left указывает, что таблица в левой части соединения отвечает за определение количества строк в результирующем наборе, тогда как таблица в правой части используется для предоставления значений столбца всякий раз, когда найдено совпадение.

Однако можно указать и правое внешнее соединение – в этом случае таблица с правой стороны от right outer join отвечает за определение количества строк в результирующем наборе, тогда как таблица с левой стороны используется для предоставления значений столбцов.

Вот последний запрос из предыдущего раздела, в котором используется правое внешнее соединение вместо левого:

%%sql
SELECT
    f.film_id,
    f.title,
    i.inventory_id
FROM inventory i
    RIGHT OUTER JOIN film f
        ON f.film_id = i.film_id
WHERE f.film_id BETWEEN 13 AND 15;

Заметим, что обе версии запроса выполняют внешнее соединение. Ключевые слова left и right служат только для того, чтобы сообщить серверу, в какой таблице разрешено иметь пробелы в данных.

Если вы хотите внешне соединить таблицы А и В и хотите, чтобы в результирующем наборе присутствовали все строки из А (с дополнительными столбцами из В всякий раз, когда есть соответствующие данные), то можете указать либо А left outer join B либо B right outer join A.

Трехсторонние внешние соединения¶

В некоторых случаях может потребоваться внешнее соединение одной таблицы с двумя другими. Например, запрос из предыдущего раздела можно расширить, включив в него данные из таблицы rental:

%%sql
SELECT
    f.film_id,
    f.title,
    i.inventory_id,
    r.rental_date
FROM film f
    LEFT JOIN inventory i
        ON f.film_id = i.film_id
    LEFT JOIN rental r
        ON i.inventory_id = r.inventory_id
WHERE f.film_id BETWEEN 13 AND 15;

Резульаты включают в себя все фильмы, имеющиеся в наличии, но у фильма Alice Fantasia в столбцах из обеих таблиц, соединенных внешним соединением находятся значения null (None).

Перекрестные соединения¶

Еще в главе 5 была представлена концепция декартова произведения, которая по сути представляет собой результат соединения нескольких таблиц без указания каких-либо условий соединения.

Декартово произведение довольно часто используется случайно (например, если вы забыли добавить условие соединения в предложение from), но преднамеренное его применение не так уж распространено. Если все же намереваетесь сгенерировать декартово произведение двух таблиц, то должны указать перекрестное соединение как в следующем примере:

%%sql
SELECT
    c.name AS category_name,
    l.name AS language_name
FROM category c
    CROSS JOIN language l;

Этот запрос генерурует декартово произведение таблиц category и language, в результате чего получается результирующий набор из 96 строк (16 строк category x 6 строк language).

Теперь, когда вы знаете что такое перекрестное соединение и как его указать, возникает вопрос А для чего оно используется? В большинстве книг по SQL после описания что такое перекрестное соединение, просто говорится что оно редко бывает полезным. Но я хотел бы поделиться с вами ситуацией, в которой считаю перекрестное соединение весьма полезным.

В главе 9 рассматривалось как использовать подзапросы для создания таблиц и как построить таблицу из трех строк, которую можно объединить с другими таблицами. Вот собранная таким образом таблица из примера:

%%sql
SELECT 'Small Fry' name, 0 low_limit, 74.99 high_limit
UNION ALL
SELECT 'Average Joes', 75, 149.99
UNION ALL
SELECT 'Heavy Hitters', 150, 9999999.99;

Хотя эта таблица – именно то, что нужно для разделения клиентов на три группы на основе их платежей, такая стратегия объединения однострочных таблиц с использованием оператора union all – все же не самый лучший вариант в случае больших таблиц.

Пусть, например, вы хотите создать запрос, который генерирует по одной строке на каждый день 2020 года, но в базе данных нет таблицы, которая содержит по строке для каждого дня. Используя стратегию из упомянутого примера, вы можете сделать что-то вроде следующего:

SELECT '2020-01-01' dt
UNION ALL
SELECT '2020-01-02' dt
UNION ALL
SELECT '2020-01-03' dt
UNION ALL
...
SELECT '2020-12-29' dt
UNION ALL
SELECT '2020-12-30' dt
UNION ALL
SELECT '2020-12-31' dt

Создание запроса, объединяющего результаты 366 запросов, несколько утомительно, поэтому пожалуй нужна другая стратегия.

Что если вы созданите таблицу с 366 строками (2020 – високосный год) с одним столбцом, содержащим числа от 0 до 366, а затем добавите это количество дней к 1 января 2020 года? Вот один из возможных способов создания такой таблицы:

SQL Style Guide

Автор использует стиль Табличного выравнивания / Alignment by Delimiter
В мире SQL такой подход называют Columnar Alignment (или выравнивание по разделителям):

SELECT ones.num + tens.num + hundreds.num AS total_num
FROM 
(SELECT 0 AS num UNION ALL
 SELECT 1 UNION ALL
 SELECT 2 UNION ALL
 SELECT 3 UNION ALL
 SELECT 4 UNION ALL
 SELECT 5 UNION ALL
 SELECT 6 UNION ALL
 SELECT 7 UNION ALL
 SELECT 8 UNION ALL
 SELECT 9) AS ones
CROSS JOIN
(SELECT 0 AS num UNION ALL
 SELECT 10 UNION ALL
 SELECT 20 UNION ALL
 SELECT 30 UNION ALL
 SELECT 40 UNION ALL
 SELECT 50 UNION ALL
 SELECT 60 UNION ALL
 SELECT 70 UNION ALL
 SELECT 80 UNION ALL
 SELECT 90) AS tens
CROSS JOIN
(SELECT 0 AS num UNION ALL
 SELECT 100 UNION ALL
 SELECT 200 UNION ALL
 SELECT 300) AS hundreds;

Компактный (Практичный) стиль:

SELECT ones.num + tens.num + hundreds.num AS total_num
FROM (
    SELECT 0 AS num UNION ALL
    SELECT 1 UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4 UNION ALL
    SELECT 5 UNION ALL
    SELECT 6 UNION ALL
    SELECT 7 UNION ALL
    SELECT 8 UNION ALL
    SELECT 9
) AS ones
CROSS JOIN (
    SELECT 0 AS num UNION ALL
    SELECT 10 UNION ALL
    SELECT 20 UNION ALL
    SELECT 30 UNION ALL
    SELECT 40 UNION ALL
    SELECT 50 UNION ALL
    SELECT 60 UNION ALL
    SELECT 70 UNION ALL
    SELECT 80 UNION ALL
    SELECT 90
) AS tens
CROSS JOIN (
    SELECT 0 AS num UNION ALL
    SELECT 100 UNION ALL
    SELECT 200 UNION ALL
    SELECT 300
) AS hundreds
ORDER BY total_num;

Гибридный вариант Табличного и Компактного стилей:

SELECT ones.num + tens.num + hundreds.num AS total_num
FROM (
    SELECT 0 AS num UNION ALL
    SELECT 1 UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4 UNION ALL
    SELECT 5 UNION ALL
    SELECT 6 UNION ALL
    SELECT 7 UNION ALL
    SELECT 8 UNION ALL
    SELECT 9) AS ones
CROSS JOIN (
    SELECT 0 AS num UNION ALL
    SELECT 10 UNION ALL
    SELECT 20 UNION ALL
    SELECT 30 UNION ALL
    SELECT 40 UNION ALL
    SELECT 50 UNION ALL
    SELECT 60 UNION ALL
    SELECT 70 UNION ALL
    SELECT 80 UNION ALL
    SELECT 90) AS tens
CROSS JOIN (
    SELECT 0 AS num UNION ALL
    SELECT 100 UNION ALL
    SELECT 200 UNION ALL
    SELECT 300) AS hundreds
ORDER BY total_num;

Академический стиль:

SELECT ones.num + tens.num + hundreds.num AS total_num
FROM 
    (
        SELECT 0 AS num UNION ALL
        SELECT 1 UNION ALL
        SELECT 2 UNION ALL
        SELECT 3 UNION ALL
        SELECT 4 UNION ALL
        SELECT 5 UNION ALL
        SELECT 6 UNION ALL
        SELECT 7 UNION ALL
        SELECT 8 UNION ALL
        SELECT 9
    ) AS ones
    CROSS JOIN (
        SELECT 0 AS num UNION ALL -- Добавили 0!
        SELECT 10 UNION ALL
        SELECT 20 UNION ALL
        SELECT 30 UNION ALL
        SELECT 40 UNION ALL
        SELECT 50 UNION ALL
        SELECT 60 UNION ALL
        SELECT 70 UNION ALL
        SELECT 80 UNION ALL
        SELECT 90
    ) AS tens
    CROSS JOIN (
        SELECT 0 AS num UNION ALL
        SELECT 100 UNION ALL
        SELECT 200 UNION ALL
        SELECT 300
    ) AS hundreds
ORDER BY total_num;

%%sql
SELECT ones.num + tens.num + hundreds.num AS total_num
FROM (
    SELECT 0 AS num UNION ALL
    SELECT 1 UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4 UNION ALL
    SELECT 5 UNION ALL
    SELECT 6 UNION ALL
    SELECT 7 UNION ALL
    SELECT 8 UNION ALL
    SELECT 9
) AS ones
CROSS JOIN (
    SELECT 0 AS num UNION ALL
    SELECT 10 UNION ALL
    SELECT 20 UNION ALL
    SELECT 30 UNION ALL
    SELECT 40 UNION ALL
    SELECT 50 UNION ALL
    SELECT 60 UNION ALL
    SELECT 70 UNION ALL
    SELECT 80 UNION ALL
    SELECT 90
) AS tens
CROSS JOIN (
    SELECT 0 AS num UNION ALL
    SELECT 100 UNION ALL
    SELECT 200 UNION ALL
    SELECT 300
) AS hundreds
ORDER BY total_num;

Если возьмете декартово произведение трех множеств {0, 1, 2, 3, 4, 5, 6, 7, 8, 9}, {0, 10, 20, 30, 40, 50, 60, 70, 80, 90} и {0, 100, 200, 300} и сложите значения в трех столбцах, то получите набор результатов из 400 строк, содержащий все числа от 0 до 399. Хотя это больше, чем 366 строк, необходимых для создания набора дней в 2020 году, избавиться от лишних строк очень легко, и вскоре я это продемонстрирую.

Следующим шагом является преобразование набора чисел в набор дат. Для этого используем функцию date_add для добавления каждого числа в результирующем набора к 1 января 2020 года. Затем добавим условие фильтрации, чтобы отбросить все даты, относящиеся к 2021 году:

%%sql
SELECT
    DATE_ADD(
        '2020-01-01',
        INTERVAL (ones.num + tens.num + hundreds.num) DAY
    ) AS dt
FROM (
    SELECT 0 AS num UNION ALL
    SELECT 1 UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4 UNION ALL
    SELECT 5 UNION ALL
    SELECT 6 UNION ALL
    SELECT 7 UNION ALL
    SELECT 8 UNION ALL
    SELECT 9
) AS ones
CROSS JOIN (
    SELECT 0 AS num UNION ALL
    SELECT 10 UNION ALL
    SELECT 20 UNION ALL
    SELECT 30 UNION ALL
    SELECT 40 UNION ALL
    SELECT 50 UNION ALL
    SELECT 60 UNION ALL
    SELECT 70 UNION ALL
    SELECT 80 UNION ALL
    SELECT 90
) AS tens
CROSS JOIN (
    SELECT 0 AS num UNION ALL
    SELECT 100 UNION ALL
    SELECT 200 UNION ALL
    SELECT 300
) AS hundreds
WHERE
    DATE_ADD(
        '2020-01-01',
        INTERVAL (ones.num + tens.num + hundreds.num) DAY
    ) < DATE '2021-01-01'
ORDER BY dt;

Приятным в этом подходе является то, что набор результатов автоматически включает дополнительный високосный день (29 февраля) без вашего вмешательства, поскольку сервер базы данных вычисляет его, когда добавляет 59 дней к 1 января 2020 года.

Теперь, когда у вас есть механизм для создания всех дней в 2020 году, что же с ним делать? Ну, вас могут попросить создать отчет, который будет показывать каждый день в 2020 году вместе с количеством прокатов фильмов в этот день. Отчет должен содержать каждый день года, включая дни, когда фильмы напрокат никто не брал.

Вот как может выглядеть такой запрос (с использованием 2005 года для сопоставления данных в таблице rental):

Оригинальный код автора

Исправлены пляшущие отступы в 1, 2, 3 и 12 пробелов на чёткую сетку с шагом 4 пробела;
Убран используемый в книжной вёрстке трюк сброса отступа для экономии ширины (Outdent / Margin Flush).

SELECT days.dt, COUNT(r.rental_id) AS num_rentals
FROM rental r
    RIGHT JOIN
    (SELECT DATE_ADD('2005-01-01',
        INTERVAL (ones.num + tens.num + hundreds.num) DAY) AS dt
    FROM
        (SELECT 0 AS num UNION ALL
         SELECT 1 UNION ALL
         SELECT 2 UNION ALL
         SELECT 3 UNION ALL
         SELECT 4 UNION ALL
         SELECT 5 UNION ALL
         SELECT 6 UNION ALL
         SELECT 7 UNION ALL
         SELECT 8 UNION ALL
         SELECT 9) AS ones
        CROSS JOIN
        (SELECT 0 AS num UNION ALL
         SELECT 10 UNION ALL
         SELECT 20 UNION ALL
         SELECT 30 UNION ALL
         SELECT 40 UNION ALL
         SELECT 50 UNION ALL
         SELECT 60 UNION ALL
         SELECT 70 UNION ALL
         SELECT 80 UNION ALL
         SELECT 90) AS tens
        CROSS JOIN
        (SELECT 0 AS num UNION ALL
         SELECT 100 UNION ALL
         SELECT 200 UNION ALL
         SELECT 300) AS hundreds
    WHERE DATE_ADD('2005-01-01',
        INTERVAL (ones.num + tens.num + hundreds.num) DAY)
        < DATE '2006-01-01'
    ) AS days
        ON days.dt = DATE(r.rental_date)
GROUP BY days.dt
ORDER BY days.dt;

%%sql
SELECT
    days.dt,
    COUNT(r.rental_id) AS num_rentals
FROM rental r
    RIGHT JOIN (
        SELECT
            DATE_ADD(
                '2005-01-01',
                INTERVAL (ones.num + tens.num + hundreds.num) DAY
            ) AS dt
        FROM (
            SELECT 0 AS num UNION ALL
            SELECT 1 UNION ALL
            SELECT 2 UNION ALL
            SELECT 3 UNION ALL
            SELECT 4 UNION ALL
            SELECT 5 UNION ALL
            SELECT 6 UNION ALL
            SELECT 7 UNION ALL
            SELECT 8 UNION ALL
            SELECT 9
        ) AS ones
        CROSS JOIN (
            SELECT 0 AS num UNION ALL
            SELECT 10 UNION ALL
            SELECT 20 UNION ALL
            SELECT 30 UNION ALL
            SELECT 40 UNION ALL
            SELECT 50 UNION ALL
            SELECT 60 UNION ALL
            SELECT 70 UNION ALL
            SELECT 80 UNION ALL
            SELECT 90
        ) AS tens
        CROSS JOIN (
            SELECT 0 AS num UNION ALL
            SELECT 100 UNION ALL
            SELECT 200 UNION ALL
            SELECT 300
        ) AS hundreds
        WHERE
            DATE_ADD(
                '2005-01-01',
                INTERVAL (ones.num + tens.num + hundreds.num) DAY
            ) < DATE '2006-01-01'
    ) AS days
        ON days.dt = DATE(r.rental_date)
GROUP BY days.dt
HAVING dt BETWEEN DATE '2005-05-15' AND DATE '2005-05-31'
ORDER BY days.dt;

Это не самое элегантное решение данной задачи, но оно должно служить примером того, как при небольшом творчестве и твердом знании языка можно сделать даже такую редко используемую функцию как перекрестное соединение мощным инструментом в своем наборе средств SQL.

Естественные соединения¶

Если вы ленивы, то можете выбрать тип соединения, который позволит вам указать таблицы, которые необходимо соединить, но при этом позволит серверу базы данных самому определять, какими должны быть условия соединения.

Этот тип соединения, известный как естественное соединение, основывается при выводе условий соединения на идентичности имен столбцов в нескольких таблицах.

Например, таблица rental включает столбец с именем customer_id, который является внешним ключом к таблице customer, первичный ключ которой также имеет имя customer_id. Таким образом, вы можете попробовать написать запрос, который использует естественное соединение двух таблиц:

%%sql
SELECT c.first_name, c.last_name, DATE(r.rental_date)
FROM customer c
    NATURAL JOIN rental r;

Поскольку мы указали естественное соединение, сервер проверяет определения таблиц и добавляет условие r.customer_id = c.customer_id для соединения двух таблиц. Это могло бы получиться, но в схеме Sakila все таблицы включают столбец last_update, чтобы знать когда каждая строка была в последний раз изменена. Поэтому сервер добавляет еще одно улсовие соединения r.last_update = c.last_update, которое и приводит к тому, что запрос не возвращает никакие данные.

Единственный способ обойти эту проблему – использовать подзапрос, чтобы ограничить столбцы как минимум одной таблицей:

%%sql
SELECT cust.first_name, cust.last_name, DATE(r.rental_date)
FROM (
    SELECT customer_id, first_name, last_name
    FROM customer
    ) AS cust
    NATURAL JOIN rental r;

Подумайте и решите, стоит ли снижение износа пальцев и клавиатуры (благодаря отказу от указания условия соединения) дополнительных хлопот? Вряд ли! Так что следует избегать этого типа соединения и использовать внутренние соединения с явными условиями.

Упражнения¶

%config SqlMagic.autopandas = False

Упражнение 10.1¶

Используя следующие определения таблиц и данные, напишите запрос который возвращает имя каждого клиента вместе с его суммами платежей:

            Customer:
Customer_id Name
----------- ---------------
1           John Smith
2           Kathy Jones
3           Greg Oliver

            Payment:
Payment_id Customer_id Amount
---------- ----------- --------
101        1           8.99
102        3           4.99
103        1           7.99

Включите в результирующий набора всех клиентов, даже если для клиента нет записей о платежах.

-- Решение
SELECT c.name, SUM(p.amount)
FROM customer c
    LEFT JOIN payment p
    	ON c.customer_id = p.customer_id
GROUP BY c.name;

%%sql
-- Решение с выводом результата
SELECT
    c.name,
    SUM(p.amount) AS tot_payments
FROM (
    SELECT 1 AS customer_id, 'John Smith' AS name
    UNION ALL
    SELECT 2, 'Kathy Jones'
    UNION ALL
    SELECT 3, 'Greg Oliver'
) AS c
LEFT JOIN (
    SELECT 101 AS payment_id, 1 AS customer_id, 8.99 AS amount
    UNION ALL
    SELECT 102, 3, 4.99
    UNION ALL
    SELECT 103, 1, 7.99
) AS p
    ON c.customer_id = p.customer_id
GROUP BY
    c.customer_id,
    c.name;
/* 
В реальных данных всегда группируют по первичному ключу customer_id,
иначе однофамильцы (например, два разных John Smith)
схлопнутся в одну строку с общей суммой
*/

Упражнение 10.2¶

Измените запрос из упражнения 10.1 таким образом, чтобы использовать другой тип внешнего соединения (например, если вы использовали левое внешнее соединение в упражнении 10.1, на этот раз используйте правое внешнее соединение) так, чтобы результаты были идентичны полученным ранее.

-- Решение
SELECT c.name, SUM(p.amount)
FROM payment p
    RIGHT JOIN customer c
        ON p.customer_id = c.customer_id
GROUP BY c.name;

%%sql
SELECT
    c.name,
    SUM(p.amount) AS tot_payments
FROM (
    SELECT 101 AS payment_id, 1 AS customer_id, 8.99 AS amount
    UNION ALL
    SELECT 102, 3, 4.99
    UNION ALL
    SELECT 103, 1, 7.99
) AS p
RIGHT JOIN (
    SELECT 1 AS customer_id, 'John Smith' AS name
    UNION ALL
    SELECT 2, 'Kathy Jones'
    UNION ALL
    SELECT 3, 'Greg Oliver'
) AS c
    ON p.customer_id = c.customer_id
GROUP BY
    c.customer_id,
    c.name;

%config SqlMagic.autopandas = True

Упражнение 10.3¶

Разработайте запрос, который будет генерировать набор {1, 2, 3, ... 99, 100}. Указание: используйте перекрестное соединение как минимум с двумя подзапросами в предложении from.

%%sql
-- Решение автора (красивое)

SELECT ones.num + tens.num + 1 AS total_num
FROM (
    SELECT 0 AS num UNION ALL
    SELECT 1 UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4 UNION ALL
    SELECT 5 UNION ALL
    SELECT 6 UNION ALL
    SELECT 7 UNION ALL
    SELECT 8 UNION ALL
    SELECT 9
) AS ones
CROSS JOIN (
    SELECT 0 AS num UNION ALL
    SELECT 10 UNION ALL
    SELECT 20 UNION ALL
    SELECT 30 UNION ALL
    SELECT 40 UNION ALL
    SELECT 50 UNION ALL
    SELECT 60 UNION ALL
    SELECT 70 UNION ALL
    SELECT 80 UNION ALL
    SELECT 90
) AS tens
ORDER BY total_num;

Мои решения возможно менее красивые, но хороши для тренировки:

%%sql
-- Вариант по аналогии с обучающим в главе примером

SELECT ones.num + tens.num + hundreds.num AS total_num
FROM (
    SELECT 0 AS num UNION ALL
    SELECT 1 UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4 UNION ALL
    SELECT 5 UNION ALL
    SELECT 6 UNION ALL
    SELECT 7 UNION ALL
    SELECT 8 UNION ALL
    SELECT 9
) AS ones
CROSS JOIN (
    SELECT 0 AS num UNION ALL
    SELECT 10 UNION ALL
    SELECT 20 UNION ALL
    SELECT 30 UNION ALL
    SELECT 40 UNION ALL
    SELECT 50 UNION ALL
    SELECT 60 UNION ALL
    SELECT 70 UNION ALL
    SELECT 80 UNION ALL
    SELECT 90
) AS tens
CROSS JOIN (
    SELECT 0 AS num UNION ALL
    SELECT 100
) AS hundreds
WHERE (ones.num + tens.num + hundreds.num) BETWEEN 1 AND 100
ORDER BY total_num;

%%sql
-- Вариант с внешней обёрткой, благодаря которой 
-- в секции WHERE становится доступен алиас `total_num`

SELECT total_num
FROM (
    SELECT ones.num + tens.num + hundreds.num AS total_num
    FROM (
        SELECT 0 AS num UNION ALL
        SELECT 1 UNION ALL
        SELECT 2 UNION ALL
        SELECT 3 UNION ALL
        SELECT 4 UNION ALL
        SELECT 5 UNION ALL
        SELECT 6 UNION ALL
        SELECT 7 UNION ALL
        SELECT 8 UNION ALL
        SELECT 9
    ) AS ones
    CROSS JOIN (
        SELECT 0 AS num UNION ALL
        SELECT 10 UNION ALL
        SELECT 20 UNION ALL
        SELECT 30 UNION ALL
        SELECT 40 UNION ALL
        SELECT 50 UNION ALL
        SELECT 60 UNION ALL
        SELECT 70 UNION ALL
        SELECT 80 UNION ALL
        SELECT 90
    ) AS tens
    CROSS JOIN (
        SELECT 0 AS num UNION ALL
        SELECT 100
    ) AS hundreds
) AS t
WHERE total_num BETWEEN 1 AND 100
ORDER BY total_num;

%%sql
-- Вариант через CTE (Common table expressions)
-- глава 9, раздел `Обобщенные табличные выражения`

WITH ones AS (
    SELECT 0 AS num UNION ALL
    SELECT 1 UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4 UNION ALL
    SELECT 5 UNION ALL
    SELECT 6 UNION ALL
    SELECT 7 UNION ALL
    SELECT 8 UNION ALL
    SELECT 9
),

tens AS (
    SELECT 0 AS num UNION ALL
    SELECT 10 UNION ALL
    SELECT 20 UNION ALL
    SELECT 30 UNION ALL
    SELECT 40 UNION ALL
    SELECT 50 UNION ALL
    SELECT 60 UNION ALL
    SELECT 70 UNION ALL
    SELECT 80 UNION ALL
    SELECT 90
),

hundreds AS (
    SELECT 0 AS num UNION ALL
    SELECT 100
),

calculated_numbers AS (
    SELECT ones.num + tens.num + hundreds.num AS total_num
    FROM ones
        CROSS JOIN tens
        CROSS JOIN hundreds
)

SELECT total_num
FROM calculated_numbers
WHERE total_num BETWEEN 1 AND 100
ORDER BY total_num;