В мире больших данных
218 subscribers
34 photos
5 files
54 links
Полезные заметки о системном анализе в мире больших данных. Если вам интересны Big Data, DWH, SQL и как навести порядок в данных — заглядывайте. Будет интересно и по делу.

Автор: @JuliaMur
Download Telegram
NULL != NULL — это True?

NULL в базах данных означает “ничего”, отсутствие данных, вместо которых "неизвестно что". Казалось бы звучит просто, но в то же время коварное NULL постоянно хочет обвести вокруг пальца. Поэтому важно помнить об его особенностях.

NULL не равен ничему, в том числе другому NULL (как одна неизвестность может быть равна другой?). При этом и выражение NULL != NULL не будет истинным, так как нельзя сравнить неизвестность с неизвестностью.

Распространённая ошибка поиск по условию WHERE column_name = NULL. Результатом такого условия будет FALSE. Вместо этого для сравнения используется оператор IS NULL (или IS NOT NULL, если нужно найти все не NULL значения).

Ну и, конечно, не стоит забывать, что NULL ни в коем случае не эквивалентен 0. 

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

#sql #null
Шпаргалка: NULL и логические операции

Я уже рассказывала про сравнение с Null, но это не все особенности работы с ним, которые важно знать при анализе данных.

Null — третье логическое значение (кроме True и False), оно обозначает неизвестность. Исходя из этого определения легко вывести следующее:

Null AND True = Null
Null AND False = False
Null AND Null = Null

Null OR True = True
Null OR False = Null
Null OR Null = Null

NOT (Null) = Null
NOT (NOT(Null)) = Null

Во всех случаях стоит помнить, что Null в результатах не равен Null в условии. Это несравнимая неизвестность.

#sql #null
Разгадка тайн соединения NULL-значений в SQL

Продолжим серию статей про особенности работы с #null 🙂 да, они ещё не закончились.

Одна из часто встречающихся проблем — непонимание как происходит JOIN таблиц с NULL-значениями. Давайте посмотрим на примерах.

Допустим у нас есть две таблицы:

user_names
us_id  name
1 Илья
2 Ольга
3 Null
Null Null

user_roles
us_id  role
1 admin
2 user
3 user
Null guest
Null Null


INNER JOIN

Строки, для которых нет совпадения в обеих таблицах, исключаются из результирующего набора. В том числе, если в столбце, по которому происходит соединение, встречаются значения с NULL, эти строки будут исключены из результата, так как два NULL нельзя сравнить между собой.
SELECT u.us_id, r.us_id, name, role
FROM user_names u
INNER JOIN user_roles r ON u.us_id = r.us_id;


Результат:
u.us_id   r.ud_id  name   role
1 1 Илья admin
2 2 Ольга user
3 3 Null user


LEFT JOIN
Возвращает все записи из левой таблицы (users_name) и соответствующие записи из правой таблицы (users_role).
SELECT u.us_id, r.us_id, name, role
FROM user_names u
LEFT JOIN user_roles r ON u.us_id = r.us_id;


Результат:
u.us_id   r.ud_id  name   role
1 1 Илья admin
2 2 Ольга user
3 3 Null user
Null Null Null Null

Здесь стоит отметить, что появившиеся Null в полях r.ud_id — не являются Null-значениями из таблицы users_role.

FULL JOIN
Возвращает все записи, включая совпадения в левой или правой таблице. Строки в любой из таблиц будут содержать NULL значения в столбцах из другой таблицы, в случае отсутствия совпадения по ключу.
SELECT u.us_id, r.us_id, name, role
FROM user_names u
FULL JOIN user_roles r ON u.us_id = r.us_id;


Результат:
u.us_id   r.ud_id  name   role
1 1 Илья admin
2 2 Ольга user
3 3 Null user
Null Null Null Null
Null Null Null guest
Null Null Null Null

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

#sql #null
Please open Telegram to view this post
VIEW IN TELEGRAM
1
NULL и агрегатные функции

И вновь разговоры про NULL. Особое внимание заслуживает работа с агрегатными функциями в выражениях, включающих NULL-значения.

NULL представляет собой отсутствие значения или неизвестное состояние, и поэтому добавляет сложности при взаимодействии с агрегатными функциями. Распространенной ошибкой является предположение, что NULL ведет себя как обычное значение. Но это не так!

