Don’t tell, show the code:

SELECT COUNT(*) FROM users;
-- 350

SELECT COUNT(*) FROM users JOIN dict ON users.id = dict.user_id;
-- 1450

Я понимаю что пишу мб очевидные вещи, но каждый JOIN потенциально может как уменьшить, так и увеличить количество записей. Проблема в том, что этого просто не всегда ожидаешь((

select
 u.id, 
 u.name,
 u.day,
 d.metric,
 d2.another_metric
from users_on_day u
  join dict d on u.id = d.user_id and u.day = d.day
  join another_dict d2 on u.id = d2.user_id

Вот мы пишем — возьми данные из словаря для юзера на определенное число (или на определенного юзера) и, зачастую, цепочка таких join’ов становится уж очень большой чтобы самостоятельно глазами ее просматривать. Если вам впадлу читать дальше — просто помните это и проверяйте количество после каждого JOIN’а. Руками писать SELECT COUNT(*) после очередного join’а достаточно, чтобы забыть об этой проблеме)) очень просто!

Почему JOIN меняет количество строк

Размножение — это понятно: у юзера два заказа, JOIN orders — и вместо одной строки получаем две.

Но есть менее очевидная штука. Мы джойним какой-то словарь или справочник. По названию интуитивно кажется, что там должны быть все юзеры/транзакции/дни/события и ровно в единственном числе. Но кто нам это гарантирует?)

В реальных системах я часто сталкивался с тем, что внезапно какой-то тип сущности в справочнике отсутствует. Самый банальный пример: есть статус “архивный” или “неактивный”. В нашем запросе такие записи нужны, а автор справочника их не собирал — не от лени, а потому что требований таких не было. Или наоборот, было умышленное требование такие записи не включать.

Итого JOIN может:

  • размножить строки — если справа несколько совпадений на одну запись слева
  • потерять строки — если в справочнике нет записи для нашей сущности (INNER JOIN тихо выкинет, LEFT JOIN оставит с NULL’ами — но вы уверены, что дальше по запросу NULL’ы не сломают логику?)

Более того, некоторые такие кейсы отсутствия значений приходится уточнять у людей, которые глубже понимают систему. Почему что-то где-то присутствует, а где-то — нет. Несовпадение количества, отсутствие сущностей, дубли в таблицах справа — это всё пища для размышлений, которую обязательно нужно поресёрчить руками. Именно там зачастую прячутся неявные кейсы.

Паттерн “Каркас”

Я привык решать это через паттерн который называю “каркас”. Клод подсказал что в dbt-мире очень похожая тема называется “spine” но я не пользуюсь dbt, не шарю.

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

WITH core AS (
    SELECT
        u.user_id,
        d.day
    FROM users u
    CROSS JOIN generate_series(
        CURRENT_DATE - INTERVAL '7 days',
        CURRENT_DATE,
        INTERVAL '1 day'
    ) AS d(day)
)

Теперь core — это наш каркас. 350 юзеров * 8 дней = 2800 строк. Это число не должно меняться до конца запроса.

Добавляем данные — и сразу проверяем

Каждую новую табличку присоединяем отдельным CTE и сразу проверяем количество:

WITH core AS (
    -- каркас: users x days
    ...
),
accounting AS (
    SELECT
        c.*,
        a.amount
    FROM core c
    LEFT JOIN accounting_source a
        ON a.user_id = c.user_id
        AND a.dt = c.day
)
-- Проверка: должно быть 2800
SELECT COUNT(*) FROM accounting;

Получили 3200? Значит, в accounting есть дубли по (user_id, dt). Не идём дальше, пока не починим — либо агрегируем до JOIN, либо добавляем условие.

Как чинить

Вариант 1: агрегировать до JOIN

LEFT JOIN (
    SELECT user_id, dt, SUM(amount) AS amount
    FROM accounting
    GROUP BY user_id, dt 
) a ON a.user_id = c.user_id AND a.dt = c.day

GROUP BY всегда гарантирует уникальность набора полей. Когда смотришь на SQL глазами “где тут дубли?” — CTE с GROUP BY сразу читается как “тут точно уникально”.

Вариант 2: “красивое” решение для гигачадов — пронумеровать оконкой по PK и в случае дубля взять первую попавшуюся

LEFT JOIN (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY user_id, dt) AS rn
    FROM accounting
) a ON a.user_id = c.user_id AND a.dt = c.day AND a.rn = 1

Такие вот, казалось бы, мелочи, могут вносить много импакта. С каждого кто после этого поста заметит странное поведение в данных и похвастается об этом на локальном мите ТРЕБОВАНИЕ притащить в паблик как минимум 10 новых читателей.