SQL Academy: всё о реляционных БД и SQL

🔑 UUIDv4 тихо убивает твою базуUUID удобен: генерируешь на бэке, ключи уникальны, и никто не подсмотрит число заказов, поменяв цифру в URL. Кажется, идеальный Primary Key.Но обычный UUIDv4 — полностью случайный. А базе это очень не нравится.⚙️ Почему такИндексы живут в B-Tree, а дереву нужен порядок.🔹 BigInt с автоинкрементом → новая запись дописывается в конец. Мгновенно.🔹UUIDv4 → случайный ID лезет в середину дерева. База рвёт заполненную страницу пополам и перетасовывает данные. Это и есть Page Split.📉 Что происходит в продакшенеПока строк пара сотен тысяч — тишина. На миллионах начинается боль:🔹 тормозят INSERT — CPU занят не записью, а перебалансировкой🔹 фрагментация — страницы полупустые, индекс пухнет в разы🔹 вымывание кэша — раздутый индекс не лезет в RAM, и база уходит на диск🚀 Решение — UUIDv7Отказываться от UUID не нужно. Просто бери седьмую версию.В начале строки — timestamp (время до миллисекунды), и только потом случайные биты.🔹 Ключи всегда растут🔹 Для базы это почти автоинкремент🔹Записи ложатся в конец, фрагментация исчезаетСкорость вставок остаётся ровной даже на таблицах в десятки гигабайт.💡 Стартуешь проект? Закладывай UUIDv7 сразу.

CHECK-ограничения в MySQL: валидация прямо в таблице ✅Когда вы хотите убедиться, что в таблицу попадают только корректные данные — не всегда нужно писать сложную логику в приложении. Иногда достаточно встроенного механизма MySQL: ограничения CHECK.🔹 Что такое CHECK?Это правило, которое автоматически проверяет значение в колонке при вставке (INSERT) или обновлении (UPDATE). Если правило нарушено — операция отменяется с ошибкой.Пример:CREATE TABLE users ( id INT PRIMARY KEY, age INT, CHECK (age >= 0 AND age <= 120));Теперь нельзя вставить пользователя с возрастом -5 или 999 — MySQL просто не даст это сделать.Синтаксис CHECK🔸 Можно добавлять при создании таблицы (CREATE TABLE) или позже (ALTER TABLE).🔸 Можно использовать AND, OR, арифметику, сравнения, функции (с ограничениями).🔸 Поддерживается в MySQL 8.0+. В старых версиях CHECK игнорировался (!). Пример с условием на строку:CREATE TABLE products ( id INT PRIMARY KEY, name VARCHAR(100), category ENUM('book', 'game', 'toy'), price DECIMAL(10,2), CHECK (price >= 0));Здесь мы запрещаем отрицательные цены.Добавление ограничения в существующую таблицу:ALTER TABLE usersADD CONSTRAINT chk_age CHECK (age >= 0 AND age <= 120);Что произойдёт при нарушении?INSERT INTO users (id, age) VALUES (1, -10);-- ERROR 3819 (HY000): Check constraint 'chk_age' is violated.⚠️ Важно помнить:🔹CHECK срабатывает только для новых данных — старые строки не проверяются.🔹Если значение NULL, правило обычно не нарушается (NULL считается «неизвестным» и не сравнивается напрямую).🔹Ошибку можно перехватывать в приложении, чтобы сообщить пользователю.✅ Когда стоит использовать CHECK:🔹 Для ограничений: возраст, положительная цена, длина строки, формат (через LIKE или REGEXP).🔹Когда хотите, чтобы в таблице «по умолчанию» всегда были только корректные данные.🔹Чтобы упростить валидацию и сделать БД самодостаточной (например, при работе с внешними источниками данных).

Больше об CTE по ссылкеhttps://sql-academy.org/ru/guide/operator-withБольше об CTE по ссылкеhttps://sql-academy.org/ru/guide/operator-with

