Глава 6. Работа с множествами

from sqlalchemy import create_engine
from sqlalchemy.engine import URL


connection_url = URL.create(
    drivername="mysql+pymysql",
    host="localhost",
    port=3306,
    database="sakila",
    username="root",
    password="********",
)

engine = create_engine(connection_url)

%load_ext sql

%config SqlMagic.displaylimit = 20
%config SqlMagic.displaycon = False
# %config SqlMagic.feedback = False

%sql engine

print("SQLAlchemy - подключение создано")
print("JupySQL - успешно подключен через SQLAlchemy Engine!")

SQLAlchemy - подключение создано
JupySQL - успешно подключен через SQLAlchemy Engine!

Основы теории множеств¶

A union B – объединение
A intersect B – пересечение; удаляет все повторяющиеся строки, обнаруженные в области перекрытия наборов данных.
А except B – исключение; возвращает первый результирующий набор за вычетом любого перекрытия со вторым результирующим набором.

Теория множеств на практике¶

%%sql
desc customer;

Loading...

%%sql
desc city;

Loading...

%%sql
SELECT 1 num, 'abc' str
UNION
SELECT 9 num, 'xyz' str;

Loading...

Операторы для работы с множествами¶

Оператор union¶

union – сортирует объединенный набор и удаляет дубликаты
union all – этого не делает, оставляет перекрывающиеся данные

import pandas as pd
print(pd.__version__)

3.0.5

%config SqlMagic.autopandas = True

%%sql
SELECT 'CUST' typ, c.first_name, c.last_name
FROM customer c
UNION ALL
SELECT 'ACTR' typ, a.first_name, a.last_name
FROM actor a;

Loading...

Запрос возвращает 799 строк: 599 строк из таблицы customer и 200 строк из таблицы actor.

%%sql
SELECT 'ACTR' typ, a.first_name, a.last_name
FROM actor a
UNION ALL
SELECT 'ACTR' typ, a.first_name, a.last_name
FROM actor a;

Loading...

200 строк из таблицы actor включаются в результирующий набор дважды.

%%sql
SELECT c.first_name, c.last_name
FROM customer c
WHERE c.first_name LIKE 'J%' AND c.last_name LIKE 'D%'
UNION ALL
SELECT a.first_name, a.last_name
FROM actor a
WHERE a.first_name LIKE 'J%' AND a.last_name LIKE 'D%';

Loading...

Из пяти строк одна является дубликатом JENNIFER DAVIS. Чтобы исключить повторяющиеся строки воспользуемся UNION вместо UNION ALL:

%%sql
SELECT c.first_name, c.last_name
FROM customer c
WHERE c.first_name LIKE 'J%' AND c.last_name LIKE 'D%'
UNION
SELECT a.first_name, a.last_name
FROM actor a
WHERE a.first_name LIKE 'J%' AND a.last_name LIKE 'D%';

Loading...

Оператор intersect¶

Удаляет все повторяющиеся строки, обнаруженные в области перекрытия наборов данных.

%%sql
SELECT c.first_name, c.last_name
FROM customer c
WHERE c.first_name LIKE 'J%' AND c.last_name LIKE 'D%'
INTERSECT
SELECT a.first_name, a.last_name
FROM actor a
WHERE a.first_name LIKE 'J%' AND a.last_name LIKE 'D%';

Loading...

Пересечение двух запросов дает единственное имя JENNIFER DAVIS, имеющееся в результирующих наборах обоих запросов.

%%sql
SELECT c.first_name, c.last_name
FROM customer c
WHERE c.first_name LIKE 'D%' AND c.last_name LIKE 'T%'
INTERSECT
SELECT a.first_name, a.last_name
FROM actor a
WHERE a.first_name LIKE 'D%' AND a.last_name LIKE 'T%';

Loading...

Оператор except¶

Возвращает первый результирующий набор за вычетом любого перекрытия со вторым результирующим набором.

%%sql
SELECT a.first_name, a.last_name
FROM actor a
WHERE a.first_name LIKE 'J%' AND a.last_name LIKE 'D%'
EXCEPT
SELECT c.first_name, c.last_name
FROM customer c
WHERE c.first_name LIKE 'J%' AND c.last_name LIKE 'D%';

Loading...

Правила применения операторов для работы с множествами¶

Сортировка результатов составного запроса¶

При указании имен столбцов в предложении order by нужно выбирать одно из имен столбцов в первом запросе составного запроса.

%%sql
SELECT a.first_name fname, a.last_name lname
FROM actor a
WHERE a.first_name LIKE 'J%' AND a.last_name LIKE 'D%'
UNION ALL
SELECT c.first_name, c.last_name
FROM customer c
WHERE c.first_name LIKE 'J%' AND c.last_name LIKE 'D%'
ORDER BY lname, fname;

Loading...

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

%%sql
SELECT a.first_name fname, a.last_name lname
FROM actor a
WHERE a.first_name LIKE 'J%' AND a.last_name LIKE 'D%'
UNION ALL
SELECT c.first_name, c.last_name
FROM customer c
WHERE c.first_name LIKE 'J%' AND c.last_name LIKE 'D%'
ORDER BY last_name, first_name;

