DROP, DELETE, TRUNCATE — погружение в мир удаления данных

В SQL есть несколько способов избавиться от данных и хорошо бы в них не путаться. Так что давайте вспоминать азы.

DROP — когда нужно грохнуть всё и сразу
Эта DDL-операция используется для удаления объектов базы данных (таблицы, представления, индексы и т.д., включая саму базу). Важно понимать, что удаляются не только данные, но и сама структура, определяющая их хранение.

DROP TABLE table_name;

DELETE — выборочное удаление
DML-операция DELETE удаляет записи из таблиц, при этом есть возможность удаления данных по условию. Если условие не указано, то удаляются все данные (но не структура объекта). При этом при удалении DELETE возвращает количество удалённых строк. Также стоит помнить, что DELETE сохраняет значения авто-инкрементального ID, т.е. если вы удалили последнюю запись в таблице и у неё был ID 37, новая строка добавится с ID 38.
Транзакцию с DELETE можно откатить. Удаление "построчное", поэтому медленное.

DELETE FROM table_name WHERE column_name=value;

TRUNCATE — быстрая очистка
DDL-операция TRUNCATE позволяет быстро очистить таблицу ото всех записей, так как в лог-транзакций не записывается построчное удаление (быстрота особенно ощущается на больших таблицах, но не экспериментируйте на проде). При удалении TRUNCATE сбрасывает все авто-инкрементальные счётчики и статистику.

TRUNCATE TABLE table_name;

Удаляйте осознано и, главное, с пониманием что и где вы делаете 😄

#sql

Сказка о потерянном времени или проблемы с датами при репликации

На хабре вышла полезная статья про то, почему стоит отказаться от timestamp в PostgreSQL. Рекомендую к прочтению, так как на самом деле тема актуальна не только для постгри.

При построении хранилища очень важно понимать какие даты лежат в источниках, какой часовой пояс на сервере и что именно приходит в нужные нам поля таблиц. Иначе мы можем получить несогласованность в данных: если в источник данные приходят без указания часового пояса, но "по умолчанию" считается, что это Москва, а само хранилище работает в UTC, при репликации даты могут сдвинуться на несколько часов (и, например, перейти в следующий день), что приведёт к недопониманию временных интервалов и ошибкам при анализе временных периодов. Это может повлиять на оценку эффективности стратегий или бизнес-процессов.

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

Что же делать?

— Необходим анализ всех полей с датами в источниках. Важно понимать, что неразбериха с часовыми поясами может быть даже в рамках одной таблицы, например, даты в тех.полях могут поступать в UTC, а в самих фактах в Московском времени.

— Нужна явная обработка часовых поясов при репликации, чтобы избежать несогласованных данных.

— Использование UTC в общем случае упростит согласование дат и предотвратит временную путаницу.

— Чаще всего лучше использовать тип timestamptz (with time zone), если данный тип поддерживается.

— Проверке времён нужно уделить особо внимание при тестировании и приёмке данных.

Управление часовыми поясами при репликации — ключевой аспект поддержания целостности данных в хранилище. Системный подход к этому вопросу поможет избежать временной путаницы и обеспечить точность аналитических отчётов.

#sql

EXCEPT в SQL: ищем уникальные значения

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

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

Важно помнить, что при выполнении операции EXCEPT автоматически устраняются дубликаты строк. Если строка существует во втором наборе данных, она не появится в окончательном результате, даже если в первом наборе присутствует несколько раз.

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

Синтаксис:

select column_1 
from table_name_1
[where condition]

except

select column_1 
from table_name_2
[where condition]
order by column_1

Если результирующий набор данных нужно отсортировать, то после второго набора данных указывается order by.

Также EXCEPT можно заменить на JOIN (и чаще всего это может дать прирост производительности):

select t1.column_1 
from table_name_1 t1
left join table_name_2 t2
on t1.column_1 = t2.column_1
where t2.column_1 is NULL
order by t1.column_1

Однако использование EXCEPT делает запрос более читабельным. Что касается эффективности — её лучше тестировать (и по возможности смотреть планы запросов) на реальных данных с учётом индексов и прочих особенностей в конкретных СУБД.

Но не только EXCEPT и LEFT JOIN помогают выявлять уникальные значения в данных. В одном из следующих постов вспомним с вами о таких методах вычитания как NOT IN и NOT EXISTS.

#sql

Магия SQL Joins: как собрать нужные данные при соединений таблиц

JOIN — фундаментальная функция в SQL, которая позволяет объединять информацию из разных таблиц. С её помощью можно объединять строки из двух или более таблиц на основе связанного между ними столбца. Основными ключевыми словами для JOIN являются INNER JOIN, LEFT JOIN (или LEFT OUTER JOIN), RIGHT JOIN (или RIGHT OUTER JOIN) и FULL JOIN (или FULL OUTER JOIN).

