Поговорим о JOIN-ах

Оператор JOIN в SQL используется для объединения таблиц и получения результатов запроса из нескольких таблиц разом. Синтаксис запроса с участием JOIN-а двух таблиц, а также различные виды JOIN-ов можно увидеть на схеме ниже (не эта с кучей кружочков, это так, шутки ради)

Что надо помнить, изучая подобное Эйлеровское представление оператора JOIN?

1. Во-первых, учить все виды JOIN-ов, может, необходимо для экзамена в университете, но для решения реальных задач не нужно. 95% задач на практике решается с использованием INNER JOIN и LEFT JOIN, и уж поверьте, намного лучше досконально разобраться для начала только в этих типах соединений, чем по верхам изучить все, в т.ч. CROSS JOIN (в схеме не упомянут) или тот же FULL OUTER JOIN, а запросы писать попеременно с использованием то LEFT, то RIGHT JOIN-ов. Я, например, когда вижу запросы с RIGHT JOIN-ами, чувствую умственное напряжение сродни тому, которое испытываешь перед пешеходным переходом в Британии или на Кипре: 2-3 секунды уходит на то, чтобы переформатировать свой мозг под необычное движение не в ту сторону )

2. Во-вторых, надо помнить степень абстракции, с которой кружочки отражают действительность при объединении таблиц. Ошибки при работе кроются в дублировании данных при JOIN-ах, про которое некоторые забывают

Пусть есть табличка с книгами и их скачиваниями (books) и табличка с авторами этих книг (authors). На что стоит обратить внимание:

1. В таблице books есть книга "Сказки народов мира", у которой нет соответствия в таблице authors

2. У книги "Чудо-пилюли" из таблицы books есть 2 соответствия в таблице authors.

Результат JOIN-а нескольких таблиц - это таблица. И для избежания ошибок в запросах необходимо научиться мысленно визуализировать эту таблицу и понимать ее состав полей и особенности.

Держим в голове 2 пункта выше. Так как использован INNER JOIN, в результирующей таблице будут только те строки, которые есть в обеих таблицах, к которым написан запрос. Соответственно, книги "Сказки народов мира" в результате не будет. А книга "Чудо-пилюли" займет в результате 2 строки, т.к. соответствие в исходных таблицах для этой книги один-к-двум.

Напоследок приложу результат того же запроса, только с использованием не INNER JOIN, а LEFT JOIN.

Как видно, изменилось только одно: книга, у которой не было соответствия в таблице authors, теперь есть в результате, а ее автор заполнился на пустое значение NULL.

Разбор задачи SQL №6

#SQL_trainer5

В прошлый раз я затронул тему JOIN-ов. В шестой задаче тренажера нам впервые потребуется объединять таблицы.

Задача - вывести названия (name) компаний, которые совершали полет на Boeing.

Предполагаю, что если одна и та же компания летала на Boeing несколько раз, нам достаточно вывести ее название единожды. Другими словами, требуется список из уникальных имен компаний, значит, в запросе будет фигурировать DISTINCT:

SELECT DISTINCT

c.name

FROM Trip AS t

JOIN Company AS c ON t.company = c.id

WHERE

t.plane = 'Boeing'

Замечу, что в запросе используются так называемые Элиасы (Alias), которые назначаются таблицам (а также полям, вложенным запросам и т.д.) с помощью конструкции table_name AS alias и позволяют задать объекту новое временное имя в рамках запроса (вместо Trip.company можно теперь писать t.company). В большинстве случаев Элиасы повышают читаемость запросов и/или интерпретируемость названий объектов.

Также стоит отметить, что в SQL JOIN эквивалентен INNER JOIN и зачастую часть INNER опускается

Разбор задачи SQL №10

#SQL_trainer6

В прошлой статье мы разобрали решение 6 задачи. Сейчас же перепрыгнем сразу на десятую, т.к. в 7, 8, и 9 нет ничего принципиально интересного или нового (разве что, в восьмой используется оператор для расчета разницы между двумя datetime-переменными TIMEDIFF(t.time_in, t.time_out) AS flight_time).

Задача - Вывести вылеты, совершенные с 10 ч. по 14 ч. 1 января 1900 г.

Задачка, казалось бы, пустяковая. Но хочу остановиться на одном моменте. По опыту проведения тестовых заданий, 7 из 10 человек в подобной задаче напишут запрос подобного вида:

SELECT *

FROM Trip AS t

WHERE

t.time_out BETWEEN '1900-01-01 10:00' AND '1900-01-01 14:00'


(Спойлер - это в том числе является правильным ответом). И у меня всегда возникает вопрос, зачем использовать BETWEEN? В задаче, где речь пойдет не о датах, а о числовых значениях (вывести, например, всех людей с кол-вом заказов от 5 до 10), те же самые люди и не вспомнят о BETWEEN, а обойдутся больше/меньше/больше или равно/меньше или равно.

Чем неудобен BETWEEN? Собственно, только тем, что границы интервала по умолчанию включены в результат. И это дает куда меньшую вариативность либо необходимость дополнительных условий. Куда проще использовать знакомые со школы "> < >= <=".

Хочешь, чтобы границы дат попадали? t.time_out >='1900-01-01 10:00' AND t.time_out <= '1900-01-01 14:00' (эквивалентно BETWEEN)

Наоборот, не попадали? t.time_out >'1900-01-01 10:00' AND t.time_out < '1900-01-01 14:00'

Вот пример реального запроса с BETWEEN. Обратите внимание, что '2021-07-22 00:00:00' попадает в условие BETWEEN '2021-07-21' AND '2021-07-22'. Если это не учитывать, то можно с легкостью посчитать лишнюю транзакцию или еще что-то, что не подразумевалось автором запроса.

