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

🆓 Ваши SQL-запросы работают, но через месяц их уже сложно прочитать и изменить? С ростом логики запросы превращаются в набор вложенных подзапросов. Разобраться в них сложно, поддержка занимает время, а любые изменения несут риск сломать результат. На открытом уроке разберём: как использовать обобщенные табличные выражения (CTE), чтобы писать сложные запросы по шагам. Покажем, как упростить структуру, сделать код читаемым и работать с иерархиями через рекурсивные CTE. 🗓 Урок проходит в преддверии старта курса «PostgreSQL для администраторов баз данных и разработчиков». Если вы хотите писать SQL, который легко читать и поддерживать — подключайтесь 27 мая в 20:00 МСК. 🔗 Регистрация открыта: https://tglink.io/79c7c811e717c6?erid=2W5zFJEXK6y Реклама. ООО "ОТУС ОНЛАЙН-ОБРАЗОВАНИЕ". ИНН 9705100963.

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

Ты аналитик. Ты тащишь на себе дашборды, A/B-тесты и SQL в 10 вкладках. А при мыслях об автоматизации задач и усилении своих скиллов, осекаешься - повышение квалифкации стоит десятки тысяч. Больше 60% работодателей уже заложили бюджет на развитие команды в этом году. Просто большинство аналитиков никогда не просят. Потому что не знают, как. Мы собрали полный гайд: → 8 шагов от «хочу прокачаться» до «компания всё оплатила» → Готовые обоснования для руководителя → Скрипты ответов на возражения → Чек-лист перед разговором с HR Здесь - твоя грамотная заявка на корпоративный грант. 📄 Забирай методичку бесплатно → https://tglink.io/7516d0cb076c3f Реклама. ООО "АЙТИ РЕЗЮМЕ". ИНН 4025460134. erid: 2W5zFHRhK4D

Задача из собеседования (VK, intern-аналитик): медиана зарплат по отделам без MEDIAN 📊 Любимый вопрос на собесах: проверяет понимание оконных функций и аккуратность с чётным/нечётным числом строк. 📋 Схема БД 🔹 employees (id, name, department, salary) 🎯 Формулировка Посчитать медианную зарплату для каждого отдела. Использовать MEDIAN / PERCENTILE_CONT нельзя — нужно реализовать руками. 📊 Пример данных

    
        id name  dept salary
1  Анна  IT   80
2  Борис IT   120
3  Вера  IT   150
4  Глеб  HR   60
5  Дина  HR   90{}
    

Ожидаемый результат:

    
        dept median
IT   120    ← центр из 3
HR   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_salary
FROM ranked
WHERE rn IN (FLOOR((cnt + 1) / 2.0), CEIL((cnt + 1) / 2.0))
GROUP BY department;{}
    

CTE ranked внутри каждого отдела нумерует сотрудников от самой низкой зарплаты к самой высокой и параллельно считает общее число сотрудников. Внешний запрос оставляет только «центральные» строки и усредняет их. На что обратить внимание❗️ 🔹 Деление на 2.0, а не на 2 — иначе в некоторых БД получим целочисленное деление и логика для чётных отделов поедет 🔹 AVG(salary * 1.0) — та же история, если зарплаты хранятся в INT, без умножения на 1.0 результат округлится до целого 🔹 COUNT(*) посчитает и NULL-зарплаты как строки. Если такое возможно — используйте COUNT(salary) и заранее отфильтруйте WHERE salary IS NOT NULL, иначе ROW_NUMBER поставит NULL в начало и сломает порядок 🎁 Бонус: если PERCENTILE_CONT всё-таки доступен В PostgreSQL, Oracle, SQL Server задача решается в одну строку:

    
        SELECT 
    department,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median
FROM employees
GROUP BY department;{}
    

Но на собесе обычно просят именно «руками» — чтобы проверить, понимаете ли вы, что происходит под капотом 🔍 🎯 Что запомнить 🔹 Медиана руками = ROW_NUMBER + COUNT в одном CTE 🔹 Центральные позиции — FLOOR и CEIL от (cnt+1)/2 🔹 Делим на 2.0, а не на 2 — иначе целочисленное деление всё сломает 🔹 Если в БД есть PERCENTILE_CONT — используйте его; ручная реализация нужна только для собесов и старых MySQL (до 8.0 там вообще нет оконных функций — придётся через переменные)