RuntimeError: (pymysql.err.OperationalError) (1054, "Unknown column 'last_name' in 'order clause'")
[SQL: SELECT a.first_name fname, a.last_name lname
FROM actor a
WHERE a.first_name LIKE 'J%%' AND a.last_name LIKE 'D%%'
UNION ALL
SELECT c.first_name, c.last_name
FROM customer c
WHERE c.first_name LIKE 'J%%' AND c.last_name LIKE 'D%%'
ORDER BY last_name, first_name;]
(Background on this error at: https://sqlalche.me/e/20/e3q8)

%%sql
SELECT a.first_name fname, a.last_name lname
FROM actor a
WHERE a.first_name LIKE 'J%' AND a.last_name LIKE 'D%'
UNION ALL
SELECT c.first_name fname, c.last_name lname
FROM customer c
WHERE c.first_name LIKE 'J%' AND c.last_name LIKE 'D%'
ORDER BY lname, fname;

Loading...

%%sql
SELECT a.first_name, a.last_name
FROM actor a
WHERE a.first_name LIKE 'J%' AND a.last_name LIKE 'D%'
UNION ALL
SELECT c.first_name, c.last_name
FROM customer c
WHERE c.first_name LIKE 'J%' AND c.last_name LIKE 'D%'
ORDER BY last_name, first_name;

Loading...

Приоритеры операций над множествами¶

%%sql
SELECT a.first_name, a.last_name
FROM actor a
WHERE a.first_name LIKE 'J%' AND a.last_name LIKE 'D%'
UNION ALL
SELECT a.first_name, a.last_name
FROM actor a
WHERE a.first_name LIKE 'M%' AND a.last_name LIKE 'T%'
UNION
SELECT c.first_name, c.last_name
FROM customer c
WHERE c.first_name LIKE 'J%' AND c.last_name LIKE 'D%';

Loading...

Размещение операторов имеет значение.
Вот тот же составной запрос с обратным размещением операторов:

%%sql
SELECT a.first_name, a.last_name
FROM actor a
WHERE a.first_name LIKE 'J%' AND a.last_name LIKE 'D%'
UNION
SELECT a.first_name, a.last_name
FROM actor a
WHERE a.first_name LIKE 'M%' AND a.last_name LIKE 'T%'
UNION ALL
SELECT c.first_name, c.last_name
FROM customer c
WHERE c.first_name LIKE 'J%' AND c.last_name LIKE 'D%';

Loading...

%%sql
SELECT a.first_name, a.last_name
FROM actor a
WHERE a.first_name LIKE 'J%' AND a.last_name LIKE 'D%'
UNION
(SELECT a.first_name, a.last_name
FROM actor a
WHERE a.first_name LIKE 'M%' AND a.last_name LIKE 'T%'
UNION ALL
SELECT c.first_name, c.last_name
FROM customer c
WHERE c.first_name LIKE 'J%' AND c.last_name LIKE 'D%'
);

Loading...

Упражнения¶

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

Пусть множество А = {L, M, N, O, P}, а множество В = {P, Q, R, S, T}. Какие множества будут сгенерированы следующими операциями?

A union B
A union all B
A intersect B
A except B

%config SqlMagic.autopandas = False

%%sql
SELECT column_0 AS my_set   -- 3. Внешний запрос: берет колонку и переименовывает её
FROM (                      -- 1. Создаем виртуальную таблицу "на лету"
    VALUES ROW('L'), ROW('M'), ROW('N'), ROW('O'), ROW('P')
    UNION
    VALUES ROW('P'), ROW('Q'), ROW('R'), ROW('S'), ROW('T')
) sub;                      -- 2. Называем всю эту виртуальную таблицу именем "sub"

Loading...

%%sql
SELECT column_0 AS my_set
FROM (
    VALUES ROW('L'), ROW('M'), ROW('N'), ROW('O'), ROW('P')
    UNION ALL
    VALUES ROW('P'), ROW('Q'), ROW('R'), ROW('S'), ROW('T')
) sub;

Loading...

%%sql
SELECT column_0 AS my_set
FROM (
    VALUES ROW('L'), ROW('M'), ROW('N'), ROW('O'), ROW('P')
    INTERSECT
    VALUES ROW('P'), ROW('Q'), ROW('R'), ROW('S'), ROW('T')
) sub;

Loading...

%%sql
SELECT column_0 AS my_set
FROM (
    VALUES ROW('L'), ROW('M'), ROW('N'), ROW('O'), ROW('P')
    EXCEPT
    VALUES ROW('P'), ROW('Q'), ROW('R'), ROW('S'), ROW('T')
) sub;

Loading...

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

Напишите составной запрос, который находит имена и фамилии всех актеров и клиентов, чьи фамилии начинаются с буквы L.

%config SqlMagic.displaylimit = 25

%%sql
SELECT a.first_name, a.last_name
FROM actor a
WHERE a.last_name LIKE 'L%'
UNION
SELECT c.first_name, c.last_name
FROM customer c
WHERE c.last_name LIKE 'L%';

Loading...

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

Отсортируйте результаты выполнения упражнения 6.2 по столбцу last_name.

%%sql
SELECT a.first_name fname, a.last_name lname
FROM actor a
WHERE a.last_name LIKE 'L%'
UNION
SELECT c.first_name, c.last_name
FROM customer c
WHERE c.last_name LIKE 'L%'
ORDER BY lname, fname;

Loading...