Группировка SQL возвращает неправильное (завышенное) агрегированное значение для столбца
У меня есть 3 таблицы: TABLE1, TABLE2 и TABLE3.
TABLE1 и TABLE2 имеют по 1 строке, а TABLE3 — 3 строки. И TABLE2, и TABLE3 имеют внешний ключ к TABLE1.
Я вижу, что если я объединим все три из них, а затем сгруппируем результат, то значение в TABLE2 будет показано в 3 раза больше реального значения. Может кто-нибудь объяснить причину?
Я попробовал это:
declare @TABLE1 table (table1_id int);
declare @TABLE2 table (table2_id int, table1_id int, earning money);
declare @TABLE3 table (table3_id int, table1_id int);
insert into @TABLE1 values (1);
insert into @TABLE2 values (49, 1, 10000);
insert into @TABLE3 values (991, 1);
insert into @TABLE3 values (992, 1);
insert into @TABLE3 values (993, 1);
select
T1.table1_id, SUM(T2.earning) 'earning'
from
@TABLE1 T1
inner join
@TABLE2 T2 on T2.table1_id = T1.table1_id
inner join
@TABLE3 T3 on T3.table1_id = T1.table1_id
group by
T1.table1_id;
Когда я запускаю этот запрос, он возвращает 1 строку с двумя столбцами (table1_id и earning).
Я ожидал, что значение столбца «Заработок» будет 10 000,00, но я получил 30 000,00. Какова причина?
Удалите группу и проверьте результаты. Присоединение дочерней таблицы с 3 строками создает, к удивлению, 3 строки.
Ваш пример не объясняет, зачем вам вообще нужна таблица 3... поэтому ее включение прояснит, чего вы пытаетесь достичь.
Судя по всему, ваш запрос включает в себя несколько независимых соединений «один ко многим», что является распространенным источником дублирования данных и завышенных агрегированных результатов. Вам необходимо разделить один или оба запроса на подзапросы, CTE или cross apply.
Спасибо, ребята, за ваш ответ. Имеет смысл. Для достижения моей цели мне придется использовать подзапросы, как рекомендует @TN.
Какова цель использования Table3 в этом запросе?
@JoelCoehoorn - Я ожидаю, что реальный запрос, над которым работает ОП, гораздо более сложен, и ссылка на Таблицу 3 была необходимой частью после того, как ОП проделал хорошую работу, сводя проблему к минимально воспроизводимому примеру.
Ответы 1
Судя по всему, ваш запрос включает в себя несколько независимых соединений «один ко многим», что является распространенным источником дублирования данных и завышенных агрегированных результатов. Вам необходимо разделить один или оба запроса на подзапросы, CTE или CROSS APPLY.
Исправлено с помощью CROSS APPLY:
select
T1.table1_id, CA1.Earning, CA2.T3Count
from
TABLE1 T1
cross apply (
select SUM(earning) AS Earning
from TABLE2 T2
where T2.table1_id = T1.table1_id
) CA1
cross apply (
select COUNT(*) AS T3Count
from TABLE3 T3
where T3.table1_id = T1.table1_id
) CA2
Исправлено использование подзапросов:
select
T1.table1_id, SS1.Earning, SS2.T3Count
from
TABLE1 T1
inner join (
select T2.table1_id, SUM(earning) AS Earning
from TABLE2 T2
group by T2.table1_id
) SS1 on SS1.table1_id = T1.table1_id
inner join (
select T3.table1_id, COUNT(*) AS T3Count
from TABLE3 T3
group by T3.table1_id
) SS2 on SS2.table1_id = T1.table1_id
Я добавил столбец T3Count, чтобы придать ссылке TABLE3 некоторое значение.
Неисправленные результаты (с некоторыми дополнительными данными испытаний):
Исправленные результаты:
См. эту db<>fiddle для демонстрации с некоторыми дополнительными тестовыми данными.
В некоторых ситуациях, когда объединенные данные могут не иметь совпадений, вам может потребоваться изменить cross apply на outer apply, а inner join на left join.
Обратите внимание: хотя CROSS APPLY обычно дает вам стиль внутреннего соединения, в этом случае вы используете скалярный агрегат (нет GROUP BY), поэтому получается левое соединение, поскольку скалярные агрегаты всегда возвращают ровно одну строку. Используйте GROUP BY () или GROUP BY TheCorrelationColumn внутри подзапроса, чтобы получить внутреннее соединение. См. sql.kiwi/2012/03/fun-with-aggregates.html
Хорошая точка зрения. Да, это верно для вышеупомянутого использования. Я думал об этом, когда писал статью, но все равно решил упомянуть об этом (не вдаваясь в подробности) на случай, если реальный реальный запрос может включать в себя что-то иное, чем скалярное агрегирование.
Мне кажется, это правильно. В неагрегированном виде у вас есть 3 строки (db<>fiddle) со значением 10 000 каждая, а
SUMиз 310000значений равно30000. Почему вы ожидаете иного?