Генерация пропущенных дат в отчётах: почему LEFT JOIN не помогает!

Классическая задача — построить отчёт по дням. Например, количество заказов по датам. И внезапно оказывается, что в выдаче нет некоторых дней.

Таблица:

orders(id, created_at)

Плохой вариант:

SELECT
    DATE(created_at) AS day,
    COUNT(*) AS orders_count
FROM orders
GROUP BY DATE(created_at)
ORDER BY day;

Проблема: если в какой-то день не было заказов — строки просто не будет. В аналитике это почти всегда ошибка, потому что отсутствие строки не равно ноль значений.

Попытка через LEFT JOIN сама по себе не помогает — без таблицы дат нечего достраивать. Правильный подход — сначала явно сгенерировать календарь, а потом присоединить данные.

В PostgreSQL для этого есть generate_series:

WITH calendar AS (
    SELECT generate_series(
        DATE '2026-01-01',
        DATE '2026-01-10',
        INTERVAL '1 day'
    )::date AS day
)
SELECT
    c.day,
    COUNT(o.id) AS orders_count
FROM calendar c
LEFT JOIN orders o
  ON DATE(o.created_at) = c.day
GROUP BY c.day
ORDER BY c.day;

Теперь: каждый день присутствует; если заказов нет — COUNT(o.id) вернёт 0.

Почему именно COUNT(o.id), а не COUNT(*): COUNT(*) посчитает строку календаря даже без совпадений — всегда ≥ 1; COUNT(o.id) считает только реальные заказы — корректный 0 при отсутствии данных.

Ещё один важный момент — условие в JOIN: ON DATE(o.created_at) = c.day. Так писать удобно, но это ломает использование индекса по created_at.

Лучше диапазон:

ON o.created_at >= c.day
AND o.created_at <  c.day + INTERVAL '1 day'

Это позволяет использовать индекс и ускоряет запрос на больших таблицах.

Расширение задачи — накопительный итог по дням (используется тот же CTE calendar):

SELECT
    c.day,
    COUNT(o.id) AS orders_count,
    SUM(COUNT(o.id)) OVER (ORDER BY c.day) AS running_total
FROM calendar c
LEFT JOIN orders o
  ON o.created_at >= c.day
 AND o.created_at <  c.day + INTERVAL '1 day'
GROUP BY c.day
ORDER BY c.day;

Если нужно ограничение по периоду по данным, а не вручную:

WITH bounds AS (
    SELECT
        MIN(created_at)::date AS min_day,
        MAX(created_at)::date AS max_day
    FROM orders
),
calendar AS (
    SELECT generate_series(min_day, max_day, INTERVAL '1 day')::date AS day
    FROM bounds
)
SELECT ...

Календарь строится автоматически по данным. Если created_at — timestamptz, границы дня зависят от timezone.

🔥 Если в отчёте есть время — сначала формируйте ось времени, потом присоединяйте данные. Иначе вы работаете не с нулями, а с отсутствием строк, что даёт искажённую картину.

➡️ SQL Ready | #практика

😍 SQL шпаргалка для начинающих — база по работе с бд!

Удобный и понятный справочник, где разобраны основные SQL-запросы и конструкции: SELECT, WHERE, JOIN, INSERT, UPDATE и другие ключевые команды. Всё объясняется простым языком с примерами, чтобы быстро понять логику работы с данными и начать применять это в реальных задачах

📌 Оставляю ссылочку: ermita.one

➡️ SQL Ready | #ресурс

BUFFERS: как увидеть цену запроса?

Обычный EXPLAIN ANALYZE показывает время выполнения, но почти ничего не говорит о том, сколько данных было прочитано с диска и из памяти.

EXPLAIN (ANALYZE, BUFFERS)
SELECT ...

Опция BUFFERS добавляет статистику по страницам памяти:
shared hit — страницы найдены в PostgreSQL shared buffers.
shared read — страницы пришлось загрузить в shared buffers; это может быть физический диск или OS page cache.

Buffers: shared hit=15000 read=2

Такой запрос почти полностью работает из памяти, что хорошо.

А здесь видно узкое место: запрос массово читает диск, даже если по времени более менее.