Задача из собеседования (VK, intern-аналитик): медиана зарплат по отделам без MEDIAN 📊Любимый вопрос на собесах: проверяет понимание оконных функций и аккуратность с чётным/нечётным числом строк.📋 Схема БД🔹 employees (id, name, department, salary)🎯 ФормулировкаПосчитать медианную зарплату для каждого отдела. Использовать MEDIAN / PERCENTILE_CONT нельзя — нужно реализовать руками.📊 Пример данныхid name dept salary1 Анна IT 802 Борис IT 1203 Вера IT 1504 Глеб HR 605 Дина HR 90Ожидаемый результат:dept medianIT 120 ← центр из 3HR 75 ← (60+90)/2Разбор🧠 Что такое медиана на пальцах🔹 Сортируем зарплаты по возрастанию🔹 Если строк нечётно — берём центральную🔹 Если чётно — среднее двух центральныхДля отдела с зарплатами [40, 50, 70, 100] медиана = (50 + 70) / 2 = 60.1️⃣ Что нам нужно знать про каждую строкуЧтобы найти «центральную» строку в отделе, нужны две вещи:🔹 её позиция в отсортированном списке внутри отдела🔹 общее число строк в отделеОбе задачи решают оконные функции — ROW_NUMBER() и COUNT(*) с PARTITION BY department.2️⃣ Как найти центральные позицииФормула для центра:🔹 FLOOR((cnt + 1) / 2) — нижняя центральная🔹 CEIL((cnt + 1) / 2) — верхняя центральнаяМагия в том, что для нечётного cnt обе формулы дают одну и ту же строку — это и есть единственная центральная. Для чётного — две соседние, которые мы потом усредним.Проверим на наших данных:🔹 IT (cnt=3): FLOOR(4/2)=2, CEIL(4/2)=2 → берём строку №2 → зарплата 120 ✅🔹 HR (cnt=2): FLOOR(3/2)=1, CEIL(3/2)=2 → строки №1 и №2 → (60+90)/2 = 75 ✅3️⃣ Решение через оконные функцииWITH ranked AS ( SELECT department, salary, ROW_NUMBER() OVER ( PARTITION BY department ORDER BY salary ) AS rn, COUNT(*) OVER (PARTITION BY department) AS cnt FROM employees)SELECT department, AVG(salary * 1.0) AS median_salaryFROM rankedWHERE rn IN (FLOOR((cnt + 1) / 2.0), CEIL((cnt + 1) / 2.0))GROUP BY department;CTE ranked внутри каждо

Один UPDATE — и данных больше нет. Как это починить?Представьте: прибегает продакт — «Почему у клиента цена 50к? Мы же вчера ставили 40к!» А в базе уже новое значение. Старое затёрто командой UPDATE. Восстановить нельзя. Никак.Если в проекте есть данные, которые меняются и за которыми нужно следить — цены, статусы, настройки — эта проблема рано или поздно прилетит. Это как редактировать документ без Ctrl+Z — каждое изменение уничтожает предыдущую версию. Для финансовой отчётности и аналитики — катастрофа.Разберём три способа этого избежать — от простого к мощному.Как это работает — коротко:1. Таблица-клон Rooms → триггер → Rooms_History2. SYSTEM_TIME Rooms → база сама ведёт историю3. JSONB-слепки Rooms → триггер → JSON-снимок в отдельную таблицу1️⃣ Вариант 1: Таблица-клонСамый понятный способ. Рядом с основной таблицей (например, Rooms) создаём её копию — Rooms_History. Каждый раз, когда кто-то меняет строку в основной таблице, автоматический механизм (триггер) сначала сохраняет старую версию строки в копию, и только потом применяет изменение.Главная боль — Schema Drift. Допустим, разработчик добавил в основную таблицу новую колонку «площадь». Если он забыл добавить эту же колонку в таблицу-клон и обновить триггер — всё ломается. Подходит только там, где структура таблиц не менялась годами и не планирует.2️⃣ Вариант 2: SYSTEM_TIME — встроенный видеорегистраторНекоторые современные базы данных умеют вести историю сами, без всяких таблиц-клонов и триггеров. Достаточно один раз включить функцию — и база начнёт запоминать, когда каждая строка появилась и когда изменилась.После этого можно буквально «заглянуть в прошлое» одной командой:SELECT * FROM Rooms FOR SYSTEM_TIME AS OF '2026-01-01';Эта строка говорит: «Покажи мне все комнаты в том виде, в каком они были 1 января 2026 года». Идеально для отладки: можно увидеть точное состояние данных в ту секунду, когда пользователь словил баг. Если нужна серьёзная аналитика и «путешествия во времени» — это лучший выб

