Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Неверное значение суммы при использовании left join  [new]
vas8
Member

Откуда:
Сообщений: 15
Имеются 4 таблицы.
tbl1(id_tbl1, f1, f2, f3, ...)
tbl2(id_tbl2, id_tbl1, ...)
tbl3(id_tbl3, id_tbl2, arr)
tbl4(id_tbl4, id_tbl2, acc)

select f1, f2, f3, 
Sum(arr),
Sum(acc) 
from tbl1 as t1
LEFT JOIN tbl2 t2 ON (t1.id_tbl1 = t2.id_tbl1)
LEFT JOIN tbl3 t3 ON (t2.id_tbl2 = t3.id_tbl2)
LEFT JOIN tbl4 t4 ON (t2.id_tbl2 = t4.id_tbl2)
group by f1, f2, f3
order by f1


Sum(arr), Sum(acc) считаются неверно, тк суммируются дублирующиеся значения после выполнения left join. Кто сталкивался с подобным, подскажите как просуммировать правильно?
26 авг 12, 21:14    [13066459]     Ответить | Цитировать Сообщить модератору
 Re: Неверное значение суммы при использовании left join  [new]
locky
Member

Откуда: Харьков, Украина
Сообщений: 62034
vas8,

нафига там tbl3?

зы весьма сомнительно что проблема именно в left join
скорее всего вы неверное себе представляете схему данных
уберите sum/group by и помострите, что в реальности выбирается - вы поймёте из за чего происходит дублирование
26 авг 12, 21:36    [13066533]     Ответить | Цитировать Сообщить модератору
 Re: Неверное значение суммы при использовании left join  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
locky
vas8,

нафига там tbl3?

зы весьма сомнительно что проблема именно в left join
скорее всего вы неверное себе представляете схему данных
уберите sum/group by и помострите, что в реальности выбирается - вы поймёте из за чего происходит дублирование
Дело, конечно же, именно в LEFT JOIN и в отношении 1:M между tbl1 и каждой из остальных таблиц.

Например:
Агрегирование строк в запросе
Вроде тривиальное объединение, но...

Опять то же самое предлагаю:
select f1, f2, f3, 
Sum(arr)/ISNULL(NULLIF(COUNT(DISTINCT t1.id_tbl1),0),1)/ISNULL(NULLIF(COUNT(DISTINCT t2.id_tbl2),0),1)/ISNULL(NULLIF(COUNT(DISTINCT t4.id_tbl4),0),1)
Sum(acc)/ISNULL(NULLIF(COUNT(DISTINCT t1.id_tbl1),0),1)/ISNULL(NULLIF(COUNT(DISTINCT t2.id_tbl2),0),1)/ISNULL(NULLIF(COUNT(DISTINCT t3.id_tbl3),0),1)
from tbl1 as t1
LEFT JOIN tbl2 t2 ON t1.id_tbl1 = t2.id_tbl1
LEFT JOIN tbl3 t3 ON t2.id_tbl2 = t3.id_tbl2
LEFT JOIN tbl4 t4 ON t2.id_tbl2 = t4.id_tbl2
group by f1, f2, f3
order by f1
Хотя есть и другие решения, естественно (см. в поиске по форуму!)
26 авг 12, 21:53    [13066581]     Ответить | Цитировать Сообщить модератору
 Re: Неверное значение суммы при использовании left join  [new]
vas8
Member

Откуда:
Сообщений: 15
locky,
tbl3.arr, tbl4.acc
Из-за того, что в таблицах t2, ,t3, t4 может содержаться более одной записи, получаются все комбинации выбираемых полей из t1,t2,t3 и t4. Уже проверено, что дублирование происходит.
26 авг 12, 21:58    [13066599]     Ответить | Цитировать Сообщить модератору
 Re: Неверное значение суммы при использовании left join  [new]
vas8
Member

Откуда:
Сообщений: 15
iap,

Спасибо!
26 авг 12, 22:01    [13066608]     Ответить | Цитировать Сообщить модератору
 Re: Неверное значение суммы при использовании left join  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
vas8
locky,
tbl3.arr, tbl4.acc
Из-за того, что в таблицах t2, ,t3, t4 может содержаться более одной записи, получаются все комбинации выбираемых полей из t1,t2,t3 и t4. Уже проверено, что дублирование происходит.
Я когда-то не только проверил, а и подсчитал
Году так в 1999 (см. результат выше)