Вернёмся к основам. Одним из фундаментальных аспектов SQL является трехзначная логика (3VL), различающая TRUE, FALSE и UNKNOWN. И при применении агрегатных функций к выражениям с NULL-значениями важно учитывать состояние UNKNOWN, которое вносит NULL. Ничего не понятно? 😁 Рассмотрим на конкретных примерах.

Предположим у нас есть таблица table_for_count:
column_1
49
60
NULL
12
50
11
NULL
5


select * from table_for_count

Результат: 8

select count(column_1) from table_for_count

Результат: 6 ☹️

В то время как COUNT(*) посчитает все строки, независимо от наличия значений NULL, COUNT(column_1) исключит в подсчёте строки, где указанный столбец равен NULL.

Аналогично, работают функции SUM, AVG, MAX и MIN. Я отдельно выделила AVG, чтобы вы обратили на него внимание.

table_for_avg:
column_2
1
NULL
2


select avg(column_2) from table_for_avg

Результат: 1.5 (в некоторых расчётах — это будет верным, но может быть и нет — учитывайте контекст запроса!)


Что делать?
Хорошая практика — использование функций COALESCE или NVL для замены NULL-значений на значения по умолчанию или определенные пользователем до применения агрегатной функции. Важно устанавливать значения осмысленно, иначе это внесёт ещё больше хаоса в результаты запроса.
Ещё один вариант — использование оператора CASE для создания условной логики и обработки NULL значений.
Выбор метода зависит от конкретной бизнес-задачи и, конечно же, СУБД (и не забывайте проверять план выполнения запроса для лучшего решения).

select count(coalesce(column_1, 0)) from table_for_count;

Результат: 8 😍

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

Другие заметки про работу с NULL-значениями можно найти по хэштегу 👇

#null
Please open Telegram to view this post
VIEW IN TELEGRAM
1
Группировка NULL-значений

Мы уже рассмотрели множество особенностей NULL, но это ещё не всё.
На очереди группировка данных, включающих NULL-значения.

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

В этот раз, если вы читали мои предыдущие статьи, ответ может вас удивить. При использовании GROUP BY, строки с NULL в группирующем столбце объединяются в одну группу. Казалось бы NULL обозначает "неизвестное" значение и как мы можем его группировать? Но факт остаётся фактом, при агрегации все NULL считаются равными между собой и формируют единую группу.

Чтобы сделать это поведение более очевидным при аналитике, мы можем использовать функции COALESCE или CASE, чтобы заменить NULL на значение, которое ясно указывает на отсутствие данных, например, на 'Неизвестно'.

#sql #null
1
UNION и UNION ALL. Так ли всё просто?

Маленькая заметка-напоминалка.

Операторы UNION и UNION ALL в SQL отвечают за объединение результатов нескольких запросов. При этом просто UNION выводит только уникальные строки в запросах, то с ALL выведет абсолютно все строки, включая возможные дубли.

Как операторы объединения работают с NULL?
UNION — объединит похожие строки, содержащие NULL в 1 (считая, что это дубли), а UNION ALL оставит все строки.

Ещё несколько особенностей:
1. Набор полей у всех объединяемых запросов должен быть одинаков.
2. Важно! При использовании UNION снижается производительность, так как приходится сканировать результат на наличие дублей. В случае, если в результатах объединения предсказуемо нет дублирующихся полей, предпочтительнее использовать UNION ALL.

#sql #null
👍5
NULL + 50: почему иногда арифметика работает не так, как ожидается

Продолжая серию статей про #null, не могу обойти стороной арифметику.

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

| product_id | current_stock | expected_delivery |
|------------|---------------|-------------------|
| 1 | 100 | 20 |
| 2 | 50 | NULL |
| 3 | 200 | 30 |


Выполняем запрос:

SELECT
product_id,
current_stock + expected_delivery AS total_stock
FROM inventory;


Результат выполнения будет таким:

| product_id | total_stock |
|------------|-------------|
| 1 | 120 |
| 2 | NULL |
| 3 | 230 |


Почему для второго продукта итог оказался NULL вместо 50? 🤔 Ведь логично ожидать, что результат будет равен текущим запасам.

И тут пора вспомнить то, о чём я всё время напоминаю. NULL — это не число, а специальный маркер. Он говорит "здесь что-то должно быть, но я не знаю что". И его нельзя использовать в любых арифметических операциях с числами напрямую, поскольку это "неопределенность". И когда мы просим прибавить к числу неизвестность, в результате мы также получаем неизвестность.