DELETE — это навсегда. Почему в разработке его избегают?Удаление данных из базы в реальном проекте — это почти всегда плохая идея. Ошибка пользователя или случайный клик могут стоить часов работы по восстановлению данных из бэкапов. Чтобы не доводить до этого, используют Soft delete.Вместо реального удаления мы просто помечаем строку как неактивную в колонке deleted_at. Данные остаются на месте, но приложение их «не видит».Где начинаются проблемыСамое неприятное — это необходимость фильтровать удаленные записи в каждом запросе. Стоит один раз забыть про WHERE deleted_at IS NULL, и в аналитике по выручке или количеству живых юзеров появятся «мертвые души».Еще одна ловушка — уникальные индексы. Если на email стоит UNIQUE, база не даст создать новый аккаунт с почтой удаленного юзера. Для индекса эта запись всё еще существует, и ему неважно, какая дата там проставлена.Как сделать архитектуру чищеЧтобы не загромождать код постоянными проверками на NULL, лучше вынести логику во View. Мы создаем виртуальную таблицу, которая сразу отсекает всё лишнееCREATE VIEW active_users ASSELECT * FROM Users WHERE deleted_at IS NULL;Для решения конфликтов уникальности идеально подходят частичные индексы. В Postgres это делается одной командой:CREATE UNIQUE INDEX idx_email ON Users (email) WHERE deleted_at IS NULL;Так индекс будет игнорировать удаленные строки. Это позволит пользователям регистрироваться заново на ту же почту и сэкономит место на диске.Итог прост: если данные представляют хоть какую-то ценность, не удаляйте их. Используйте флаги удаления и View. Это в разы безопаснее, чем чистить базу физически.

Views vs Materialized Views: в чем разница?Когда вы только начинаете работать с SQL, вам быстро надоедает писать один и тот же запрос на 20 строк с кучей джоинов. Чтобы не копипастить код, его можно сохранить прямо в базе. Для этого есть два способа: обычные Views (вьюхи) и материализованные.Что такое View?Это просто запрос, которому дали имя. База не создает новую таблицу и не копирует данные. Она просто запоминает ваш SELECT.Когда вы пишете SELECT * FROM my_view, база «под капотом» подставляет ваш исходный код и выполняет его заново.Представьте, что вам постоянно нужно видеть комнаты вместе с именами владельцев. Чтобы не джойнить таблицы каждый раз руками, делаем так:CREATE VIEW room_owners ASSELECT r.id, r.home_type, u.name as owner_nameFROM Rooms rJOIN Users u ON r.owner_id = u.id;Теперь можно просто писать SELECT * FROM room_owners. База будет каждый раз пересчитывать джойн. Плюс в том, что данные всегда актуальные. Минус: если в таблицах миллионы строк, такая View начнет подтормаживать.Что такое Materialized ViewЗдесь база выполняет запрос один раз и сохраняет результат в отдельную скрытую таблицу. Это спасение для тяжелых расчетов. Например, если мы хотим посчитать общую выручку по каждой комнате за всё время:CREATE MATERIALIZED VIEW room_stats ASSELECT room_id, SUM(total) as total_earnedFROM ReservationsGROUP BY room_id;Результат читается мгновенно, потому что сумма уже посчитана и лежит на диске. Но есть нюанс: данные в ней «застывают». Если пришла новая бронь, сумма не изменится, пока вы не обновите View командой:REFRESH MATERIALIZED VIEW room_stats;Что выбрать?Все зависит от того, что вам важнее: скорость или актуальность. Если данные нужны «прямо сейчас» — делайте обычную View. Если вы строите отчет за прошлый месяц, который не меняется каждую секунду — Materialized View сэкономит кучу ресурсов.

