Глава 4. Фильтрация - Изучаем SQL. Алан Болье

Abstract¶

В последнем запросе главы, в разделе Этот таинственный null, увидим причину, по которой для усечения строк в дальнейшем будет использоваться Pandas.

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="*UHB5rdx",
)

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!

Вычисление условий¶

-- Разделение условий оператором `AND`
WHERE first_name = 'STEVEN' AND create_date > '2006-01-01`

-- Разделение условий оператором `OR`
WHERE first_name = 'STEVEN' OR create_date > '2006-01-01`

-- Использование скобок
WHERE (first_name = 'STEVEN' OR last_name = 'YOUNG')
  AND create_date > '2006-01-01')

-- Использование оператора `NOT`
WHERE NOT (first_name = 'STEVEN' OR last_name = 'YOUNG')
  AND create_date > '2006-01-01')

-- Аналог без оператора `NOT`
WHERE first_name <> 'STEVEN' AND last_name <> 'YOUNG'    -- OR заменен на AND !
  AND create_date > '2006-01-01'

Вычисление условий с операторами OR, AND и NOT

# Вычисление двух условий с оператором OR

| Условие              | Результат |
| -------------------- | --------- |
| WHERE true OR true   | true      |
| WHERE true OR false  | true      |
| WHERE false OR true  | true      |
| WHERE false OR false | false     |

# Вычисление трех условий с операторами AND и OR

| Условие                          | Результат |
| -------------------------------- | --------- |
| WHERE (true OR true) AND true    | true      |
| WHERE (true OR false) AND true   | true      |
| WHERE (false OR true) AND true   | true      |
| WHERE (false OR false) AND true  | false     |
| WHERE (true OR true) AND false   | false     |
| WHERE (true OR false) AND false  | false     |
| WHERE (false OR true) AND false  | false     |
| WHERE (false OR false) AND false | false     |

# Вычисление трех условий с операторами AND, OR и NOT

| Условие                              | Результат |
| ------------------------------------ | --------- |
| WHERE NOT (true OR true) AND true    | false     |
| WHERE NOT (true OR false) AND true   | false     |
| WHERE NOT (false OR true) AND true   | false     |
| WHERE NOT (false OR false) AND true  | true      |
| WHERE NOT (true OR true) AND false   | false     |
| WHERE NOT (true OR false) AND false  | false     |
| WHERE NOT (false OR true) AND false  | false     |
| WHERE NOT (false OR false) AND false | false     |

Построение условия¶

Условие состоит из одного или нескольких выражений в сочетании с одним или несколькими операторами.

Выражением может быть

Число
Столбец таблицы или представления
Встроенная функция, такая как concat('Learning', ' ', 'SQL')
Подзапрос
Список выражений, такой как ('Boston', 'New York', 'Chicago')

Операторы используемые в условиях

Операторы	Состав
Сравнения	`=`, `!=`, `<`, `>`, `<>`, `like`, `in`, `between`
Арифметические	`+`, `-`, `*`, `/`

Типы условий¶

Условия равенства¶

title = 'RIVER OUTLAW'
fed_id = '111-11-1111'
amount = 375.25
film_id = (SELECT film_id FROM film WHERE title = 'RIVER OUTLAW')

%%time
%%sql
SELECT c.email
FROM customer c
  INNER JOIN rental r
  ON c.customer_id = r.customer_id
WHERE date(r.rental_date) = '2005-06-14';

CPU times: total: 0 ns
Wall time: 45 ms

Условия неравенства¶

import polars as pl
print(f"Polars ver. {pl.__version__}")

Polars ver. 1.43.1

%config SqlMagic.autopolars = True

%%sql
SELECT c.email
FROM customer c
  INNER JOIN rental r
  ON c.customer_id = r.customer_id
WHERE date(r.rental_date) <> '2005-06-14';

-- Модификация условий равенства/неравенства

DELETE FROM rental
WHERE year(rental_date) = 2024;

DELETE FROM rental
WHERE year(rental_date) <> 2005 AND year(rental_date) <> 2006;

Условие диапазона¶

%%sql
SELECT customer_id, rental_id
FROM rental
WHERE rental_date < '2005-05-25';

%%sql
SELECT customer_id, rental_date
FROM rental
WHERE rental_date <= '2005-06-16'
  AND rental_date >= '2005-06-14';

Оператор beetween¶