Понимание принципов работы с JOIN поможет эффективно объединять данные и избежать множества ошибок.

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

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

SELECT
    students.student_id,
    students.student_name,
    students.grade,
    courses.course_name
FROM students
INNER JOIN
    courses ON students.student_id = courses.student_id;

LEFT JOIN
Теперь перейдём к электронной коммерции. Воспользуемся LEFT JOIN (INCLUSIVE), чтобы получить список всех клиентов, включая тех, кто еще не совершил покупку.

SELECT
    customers.customer_id,
    orders.order_id
FROM customers
LEFT JOIN
    orders ON customers.customer_id = orders.customer_id;

Если же нам нужно найти только тех покупателей, которые не совершали заказ, то вносим небольшие правки в EXCLUSIVE-запрос:

SELECT 
    customers.customer_id,
    orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
WHERE orders.customer_id IS NULL;

RIGHT JOIN
Теперь представим, что перед нами база библиотеки. Воспользуемся RIGHT JOIN (INCLUSIVE), чтобы получить список всех книг, даже тех, которые не выданы ни одному читателю.

SELECT
    books.book_id,
    borrowers.borrower_name,
    books.title,
    books.author,
    books.genre
FROM
    books
RIGHT JOIN
    borrowers ON books.borrower_id = borrowers.borrower_id;

Если же нужно найти только книги, которые выданы читателям, добавляем EXCLUSIVE условие:

SELECT
    books.book_id,
    borrowers.borrower_name,
    books.title,
    books.author,
    books.genre
FROM
    books
RIGHT JOIN
    borrowers ON books.borrower_id = borrowers.borrower_id
WHERE books.borrower_id IS NULL;

FULL JOIN
Перейдём к управлению проектами. С помощью FULL JOIN (INCLUSIVE) выведем всех сотрудников и все проекты, независимо есть ли между ними связь.

SELECT
    e.employee_id,
    e.employee_name,
    pr.project_id,
    pr.project_name
FROM
    employees e
FULL JOIN
    projects pr ON e.employee_id = pr.employee_id;

Если же нам нужно вывести всех сотрудников без проектов и все проекты, без связанных сотрудников, модифицируем запрос до EXCLUSIVE:

SELECT
    e.employee_id,
    e.employee_name,
    pr.project_id,
    pr.project_name
FROM
    employees e
FULL JOIN
    projects pr ON e.employee_id = pr.employee_id
WHERE
    pr.employee_id IS NULL OR pr.project_id IS NULL;

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

#sql #join

Explicit is better than implicit: не используйте SELECT *

Один из моих любимых принципов в The Zen of Python (PEP-20) — это "Explicit is better than implicit" (явное — лучше неявного). Он актуален не только для питона, но и для любого кода, документации и в целом жизни. Сегодня я хочу поговорить о примере использования этого принципа.

Часто для сокращения sql-кода используется конструкция SELECT *, которая выгружает информацию сразу из всех колонок. Согласитесь, если у вас таблица с 200 столбцами, то вместо того, чтобы писать колбасу из перечислений, куда проще просто поставить *. Ведь мы на 100% уверены, что все столбцы нужны.

Почему так делать не нужно?

1. Структура данных: ничто не вечно
Даже если кажется, что исходная таблица никогда не изменится, через Х времени это произойдёт. Столбцы могут быть добавлены или удалены, и отлаженный процесс будет сломан. Но из-за отсутствия явного указания, найти и исправить ошибку будет не так просто.

2. Снижение производительности
SELECT * в запросах извлекает все столбцы из указанных объектов, включая те, которые не требуются. Это может значительно увеличить объем получаемых данных и снизить скорость выполнения запроса и его производительность (это особенно актуально для колоночного хранения).

3. Сложность в понимании логики запроса
Используя неявное указание, мы усложняем дальнейшую поддержку запроса другими людьми. Явное перечисление — это самодокументирующийся код с четким пониманием смыслов.

4. Непреднамеренное раскрытие данных
SELECT * может случайно раскрыть конфиденциальную информацию, которая не предназначалась для текущего контекста или анализа. К примеру, это может случиться после того как в исходную таблицу будут добавлены новые столбцы о которых заранее никто не предполагал.

Для четкой понимании логики запроса, оптимизации запросов и упрощения рефакторинга всегда указывайте столбцы явно (разве что речь не идёт об ad-hoc запросах).

#sql

Группировка NULL-значений

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

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

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

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

#sql #null