🚀 Вопросы с собеседования на позицию intern аналитика в Тинькофф: разбор SQL 🚀Сегодня мы разберем некоторые интересные вопросы по SQL, которые могут встретиться на собеседовании в Тинькофф. 📊🔍1️⃣ Может ли измениться результат запроса, если в LEFT JOIN поменять местами таблицы ?Да, если поменять местами таблицы в LEFT JOIN, результат запроса кардинально изменится. Все потому, что LEFT JOIN берет все строки из "левой" таблицы, дополняя их данными из "правой". Смена местами меняет логику: теперь "правая" становится "левой" и наоборот. Это влияет на то, какие строки и как будут включены в результат. 🔄2️⃣ 5 + NULL это сколько?В SQL, когда вы выполняете арифметическую операцию с NULL, результатом всегда будет NULL. Это связано с тем, что NULL представляет собой неопределенное значение, и любая операция с неопределенным значением также является неопределенной. Таким образом, 5 + NULL будет равно NULL.3️⃣ Какие функции умеют возвращать значения из предыдущих/последующих строк для заданной строки таблицы ?В SQL, чтобы работать с данными из строк до и после текущей, используются оконные функции. Эти функции обеспечивают доступ к значениям предыдущих/последующих строк:ℹ️LEAD(): Получает данные из строки после текущей, позволяя смотреть вперед на заданное количество строк.ℹ️ LAG(): Доступ к данным из строки перед текущей, предоставляя возможность анализировать предыдущие значения.ℹ️ FIRST_VALUE() и LAST_VALUE(): Возвращают первое и последнее значение в наборе строк соответственно, идеально для сравнения текущих значений с крайними в диапазоне.ℹ️ NTH_VALUE(): Дает значение из конкретной позиции в окне, полезно для нахождения конкретных точек данных в последовательности.Для тех, кто хочет углубиться в тему оконных функций: https://sql-academy.org/ru/guide/windows-functions#задание_из_собеседования #tinkoff #intern #analytic

SQL & AI: Друзья или враги? Еще пару лет назад мы обсуждали, заменит ли нейросеть разработчика. В 2026 году ответ стал очевиден: AI не заменил SQL-специалиста, но он радикально изменил правила игры. Сегодня разбираемся, как превратить AI в мощного союзника и где расставлены ловушки, в которые попадают даже мидлы.🤝 Почему AI - ваш лучший друг🔹Прощай, шаблонный кодНаписать 10 однотипных JOIN или сложную структуру CASE WHEN нейросеть может за секунды. Это экономит до 40% времени на рутине.🔹Объяснение чужого кодаПолучили в наследство legacy-запрос на 200 строк без единого комментария? AI отлично справляется с декомпозицией и объяснением логики «человеческим» языком.🔹Генерация синтетических данныхНужно быстро наполнить таблицу для тестов, соблюдая типы данных и связи? AI сделает это лучше любого скрипта-заполнителя.⚠️ Почему AI - коварный врагГлавная проблема в том, что AI галлюцинирует уверенно.🔹Каша из диалектовМодель может предложить изящное решение на функциях PostgreSQL (например, DISTINCT ON), которые просто не существуют или работают иначе в вашей версии MySQL или ClickHouse.🔹Производительность — не приоритет по умолчанию Если не попросить специально оптимизировать запрос, AI выдаст код, который просто «работает». Он часто предлагает вложенные подзапросы там, где эффективнее CTE или оконные функции, и редко думает об использовании индексов.🔹Отсутствие контекста БДНейросеть не знает объема ваших данных, наличия индексов и специфики «железа». Запрос, который AI посчитал идеальным, может «положить» ваш прод в пиковую нагрузку.💡 Как выжить в эпоху AI-SQL?Чтобы оставаться востребованным профи, фокус должен сместиться с «написания кода» на его аудит и архитектуру.🔹Валидация — это законНикогда не копируйте код из чата в консоль без проверки EXPLAIN ANALYZE.🔹AI как ревьюерПопробуйте обратный подход — скормите нейросети свой рабочий запрос и спросите: «Как это оптимизировать?». Иногда она подсвечивает неочевидные узкие места.🔹Изучайте теорию глубжеЧем лучше вы п

