Совместное использование GROUP BY и JOIN в MySQL - как объединить таблицы после группировки

Что выполняется раньше - JOIN или GROUP BY? Этот неявный вопрос не возникал у меня до тех пор, пока ответ на него не всплыл в виде ошибочных данных, возвращаемых моим запросом. Разумеется, JOIN выполняется раньше, он и в запросах пишется до GROUP BY. Но что, если мне нужно объединить таблицы уже после группировки? Рассмотрим простой пример, когда это может потребоваться.

Представим себе не слишком удобную, но очень простую систему бухгалтерии, состоящую из двух таблиц: Income - таблица доходов и Outlay - таблица расходов. В обеих таблицах по 3 поля: id (INT), time (DATETIME), sum (INT):

# Income (доходы)                            # Outlay (расходы)
id     time                    sum           id     time                    sum
1      2014-01-01 00:00:00     100           1      2014-01-02 00:00:00     100
2      2014-01-01 23:59:59     100           2      2014-01-02 23:59:59     100
3      2014-01-02 00:00:00     500

Как видите, 1 января у нас было две продажи по 100 рублей, а 2 января - продажа на 500 и два расхода по 100 рублей.

Объединяем, агрегируем, группируем

Вообразим, что перед нами стоит задача вывести таблицу ежедневных доходов. Всё просто, мы должны сложить суммы, выполнив группировку по дате:


# Суммируем доходы и группируем их по дням
SELECT DATE(`income`.`time`) `date`, SUM(`income`.`sum`) `inSum`
    FROM `Income` `income`
    GROUP BY DATE(`income`.`time`);

# Результат выполнения - сумма доходов по дням
date           inSum
2014-01-01     200
2014-01-02     500

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


# Объединяем таблицы доходов и расходов по дате, суммируем доходы и расходы, группируя по дням
# Так писать НЕ НАДО:
SELECT DATE(`income`.`time`) `date`, SUM(`income`.`sum`) `inSum`, SUM(`outlay`.`sum`) `outSum`
    FROM `Income` `income`
        LEFT JOIN `Outlay` `outlay` ON DATE(`income`.`time`) = DATE(`outlay`.`time`)
    GROUP BY DATE(`income`.`time`);

# Ёлки-палки! Прибыль на 2 января увеличилась в 2 раза!
date           inSum     outSum
2014-01-01     200       NULL
2014-01-02     1000(!)   200

Как видите, в ответе вернулся абсолютный ужас - доходы на 2 января увеличились в 2 раза. Как уже было отмечено в начале статьи, JOIN выполняется до GROUP BY, соответственно, из-за того, что 2 января у нас было две статьи расходов, статьи доходов продублировались при объединении таблиц, что привело к задвоению доходов при суммировании. Логика подсказывает, что для исправления проблемы мы должны объединять таблицы, уже сгруппированные по дате.

Ранняя группировка при объединении таблиц MySQL при помощи вложенного запроса

В пределах одного запроса мы не можем сперва сгруппировать, а затем связать таблицы, но мы можем осуществить отложенное объединение с результатом вложенного запроса, а группировку выполнить заранее в нём. Делается это так:


# Заранее группируем данные в таблице Outlay, затем производим объединение с таблицей Income
SELECT DATE(`income`.`time`) `date`, SUM(`income`.`sum`) `inSum`, `outlayGroupped`.`outSum`
    FROM `Income` `income`
        LEFT JOIN (
            # Наш вложенный запрос с группировкой
            SELECT SUM(`outlay`.`sum`) `outSum`, DATE(`outlay`.`time`) `outDate`
                FROM `Outlay` `outlay`
                GROUP BY DATE(`outlay`.`time`)
        ) `outlayGroupped` ON DATE(`income`.`time`) = `outlayGroupped`.`outDate`
    GROUP BY DATE(`income`.`time`);

# Прибыли конечно меньше, чем в прошлый раз, но зато цифры правильные :)
date           inSum     outSum
2014-01-01     200       NULL
2014-01-02     500       200

Обычно такие неявные проблемы требуют столь же неявных решений в двух случаях - когда не совсем корректно поставлена задача или когда архитектура проекта оставляет желать лучшего. В данном случае конечно структура базы данных притянута за уши ради примера.

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

22.09.2014