Забыл сказать, предполагается, что tbl1.id_tbl1, tbl2.id_tbl2, tbl3.id_tbl3 и tbl4.id_tbl4 - уникальные ключи
26 авг 12, 22:03    [13066613]     Ответить | Цитировать Сообщить модератору
 Re: Неверное значение суммы при использовании left join  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
vas8
iap,

Спасибо!
Не забывайте алиасы таблиц у полей всегда писать!
26 авг 12, 22:05    [13066616]     Ответить | Цитировать Сообщить модератору
 Re: Неверное значение суммы при использовании left join  [new]
vas8
Member

Откуда:
Сообщений: 15
iap,
возникла проблемка. Алиасы прописал.
incorrect syntax near "acc"
Если убрать строку
Sum(acc)/ISNULL(NULLIF(COUNT(DISTINCT t1.id_tbl1),0),1)/ISNULL(NULLIF(COUNT(DISTINCT t2.id_tbl2),0),1)/ISNULL(NULLIF(COUNT(DISTINCT t3.id_tbl3),0),1)
from tbl1 as t1
то multi-part identifier t2.id_tbl2 could not be bound
27 авг 12, 08:30    [13067460]     Ответить | Цитировать Сообщить модератору
 Re: Неверное значение суммы при использовании left join  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
vas8
iap,
возникла проблемка. Алиасы прописал.
incorrect syntax near "acc"
Если убрать строку
Sum(acc)/ISNULL(NULLIF(COUNT(DISTINCT t1.id_tbl1),0),1)/ISNULL(NULLIF(COUNT(DISTINCT t2.id_tbl2),0),1)/ISNULL(NULLIF(COUNT(DISTINCT t3.id_tbl3),0),1)
from tbl1 as t1
то multi-part identifier t2.id_tbl2 could not be bound
Там запятая куда-то делась.
И надо не acc писать, а t4.acc

Кстати, тщательно проверьте, правильный ли результат получается.
Можно ведь просто коррелированными подзапросами в списке полей SELECT обойтись.
Или OUTER APPLY...
27 авг 12, 08:57    [13067503]     Ответить | Цитировать Сообщить модератору
 Re: Неверное значение суммы при использовании left join  [new]
vas8
Member

Откуда:
Сообщений: 15
iap
Там запятая куда-то делась.
И надо не acc писать, а t4.acc

Кстати, тщательно проверьте, правильный ли результат получается.
Можно ведь просто коррелированными подзапросами в списке полей SELECT обойтись.
Или OUTER APPLY...


Да, прошу прощения, это я с синтаксисом напортачил. А вот результат не тот, который ожидался, правильно обработались лишь случаи, где в таблицах t3 и t4 по одной записи.
27 авг 12, 09:09    [13067522]     Ответить | Цитировать Сообщить модератору
 Re: Неверное значение суммы при использовании left join  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
vas8
iap
Там запятая куда-то делась.
И надо не acc писать, а t4.acc

Кстати, тщательно проверьте, правильный ли результат получается.
Можно ведь просто коррелированными подзапросами в списке полей SELECT обойтись.
Или OUTER APPLY...


Да, прошу прощения, это я с синтаксисом напортачил. А вот результат не тот, который ожидался, правильно обработались лишь случаи, где в таблицах t3 и t4 по одной записи.
А какой результат ожидался?
Надо было скрипт создания таблиц и заполнения их тестовыми данными здесь выложить.
Плюс ожидаемый результат.
Вот я опирался на нечто такое:
SET NOCOUNT ON;

IF OBJECT_ID(N'tempdb..#T1','U') IS NOT NULL DROP TABLE #T1;
IF OBJECT_ID(N'tempdb..#T2','U') IS NOT NULL DROP TABLE #T2;
IF OBJECT_ID(N'tempdb..#T3','U') IS NOT NULL DROP TABLE #T3;
IF OBJECT_ID(N'tempdb..#T4','U') IS NOT NULL DROP TABLE #T4;

CREATE TABLE #T1(ID INT NOT NULL IDENTITY);
CREATE TABLE #T2(ID INT NOT NULL IDENTITY, IDT1 INT);
CREATE TABLE #T3(ID INT NOT NULL IDENTITY, IDT2 INT, V INT);
CREATE TABLE #T4(ID INT NOT NULL IDENTITY, IDT2 INT, V INT);

INSERT #T1 DEFAULT VALUES;
INSERT #T1 DEFAULT VALUES;
INSERT #T1 DEFAULT VALUES;
INSERT #T2(IDT1) VALUES(1),(2),(2);
INSERT #T3(IDT2,V) VALUES(1,2),(2,3),(2,4);
INSERT #T4(IDT2,V) VALUES(1,1),(1,5);