%%sql
SELECT customer_id, rental_date
FROM rental
WHERE rental_date BETWEEN '2005-06-14' AND '2005-06-16';

Поскольку не указываем компонент времени, а время по умолчанию полночь, то диапазон длится от 2005-06-14 00:00:00 до 2005-06-16 00:00:00 и включает все фильмы взятые напрокат 14 или 15 июня.

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

%%sql
SELECT customer_id, rental_date
FROM rental
WHERE rental_date BETWEEN '2005-06-16' AND '2005-06-14';

%%sql
SELECT customer_id, rental_date
FROM rental
WHERE rental_date >= '2005-06-16'
  AND rental_date <= '2005-06-14';

%%sql
SELECT customer_id, payment_id, amount
FROM payment
WHERE amount BETWEEN 10.0 AND 11.99;

Строковые диапазоны¶

%%sql
SELECT last_name, first_name
FROM customer
WHERE last_name BETWEEN 'FA' AND 'FR';

Фамилии начинающиеся с FR не попали в результат, т.к. находятся за пределами диапазона. Можем расширить диапазон:

%%sql
SELECT last_name, first_name
FROM customer
WHERE last_name BETWEEN 'FA' AND 'FRB';

Условия членства¶

%%sql
SELECT title, rating
FROM film
WHERE rating = 'G' OR rating = 'PG';

Оператор in¶

%%sql
SELECT title, rating
FROM film
WHERE rating IN ('G', 'PG');

Использование подзапросов¶

%%sql
SELECT title, rating
FROM film
WHERE rating IN (SELECT rating FROM film WHERE title LIKE '%PET%');

-- Пояснение подзапроса
SELECT title, rating FROM film WHERE title LIKE '%PET%';

| title           | rating |
| --------------- | ------ |
| "MALKOVICH PET" | "G"    |
| "MUPPET MILE"   | "PG"   |
| "PET HAUNTING"  | "PG"   |

Использование not in¶

%%sql
SELECT title, rating
FROM film
WHERE rating NOT IN ('PG-13', 'R', 'NC-17');

Условия соответствия¶

%%sql
SELECT last_name, first_name
FROM customer
WHERE left(last_name, 1) = 'Q';

Использование подстановочных знаков¶

Подстановочные символы

Подстановочный символ	Соответствие
`_`	В точности один символ
`%`	Любое количество символов, включая 0

Примеры выражений поиска

Выражение поиска	Интерпретация
`F%`	Строка, начинающаяся с F
`%f`	Строка, заканчивающаяся f
`%bas%`	Строка, содержащая подстроку `bas`
`__t_`	Строка из 4 символов с t в третьей позиции
`___-__-____`	Строка из 11 символов с дефисами в 4-й и 7-й позициях

%%sql
SELECT last_name, first_name
FROM customer
WHERE last_name LIKE '_A_T%S';

%%sql
SELECT last_name, first_name
FROM customer
WHERE last_name LIKE 'Q%' OR last_name LIKE 'Y%';

Использование регулярных выражений¶

%%sql
SELECT last_name, first_name
FROM customer
WHERE last_name REGEXP '^[QY]';

Этот таинственный null¶

%%sql
SELECT rental_id, customer_id
FROM rental
WHERE return_date IS NULL;

Выражение может быть null, но оно никогда не может быть равным null:

%%sql
SELECT rental_id, customer_id
FROM rental
WHERE return_date = NULL;

%%sql
SELECT rental_id, customer_id, return_date
FROM rental
WHERE return_date IS NOT NULL;

%%sql
SELECT rental_id, customer_id, return_date
FROM rental
WHERE return_date NOT BETWEEN '2005-05-01' AND '2005-09-01';

%%sql
SELECT rental_id, customer_id, return_date
FROM rental
WHERE return_date IS NULL
  OR return_date NOT BETWEEN '2005-05-01' AND '2005-09-01';

Polars выдал ошибку ComputeError. Очистил длинный вывод через Clear Cell Output и добавил заметку:

Библиотека Polars упала с ошибкой ComputeError

Polars не смог автоматически распознать тип данных для столбца return_date, так как функция парсинга Polars (iterable_to_pydf) споткнулась: она начала строить колонку на основе первых попавшихся None, зафиксировала схему, а затем внезапно встретила реальный объект даты datetime.datetime. Произошел конфликт типов прямо во время сборки таблицы, из-за чего библиотека и выбросила ComputeError.