Buffers: shared hit=10 read=15000

🔥 Два запроса с одинаковым execution time могут иметь абсолютно разную нагрузку на систему.

➡️ SQL Ready | #совет

📂 Напоминалка по Data Engineering и аналитической архитектуре!

Например, Data Warehouse хранит уже очищенные и структурированные данные для BI и отчетности, Data Lake позволяет складировать сырые данные любого типа и обрабатывать их по мере необходимости, а Data Mesh распределяет владение данными между бизнес-доменами и превращает данные в полноценные data products.

На картинке — основные отличия: как хранятся данные, кто ими управляет, как происходит обработка и для каких задач лучше подходит каждый подход.

Сохрани, чтобы не потерять!

➡️ SQL Ready | #ресурс

Почему NOT IN может вернуть пустой результат из-за NULL!

Одна из самых неприятных ловушек SQL — поведение NOT IN при наличии NULL. Запрос выглядит абсолютно корректно, но внезапно перестаёт возвращать строки.

Таблицы:

users(id)
bans(user_id)

Допустим, нужно получить пользователей, которых нет в bans. Часто пишут так:

SELECT *
FROM users
WHERE id NOT IN (
    SELECT user_id
    FROM bans
);

Пока в bans.user_id нет NULL — всё работает нормально. Но представим данные:

bans
-------
1
2
NULL

Теперь запрос внезапно вернет:

0 rows

Хотя пользователи без бана есть. Почему так происходит — SQL сравнивает условие примерно как:

id <> 1
AND id <> 2
AND id <> NULL

Но:

id <> NULL

не даёт TRUE или FALSE. Результат: UNKNOWN. А в WHERE проходят только TRUE. Из-за этого всё условие целиком перестаёт выполняться.

Это особенность трёхзначной логики SQL: TRUE, FALSE, UNKNOWN

Любое сравнение с NULL даёт UNKNOWN:

NULL = 1
NULL <> 1
NULL = NULL

Поэтому NOT IN с NULL внутри подзапроса становится опасным.

Безопасный вариант — NOT EXISTS:

SELECT *
FROM users u
WHERE NOT EXISTS (
    SELECT 1
    FROM bans b
    WHERE b.user_id = u.id
);

Почему EXISTS работает нормально: сравнение идёт построчно; NULL не ломает всю проверку; оптимизатор обычно хорошо превращает это в anti-join.

Ещё вариант — явно убрать NULL:

SELECT *
FROM users
WHERE id NOT IN (
    SELECT user_id
    FROM bans
    WHERE user_id IS NOT NULL
);

Но на практике: NOT EXISTS обычно считается более безопасным и читаемым решением.

Отдельный момент: NOT IN () и NOT EXISTS могут давать разные планы выполнения в зависимости от СУБД. Но логически для nullable данных: NOT EXISTS почти всегда предпочтительнее.

Быстрая проверка проблемы:

SELECT COUNT(*)
FROM bans
WHERE user_id IS NULL;

Если такие строки есть — NOT IN уже потенциально опасен.

🔥 NOT IN и NULL плохо сочетаются. Если в подзапросе появляется хотя бы один NULL, условие может перестать возвращать строки вообще. Для таких проверок надёжнее использовать NOT EXISTS.

➡️ SQL Ready | #практика

❤️ SQL DEV — большая база для изучения SQL и PostgreSQL!

Здесь собрано огромное количество материалов по SQL: запросы, JOIN’ы, подзапросы, оконные функции, индексы, оптимизация и работа с PostgreSQL. Репозиторий отлично подходит как для изучения базы, так и для углубления в более сложные темы. Особенно полезно то, что здесь много практических примеров и разборов реальных SQL-конструкций.

Оставляю ссылочку: GitHub 📱

➡️ SQL Ready | #репозиторий

📂 Напоминалка по оптимизации баз данных!

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

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

Сохрани, чтобы не потерять!

➡️ SQL Ready | #ресурс

PostgreSQL может избежать полной сортировки таблицы при ORDER BY ... LIMIT!

Большинство думает, что ORDER BY всегда сортирует вообще всю таблицу. Но в PostgreSQL для LIMIT есть отдельная оптимизация — Top-N Heap Sort.