SELECT T1.ID[T1.ID],T2.ID[T2.ID],T3.ID[T3.ID],T3.IDT2[T3.IDT2],T4.ID[T4.ID],T4.IDT2[T4.IDT2],T3.V[T3.V],T4.V[T4.V]
FROM #T1 T1
LEFT JOIN #T2 T2 ON T1.ID=T2.IDT1
LEFT JOIN #T3 T3 ON T2.ID=T3.IDT2
LEFT JOIN #T4 T4 ON T2.ID=T4.IDT2
ORDER BY 1,2,3,4;

SELECT T1.ID[T1.ID],T2.ID[T2.ID]
,SUM(T3.V)/ISNULL(NULLIF(COUNT(DISTINCT T4.ID),0),1)[T3.V]
,SUM(T4.V)/ISNULL(NULLIF(COUNT(DISTINCT T3.ID),0),1)[T4.V]
FROM #T1 T1
LEFT JOIN #T2 T2 ON T1.ID=T2.IDT1
LEFT JOIN #T3 T3 ON T2.ID=T3.IDT2
LEFT JOIN #T4 T4 ON T2.ID=T4.IDT2
GROUP BY T1.ID,T2.ID;

DROP TABLE #T1,#T2,#T3,#T4;
Что тут неправильно, по-Вашему?
27 авг 12, 09:48    [13067624]     Ответить | Цитировать Сообщить модератору
 Re: Неверное значение суммы при использовании left join  [new]
vas8
Member

Откуда:
Сообщений: 15
iap,
не знаю что неправильно, вот какие получились результаты
выборка без group by

t1.idt1	t1.f1	t1.f2  t1.f3   t2.idt2  t3.idt3 t3.idt2  t3.V   t4.idt4 t4.idt2  t4.V  

193258	ВАР	002017	5	NULL	NULL	NULL	NULL	NULL	NULL	NULL
190638	ВАР 	002017	5	NULL	NULL	NULL	NULL	NULL	NULL	NULL
174261	ВАР 	002017	5	66608	64485	66608	50	45891	66608	100.000
174261	ВАР 	002017	5	66608	64971	66608	35	45891	66608	100.000
174818	ВАР 	002017	5	66688	67118	66688	30	46786	66688	30.000
177397	ВАР 	002017	5	NULL	NULL	NULL	NULL	NULL	NULL	NULL
186677	ВАР 	002017	5	72282	69360	72282	50	49403	72282	100.000


после предложенного вами варианта
       

t1.f1                     t1.f2     t1.f3    t3.V       t4.V
ВАР               00201756000200000   501	2	6.111111


t3.V - int, t4.V - numeric(10,3)
27 авг 12, 11:52    [13068340]     Ответить | Цитировать Сообщить модератору
 Re: Неверное значение суммы при использовании left join  [new]
vas8
Member

Откуда:
Сообщений: 15
Ожидаемый результат
t1.f1                   t1.f2     t1.f3    t3.V       t4.V
ВАР             00201756000200000   5	    165	       230

вместо 501 - 5
27 авг 12, 12:02    [13068420]     Ответить | Цитировать Сообщить модератору
 Re: Неверное значение суммы при использовании left join  [new]
aleks2
Guest
iap
Что тут неправильно, по-Вашему?


По нашему тут неправилен подход. Неправильно сначала размножать записи, а потом применять distinct.

Правильно сначала вычислить сумму, а потом прихерачить к ней фсе остальное.
27 авг 12, 12:12    [13068504]     Ответить | Цитировать Сообщить модератору
 Re: Неверное значение суммы при использовании left join  [new]
vas8
Member

Откуда:
Сообщений: 15
vas8
вместо 501 - 5

Я к тому, что на это внимания можно не обращать, не то значение скопировал.
27 авг 12, 12:12    [13068507]     Ответить | Цитировать Сообщить модератору
 Re: Неверное значение суммы при использовании left join  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
aleks2
iap
Что тут неправильно, по-Вашему?


По нашему тут неправилен подход. Неправильно сначала размножать записи, а потом применять distinct.

Правильно сначала вычислить сумму, а потом прихерачить к ней фсе остальное.
Ну, я же словами описал альтернативы...
Как кому нравится. Ни на чём не настаиваю
27 авг 12, 12:35    [13068733]     Ответить | Цитировать Сообщить модератору
 Re: Неверное значение суммы при использовании left join  [new]
vas8
Member

