Как объединить две таблицы истории для одного пользователя без связей

При разработке системы аудита изменений часто возникает ситуация, когда данные об изменении разных полей одной записи сохраняются в отдельные таблицы без внешних ключей и связей между ними. В вашем случае при изменении пользователя в таблице users создаются записи в двух таблицах истории: names (история изменения имени) и birthdays (история изменения даты рождения). Каждый раз добавляется новая строка в обе таблицы, даже если значение поля не изменилось. Задача - получить выборку для конкретного user_id, где количество строк равно количеству записей в любой из этих таблиц для данного пользователя, и строки сгруппированы по порядку (первая запись из names с первой записью из birthdays и так далее).

Проблема синхронизации по времени

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

Решение: нумерация строк с помощью ROW_NUMBER

Наиболее эффективный способ - присвоить каждой записи в каждой таблице порядковый номер (ROW_NUMBER) в рамках одного user_id, отсортировав по первичному ключу (или по дате создания, если она есть). Затем объединить две таблицы по этому номеру. Ниже приведён пример SQL-запроса для PostgreSQL:

WITH numbered_names AS (
    SELECT 
        id,
        name,
        user_id,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY id) AS rn
    FROM names
    WHERE user_id = 1
),
numbered_birthdays AS (
    SELECT 
        id,
        birthday,
        user_id,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY id) AS rn
    FROM birthdays
    WHERE user_id = 1
)
SELECT 
    COALESCE(n.user_id, b.user_id) AS user_id,
    n.id AS name_id,
    n.name,
    b.id AS birthday_id,
    b.birthday
FROM numbered_names n
FULL OUTER JOIN numbered_birthdays b ON n.rn = b.rn
ORDER BY COALESCE(n.rn, b.rn);

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

• ROW_NUMBER присваивает каждой строке уникальный номер в порядке возрастания id (или другого поля, отражающего хронологию).
• FULL OUTER JOIN гарантирует, что строки из обеих таблиц будут включены, даже если в одной таблице записей больше, чем в другой.
• COALESCE используется для выбора user_id из той таблицы, где он не NULL.

Альтернативные подходы

Если в вашей СУБД нет поддержки оконных функций (например, старая версия MySQL), можно использовать пользовательские переменные или нумерацию через подзапросы. Однако ROW_NUMBER - самый лаконичный и производительный вариант.

Важные замечания

• Убедитесь, что в таблицах истории есть поле, которое можно использовать для сортировки (например, автоинкрементный id или created_at).
• Если записи добавляются в транзакции одновременно, порядок может быть непредсказуемым - используйте монотонно возрастающий идентификатор.
• Данный метод не требует создания дополнительных таблиц (pivot) и работает без циклов на стороне приложения.

Заключение

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