Можем просто отключить автоконвертацию в Polars DataFrame и получать стандартный вывод JupySQL без усечения через многоточие большого количества строк, закончив эксперименты с Polars;
Я все-таки хочу сохранить усечение в большом выводе, поэтому вернусь к старому доброму Pandas DataFrame, который менее строго типизирован. Пропуски в датах Pandas приведет к специальному маркеру NaT (Not a Time) – стандартному обозначению пустого временного значения для Pandas.

%config SqlMagic.autopolars = False

import pandas as pd
print(f"Pandas ver. {pd.__version__}")

Pandas ver. 3.0.3

%config SqlMagic.autopandas = True

%%sql
SELECT rental_id, customer_id, return_date
FROM rental
WHERE return_date IS NULL
  OR return_date NOT BETWEEN '2005-05-01' AND '2005-09-01';

Упражнения¶

Предлагаемые упражнения призваны закрепить понимание условий фильтрации. В первых двух упражнениях потребуется подмножество строк из таблицы payment:

| payment_id | customer_id | amount | date(payment date) |
| ---------- | ----------- | ------ | ------------------ |
| 101        | 4           | 8.99   | 2005-08-19         |
| 102        | 4           | 1.99   | 2005-08-19         |
| 103        | 4           | 2.99   | 2005-08-20         |
| 104        | 4           | 6.99   | 2005-08-20         |
| 105        | 4           | 4.99   | 2005-08-21         |
| 106        | 4           | 2.99   | 2005-08-22         |
| 107        | 4           | 1.99   | 2005-08-23         |
| 108        | 5           | 0.99   | 2005-05-29         |
| 109        | 5           | 6.99   | 2005-05-31         |
| 110        | 5           | 1.99   | 2005-05-31         |
| 111        | 5           | 3.99   | 2005-06-15         |
| 112        | 5           | 2.99   | 2005-06-16         |
| 113        | 5           | 4.99   | 2005-06-17         |
| 114        | 5           | 2.99   | 2005-06-19         |
| 115        | 5           | 4.99   | 2005-06-20         |
| 116        | 5           | 4.99   | 2005-07-06         |
| 117        | 5           | 2.99   | 2005-07-08         |
| 118        | 5           | 4.99   | 2005-07-09         |
| 119        | 5           | 5.99   | 2005-07-09         |
| 120        | 5           | 1.99   | 2005-07-09         |

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

Какие из идентификаторов платежей будут возвращены при следующих условиях фильтрации?

customer_id <> 5
  AND (amount > 8 OR date(payment_date) = '2005-08-23')

| payment_id | customer_id | amount | date(payment date) |
| ---------- | ----------- | ------ | ------------------ |
| 101        | 4           | 8.99   | 2005-08-19         |
| 107        | 4           | 1.99   | 2005-08-23         |

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

Какие из идентификаторов платежей будут возвращены при следующих условиях фильтрации?

customer_id = 5 AND
  NOT (amount > 6 OR date(payment_date) = '2005-06-19')

| payment_id | customer_id | amount | date(payment date) |
| ---------- | ----------- | ------ | ------------------ |
| 108        | 5           | 0.99   | 2005-05-29         |
| 110        | 5           | 1.99   | 2005-05-31         |
| 111        | 5           | 3.99   | 2005-06-15         |
| 112        | 5           | 2.99   | 2005-06-16         |
| 113        | 5           | 4.99   | 2005-06-17         |
| 115        | 5           | 4.99   | 2005-06-20         |
| 116        | 5           | 4.99   | 2005-07-06         |
| 117        | 5           | 2.99   | 2005-07-08         |
| 118        | 5           | 4.99   | 2005-07-09         |
| 119        | 5           | 5.99   | 2005-07-09         |
| 120        | 5           | 1.99   | 2005-07-09         |

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

Создайте запрос, который извлекает из таблицы payments все строки, в которых сумма равна 1.98, 7.98 или 9.98.

%%sql
SELECT amount
FROM payment
WHERE amount IN (1.98, 7.98, 9.98)

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

Создайте запрос, который находит всех клиентов, в фамилиях которых содержится буква А во второй позиции и буква W – в любом месте после А.

%%sql
SELECT first_name, last_name
FROM customer
WHERE last_name LIKE '_A%W%'
ORDER BY last_name;