❗️ SQL-запросы со временем превращаются в длинные конструкции: вычисления повторяются, логика расползается по коду, поддерживать запросы становится сложнее. 🚀 Функции SQL позволяют вынести вычисления и бизнес-логику в отдельный блок и использовать их повторно в разных запросах. 🗓 21 апреля в 20:00 МСК на открытом уроке разберём, чем функции отличаются от хранимых процедур, какие виды функций существуют и когда их применять. Покажем разницу между скалярными и табличными функциями, разберём создание функций на примере PostgreSQL и MS SQL Server, а также обсудим влияние функций на производительность запросов. Вы увидите практические примеры использования функций в бизнес-логике и научитесь структурировать SQL-код так, чтобы его было легче поддерживать. ➡️ Открытый урок проходит в преддверии старта курса «SQL для разработчиков и аналитиков». Зарегистрируйтесь и получите доступ к занятию: https://tglink.io/fd3eee7a304976?erid=2W5zFJmmRLM #реклама О рекламодателе

Один UPDATE — и данных больше нет. Как это починить? Представьте: прибегает продакт — «Почему у клиента цена 50к? Мы же вчера ставили 40к!» А в базе уже новое значение. Старое затёрто командой UPDATE. Восстановить нельзя. Никак. Если в проекте есть данные, которые меняются и за которыми нужно следить — цены, статусы, настройки — эта проблема рано или поздно прилетит. Это как редактировать документ без Ctrl+Z — каждое изменение уничтожает предыдущую версию. Для финансовой отчётности и аналитики — катастрофа. Разберём три способа этого избежать — от простого к мощному.

    
        Как это работает — коротко:

1. Таблица-клон     
Rooms → триггер → Rooms_History
2. 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 года». Идеально для отладки: можно увидеть точное состояние данных в ту секунду, когда пользователь словил баг. Если нужна серьёзная аналитика и «путешествия во времени» — это лучший выбор. Но есть нюанс: SYSTEM_TIME поддерживают не все базы данных. В PostgreSQL и MariaDB это работает, а вот в MySQL — нет. 3️⃣ Вариант 3: История через JSONB-слепки Идея простая: каждый раз, когда строка меняется, мы берём её текущую версию, упаковываем целиком в один JSON-объект и сохраняем в отдельную таблицу. Если комнату не трогали полгода, ни одной новой записи за это время не появится. Допустим, у комнаты №42 три раза менялась цена. В таблице истории будет:

    
        room  snapshot                           changed_at
42    {"price": 300, "status": "active"}  1 января
42    {"price": 400, "status": "active"}  15 марта
42    {"price": 500, "status": "paused"}  10 апреля{}
    

Хотим узнать цену в феврале? Берём последнюю запись до февраля — 300. Почему JSON, а не обычная таблица-клон? Потому что JSON — это «резиновый» контейнер. Неважно, сколько новых колонок добавили в основную таблицу — JSON примет их все без единого изменения в таблице истории. Та самая проблема Schema Drift из первого варианта здесь просто не существует. Ложка дёгтя: история раздувает базу Об этом забывают чаще всего. Если данные меняются часто, таблица истории за полгода легко вырастает до сотен гигабайт. Проверенная стратегия — разделять данные по «температуре»: 🔹«Горячая» история за последние 3 месяца — живёт в основной базе (PostgreSQL), к ней обращаются постоянно. 🔹 Всё, что старше — уезжает в «холодное» хранилище (ClickHouse, S3). Там место дешевле, а аналитика по миллионам старых строк работает даже быстрее. Итого, что выбрать: 🔹 Простая система, структура не меняется → Таблица-клон с триггером 🔹 Нужна точная аналитика и «путешествие во времени» → SYSTEM_TIME 🔹 Структура часто меняется, средний масштаб → JSONB-слепки И в любом случае — заранее решите, куда архивировать историю через год. Потом будет поздно.

Важная новость на российском ИТ-рынке: анонс национальной бесплатной СУБД для всех отраслей экономики💥 Для этого события Диасофт проведет первую в своей истории конференцию, посвященную промышленной эксплуатации СУБД и архитектуре корпоративных данных – День СУБД 2026. Два важных факта о Digital Q.DataBase от Диасофт: 🪄 Это СУБД – "полиглот", которая понимает диалекты Oracle, MS SQL и PostgreSQL, без переписывания кода приложений. 🪄 У решения первое место в рейтинге конвергентных СУБД по версии CNews 2025. Если вам интересна тема систем управления базами данных и нового подхода к их импортозамещению, приходите на наш День СУБД 2026! 💌 21 апреля в Кибердоме: 🟣 топ-менеджеры Диасофт расскажут о Национальной СУБД для обеспечения технологического суверенитета; 🟣 гости станут участниками деловой и музыкальной программы; 🟣 участники события смогут обменяться идеями и мнениями. Регистрируйтесь по ссылке и до встречи! #реклама О рекламодателе