SELECT *
FROM orders
ORDER BY created_at DESC
LIMIT 10;

Если нужен только top-10 результат, PostgreSQL не обязан сортировать миллионы строк полностью. Вместо этого он держит в памяти только N лучших строк во время scan.

Это видно прямо в execution plan:

Sort Method: top-N heapsort

Особенно интересно становится на огромных таблицах, где полный sort мог бы уйти на диск:

SET work_mem = '4MB';

EXPLAIN ANALYZE
SELECT *
FROM events
ORDER BY created_at DESC
LIMIT 50;

Даже при маленьком work_mem PostgreSQL может избежать полной сортировки всех строк и работать заметно быстрее именно благодаря оптимизации Top-N.

А если добавить подходящий индекс:

CREATE INDEX idx_events_created_at
ON events (created_at DESC);

🔥 PostgreSQL вообще сможет обойтись без Sort и просто сделать Index Scan в нужном порядке.

➡️ SQL Ready | #совет

Почему индекс может не использоваться, даже если он есть!

Очень частая ситуация: индекс создан, запрос написан вроде нормально, но SQL всё равно делает Seq Scan или Full Table Scan.

Имеем такую таблицу:

users(
    id,
    email,
    created_at
)

Индекс:

CREATE INDEX idx_users_email
ON users(email);

Кажется, что такой запрос точно должен использовать индекс:

SELECT *
FROM users
WHERE email = 'test@example.com';

И обычно действительно будет Index Scan.

Но достаточно небольшой детали — и индекс может перестать использоваться. Например:

SELECT *
FROM users
WHERE LOWER(email) = 'test@example.com';

Проблема в том, что индекс построен по колонке: email. А в условии используется выражение:

LOWER(email)

Для оптимизатора это уже не то же самое условие. В итоге серверу часто приходится: применять LOWER() к строкам; сравнивать результат; читать гораздо больше данных, чем ожидалось.

Исправляется это expression/functional index:

CREATE INDEX idx_users_email_lower
ON users(LOWER(email));

То же самое часто происходит с датами:

SELECT *
FROM orders
WHERE DATE(created_at) = '2025-01-10';

Из-за:

DATE(created_at)

обычный индекс по created_at может не помочь, потому что функция применяется к колонке.

Правильнее писать диапазон:

SELECT *
FROM orders
WHERE created_at >= '2025-01-10'
  AND created_at <  '2025-01-11';

Так условие остаётся sargable — то есть пригодным для эффективного использования индекса.

Ещё одна частая проблема — преобразования типов. Например, плохо:

WHERE user_id::text = '100'

Если user_id — integer, то здесь преобразование применяется к колонке. В такой ситуации обычный индекс по user_id может не использоваться.

Правильнее:

WHERE user_id = 100

Важно: простое условие вида:

WHERE user_id = '100'

в некоторых СУБД может нормально привести литерал к integer и всё равно использовать индекс. Проблема чаще начинается там, где преобразуется сама колонка или выражение становится сложнее.

Популярная ошибка с LIKE:

WHERE email LIKE '%gmail.com'

Здесь обычный B-Tree индекс обычно бесполезен. Так как поиск начинается не с начала строки; сервер не может эффективно использовать упорядоченность индекса.

Если таблица маленькая: 100–1000 строк, то Full Scan может быть дешевле, чем прыжки по индексу.

🔥 Наличие индекса ещё не гарантирует его использование. Функции над колонками, преобразования типов, неправильные LIKE, OR-условия и устаревшая статистика могут сделать индекс бесполезным или менее выгодным для оптимизатора.

➡️ SQL Ready | #практика

😎 SQL Cheatsheet — удобная шпаргалка для повседневной работы!

Этот репозиторий хорошо подойдёт тем, кто постоянно работает с базами данных и хочет быстро освежать в памяти нужные SQL-конструкции. Здесь всё подано компактно и по делу: запросы, JOIN’ы, агрегации, подзапросы и др. Особенно удобно использовать для подготовки к собеседованиям.

Оставляю ссылочку: GitHub 📱

➡️ SQL Ready | #репозиторий

PostgreSQL умеет обновлять только реально изменившиеся строки и это может сильно сократить WAL и нагрузку!