Что делать?
Как и всегда, важно всегда обрабатывать NULL-значения. Тут нам снова помогут функции COALESCE и IFNULL:


SELECT
product_id,
current_stock + COALESCE(expected_delivery, 0) AS total_stock
FROM inventory;


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

#null
Please open Telegram to view this post
VIEW IN TELEGRAM
👍3🔥1
Иногда приходится разбирать чужие sql-запросы и периодически сталкиваюсь с различными ошибками. Сегодня хочу рассказать о трёх наиболее распространённых.

Некорректная работа с NULL
Я уже много раз писала, NULL — не просто пустота, это неизвестность. Поэтому нельзя сравнивать с NULL в лоб. Запрос вам ошибку не выдаст, но отработает некорректно.


-- неправильно:
SELECT * FROM users WHERE age = NULL;
SELECT * FROM users WHERE age != NULL;

-- правильно:
SELECT * FROM users WHERE age IS NULL;
SELECT * FROM users WHERE age IS NOT NULL;


Также при подсчёте количества строк COUNT(column_name) пропустит все NULL-значения. Поэтому если нужно посчитать прям вообще всё используйте COUNT(*).


-- считает количество заполненных номеров:
SELECT COUNT(phone) FROM users;

-- считает все строки, в том числе с NULL:
SELECT COUNT(*) FROM users;


Больше про #null я писала в постах с соответствующим тегом) на собесах часто про это спрашивают, но уделить внимание теме, конечно же, стоит не только поэтому.

Неправильное использование оператора BETWEEN
Ещё часто вижу, как забывают об особеннстях BETWEEN, забывая, что он включает и верхнюю, и нижнюю границы диапазона. Это может привести к дублированию данных или их пропуску при последовательной выборке.


-- пример кода с ошибкой:
-- выборка за 1 марта о полю типа дата-время
SELECT * FROM orders WHERE order_dttm BETWEEN '2024-03-01' AND '2024-03-02';
-- Выборка за 2 марта
SELECT * FROM orders WHERE order_dttm BETWEEN '2024-03-02' AND '2024-03-03';


В этом примере заказы, созданные ровно в полночь 2 марта (2024-03-02 00:00:00), будут включены в обе выборки! Лучше использовать явные полуинтервалы:

-- правильно:
-- выборка за 1 марта
SELECT * FROM orders WHERE order_dttm >= '2024-03-01' AND order_dttm < '2024-03-02';
-- выборка за 2 марта
SELECT * FROM orders WHERE order_dttm >= '2024-03-02' AND order_dttm < '2024-03-03';


Но если сильно хочется BETWEEN, то:

-- выборка за 1 марта
SELECT * FROM orders WHERE order_dttm BETWEEN '2024-03-01 00:00:00' AND '2024-03-01 23:59:59';
-- выборка за 2 марта
SELECT * FROM orders WHERE order_dttm BETWEEN '2024-03-01 00:00:00.000' AND '2024-03-01 23:59:59.999';


Да, про миллисекунды забывать не нужно, а то можно что-то потерять. И всё-таки проще использовать полуинтервалы)

Ошибки в логических операторах
Ещё часто забывают про приоритеты при использовании AND и OR в одном условии. В SQL сначала выполняются все AND, а затем уже OR.
Например, нужно найти все транзакции на сумму больше 100.000, которые имеют статус "completed" и при этом либо от премиум-пользователя, либо оплачены кредитной картой.

-- неправильно:
SELECT * FROM transactions
WHERE amount > 100000
AND status = 'completed'
AND user_type = 'premium' OR payment_method = 'credit_card'


По правилам SQL операторы AND приоритетнее. Поэтому запрос интерпретируется так:

SELECT * FROM transactions
WHERE (status = 'completed' AND amount > 100000 AND user_type = 'premium')
OR (payment_method = 'credit_card')


То есть мы получим все завершённые транзакции премиум-пользователей с суммой больше 100000, плюс абсолютно все транзакции с кредитных карт (даже незавершённые и с маленькими суммами).

Так мы получим именно то, что хотели:

-- правильно:
SELECT * FROM transactions
WHERE status = 'completed'
AND amount > 100000
AND (user_type = 'premium' OR payment_method = 'credit_card')


В целом, проще лишний раз указать скобки, чем запутаться и получить ошибочный результат.

Кому-то кажется очевидным, но такие вещи, действительно, встречаются. А с какими ошибками в sql вы часто сталкиваетесь?

#sql
👍6🔥5