SQL-запросы, которые вы рано или поздно погуглите
Разобрали на примерах самые популярные SQL-запросы, связанные с модификацией таблиц, изменением записей и условиями,
2К открытий5К показов
В этой статье отвечу на вопросы, которые возникают у новичков в SQL, в частности в PostgreSQL и BigQuery. Мы не будем обсуждать совсем базовые SELECT, CREATE или DROP. Если вы еще не знакомы с ними, советую почитать про основные команды.
Для удобства восприятия будем использовать тестовые данные. Первая таблица players содержит данные о пользователях и дате установки мобильной игры:
вторая, levels, — даты прохождения игроком с определенным ID того или иного уровня:
Я буду верстать в BigQuery на таком же датасете, так что использую соответствующий диалект.
Разница между INNER JOIN и OUTER JOIN
Допустим, геймдев-студия хочет объединить эти две таблицы, чтобы в дальнейшем вычислить глубину прохождения. Как вы уже догадались, такую операцию можно провести только по столбцу playerId (идентификатор игрока). Давайте вспомним, какие виды слияния существуют:
INNER JOIN
Это тип объединения по умолчанию, и он оставит наименьшее число строк. Слово INNER можно опустить:
Посмотрим, что здесь происходит:
- В строках 1-6 мы выбираем только необходимые столбцы;
- 7-8: командой AS задаем псевдонимы таблицам;
- 8: определяем логику объединения по совпадающим playerId. Это означает также, что мы сохраним данные только об первом попавшемся уровне.
Получим всего четыре строки:
OUTER JOIN
Этот тип объединения, напротив, куда «добрее» и в случае FULL-объединения сохранит записи обо всех игроках и всех пройденных уровнях:
У нас появятся записи, где playerId пуст, поскольку попросили мы идентификаторы только из первой таблицы:
В моей практике LEFT / RIGHT-джойны пригождаются реже, но все равно порой бывает полезно получить такую выборку. К примеру, это поможет впоследствии вычислить тех, кто вообще дошел до конца игры.
Как обновить запись?
Если, скажем, в данные закралась ошибка, мы можем изменить одну или несколько строк таким образом:
Запрос выбирает все, где game равен ‘curtain’, и задает там новую дату установки – 23 августа 2023 г.
Теперь players выглядит так:
Как добавить столбец?
У нас были playerId, name, installationDate, game, os, а теперь аналитики студии добавляют модель устройства, чтобы изучить его влияние на проходимость:
Запрос добавляет столбец, в который может поместиться название устройства (iPhone, Samsung и проч.):
Когда такие вопросы становятся самыми популярными на Stack Overflow, понимаешь, что у SQL документация менее удобочитаема, чем тред на форуме.
Как удалить дубликаты?
Команда разработчиков хочет подчистить данные об игроках и выберет только уникальных. Удивительно, как часто возникает такая потребность, и как «костыляет» ее решение в BigQuery:
Здесь мы просто выделили уникальные (distinct) записи, но как удалить дубликаты? Мы создаём новую таблицу: жмем кнопку Save Results / BigQuery Table:
Вставка нескольких записей одновременно
C этой командой многие разработчики знакомятся в ходе обучения. Но вопрос: «Как добавить несколько записей?» — все равно остается популярным.
В коде выше мы указываем порядок столбцов, согласно которому будут вноситься новые строки – от идентификатора пользователя (playerId) до названия игры (game). Затем перечисляем новые записи в нужном порядке: ‘0e95168a’, ‘Алексеев Максим Артёмович’, 2022-01-21, ‘brief’.
Если уж приходится вставлять ряды таким способом, можно воспользоваться онлайн-генератором SQL вроде filldb.info. Этот сервис позволяет не только сгенерировать игрушечные данные с тонкими настройками, но и связать таблицы между собой.
Условия
Создатели SQL Рэймонд Бойс и Дональд Чемберлин добавили условные ветвления в язык. И вышло нетривиально. Если аналитик хочет изучить бестселлеры ‘basketball’ и ‘curtain’, то выделить записи, касающиеся только этих игр, можно с помощью CASE-выражения:
Давайте изучим построчно, что здесь происходит:
- В строке 8 определим тип нового столбца (INT) и назовем его isPopularGame;
- 3-6: потребуем выставлять значение 1 для двух популярных игр;
- 7: выставим ноль для всех остальных игр;
- 2: мы используем CAST … AS INT, чтобы единицы и ноли, что мы присваиваем новому столбцу, стали целочисленным столбцом
Мы получим такой лаконичный ответ:
Кстати, в BI-системах вроде Tableau или Google Looker CASE-выражение играет важную роль: с его помощью создаются временные вычисляемые столбцы, которые нужны только на одном дашборде без затрат на хранение данных. К примеру, в том же Looker мы можем создать такой для группировки по типу операционной системы (iOS / Android):
Оператор IF – ELSE
Если-выражения тоже реализованы, но используются в конструкциях попроще. Допустим, аналитик проекта хочет выделить из общего массива игроков тех, что пришли недавно:
Мы получим дополненные столбцом recentInstallation данные:
Запрос сработает, только если столбец installationDate типа TIMESTAMP (‘2023-04-12 01:12:22’). Для сравнения дат, выраженных целым числом (20230412), строкой (‘2023-04-12’) используется CAST.
Заключение
В следующей статье мы разберем операции, затрагивающие несколько таблиц: научимся вносить изменения с помощью JOIN, вытаскивать последние записи в группированной таблице и работать с UPSERT.
Делитесь в комментариях, какие запросы вам чаще всего приходилось гуглить (или какие бесят больше всего).
2К открытий5К показов