Проект растет, и быстро искать нужные данные становится все сложнее? Ждем вас на авторском практикуме Дмитрия Кириллова «Разработка корпоративных RAG-систем на PostgreSQL» от ОТУС! О чём будет эфир: 💾Почему классический поиск и документация перестают работать в сложных системах 💾Что такое RAG-системы и как они устроены 💾Архитектура решения: от данных до ответа пользователю 💾Практика: реализация RAG на базе PostgreSQL 💾Как улучшать качество ответов и снижать галлюцинации Ведущий: Дмитрий Кириллов — педагог, соучредитель и технический директор одного из крупнейших сервисов онлайн-регистрации бизнеса в России. 🎁Бонусы для участников: 7% скидка на любой курс ОТУС + доступ к бесплатному пробному периоду корпоративной платформы 🎁Подарок - мини-курс по PostgreSQL Регистрация открыта: https://tglink.io/af4d251c1739d8 Увидимся на эфире! Реклама. ООО "ОТУС ОНЛАЙН-ОБРАЗОВАНИЕ". ИНН 9705100963. erid: 2W5zFJVxSLy

DELETE — это навсегда. Почему в разработке его избегают? Удаление данных из базы в реальном проекте — это почти всегда плохая идея. Ошибка пользователя или случайный клик могут стоить часов работы по восстановлению данных из бэкапов. Чтобы не доводить до этого, используют Soft delete. Вместо реального удаления мы просто помечаем строку как неактивную в колонке deleted_at. Данные остаются на месте, но приложение их «не видит». Где начинаются проблемы Самое неприятное — это необходимость фильтровать удаленные записи в каждом запросе. Стоит один раз забыть про WHERE deleted_at IS NULL, и в аналитике по выручке или количеству живых юзеров появятся «мертвые души». Еще одна ловушка — уникальные индексы. Если на email стоит UNIQUE, база не даст создать новый аккаунт с почтой удаленного юзера. Для индекса эта запись всё еще существует, и ему неважно, какая дата там проставлена. Как сделать архитектуру чище Чтобы не загромождать код постоянными проверками на NULL, лучше вынести логику во View. Мы создаем виртуальную таблицу, которая сразу отсекает всё лишнее

    
        CREATE VIEW active_users AS
SELECT * FROM Users WHERE deleted_at IS NULL;{}
    

Для решения конфликтов уникальности идеально подходят частичные индексы. В Postgres это делается одной командой:

    
        CREATE UNIQUE INDEX idx_email ON Users (email) WHERE deleted_at IS NULL;{}
    

Так индекс будет игнорировать удаленные строки. Это позволит пользователям регистрироваться заново на ту же почту и сэкономит место на диске. Итог прост: если данные представляют хоть какую-то ценность, не удаляйте их. Используйте флаги удаления и View. Это в разы безопаснее, чем чистить базу физически.

🔥 ПОЛУЧИТЕ БЕСПЛАТНЫЙ ДОСТУП К ПРАКТИКУМУ ПО СОЗДАНИЮ AI-АГЕНТОВ🚀 AI-агенты - это системы, которые принимают решения, работают с данными, обращаются к сервисам и выполняют реальные задачи бизнеса🦾🦾 И самое главное - сегодня их можно создавать быстро и без жёсткого кодинга, через vibe-codingВ практикум входит 5 занятий:🔹 Основы Google AI Studio. Создание AI-секретаря по совещаниям🔹 Основы промт-инжиниринга. Создание AI-продажника абонементов в фитнес-центр🔹 Создание AI-ветеринара на Google AI Studio🔹 Разбор самых востребованных AI-ассистентов и ключевых технологий 2026 года🔹 AI-сервис по аренде автомобилей на базе N8N и weweb💡 То, что раньше требовало команды разработчиков, теперь можно собрать быстрее, если понимаешь архитектуру и умеешь работать через AIЕсли хотите начать создавать полноценные AI-системы - приходите 👇🤖 Получить доступ