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