SQL, Анализ данных

5 признаков того, что ваша дальнейшая аналитика отправится в корзину

Время прочтения: 5 мин.

Приведённые ниже примеры написаны на T-SQL, но будут актуальны и на других диалектах.

1.Забыть о приоритете операторов.

Имеем таблицу клиентов с их возрастом и флагом активности. Её мы будем использовать и в дальнейшем.

Нам необходимо получить всех активных клиентов в возрасте 30 или 40 лет.

Пример, уже набивший оскомину, но всё же являющийся, пожалуй, самой распространенной ошибкой новичков, да и не только. Бывало, что нет-нет да и отвлечешься на глоток чая, и попросту вылетит из головы необходимость учитывать приоритет операторов.

Написав, впопыхах, следующий скрипт:

SELECT Client_Id,  Age, Is_active
FROM Client
WHERE IS_active=1 and Age=30 or Age=40

Получим:

Явно не то, что мы хотели.

По приоритету на сайте docs.microsoft.com (https://docs.microsoft.com/ru-ru/sql/t-sql/language-elements/operator-precedence-transact-sql?view=sql-server-ver15) показана удобная таблица. В целом, всё похоже на арифметику: помни о приоритете, а если хочешь его выделить, ставь круглые скобки.

SELECT Client_Id,  Age, Is_active
FROM Client
WHERE IS_active=1 and (Age=30 or Age=40)

2.Неправильно применить условия при left (right и тп.) join.

Предположим, мы получили такое задание:

Из таблицы п1. вывести информацию: были ли по клиентам проверки после 01.09.2019. И опираться на дополнительную таблицу проверок:

При такой трактовке задания мы держим в голове, что необходимо вывести информацию о всех клиентах, а значит можно использовать left join. При этом помним, что нужно наложить условие на дату. Но где описывать условие? Конечно, в блоке «where». Будучи уверенными в простоте и очевидности решения прописываем:

SELECT Client.Client_Id,  Client.Age, Client.Is_active , Audit.Audit_date
FROM Client
             left join Audit on Client.Client_Id=Audit.Client_Id
WHERE  Audit.Audit_date>='2019-09-01'

И получаем:

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

Дело в том, что, прописывая условия в блоке «where» на таблицу, которую left join’им, мы нивелируем этот left join, превращая его в обычный join, а значит теряем информацию, которую хотели бы сохранить в «левой» таблице.

Корректным будет прописать условие в момент присоединения таблицы или дописать условие на NULL:

SELECT Client.Client_Id,  Client.Age, Client.Is_active , Audit.Audit_date
FROM Client
             left join Audit on Client.Client_Id=Audit.Client_Id and Audit.Audit_date>='2019-09-01'

Итог:

Заметьте, по клиенту 5 проставилась информация, что проверок не было несмотря на то, что в таблице проверок по нему имеется запись. Таким образом, условие на дату наложилось корректно.

3.Забывать, что null своенравен.

NULL – это особое значение и работать с ним нужно аккуратно, иначе мы рискуем потерять важные для дальнейшего анализа данные. Так как мы живём не в идеальном мире и в данных систем не всегда есть полная информация, может так случится, что некоторые поля пропущены.

Нам необходимо по клиентам вывести информацию об имеющихся продуктах (Product), но не выводить тех, у кого есть инвестиционный кредит (Subproduct).

Запрос вида:

SELECT Client_Id, Product, Subproduct
FROM Product
WHERE Subproduct!='Invest'

Выдает:

Но мы потеряли клиента 2 с дебетовой картой и клиента 8 с кредитом, у которых в поле Subproduct было в силу различных обстоятельств пропущено.

Поэтому нужно перед аналитикой проработать пустые значения: понять, насколько они важны тебе в анализе, можно ли их на что-то заменить, а если нет, учитывать как особое значение:

SELECT Client_Id, Product, Subproduct
FROM Product
WHERE Subproduct!='Invest' or Subproduct is null

4.Путать Union и Union ALL.

Нужно помнить, что использование Union удаляет повторы в записях, а Union ALL выдает объединенные записи без дубликатов.

У нас есть две команды проверяющих, которые отмечают успешное завершение проверки по клиенту.

Нам необходимо посчитать для нашего руководителя сколько клиентов уже проверено всеми бригадами.

SELECT sum(summ_is_done)
FROM
(
  SELECT sum(Is_Done) summ_is_done
  FROM Team1
  union 
  SELECT sum(Is_Done) summ_is_done
  FROM Team2
) Team_all

Результат – 2 клиента.

Несмотря на то, что первая и вторая команды проверили по 2 клиента каждая, UNION (получив оба раза по 2) просто удалил дубликаты записей, нивелировав вклад одной из команд.

Если в скрипте выше использовать Union ALL, то результат будет – 4.

Выбирая тот или иной метод объединения, нужно понимать свои данные и представлять, что мы хотим получить.

5.Не понимать BETWEEN.

Используя оператор BETWEEN вместо логических («>», «<», «=»), нужно помнить, что BETWEEN включает в себя как начальный диапазон, так и конечный. Вроде бы всё просто, но бывает, что даже уважаемые создатели тестов по SQL, забывают про это.

Из таблицы п1:

SELECT *
FROM Client
WHERE Age between 20 and 30

Результат:

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

Советуем почитать