Поэтому я бы запрос к задаче №10 сформировал такой:

SELECT *

FROM Trip AS t

WHERE t.time_out >='1900-01-01 10:00'

AND t.time_out <='1900-01-01 14:00'

Разбор задачи SQL №13

#SQL_trainer7

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

Задача - Вывести имена людей, у которых есть полный тёзка среди пассажиров.

На самом деле, сообразительность нужна только для того, чтобы переформулировать постановку "Вывести имена людей, у которых есть полный тёзка среди пассажиров" в более понятную "Вывести name из таблицы Passenger, встречающиеся более 1 раза".

Для начала поймем, как построить запрос с группировкой по имени пассажиров (name) и кол-вом таких имен в таблице. Это не очень сложно:

SELECT

name, COUNT(*)

FROM Passenger

GROUP BY name

ORDER BY COUNT(*) DESC

Видим, что имена всех пассажиров, кроме одного имени, встречаются по 1 разу. Собственно, именно это имя нам и нужно - осталось убрать из ответа всех остальных. Для этого нам как раз понадобится HAVING. HAVING - команда, аналогичная WHERE, но с той разницей, что WHERE идет в запросе до группировки и накладывает условия на поля в таблице, а HAVING идет после группировки GROUP BY и накладывает условия на результат этой группировки.

Так, если в запросе выше после GROUP BY добавить условие HAVING COUNT(*)>1, мы получим почти то, что нужно в задаче:

SELECT

name, COUNT(*)

FROM Passenger

GROUP BY name

HAVING COUNT(*)>1

В целом, ответ правильный, с той лишь разницей, что формально по условию задачи в результате должно быть только одно поле name. А вот теперь все формальности тренажера учтены

Всем привет, совместно с Мариной с канала Продакт аналитикс - продуктовым аналитиком в AliExpress - подготовили небольшой разбор BI-систем: я имел значительный опыт работы с MS PowerBI, Марина - с Tableau. Бесспорно, это самые распространенные BI-инструменты, каждый из нас расскажет о своем.


1. PowerBI (сайт PowerBI)


➕ Гибкая ценовая политика. Есть лицензии Pro, которые стоят 10$ в месяц на пользователя. То есть, если в небольшой компании, скажем, 2 аналитика и 28 человек, которым нужен доступ на просмотр отчетов - то это выйдет в 300$ в месяц (250 тысяч рублей в год). Возможности в Pro-версии, конечно, порезаны, но многим с головой хватит и этого - обновление по расписанию есть, коннекторы к десяткам источников данных на месте. Premium-версия отличается возможностью развернуть PBI на своем сервере и выделенным сервером отчетов, что нужно для компаний со строгой политикой безопасности и работы с большими объемами данных

➕ Низкий порог вхождения в создание отчетов, буквально возможность создать отчет, не написав ни одной строчки кода. Да, в PBI есть свой язык формул - DAX и язык запросов Power Query (как и в Excel), но для несложных отчетов про это можно не вспоминать. После загрузки данных в PBI для работы с ними открывается визуальный интерфейс, очень сильно напоминающий Excel, в котором табличные данные легко обрабатываются, преобразуются, добавляются новые столбцы и вычисляются новые меры. Опять же, если приводить в качестве аналогии Excel, то это как записать макрос, только проще.

➕ Много разнообразных визуализаций - от столбчатых диаграмм до диаграмм Ганта и карты мира.

➕ Неплохая мобильная версия, в т.ч. приложения для IOS/Android. Как и в Tableau, посмотреть в дороге, не упала ли вчера выручка, очень подойдет.

➕ По единичному личному опыту - неплохая русскоязычная поддержка, вопрос был решен в течение 2 дней.

Минусы, конечно же, тоже есть.

➖ Отсутствие PowerBI Desktop - основного инструмента создания отчетов и подключения к данным - на Mac OS.

➖ Ограничения на объемы. Смешные для некоторых компаний ограничения на объемы в лицензии Pro - это еще ничего (1Гб размер отчета/файла .pbix, 10Гб размер одного источника данных), т.к. в Premium на порядки больше, подробнее здесь. А вот ограничение в 150 тысяч строк на экспорт из отчета в Excel/csv - это в 2021 году недопустимо. Конечно, прекрасно, что любой пользователь опубликованного отчета может скачать сырые данные таблиц/графиков себе в Excel/csv и работать с ними самостоятельно. Но 150 тысяч строк? Excel уже давно поддерживает 2 в степени 20 строк (если калькулятора под рукой нет, то это 1 048 576 строк), я писал об этом здесь. И этот объем лицензией Premium не увеличить.

➖ Отсутствие в лицензии Pro жизненно необходимых функций. Понятно, что MS стимулирует выбирать более дорогую лицензию - но, например, инкрементальное добавление данных есть только в премиуме, что уже совсем нехорошо.

➖ Подключение к локальным источникам через персональный шлюз работает не очень хорошо. Обновление в оперативной памяти компьютера, на котором установлен PowerBI Desktop при использовании персонального шлюза, ограничивает объем данных, которые может вместить PBI, а также загружает оперативку компьютера под 100%.

➖ Несмотря на обилие визуализаций, к ним иногда возникают вопросы. Во-первых, вырвиглазные цвета в стандартной цветовой схеме - ярко-красный, очень интенсивный цвет морской волны, обилие каких-то козявочных оттенков. Иногда, чтобы добиться приемлемого вида, если категорий много, сидишь только над цветами по полчаса. Во-вторых, например, сглаженная линия, которую в 2 клика можно сделать в Excel, тут недоступна, что делает многие графики неприятно ломаными.