Soft Delete, Hard Delete или Архив? Как правильно удалять данные 🗑️Задача: Пользователь нажал «Удалить аккаунт». Что делать базе? Стереть навсегда? Скрыть? Или перенести в чулан? Разбираем три стратегии.1️⃣ Hard Delete (Физическое удаление) 🧨Классический DELETE. Данные исчезают, место освобождается.Плюсы: 🔹 Максимальная скорость работы базы (таблицы компактные). 🔹 Соблюдение GDPR (право на забвение). 🔹 Нет проблем с уникальностью (email можно использовать повторно сразу). Минусы: Данные не восстановить без бэкапа. Опасность «битых ссылок», если забыли настроить каскадное удаление (Foreign Keys).2️⃣ Soft Delete (Мягкое удаление) 👻Мы не удаляем строку, а ставим метку deleted_at = NOW(). Данные лежат на месте, но приложение делает вид, что их нет.Плюсы: 🔹 Легко восстановить («Ой, я случайно!»). 🔹 Сохраняется история и связи. Минусы: 🔸 Раздувание таблицы: База хранит тонны «мертвых» данных, индексы тормозят. 🔸 Сложность запросов: Везде нужно писать WHERE deleted_at IS NULL.🔸 Проблема уникальности: Если bob@site.com удален «мягко», создать нового Боба с тем же email не даст уникальный индекс (нужен частичный индекс).3️⃣ Archiving (Архивирование / Cold Storage) 📦Гибридный подход. Данные удаляются из основной («горячей») таблицы, но перед этим переносятся в отдельную таблицу-архив (users_archive).Как это работает:🔹Стартуем транзакцию.🔹INSERT данные в таблицу архива.🔹DELETE данные из основной таблицы.🔹Коммит.Плюсы: ✅ Основная таблица летает (в ней только активные данные). ✅ История сохранена (в архиве). ✅ Нет проблем с уникальными индексами в основной таблице. Элегантный перенос одной командой (PostgreSQL) :WITH moved_rows AS ( DELETE FROM users WHERE id = 101 RETURNING *)INSERT INTO users_archiveSELECT * FROM moved_rows;

Всем привет! На канале Data analysis | Анализ данных | DA разбираются темы и вопросы, которые должен знать аналитик данных, имеющий опыт 3-6 лет. Все темы взяты из реальных вакансий, опубликованных на hh.ru. Будет полезно, если вы являетесь аналитиком данных (начинающим или опытным) или работаете по смежной профессии, либо просто интересуетесь базами данных, Python, SQL, экономикой и финансами и всеми производными от этих тем.Список разобранных вопросов:🐍 Python:— Эмбеддинги предложений— Алгоритм кластеризации— Кластеризация текстовой информации— Визуализация: Matplotlib— Визуализация: Seaborn— Python в Tableau— Python + SQL: Cx_oracle— Большие данные в Python: Dask— Массовая загрузка файлов в БД— Продвинутая запись в Excel: XlsxWriter— Аномалии в данных— Аномалии в данных: применение скользящих средних — Автоматизация подбора чисел— Анализ динамики📖 SQL:— PARTITION (оконные функции)— PARTITION (партиционирование)— Процедуры: разбор IN | OUT | IN OUT— Процедуры: объявления и исключения— PACKAGE (пакеты)— Циклы LOOP, WHILE, FOR— CURSOR— Индексы— Представления (Views)— Материализованные и нематериализованные views— Hints (хинты)— EXPLAIN PLAN— TRIGGER (триггеры)📖 Базы данных:— Какие бывают базы данных— Виды БД наглядно— ACID и BASE— OLAP-кубы— Проектирование баз данных— Разница между БД и DWH— Витрины данных— ETL и ELT процессы— Звездочка, снежинка, Data Vault— Слои данных в DWH— Нормализация⚙️ Инструменты:— Обзор Hadoop— Обзор Hive— Обзор Impala— Обзор Airflow— Обзор ClickHouse— Tableau— Arenadata Catalog— Qlik Sense— Informatica PowerCenter🐞 А/Б тестирование:— Основы А/Б тестов— А/Б тесты на практике— Математические методы проверки результатов— Инструменты А/Б тестирования📊 Работа с данными:— Парадокс Симпсона— Банковские клиенты— Клиентская информация в банковском DWH— Банковские продукты— Продуктовая информация в банковском DWH— Счета, баланс и фин рез в банковском DWH— Качество данных— Метаданные— Source-to-Target MappingВ ближайшем будущем будем разбирать ���

Мини-квиз по дате и времени в PostgreSQL1️⃣ Вопрос 1: Парсинг даты одной функциейSELECT to_date('16-08-2024', 'DD-MM-YYYY');a) 16/08/2024b) 2024-16-08c) 2024-08-16d) Ошибка парсингаПравильный ответ: c) 2024-08-162️⃣ Вопрос 2: Разница в дняхSELECT DATE '2024-03-15' - DATE '2024-03-01';a) 13b) 14c) 15d) 16Правильный ответ: b) 143️⃣ Вопрос 3: Номер недели (ISO) для актуальной датыSELECT EXTRACT(WEEK FROM DATE '2026-01-01');a) 0b) 1c) 52d) 53Правильный ответ: b) 14️⃣ Вопрос 4: Переход через полночьSELECT TIMESTAMP '2024-08-16 23:00:00' + INTERVAL '2 hours';a) 2024-08-16 25:00:00b) 2024-08-17 00:00:00c) 2024-08-17 01:00:00d) 2024-08-16 23:02:00Правильный ответ: c) 2024-08-17 01:00:00

Топ-вопросы с собеседований по SQL + ответы 📝Мы собрали самые популярные вопросы и варианты ответов — читайте по ссылке:https://sql-academy.org/ru/interview-questionsЧтобы было удобнее, подготовили офлайн-версию (PDF). Сохраните и используйте, чтобы быстро освежить знания перед важным собесом.📥 офлайн-версия — в файле к этому постуудачи на собеседованиях! 💪

Blob в mysql или ссылки на файлы — как выбрать? 🗂️Задача: нужно хранить файлы (фото, pdf, видео). Где их держать: прямо в базе (BLOB) или во внешнем хранилище (например, S3) и в БД — только ссылку? Разберёмся коротко и понятно.Что такое blob и «ссылки»🔹 BLOB — двоичные данные прямо в таблице MySQL (поле BLOB/LONGBLOB).🔹 Ссылка — файл лежит во внешнем хранилище, а в БД мы храним URL + метаданные (имя, размер, тип).Когда хранить в базе (blob) 📦Подходит, если:🔹файлы небольшие (аватарки, превью, ≤ ~1 МБ);🔹важна атомарность: данные и файл сохраняются/откатываются вместе;🔹проект простой, без CDN и стриминга.Плюсы: просто, транзакционно, один бэкап.Минусы: база быстро растёт, бэкапы тяжелее, отдавать большие файлы медленно.Мини-примерCREATE TABLE file_blobs ( id BIGINT PRIMARY KEY, name VARCHAR(255), mime VARCHAR(100), size INT, -- для дедупликации sha256 BINARY(32), -- сам файл data LONGBLOB NOT NULL, created_at TIMESTAMP, UNIQUE KEY (sha256));Когда хранить снаружи (ссылки) 🔗Подходит, если:🔹файлов много или они крупные (фото/видео/доки);🔹нужна раздача через CDN (быстрая доставка);🔹важны дешёвое хранилище и лёгкие бэкапы БД.Плюсы: масштабируемо, дёшево, быстро отдаётся.Минусы: две системы (БД + хранилище).Мини-примерCREATE TABLE files ( id BIGINT PRIMARY KEY, name VARCHAR(255), mime VARCHAR(100), size INT, sha256 BINARY(32), -- https://... или s3://... storage_url VARCHAR(2048) NOT NULL, created_at TIMESTAMP, UNIQUE KEY (sha256));Быстрый чек-лист выбора ✅🔹Размер: маленькие → BLOB; большие → ссылка.🔹Транзакционность (вместе с бизнес-данными): нужна → BLOB.🔹 Отдача файлов пользователям: нужен CDN → ссылка.🔹Бэкапы и восстановление: хочется лёгких дампов → ссылка.🔹Стоимость хранения: экономим → ссылка.Вывод🔹BLOB хорош для маленьких файлов и строгой консистентности.🔹Ссылки — почти всегда лучший выбор для больших объёмов, скорости и экономии.