Всем привет! Меня зовут Андрей, и чуть больше трех лет я занимаюсь анализом данных и использую SQL для работы с разными БД. Сейчас работаю дата-аналитиком в компании Genesis. В статье расскажу о том, как писать SQL-скрипты для получения выборок (из нуля в один), на примерах.
Материал ориентирован на человека с начальным уровнем владения SQL для максимально быстрого вхождения в технологию.
Программа курса попала ко мне на «переосмысление», в результате чего из неё была выброшена история, теория, все, что касается проектирования и создания БД, а остались способы получения выборок данных.
Это все напомнило мне, как когда-то я сам был вынужден быстро освоить SQL. Шпаргалки содержали всевозможные типы соединений таблиц и минималистичные примеры, а онлайн-курсы рассказывали про нормальные формы, хранимые процедуры и типы данных, что мало подходило для моих целей.
Так и возникла идея написать статью, которая сможет последовательно и без воды рассказать, как использовать SQL для получения выборок из нескольких источников с нужными ограничениями, в нужном виде и разрезах. Особенности используемой БД, оптимизация и прочая теория остается для самоизучения.
Это первая часть материала. Вторая часть будет содержать некоторые фишки и приемы для сложных выборок, чтобы решить все практические задания по SQL, которые находятся на первых страницах выдачи Google.
Итак, начнем.
Допустим, у нас есть таблица users, содержащая данные о регистрациях пользователей, которая лежит в схеме (скажем, папке) product. В табличке есть несколько десятков колонок (полей). Выберем интересующие нас:
SELECT reg_dt, id, gender, age, country_code, app — список интересующих полей.
FROM product.users — источник, схема.название_таблицы.
LIMIT 5 — количество строк, которые нужно вывести.
Примечание. Большими буквами обозначим зарезервированные слова (команды).
Будет работать и с маленькими буквами, но так принято для наглядности.
Как говорит мой коллега: «SQL-запрос — это же просто английский текст. Возьми это отсюда, отфильтруй, отсортируй и так далее...»
Итак, мы получили табличку:
То же самое, отсортированное по возрасту и коду страны:
SELECT reg_dt, id, gender, age, country_code, app
FROM product.users
ORDER BY age DESC, country_code
— через запятую укажем поля, по которым будет отсортирована полученная выборка.
LIMIT 5 — эту строку можно удалить, тогда у нас будет список из всех пользователей (но их много).
По умолчанию команда ORDER BY отсортирует поле по возрастанию (текстовое по алфавиту, например), но, указав DESC (descending) после названия поля, мы зададим сортировку по убыванию.
(Чтобы выбрать все поля, укажите астериск * вместо названий полей SELECT * FROM product.users.)
SELECT reg_dt, id, gender, age, country_code, app
FROM product.users
WHERE gender = 'f' AND age > 45 – указываем условия для выводимых полей.
ORDER BY age DESC, country_code
'f' значение заключено в одинарные кавычки, чтобы указать, что это текст, а не название поля.
Порядок команд в запросе фиксированный:
SELECT 'столбцы или * для выбора всех столбцов; обязательно'
FROM 'таблица'*
WHERE 'условие/фильтрация; необязательно'
GROUP BY 'столбцы, по которым хотим сгруппировать данные; необязательно'
HAVING 'условие/фильтрация на уровне сгруппированных данных; необязательно'
ORDER BY 'столбцы, по которым хотим отсортировать вывод; необязательно'
*Если вдаваться в детали, можно написать запрос без указания таблицы, пример: SELECT 1 column1, 'text' column2. В данном блоке можно записать несколько источников, но об этом поговорим далее.
В условии WHERE можно использовать большое количество критериев, связанных логическими операторами AND и OR.
age > 45 (еще варианты: =, >, <, <=, >= и != не равно).
Стоит добавить, что 45 < age даст эквивалентный результат, кроме того, возможно сравнить поля между собой age > steps_count.
country_code IN ('US', 'PL', 'CL') Если нужно сравнить наши значения со значениями в списке, используем IN, указав в скобках возможные варианты через запятую.
Условия также можно задавать с приставкой «не» country_code NOT IN ('RU', 'VE').
Это исключит из выборки страны, перечисленные в списке.
BETWEEN '2020-02-02' AND '2020-07-04' — значение между указанными датами, включительно.
В SQL дата записывается в следующем формате: '2020-12-29' (год-месяц-день).
Дата и время: '2020-04-15 06:25:49.000009'. Если не требуется точность, то запись можно сократить '2020-04-15 06:25:49' (отброшенная справа часть времени будет заменена нулями '2020-04-15 06:25:49.000000'). Соответственно, когда мы фильтруем поле, которое содержит дату и время, указывая только дату BETWEEN '2020-02-02' AND '2020-07-04', это равносильно '2020-02-02 00:00:00.000000' AND '2020-07-04 00:00:00.000000'.
Для следующих примеров рассмотрим табличку:
Сравнивать строки можно конструкцией site LIKE '%.com'.
Эта формулировка поможет найти пользователей, зарегистрированных на ресурсах, которые содержат любой набор символов (задано зарезервированным символом %), затем .com, таким образом мы получим пользователей, зарегистрированных на ресурсе с доменом .com.
SELECT site FROM product.users WHERE site LIKE '%.com';
SELECT site FROM product.users WHERE site LIKE 'abcdefghij.%';
Символ % может содержаться в любой части строки или быть указан несколько раз. Означает «любой набор символов или отсутствие символа».
Написав 'a%.com', мы найдем пользователей в домене .com, где название сайта начинается с буквы a.
Есть еще один зарезервированный символ _ (нижнее подчеркивание), обозначающий один символ. Таким образом найдем пользователей ресурса, который содержит вторым символом букву m и заканчивается доменом .us, можно так: site LIKE '_m%.us'.
Как и в курсе школьной математики (умножение выполняется раньше, чем вычитание), операторы имеют порядок выполнения.
При использовании логического оператора OR («или») стоит быть осторожным и ставить скобки, четко обозначая альтернативы.
Если мы захотим найти пользователей, что зарегистрировались в конкретном временном диапазоне с доменом '%.com' или '%.us', напишем запрос:
SELECT reg_dt, gender, age, site
FROM product.users
WHERE reg_dt BETWEEN '2012-01-01' AND '2015-01-01'
AND site LIKE '%.us' OR site LIKE '%.com'
В SQL можно использовать переносы строк и отступы для форматирования, они не влияют на выполнение запроса.
Примечание. В условии хочется написать site LIKE '%.com' OR '%.us', но так не работает, после команды LIKE ожидается один искомый паттерн, нужно повторить конструкцию для второго паттерна.
Выполнив запрос, получим список пользователей. Некоторые из них зарегистрированы за пределами выделенного нами диапазона времени 2012–2014 гг.
А все потому, что указанное в конце условие OR отменяет все предыдущие условия, связанные оператором AND.
Это условие стоит интерпретировать как «дай указанные поля для пользователей, которые зарегистрированы в дату такую-то и имеют домен .us или просто имеют домен .com (без условия по дате)».
Происходит это как раз по той причине, что AND выполняется раньше, чем OR.
Если мы хотим, чтобы условие даты сохранялось для обоих доменов, стоит явно выделить альтернативы для оператора OR с помощью круглых скобок:
WHERE reg_dt BETWEEN '2012-01-01' AND '2015-01-01'
AND (site LIKE '%.us' OR site LIKE '%.com')
Условие может быть сформулировано и как результат преобразований:
SELECT age, site, LENGTH(site)
FROM product.users
WHERE age%2 != 0 OR LENGTH(site) > 17
Возраст — непарное число (остаток от деления на 2 не равен 0) или длина ресурса — больше 17 символов. Причем поле, для которого создано условие, необязательно выводить в SELECT.
В нашей схеме, кроме таблицы с регистрациями:
Есть таблица заказов:
И таблица-справочник для расшифровки типов сервисов:
Чтобы извлечь данные из дополнительных таблиц, используем LEFT JOIN, что означает, что наша исходная таблица users словно находится слева и мы приставляем к ней соответствующие строки из таблицы orders_paid.
SELECT u.reg_dt, u.gender AS sex, u.age, u.id, op.user_id, order_id,
op.dt order_dt, op.service_id, pay_services.*
FROM product.users AS u
LEFT JOIN product.orders_paid op ON u.id = op.user_id
LEFT JOIN dictionary.pay_services ON pay_services.id = op.service_id
WHERE u.reg_dt > '2013-02-02' – условия, применимы к полю из любой таблицы.
ORDER BY u.reg_dt DESC
Для поля gender мы использовали так называемый алиас (иначе говоря, переименовали для нашей результирующей таблицы в sex) u.gender AS sex. Для этого можно использовать слово AS или написать алиас через пробел, работает идентично dt order_dt.
Алиасы применимы и к названиям таблиц с тем же синтаксисом (с AS или без).
Чаще всего алиас для таблицы — это аббревиатура её названия orders_paid op.
Это удобнее, чем название t1, table_1, и позволяет ускорить запись op.user_id вместо orders_paid.user_id.
Несмотря на удобство сокращения, иногда лучше не использовать алиасы, так как это может усложнить понимание скрипта.
Как видно в примере order_id, можно использовать только название поля, без указания таблицы, если это название уникально. Но все же более наглядный и универсальный способ писать таблица.поле.
pay_services.* позволяет вывести все поля из таблицы pay_services.
В результате выполнения запроса получили следующую таблицу:
Для пользователей, которые совершили несколько покупок, строки из user задублировались.
Для пользователей, у которых нет оплат, мы получили значения NULL. Его нужно понимать как «нет записей в присоединяемой таблице».
Таблицы users и orders_paid связаны через ID покупателя, его называют link’ом (связью). К одной строке в пользователях может находиться несколько (в том числе 0) строк заказов, таким образом можно сказать, что эта связь имеет тип (1:М) один ко многим.
Для того чтобы корректно сопоставить строки таблиц, после ON указываются условия, которые должны выполняться, чтобы строка была добавлена в выборку. В нашем случае мы обозначаем, что строки из таблицы заказов должны быть выведены в тех случаях, когда идентификатор пользователя в таблице регистраций равен идентификатору пользователя в таблице оплат u.id = op.user_id.
Аналогично связаны ps.id = op.service_id за тем исключением, что справочник имеет только по одной записи для каждого идентификатора и не увеличивает количество строк.
Дальнейшее взаимодействие с полученным набором данных не отличается от одной таблицы — выбираем интересующие нас поля, фильтруем и сортируем.
Таким образом, если мы после объединения таблиц напишем условие, которое оставит только те строки, где идентификатор заказа неизвестен, получим список пользователей, для которых нет ни одного заказа.
SELECT u.reg_dt, u.gender, u.age, u.id, op.order_id
FROM product.users AS u
LEFT JOIN product.orders_paid op ON u.id = op.user_id
WHERE op.order_id IS NULL
Причем нам необязательно выводить поле, которое проверяем в WHERE.
Сравнение с NULL производится через IS, ведь NULL — неопределенное значение и не равно само себе. Поскольку если мы используем равенство, то результат сравнения order_id = NULL — ложь, даже если order_id примет значение NULL.
Чтобы найти строки для пользователей, которые имели заказы, модифицируем условие WHERE op.order_id IS NOT NULL или используем другой вид соединения таблиц.
LEFT JOIN оставляет все значения таблицы «слева» и добавляет значения из указанной таблицы, если выполняется условие в ON.
INNER JOIN, или JOIN, влияет на обе таблицы и полностью скрывает строку, в которой не выполняется условие ON.
Для нахождения строк, которые имеют соответствие в обеих таблицах, можем использовать JOIN product.orders_paid op ON u.id = op.user_id. Нужно учитывать особенность INNER JOIN скрывать значения.
Если JOIN’ы используются для горизонтального объединения таблиц, существует способ вертикально объединить выборки с помощью UNION:
SELECT id, country_code
FROM product.users
WHERE id IN (4446556074, 4436447691, 4446022755)
UNION ALL
SELECT id, country_code
FROM product.users
WHERE id IN (4446022755, 4436702697, 4481548107)
UNION ALL произведет вертикальное объединение всех строк.
UNION при этом отбросит дубликаты (за счет поиска дублей выполняется дольше).
Оба запроса должны иметь одинаковое количество столбцов и подходящие типы данных для колонок (если в первом запросе цифры в виде int, то они не смогут объединиться с VARCHAR во второй).
Таким образом можно объединить большое количество выборок с подходящим набором полей, получив на выходе одну таблицу.
SELECT u.id, op.user_id, op.order_id
FROM product.users u, product.orders_paid op
Что эквивалентно:
SELECT u.id, op.user_id, op.order_id
FROM product.users u
CROSS JOIN product.orders_paid op
В нашем случае для каждого пользователя будут записаны заказы всех пользователей.
Указав в условии WHERE, что ID пользователя должен быть общий для двух таблиц:
SELECT u.id, op.user_id, op.order_id
FROM product.users u, product.orders_paid op
WHERE u.id = op.user_id
Получим результат, аналогичный следующему:
SELECT u.id, op.user_id
FROM product.users u
INNER JOIN product.orders_paid op ON u.id = op.user_id
Каждому пользователю отнесены строки его заказов из таблицы orders_paid (исключая пользователей без заказов).
SELECT u.id, op.service_id, op.user_id
FROM product.users u
LEFT JOIN product.orders_paid op ON u.id = op.user_id AND op.service_id = 14
Сначала была произведена фильтрация таблицы orders_paid, и остались только все заказы сервиса № 14, затем — объединение. Таким образом получили из users всех зарегистрированных пользователей. Для пользователей, у которых есть заказ сервиса № 14, будут выведены строки из таблицы заказов, для остальных — NULL.
Когда условие написано в WHERE, оно фильтрует выборку уже после объединения таблиц.
До фильтрации:
SELECT u.id, op.service_id, op.user_id
FROM product.users u
LEFT JOIN product.orders_paid op ON u.id = op.user_id
После фильтрации:
SELECT u.id, op.service_id, op.user_id
FROM product.users u
LEFT JOIN product.orders_paid op ON u.id = op.user_id
WHERE op.service_id = 14
В итоге, несмотря на использование LEFT JOIN, мы получили аналогичное поведение, как в INNER JOIN (в способности скрывать строки, не подходящие под условие одной из таблиц).
Условия в блоке WHERE влияют на всю полученную после объединения выборку, это нужно учитывать. Как видно в примере выше ON u.id = op.user_id AND op.service_id = 14, блок ON не ограничивается равенством идентификаторов. Это просто блок с условиями (по тем же правилам, что и в WHERE), которые могут быть разными в зависимости от ситуации.
Одна и та же таблица может быть присоединена несколько раз или присоединена сама к себе. В нашей таблице с ордерами есть поле parent_order_id. Оно указывает на заказ, который инициировал подписку у пользователя. Таким образом первые платежи и продления хранятся в одной таблице, и мы связываем таблицу orders_paid саму с собой по идентификатору родительского платежа.
Так же привяжем справочник с названиями сервисов. Используем INNER JOIN для связи с orders_paid, чтобы остались только пользователи с заказами.
SELECT u.id, opp.order_id parent_order, opp.dt AS parent_dt, op.order_id, op.dt AS order_dt, ps.service_name
FROM product.users u
INNER JOIN product.orders_paid op ON u.id = op.user_id
LEFT JOIN product.orders_paid opp ON op.parent_order_id = opp.order_id
LEFT JOIN product.pay_services ps ON ps.id = op.service_id
ORDER BY op.order_id
Видим, что для инициирующего платежа parent_order = order_id. А сервис vip не имеет parent_order, так как это иной тип сервиса. Таким образом parent_dt — это дата начала подписки, а order_dt — дата конкретного платежа.
SUM, COUNT, AVG, MIN, MAX и так далее.
Возвращает одну строку:
SELECT COUNT(u.id)
FROM product.users u
8073565 — количество строк в таблице users.
Функция COUNT позволяет вписать *, чтобы подсчитать количество строк в выборке:
SELECT COUNT(*)
FROM product.users u
В большинстве случаев нам требуется считать величины в каких-либо разрезах, оставляя несгруппированные поля.
Мы можем найти дату первой регистрации и количество зарегистрированных пользователей в каждой стране отдельно для мужчин и женщин (упорядочено от самой большой группы):
SELECT country_code, gender, MIN(reg_dt), COUNT(id) id_count
FROM product.users
GROUP BY country_code, gender
ORDER BY COUNT(id) DESC
Общее правило будет звучать так: для агрегации данных нужно внести все поля разрезов (поля, что не должны быть агрегированы) в конструкцию GROUP BY, а для полей, что должны быть агрегированы, — выбрать агрегатные функции. Также, разумеется, доступны математические преобразования +, -, *, /.
Если в SELECT указаны только агрегированные выражения, GROUP BY отсутствует.
Если в SELECT все поля без агрегаций и они же в GROUP BY — запрос вернет только уникальные значения.
Важно держать в уме уровень детализации данных, который получается на каждом этапе запроса. Иначе говоря, чему соответствует одна строка данных в результате выполнения запроса.
SELECT u.country_code,
COUNT(DISTINCT u.id) registered,
COUNT(DISTINCT op.user_id) payers,
COUNT(DISTINCT op.user_id) / COUNT(DISTINCT u.id) * 100 conv
FROM product.users u
LEFT JOIN product.orders_paid op ON u.id = op.user_id
WHERE reg_dt BETWEEN '2020-02-02' AND '2020-02-03'
GROUP BY u.country_code
HAVING COUNT(DISTINCT op.user_id) > 100
Этим запросом мы хотим посчитать процент пользователей, которые совершили хотя бы одну оплату. Для этого нужно количество уникальных пользователей COUNT(DISTINCT u.id), что зарегистрировались, поделить на количество уникальных пользователей из таблицы оплат COUNT(DISTINCT op.user_id). Слово DISTINCT удалит дубликаты.
Подсчет мы проведем отдельно для каждой локации GROUP BY u.country_code.
Из полученной выборки HAVING COUNT(DISTINCT op.user_id) > 100 оставим только строки, что соответствуют условию, в нашем случае это локации, в которых больше 100 плательщиков.
В блоке WHERE указываются условия, относящиеся к данным в исходных таблицах, а в HAVING — условия, которые относятся к результатам агрегации.
Запрос обрабатывается в следующем порядке:
SELECT u.country_code, u.id id_from_u, op.user_id id_from_op, op.order_id*
FROM product.users u
LEFT JOIN product.orders_paid op ON u.id = op.user_id
WHERE reg_dt BETWEEN '2020-02-02' AND '2020-02-03'
*op.order_id выведено для демонстрации.
Видим, что для пользователей, у которых больше одного заказа, появилось по несколько строчек. И если бы мы использовали COUNT без DISTINCT, то подсчитали бы количество пользователей, умноженное на количество их заказов.
Затем будут проведены агрегации и удаление из списка локаций с малым количеством пользователей HAVING COUNT(DISTINCT op.user_id) > 100.
Агрегатные функции будут подсчитаны, не учитывая значения NULL.
В следующей части рассмотрим выделение признаков, подзапросы, работу с временными промежутками, транспонирование и другие приемы, с которыми сталкивается аналитик в работе.
dou.ua
Материал ориентирован на человека с начальным уровнем владения SQL для максимально быстрого вхождения в технологию.
Предыстория
Однажды компания, в которой я занимал должность Data Analyst, решила обучить персонал основам работы с базой данных и для этого наняла преподавателя. Мотивация была в том, чтобы коллеги, имея read-only доступы, получали актуальную информацию и простую статистику, не обращаясь к техническим специалистам. Спустя три занятия ученики не написали ни одного запроса и были недовольны материалом.Программа курса попала ко мне на «переосмысление», в результате чего из неё была выброшена история, теория, все, что касается проектирования и создания БД, а остались способы получения выборок данных.
Это все напомнило мне, как когда-то я сам был вынужден быстро освоить SQL. Шпаргалки содержали всевозможные типы соединений таблиц и минималистичные примеры, а онлайн-курсы рассказывали про нормальные формы, хранимые процедуры и типы данных, что мало подходило для моих целей.
Так и возникла идея написать статью, которая сможет последовательно и без воды рассказать, как использовать SQL для получения выборок из нескольких источников с нужными ограничениями, в нужном виде и разрезах. Особенности используемой БД, оптимизация и прочая теория остается для самоизучения.
Это первая часть материала. Вторая часть будет содержать некоторые фишки и приемы для сложных выборок, чтобы решить все практические задания по SQL, которые находятся на первых страницах выдачи Google.
Итак, начнем.
Первый SELECT, сортировка, LIMIT
В базе данных информация хранится в табличках.Допустим, у нас есть таблица users, содержащая данные о регистрациях пользователей, которая лежит в схеме (скажем, папке) product. В табличке есть несколько десятков колонок (полей). Выберем интересующие нас:
SELECT reg_dt, id, gender, age, country_code, app — список интересующих полей.
FROM product.users — источник, схема.название_таблицы.
LIMIT 5 — количество строк, которые нужно вывести.
Примечание. Большими буквами обозначим зарезервированные слова (команды).
Будет работать и с маленькими буквами, но так принято для наглядности.
Как говорит мой коллега: «SQL-запрос — это же просто английский текст. Возьми это отсюда, отфильтруй, отсортируй и так далее...»
Итак, мы получили табличку:
| reg_dt | id | gender | age | country_code | app |
| 2014-01-02 17:30:21 | 4446022755 | f | 37 | US | desktop |
| 2014-01-02 21:07:08 | 4446556074 | f | 40 | CH | android |
| 2014-01-14 21:07:15 | 4481548107 | m | 40 | GE | mobile |
| 2013-12-30 8:33:09 | 4436447691 | m | 49 | US | mobile |
| 2013-12-30 11:04:15 | 4436702697 | m | 61 | CH | desktop |
SELECT reg_dt, id, gender, age, country_code, app
FROM product.users
ORDER BY age DESC, country_code
— через запятую укажем поля, по которым будет отсортирована полученная выборка.
LIMIT 5 — эту строку можно удалить, тогда у нас будет список из всех пользователей (но их много).
По умолчанию команда ORDER BY отсортирует поле по возрастанию (текстовое по алфавиту, например), но, указав DESC (descending) после названия поля, мы зададим сортировку по убыванию.
(Чтобы выбрать все поля, укажите астериск * вместо названий полей SELECT * FROM product.users.)
Структура запроса, условия WHERE, логические операторы
Модифицируем запрос, чтобы найти пользователей-женщин старше 45 лет:SELECT reg_dt, id, gender, age, country_code, app
FROM product.users
WHERE gender = 'f' AND age > 45 – указываем условия для выводимых полей.
ORDER BY age DESC, country_code
'f' значение заключено в одинарные кавычки, чтобы указать, что это текст, а не название поля.
Порядок команд в запросе фиксированный:
SELECT 'столбцы или * для выбора всех столбцов; обязательно'
FROM 'таблица'*
WHERE 'условие/фильтрация; необязательно'
GROUP BY 'столбцы, по которым хотим сгруппировать данные; необязательно'
HAVING 'условие/фильтрация на уровне сгруппированных данных; необязательно'
ORDER BY 'столбцы, по которым хотим отсортировать вывод; необязательно'
*Если вдаваться в детали, можно написать запрос без указания таблицы, пример: SELECT 1 column1, 'text' column2. В данном блоке можно записать несколько источников, но об этом поговорим далее.
| column1 | column2 |
| 1 | text |
age > 45 (еще варианты: =, >, <, <=, >= и != не равно).
Стоит добавить, что 45 < age даст эквивалентный результат, кроме того, возможно сравнить поля между собой age > steps_count.
country_code IN ('US', 'PL', 'CL') Если нужно сравнить наши значения со значениями в списке, используем IN, указав в скобках возможные варианты через запятую.
Условия также можно задавать с приставкой «не» country_code NOT IN ('RU', 'VE').
Это исключит из выборки страны, перечисленные в списке.
BETWEEN '2020-02-02' AND '2020-07-04' — значение между указанными датами, включительно.
В SQL дата записывается в следующем формате: '2020-12-29' (год-месяц-день).
Дата и время: '2020-04-15 06:25:49.000009'. Если не требуется точность, то запись можно сократить '2020-04-15 06:25:49' (отброшенная справа часть времени будет заменена нулями '2020-04-15 06:25:49.000000'). Соответственно, когда мы фильтруем поле, которое содержит дату и время, указывая только дату BETWEEN '2020-02-02' AND '2020-07-04', это равносильно '2020-02-02 00:00:00.000000' AND '2020-07-04 00:00:00.000000'.
Для следующих примеров рассмотрим табличку:
| reg_dt | gender | age | site |
| 2012-08-01 0:43:09 | m | 34 | ametconsectetur.us |
| 2014-02-02 0:49:19 | f | 28 | abcdefghij.com |
| 2018-04-02 4:07:42 | f | 55 | loremipsum.com |
| 2021-05-08 12:00:02 | m | 48 | ametconsectetur.com |
| 2021-09-09 10:10:37 | m | 44 | abcdefghij.us |
Эта формулировка поможет найти пользователей, зарегистрированных на ресурсах, которые содержат любой набор символов (задано зарезервированным символом %), затем .com, таким образом мы получим пользователей, зарегистрированных на ресурсе с доменом .com.
SELECT site FROM product.users WHERE site LIKE '%.com';
SELECT site FROM product.users WHERE site LIKE 'abcdefghij.%';
Символ % может содержаться в любой части строки или быть указан несколько раз. Означает «любой набор символов или отсутствие символа».
Написав 'a%.com', мы найдем пользователей в домене .com, где название сайта начинается с буквы a.
Есть еще один зарезервированный символ _ (нижнее подчеркивание), обозначающий один символ. Таким образом найдем пользователей ресурса, который содержит вторым символом букву m и заканчивается доменом .us, можно так: site LIKE '_m%.us'.
Как и в курсе школьной математики (умножение выполняется раньше, чем вычитание), операторы имеют порядок выполнения.
При использовании логического оператора OR («или») стоит быть осторожным и ставить скобки, четко обозначая альтернативы.
Если мы захотим найти пользователей, что зарегистрировались в конкретном временном диапазоне с доменом '%.com' или '%.us', напишем запрос:
SELECT reg_dt, gender, age, site
FROM product.users
WHERE reg_dt BETWEEN '2012-01-01' AND '2015-01-01'
AND site LIKE '%.us' OR site LIKE '%.com'
В SQL можно использовать переносы строк и отступы для форматирования, они не влияют на выполнение запроса.
Примечание. В условии хочется написать site LIKE '%.com' OR '%.us', но так не работает, после команды LIKE ожидается один искомый паттерн, нужно повторить конструкцию для второго паттерна.
Выполнив запрос, получим список пользователей. Некоторые из них зарегистрированы за пределами выделенного нами диапазона времени 2012–2014 гг.
| reg_dt | gender | age | site |
| 2012-08-01 0:43:09 | m | 34 | ametconsectetur.us |
| 2014-02-02 0:49:19 | f | 28 | abcdefghij.com |
| 2018-04-02 4:07:42 | f | 55 | loremipsum.com |
| 2021-05-08 12:00:02 | m | 48 | ametconsectetur.com |
Это условие стоит интерпретировать как «дай указанные поля для пользователей, которые зарегистрированы в дату такую-то и имеют домен .us или просто имеют домен .com (без условия по дате)».
Происходит это как раз по той причине, что AND выполняется раньше, чем OR.
Если мы хотим, чтобы условие даты сохранялось для обоих доменов, стоит явно выделить альтернативы для оператора OR с помощью круглых скобок:
WHERE reg_dt BETWEEN '2012-01-01' AND '2015-01-01'
AND (site LIKE '%.us' OR site LIKE '%.com')
| reg_dt | gender | age | site |
| 2012-08-01 0:43:09 | m | 34 | ametconsectetur.us |
| 2014-02-02 0:49:19 | f | 28 | abcdefghij.com |
SELECT age, site, LENGTH(site)
FROM product.users
WHERE age%2 != 0 OR LENGTH(site) > 17
Возраст — непарное число (остаток от деления на 2 не равен 0) или длина ресурса — больше 17 символов. Причем поле, для которого создано условие, необязательно выводить в SELECT.
| age | site | LENGTH |
| 34 | ametconsectetur.us | 18 |
| 55 | loremipsum.com | 14 |
| 48 | ametconsectetur.com | 19 |
JOIN, что видно и чего не видно. UNION
В основном приходится использовать более одного источника.В нашей схеме, кроме таблицы с регистрациями:
| reg_dt | id | gender | age | country_code | ... |
| 2014-01-02 17:30:21 | 4446022755 | f | 37 | US | ... |
| 2014-01-02 21:07:08 | 4446556074 | f | 40 | CH | ... |
| 2014-01-14 21:07:15 | 4481548107 | m | 40 | GE | ... |
| 2013-12-30 8:33:09 | 4436447691 | m | 49 | US | ... |
| ... | ... | ... | ... | ... | ... |
| user_id | dt | order_id | service_id | ... |
| 4446022755 | 2014-01-02 18:30:01 | 2435206 | 14 | ... |
| 4446022755 | 2014-02-02 18:25:17 | 2437018 | 14 | ... |
| 4481548107 | 2014-01-14 21:08:45 | 2455378 | 18 | ... |
| 4481548107 | 2014-04-11 15:11:18 | 2460491 | 14 | ... |
| 4481548107 | 2014-04-13 12:10:09 | 2460602 | 5 | ... |
| ... | ... | ... | ... | ... |
| id | service_name |
| 5 | test_srv |
| 9 | vip |
| 14 | month |
| 18 | 90 day |
| ... | ... |
SELECT u.reg_dt, u.gender AS sex, u.age, u.id, op.user_id, order_id,
op.dt order_dt, op.service_id, pay_services.*
FROM product.users AS u
LEFT JOIN product.orders_paid op ON u.id = op.user_id
LEFT JOIN dictionary.pay_services ON pay_services.id = op.service_id
WHERE u.reg_dt > '2013-02-02' – условия, применимы к полю из любой таблицы.
ORDER BY u.reg_dt DESC
Для поля gender мы использовали так называемый алиас (иначе говоря, переименовали для нашей результирующей таблицы в sex) u.gender AS sex. Для этого можно использовать слово AS или написать алиас через пробел, работает идентично dt order_dt.
Алиасы применимы и к названиям таблиц с тем же синтаксисом (с AS или без).
Чаще всего алиас для таблицы — это аббревиатура её названия orders_paid op.
Это удобнее, чем название t1, table_1, и позволяет ускорить запись op.user_id вместо orders_paid.user_id.
Несмотря на удобство сокращения, иногда лучше не использовать алиасы, так как это может усложнить понимание скрипта.
Как видно в примере order_id, можно использовать только название поля, без указания таблицы, если это название уникально. Но все же более наглядный и универсальный способ писать таблица.поле.
pay_services.* позволяет вывести все поля из таблицы pay_services.
В результате выполнения запроса получили следующую таблицу:
| reg_dt | sex | age | id | user_id | order_id | order_dt | service_id | id | service_name |
| 2014-01-14 21:07:15 | m | 37 | 4481548107 | 4481548107 | 2460602 | 2014-04-13 12:10:09 | 9 | 9 | vip |
| 2014-01-14 21:07:15 | m | 37 | 4481548107 | 4481548107 | 2460491 | 2014-04-11 15:11:18 | 14 | 14 | month |
| 2014-01-14 21:07:15 | m | 37 | 4481548107 | 4481548107 | 2455378 | 2014-01-14 21:08:45 | 18 | 18 | 90 day |
| 2014-01-02 21:07:08 | f | 37 | 4446556074 | NULL | NULL | NULL | NULL | NULL | NULL |
| 2014-01-02 17:30:21 | f | 37 | 4446022755 | 4446022755 | 2435206 | 2014-01-02 18:30:01 | 14 | 14 | month |
| 2014-01-02 17:30:21 | f | 37 | 4446022755 | 4446022755 | 2437018 | 2014-02-02 18:25:17 | 14 | 14 | month |
| 2013-12-30 11:04:15 | m | 37 | 4436702697 | NULL | NULL | NULL | NULL | NULL | NULL |
| 2013-12-30 8:33:09 | m | 37 | 4436447691 | NULL | NULL | NULL | NULL | NULL | NULL |
Для пользователей, у которых нет оплат, мы получили значения NULL. Его нужно понимать как «нет записей в присоединяемой таблице».
Таблицы users и orders_paid связаны через ID покупателя, его называют link’ом (связью). К одной строке в пользователях может находиться несколько (в том числе 0) строк заказов, таким образом можно сказать, что эта связь имеет тип (1:М) один ко многим.
Для того чтобы корректно сопоставить строки таблиц, после ON указываются условия, которые должны выполняться, чтобы строка была добавлена в выборку. В нашем случае мы обозначаем, что строки из таблицы заказов должны быть выведены в тех случаях, когда идентификатор пользователя в таблице регистраций равен идентификатору пользователя в таблице оплат u.id = op.user_id.
Аналогично связаны ps.id = op.service_id за тем исключением, что справочник имеет только по одной записи для каждого идентификатора и не увеличивает количество строк.
Дальнейшее взаимодействие с полученным набором данных не отличается от одной таблицы — выбираем интересующие нас поля, фильтруем и сортируем.
Таким образом, если мы после объединения таблиц напишем условие, которое оставит только те строки, где идентификатор заказа неизвестен, получим список пользователей, для которых нет ни одного заказа.
SELECT u.reg_dt, u.gender, u.age, u.id, op.order_id
FROM product.users AS u
LEFT JOIN product.orders_paid op ON u.id = op.user_id
WHERE op.order_id IS NULL
| reg_dt | gender | age | id | order_id |
| 2013-12-30 11:04:15 | m | 37 | 4436702697 | NULL |
| 2013-12-30 8:33:09 | m | 37 | 4436447691 | NULL |
| 2014-01-02 21:07:08 | f | 37 | 4446556074 | NULL |
Сравнение с NULL производится через IS, ведь NULL — неопределенное значение и не равно само себе. Поскольку если мы используем равенство, то результат сравнения order_id = NULL — ложь, даже если order_id примет значение NULL.
Чтобы найти строки для пользователей, которые имели заказы, модифицируем условие WHERE op.order_id IS NOT NULL или используем другой вид соединения таблиц.
LEFT JOIN оставляет все значения таблицы «слева» и добавляет значения из указанной таблицы, если выполняется условие в ON.
INNER JOIN, или JOIN, влияет на обе таблицы и полностью скрывает строку, в которой не выполняется условие ON.
Для нахождения строк, которые имеют соответствие в обеих таблицах, можем использовать JOIN product.orders_paid op ON u.id = op.user_id. Нужно учитывать особенность INNER JOIN скрывать значения.
Если JOIN’ы используются для горизонтального объединения таблиц, существует способ вертикально объединить выборки с помощью UNION:
SELECT id, country_code
FROM product.users
WHERE id IN (4446556074, 4436447691, 4446022755)
UNION ALL
SELECT id, country_code
FROM product.users
WHERE id IN (4446022755, 4436702697, 4481548107)
UNION ALL произведет вертикальное объединение всех строк.
| id | country_code |
| 4446556074 | CH |
| 4436702697 | CH |
| 4481548107 | GE |
| 4436447691 | US |
| 4446022755 | US |
| 4446022755 | US |
| id | country_code |
| 4446556074 | CH |
| 4436447691 | US |
| 4436702697 | CH |
| 4446022755 | US |
| 4481548107 | GE |
Таким образом можно объединить большое количество выборок с подходящим набором полей, получив на выходе одну таблицу.
Типы связей, отличие условий в WHERE и LEFT JOIN
Если перечислить таблицы через запятую в FROM, получим результат как в CROSS JOIN, который сопоставляет строки «каждая с каждой» (количество строк в таблице, будет равно произведению количества строк исходных таблиц). CROSS JOIN при этом более распространенный и явный способ сделать декартово произведение множеств.SELECT u.id, op.user_id, op.order_id
FROM product.users u, product.orders_paid op
Что эквивалентно:
SELECT u.id, op.user_id, op.order_id
FROM product.users u
CROSS JOIN product.orders_paid op
| id | user_id | order_id |
| 4436447691 | 4446022755 | 2435206 |
| 4436447691 | 4446022755 | 2437018 |
| 4436447691 | 4481548107 | 2455378 |
| 4436447691 | 4481548107 | 2460491 |
| 4436447691 | 4481548107 | 2460602 |
| 4436702697 | 4446022755 | 2435206 |
| ... | ... | ... |
Указав в условии WHERE, что ID пользователя должен быть общий для двух таблиц:
SELECT u.id, op.user_id, op.order_id
FROM product.users u, product.orders_paid op
WHERE u.id = op.user_id
Получим результат, аналогичный следующему:
SELECT u.id, op.user_id
FROM product.users u
INNER JOIN product.orders_paid op ON u.id = op.user_id
| id | user_id | order_id |
| 4481548107 | 4481548107 | 2455378 |
| 4446022755 | 4446022755 | 2437018 |
| 4481548107 | 4481548107 | 2460491 |
| 4481548107 | 4481548107 | 2460602 |
| 4446022755 | 4446022755 | 2435206 |
Отличие условий в WHERE и LEFT JOIN
Условие, написанное в LEFT JOIN, влияет только на поля из присоединяемой таблицы:
SELECT u.id, op.service_id, op.user_id
FROM product.users u
LEFT JOIN product.orders_paid op ON u.id = op.user_id AND op.service_id = 14
| id | service_id | user_id |
| 4446022755 | 14 | 4446022755 |
| 4446022755 | 14 | 4446022755 |
| 4481548107 | 14 | 4481548107 |
| 4436702697 | NULL | NULL |
| 4436447691 | NULL | NULL |
| 4446556074 | NULL | NULL |
Когда условие написано в WHERE, оно фильтрует выборку уже после объединения таблиц.
До фильтрации:
SELECT u.id, op.service_id, op.user_id
FROM product.users u
LEFT JOIN product.orders_paid op ON u.id = op.user_id
| id | service_id | user_id |
| 4446022755 | 14 | 4446022755 |
| 4446022755 | 14 | 4446022755 |
| 4436702697 | NULL | NULL |
| 4446556074 | NULL | NULL |
| 4436447691 | NULL | NULL |
| 4481548107 | 14 | 4481548107 |
| 4481548107 | 9 | 4481548107 |
| 4481548107 | 18 | 4481548107 |
SELECT u.id, op.service_id, op.user_id
FROM product.users u
LEFT JOIN product.orders_paid op ON u.id = op.user_id
WHERE op.service_id = 14
| id | service_id | user_id |
| 4446022755 | 14 | 4446022755 |
| 4446022755 | 14 | 4446022755 |
| 4481548107 | 14 | 4481548107 |
Условия в блоке WHERE влияют на всю полученную после объединения выборку, это нужно учитывать. Как видно в примере выше ON u.id = op.user_id AND op.service_id = 14, блок ON не ограничивается равенством идентификаторов. Это просто блок с условиями (по тем же правилам, что и в WHERE), которые могут быть разными в зависимости от ситуации.
Одна и та же таблица может быть присоединена несколько раз или присоединена сама к себе. В нашей таблице с ордерами есть поле parent_order_id. Оно указывает на заказ, который инициировал подписку у пользователя. Таким образом первые платежи и продления хранятся в одной таблице, и мы связываем таблицу orders_paid саму с собой по идентификатору родительского платежа.
Так же привяжем справочник с названиями сервисов. Используем INNER JOIN для связи с orders_paid, чтобы остались только пользователи с заказами.
SELECT u.id, opp.order_id parent_order, opp.dt AS parent_dt, op.order_id, op.dt AS order_dt, ps.service_name
FROM product.users u
INNER JOIN product.orders_paid op ON u.id = op.user_id
LEFT JOIN product.orders_paid opp ON op.parent_order_id = opp.order_id
LEFT JOIN product.pay_services ps ON ps.id = op.service_id
ORDER BY op.order_id
| id | parent_order | parent_dt | order_id | order_dt | service_name |
| 4446022755 | 2435206 | 2014-01-02 18:30:01 | 2435206 | 2014-01-02 18:30:01 | month |
| 4446022755 | 2435206 | 2014-01-02 18:30:01 | 2437018 | 2014-02-02 18:25:17 | month |
| 4481548107 | 2455378 | 2014-01-14 21:08:45 | 2455378 | 2014-01-14 21:08:45 | 90 day |
| 4481548107 | 2460491 | 2014-04-11 15:11:18 | 2460491 | 2014-04-11 15:11:18 | month |
| 4481548107 | NULL | NULL | 2460602 | 2014-04-13 12:10:09 | vip |
Агрегации (distinct и разрезы)
В SQL есть агрегатные функции, которые могут превратить несколько строк в одну:SUM, COUNT, AVG, MIN, MAX и так далее.
Возвращает одну строку:
SELECT COUNT(u.id)
FROM product.users u
| count |
| 8073565 |
Функция COUNT позволяет вписать *, чтобы подсчитать количество строк в выборке:
SELECT COUNT(*)
FROM product.users u
В большинстве случаев нам требуется считать величины в каких-либо разрезах, оставляя несгруппированные поля.
Мы можем найти дату первой регистрации и количество зарегистрированных пользователей в каждой стране отдельно для мужчин и женщин (упорядочено от самой большой группы):
SELECT country_code, gender, MIN(reg_dt), COUNT(id) id_count
FROM product.users
GROUP BY country_code, gender
ORDER BY COUNT(id) DESC
| country_code | gender | MIN | id_count |
| CH | f | 2014-01-02 21:07:08 | 983742 |
| GE | m | 2014-01-14 21:07:15 | 464232 |
| CH | m | 2013-12-30 11:04:15 | 534654 |
| US | f | 2014-01-02 17:30:21 | 453645 |
| US | m | 2013-12-30 8:33:09 | 236235 |
Если в SELECT указаны только агрегированные выражения, GROUP BY отсутствует.
Если в SELECT все поля без агрегаций и они же в GROUP BY — запрос вернет только уникальные значения.
Важно держать в уме уровень детализации данных, который получается на каждом этапе запроса. Иначе говоря, чему соответствует одна строка данных в результате выполнения запроса.
SELECT u.country_code,
COUNT(DISTINCT u.id) registered,
COUNT(DISTINCT op.user_id) payers,
COUNT(DISTINCT op.user_id) / COUNT(DISTINCT u.id) * 100 conv
FROM product.users u
LEFT JOIN product.orders_paid op ON u.id = op.user_id
WHERE reg_dt BETWEEN '2020-02-02' AND '2020-02-03'
GROUP BY u.country_code
HAVING COUNT(DISTINCT op.user_id) > 100
Этим запросом мы хотим посчитать процент пользователей, которые совершили хотя бы одну оплату. Для этого нужно количество уникальных пользователей COUNT(DISTINCT u.id), что зарегистрировались, поделить на количество уникальных пользователей из таблицы оплат COUNT(DISTINCT op.user_id). Слово DISTINCT удалит дубликаты.
Подсчет мы проведем отдельно для каждой локации GROUP BY u.country_code.
Из полученной выборки HAVING COUNT(DISTINCT op.user_id) > 100 оставим только строки, что соответствуют условию, в нашем случае это локации, в которых больше 100 плательщиков.
| country_code | registered | payers | conv |
| GE | 491871 | 16164 | 3.286227486 |
| CH | 232116 | 9128 | 3.9325165 |
| US | 267327 | 13758 | 5.146505965 |
Запрос обрабатывается в следующем порядке:
- FROM (и JOIN’ы)
- WHERE
- GROUP BY
- HAVING
- SELECT
- ORDER BY
SELECT u.country_code, u.id id_from_u, op.user_id id_from_op, op.order_id*
FROM product.users u
LEFT JOIN product.orders_paid op ON u.id = op.user_id
WHERE reg_dt BETWEEN '2020-02-02' AND '2020-02-03'
*op.order_id выведено для демонстрации.
Видим, что для пользователей, у которых больше одного заказа, появилось по несколько строчек. И если бы мы использовали COUNT без DISTINCT, то подсчитали бы количество пользователей, умноженное на количество их заказов.
| country_code | id_from_u | id_from_op | order_id |
| US | 4446022755 | 4446022755 | 2435206 |
| US | 4446022755 | 4446022755 | 2437018 |
| CH | 4436702697 | NULL | NULL |
| CH | 4446556074 | NULL | NULL |
| US | 4436447691 | NULL | NULL |
| GE | 4481548107 | 4481548107 | 2460491 |
| GE | 4481548107 | 4481548107 | 2460602 |
| GE | 4481548107 | 4481548107 | 2455378 |
Агрегатные функции будут подсчитаны, не учитывая значения NULL.
Итог
Здесь мы проанализировали, как извлечь данные из таблиц, корректно их объединять, фильтровать и группировать. Этого достаточно для того, чтобы получать несложные выборки и считать статистики.В следующей части рассмотрим выделение признаков, подзапросы, работу с временными промежутками, транспонирование и другие приемы, с которыми сталкивается аналитик в работе.
DOU
DOU – Найбільша спільнота розробників України. Все про IT: цікаві статті, інтервʼю, розслідування, дослідження ринку, свіжі новини та події. Спілкування на форумі з айтівцями на найгарячіші теми та технічні матеріали від експертів. Вакансії, рейтинг IT-компаній, відгуки співробітників, аналітика...
dou.ua