Многие приложения делают UPDATE даже тогда, когда данные вообще не изменились.

UPDATE users
SET name = 'Alex'
WHERE id = 1;

Даже если name уже равен 'Alex', PostgreSQL всё равно создаст новую версию строки (MVCC), запишет WAL, обновит индексы и увеличит нагрузку на autovacuum.

Проверить это можно через системную статистику:

SELECT n_tup_upd
FROM pg_stat_user_tables
WHERE relname = 'users';

Чтобы избежать “пустых” UPDATE, можно сравнивать старые и новые значения прямо в WHERE:

UPDATE users
SET
  name = $1,
  email = $2
WHERE id = $3
  AND (name, email) IS DISTINCT FROM ($1, $2);

IS DISTINCT FROM безопасно сравнивает даже NULL значения, в отличие от обычного !=:

SELECT
  NULL = NULL,
  NULL IS DISTINCT FROM NULL;

На highload-системах это может заметно уменьшить WAL, bloat, количество HOT/non-HOT update и нагрузку на autovacuum без изменения архитектуры.

➡️ SQL Ready | #совет

✍️ Информативная статья вышла на Хабре: «Обратная сторона массивов в PostgreSQL»!

В этой статье:
• Разбирается, почему массивы в PostgreSQL — это отдельная модель хранения со своими ограничениями и компромиссами;
• Показываются скрытые проблемы массивов: потеря ссылочной целостности, особенности GIN-индексов, TOAST, MVCC и дорогостоящие обновления;
• Объясняется, как правильно работать с массивами, когда использовать JSONB, intarray, pgvector и в каких случаях массивы действительно оправданы.

🔊 Продолжайте читать на Habr!

➡️ SQL Ready | #статья

❤️ PostgreSQL Tutorial — подробная документация и учебник по PostgreSQL!

На сайте собрана большая база материалов по PostgreSQL: установка, настройка, SQL-запросы, работа с таблицами, индексами, функциями, транзакциями и администрированием базы данных. Материал подаётся последовательно. Отличный ресурс как для новичков, так и для разработчиков, которым нужен удобный справочник с примерами и практическими объяснениями.

📌 Оставляю ссылочку: postgresql.leopard.in.ua

➡️ SQL Ready | #ресурс

NOT VALID constraints — как добавить CHECK и FOREIGN KEY на huge таблицу без долгой блокировки?

Обычно добавление CHECK или FOREIGN KEY на большую таблицу рискованная операция, потому что PostgreSQL начинает сразу сканировать все старые данные.

ALTER TABLE orders
ADD CONSTRAINT orders_user_fk
FOREIGN KEY (user_id)
REFERENCES users(id);

На production-таблицах в сотни строк это может превратиться в очень долгую блокировку DDL.

Но в PostgreSQL есть фича — NOT VALID:

ALTER TABLE orders
ADD CONSTRAINT orders_price_check
CHECK (price > 0)
NOT VALID;

Без полного сканирования таблицы, сразу начинает проверять все новые записи. При этом старые строки пока не валидируются.

Позже ограничение можно провалидировать отдельно:

ALTER TABLE orders
VALIDATE CONSTRAINT orders_price_check;

Самое интересное — VALIDATE CONSTRAINT не блокирует обычный concurrent DML как классический ALTER TABLE.

То же самое работает и для FOREIGN KEY:

ALTER TABLE orders
ADD CONSTRAINT orders_user_fk
FOREIGN KEY (user_id)
REFERENCES users(id)
NOT VALID;

🔥 Это одна из самых полезных возможностей PostgreSQL для безопасных миграций, постепенного внедрения ограничений и наведения порядка в старых базах без длительного простоя.

➡️ SQL Ready | #совет

✍️ PostgreSQL Querying — практическое изучение SQL на PostgreSQL!

Здесь подробно разбираются запросы, работа с данными, фильтрация, JOIN’ы, агрегации и другие конструкции, которые постоянно используются в разработке. Материал подаётся последовательно и на примерах, поэтому намного проще понять логику запросов и научиться писать их самостоятельно.

Оставляю ссылочку: GitHub 📱

➡️ SQL Ready | #репозиторий