Откуда:
Сообщений: 15
Мне любой вариант годится, главное чтобы правильно считалась сумма.
С использованием подзапросов мне понятен вариант, когда только две связанных таблицы:
select ....,
(select sum(t2.V) from t2 where t2.idt1 = t1.idt1)
from ...
а как быть с тремя, может подскажете?
27 авг 12, 13:26    [13069267]     Ответить | Цитировать Сообщить модератору
 Re: Неверное значение суммы при использовании left join  [new]
aleks2
Guest
vas8
Мне любой вариант годится, главное чтобы правильно считалась сумма.
С использованием подзапросов мне понятен вариант, когда только две связанных таблицы:
select ....,
(select sum(t2.V) from t2 where t2.idt1 = t1.idt1)
from ...
а как быть с тремя, может подскажете?


Конешно подскажем....

Как тока ты разжуешь: нафега в твоем примере JOIN-ы и каков смысл этих таблиц?

vas8
Имеются 4 таблицы.
tbl1(id_tbl1, f1, f2, f3, ...)
tbl2(id_tbl2, id_tbl1, ...)
tbl3(id_tbl3, id_tbl2, arr)
tbl4(id_tbl4, id_tbl2, acc)

select f1, f2, f3, 
Sum(arr),
Sum(acc) 
from tbl1 as t1
LEFT JOIN tbl2 t2 ON (t1.id_tbl1 = t2.id_tbl1)
LEFT JOIN tbl3 t3 ON (t2.id_tbl2 = t3.id_tbl2)
LEFT JOIN tbl4 t4 ON (t2.id_tbl2 = t4.id_tbl2)
group by f1, f2, f3
order by f1


Sum(arr), Sum(acc) считаются неверно, тк суммируются дублирующиеся значения после выполнения left join. Кто сталкивался с подобным, подскажите как просуммировать правильно?
27 авг 12, 13:40    [13069415]     Ответить | Цитировать Сообщить модератору
 Re: Неверное значение суммы при использовании left join  [new]
vas8
Member

Откуда:
Сообщений: 15
aleks2,
я под другим профилем сидел, просто прилогиниться не удалось, вы мне подсказали как другую задачу решить по этой теме

а более подробно


Поставки(id - Код_наименования(int), Код_наимен_архив(int), Номер_задания(varchar), Номер_ПКИ(int), Наименование(varchar), Обозначение(varchar),
Группа_инженера(int), Количество_плановое(numeric),...) 

Поставщики(id - Код_поставщика(int), Код_наименования, Поставщик(nvarchar))

Приход(id - Код_прихода(int), Код_поставщика, Количество_прихода)

Счета(id - Код_счета(int), Код_поставщика, Количество)

Мне необходимо сгруппировать записи из таблицы Поставки по полям Наименование, Обозначение, Группа_инженера и посчитать для каждой записи
сумму по полям Приход.[Количество_прихода] и Счета.[Количество].
27 авг 12, 14:02    [13069624]     Ответить | Цитировать Сообщить модератору
 Re: Неверное значение суммы при использовании left join  [new]
aleks2
Guest
Да, бред...

-- позаботимся, шоб "не размножались"
;with
   ГруппСчета  as (select Код_поставщика, sum(Количество) Количество from Счета group by Код_поставщика)
 , ГруппПриход as (select Код_поставщика, sum(Количество_прихода) Количество from Приход group by Код_поставщика)

select P.*, sum(GS.Количество)...
from Поставки P 
     left outer join Поставщики PP on p.Код_наименования = PP.Код_наименования
     left outer join ГруппСчета GS on GS.Код_поставщика = PP.Код_поставщика
     left outer join ГруппПриход GP on GP.Код_поставщика = PP.Код_поставщика
group by
Наименование, Обозначение, Группа_инженера 

-- здесь есть допущения
-- 1. Поставки(Код_наименования) - уникален.
-- 2. Поставщики(Код_поставщика(int), Код_наименования) содержит однозначную связь Код_наименования <-> Код_поставщика.
27 авг 12, 14:28    [13069803]     Ответить | Цитировать Сообщить модератору
 Re: Неверное значение суммы при использовании left join  [new]
vas8
Member

Откуда:
Сообщений: 15
aleks2,
Все идеально! Спасибо, в очередной раз выручили
27 авг 12, 14:59    [13070083]     Ответить | Цитировать Сообщить модератору
 Re: Неверное значение суммы при использовании left join  [new]
vas8
Member

Откуда:
Сообщений: 15
aleks2,
А почему бред?( Если что, то этот вопрос не имеет отношения к вопросу про триггер, который я задавал в той теме.)
28 авг 12, 10:33    [13074051]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить