Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Firebird, InterBase Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Прошу помощи с JOIN  [new]
Rom1
Member

Откуда:
Сообщений: 33
Есть таблица идентификаторов товаров:
[GOODS]
ID INTEGER;

Содержание:
ID = 123

Есть три таблицы, где есть расход по этим товарам:
[USAGE1]
GOODSID INTEGER;
AMOUNT NUMERIC(10,2);

Содержание:
GOODSID = 123; AMOUNT = 101

[USAGE2]
GOODSID INTEGER;
AMOUNT NUMERIC(10,2);

Содержание:
GOODSID = 123; ABOUNT = 201
GOODSID = 123; ABOUNT = 202

[USAGE3]
GOODSID INTEGER;
AMOUNT NUMERIC(10,2);

Содержание:
GOODSID = 123; ABOUNT = 301
GOODSID = 123; ABOUNT = 302
GOODSID = 123; ABOUNT = 301


Нужно одним запросом посчитать суммы по всем трем таблицам:
select G.ID, sum(U1.AMOUNT) SUM1, sum(U2.AMOUNT) SUM2, sum(U3.AMOUNT) SUM3
from GOODS1 G
left outer join USAGE1 U1 on U1.GOODSID = G.ID
left outer join USAGE2 U2 on U2.GOODSID = G.ID
left outer join USAGE3 U3 on U3.GOODSID = G.ID
group by G.ID

Получаю это:
ID	SUM1	SUM2	SUM3
123 606 1209 1812

Ну и без группировки это выглядит так:
ID	SUM1	SUM2	SUM3
123 101 201 301
123 101 201 302
123 101 201 303
123 101 202 301
123 101 202 302
123 101 202 303

Какой JOIN мне нужно использовать чтобы данные не дублировались?
Сейчас делаю три отдельных запроса, но это очень некрасиво.
7 окт 21, 08:25    [22380395]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с JOIN  [new]
rdb_dev
Member

Откуда: с болот
Сообщений: 3812
Rom1, тебе нужен не JOIN, а UNION из SELECT'ов сумм, если хочешь суммы построчно. Если хочешь суммы по столбцам, то нужен один SELECT, у которого значение каждого поля суммы представлено результатом суммирующего SELECT'а.

Сообщение было отредактировано: 7 окт 21, 08:59
7 окт 21, 09:06    [22380409]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с JOIN  [new]
wadman
Member

Откуда: Санкт-Петербург
Сообщений: 27315
Rom1
GOODSID = 123; ABOUNT = 301
GOODSID = 123; ABOUNT = 302
GOODSID = 123; ABOUNT = 301

rdb_dev
тебе нужен не JOIN, а UNION из SELECT'ов сумм

Надеюсь, понимаешь разницу между UNION и UNION ALL?
7 окт 21, 11:06    [22380514]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с JOIN  [new]
rdb_dev
Member

Откуда: с болот
Сообщений: 3812
wadman, ALL, это опция UNION. Возьмёт Reference Manual и разберётся.
7 окт 21, 12:56    [22380606]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с JOIN  [new]
Мимопроходящий
Member

Откуда: бурятский тундрюк, эсквайр
Сообщений: 32896

не разберётся.
это тестовая задача.
дорога' ложка к обеду.

Posted via ActualForum NNTP Server 1.5

7 окт 21, 12:59    [22380613]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с JOIN  [new]
rdb_dev
Member

Откуда: с болот
Сообщений: 3812
Мимопроходящий, значит в этот раз повезёт тестирующему, а не тестируемому.
Знавал я одного ребятёнка, который после ВУЗа с дипломом программиста пытался устроиться в Корус вообще без знаний, что такое СУБД и чем её едят. Корусу повезло, ребятёнку нет.

Сообщение было отредактировано: 7 окт 21, 12:59
7 окт 21, 13:07    [22380631]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с JOIN  [new]
Rom1
Member

Откуда:
Сообщений: 33
rdb_dev
Rom1, тебе нужен не JOIN, а UNION.

Но ведь UNION создаёт новую временную таблицу (в MySQL, в Firebrd не знаю), это же даст отрицательный результат производительности?
7 окт 21, 16:26    [22380783]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с JOIN  [new]
Rom1
Member

Откуда:
Сообщений: 33
Мимопроходящий

не разберётся.
это тестовая задача.
дорога' ложка к обеду.

Молодой человек, с чего вы взяли что я студент?
7 окт 21, 16:27    [22380784]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с JOIN  [new]
Симонов Денис
Member

Откуда: Рязань
Сообщений: 11092
Rom1,

не создаёт он никаких временных таблиц, уж в ФБ точно.
UNION в отличие от UNION ALL требует сортировку для удаления дубликатов и вот тут есть потеря производительности.
7 окт 21, 16:34    [22380791]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с JOIN  [new]
wadman
Member

Откуда: Санкт-Петербург
Сообщений: 27315
Симонов Денис
UNION в отличие от UNION ALL требует сортировку для удаления дубликатов и вот тут есть потеря производительности.

Еще хуже, когда плюс потеря данных в некоторых случаях, как выше.
7 окт 21, 16:35    [22380793]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с JOIN  [new]
Rom1
Member

Откуда:
Сообщений: 33
Хорошо, если я вынесу (вынес) все эти "три" запроса в процедуру (ранее выполнял их на клиенте), а процедура выдаёт агрегированный результат, насколько это может отличаться по производительности по сравнению с UNION?
7 окт 21, 16:38    [22380796]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с JOIN  [new]
KreatorXXI
Member

Откуда: Москва
Сообщений: 1119
Rom1,

а чем вот такой вариант не подходит?
select G.ID, 
         ( select sum(U.AMOUNT)
           from USAGE1 U
           where U.GOODSID = G.ID
         ),
         ( select sum(U.AMOUNT)
           from USAGE2 U
           where U.GOODSID = G.ID
         ),
         ( select sum(U.AMOUNT)
           from USAGE3 U
           where U.GOODSID = G.ID
         )
from GOODS1 G
7 окт 21, 16:53    [22380806]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с JOIN  [new]
Rom1
Member

Откуда:
Сообщений: 33
KreatorXXI
Rom1,

а чем вот такой вариант не подходит?
select G.ID, 
         ( select sum(U.AMOUNT)
           from USAGE1 U
           where U.GOODSID = G.ID
         ),
         ( select sum(U.AMOUNT)
           from USAGE2 U
           where U.GOODSID = G.ID
         ),
         ( select sum(U.AMOUNT)
           from USAGE3 U
           where U.GOODSID = G.ID
         )
from GOODS1 G


Да, сам уже дошел до такого:
select G.ID, U.AMOUNT SUM1, null SUM2, null SUM3
from GOODS1 G
left outer join USAGE1 U on U.ID = G.ID

union all

select G.ID, null, U.AMOUNT, null
from GOODS1 G
left outer join USAGE2 U on U.ID = G.ID

union all

select G.ID, null, null, U.AMOUNT
from GOODS1 G
left outer join USAGE3 U on U.ID = G.ID


Результат:
ID	SUM1	SUM2	SUM3
123 101 null null
123 null 201 null
123 null 202 null
123 null null 301
123 null null 302
123 null null 303

Честно говоря, если встречу такой запрос в коде, решу что его писал наркоман.

"from GOODS" у меня не просто таблица, а ресурсоёмкий запрос по выборке товаров, по которым надо сделать такую агрегацию, поэтому сделать его хочу один раз за запрос. А вот трижды делать его - полный провал.
Как и в вашем примере, если гудсов тысяча, то запросов будет три тысячи, если я правильно понимаю, а это тоже фейл.
7 окт 21, 17:03    [22380815]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с JOIN  [new]
Dimitry Sibiryakov
Member

Откуда:
Сообщений: 54807

Rom1
с чего вы взяли что я студент?

Слишком кривая схема данных, слишком странное требование "одним запросом".

Posted via ActualForum NNTP Server 1.5

7 окт 21, 17:10    [22380818]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с JOIN  [new]
Симонов Денис
Member

Откуда: Рязань
Сообщений: 11092
Rom1,

причём тут GOODS1, если в запросе KreatorXXI он вычитывается ровно один раз. Это к его результатам добавляются результаты 3-х подзапросов
7 окт 21, 17:11    [22380820]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с JOIN  [new]
wadman
Member

Откуда: Санкт-Петербург
Сообщений: 27315
Rom1,

можно и таким образом сделать:

select G.ID, (select sum(amount) from USAGE1 where GOODSID = G.ID) sum1
from GOODS1 G
7 окт 21, 17:14    [22380821]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с JOIN  [new]
Rom1
Member

Откуда:
Сообщений: 33
Dimitry Sibiryakov

Слишком кривая схема данных, слишком странное требование "одним запросом".

Ничего кривого не вижу.
Требование простое "отдать всю работу с клиента на сервер", в идеале одним запросом.

Симонов Денис
Rom1,
причём тут GOODS1, если в запросе KreatorXXI он вычитывается ровно один раз. Это к его результатам добавляются результаты 3-х подзапросов

Я не знаю как СУБД устроена внутри, возможно будет основной запрос товаров, после идентификаторы сериализуются и передаются в те три запроса, в результате имеем четыре запроса. Может быть и так, я не знаю, пусть эксперты подтвердят.
Лично я смотрю "в лоб" и вижу в этом примере, что на каждый товар по три запроса, 1000 товаром, 3000 запросов. Но может это и не так, я же написал - "насколько понимаю".

wadman
Rom1,
можно и таким образом сделать:
[/src]

KreatorXXI уже привел подобный пример
7 окт 21, 17:25    [22380825]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с JOIN  [new]
Dimitry Sibiryakov
Member

Откуда:
Сообщений: 54807

Rom1
Ничего кривого не вижу.

Три разные таблицы с общим назначением "расход" и совершенно одинаковой
структурой. Так БД обычно не делают.

Rom1
Требование простое "отдать всю работу с клиента на сервер", в идеале
одним запросом.

Вот этот "идеал" - как раз студенческий уровень. Три простых отдельных запроса
часто быстрее одного хитроподвыподвертнутого.

Posted via ActualForum NNTP Server 1.5

7 окт 21, 17:47    [22380839]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с JOIN  [new]
shalamyansky
Member

Откуда:
Сообщений: 290
У дубликатов значений полей в результирующем наборе видны 2 причины:

1. Таковы данные, как мы видим в топике (похоже, ошибочные, но весьма показательные)

GOODSID = 123; ABOUNT = 301
GOODSID = 123; ABOUNT = 302
GOODSID = 123; ABOUNT = 301
Значение ABOUNT=301 повторяется два раза.

2. Размножение в результате слияний по JOIN OUTER.

Задача, позволю себе домыслить за автора, состоит в удалении дубликатов по второй причине при сохранении таковых по причине 1.

Любой инструмент, действующий на результирующий набор, будь то UNION ALL или SUM( U3.AMOUNT distinct ) или еще подобный, удалит дубликаты независимо от причины, и, значит, не решит задачу.

Посему пока что видны 2 направления в сторону решения:

а) Принять как постулат, что исходные данные не могут повторяться, и применить к выходному набору вышеуказанные инструменты.

б) Писать отдельные запросы (хотя бы в виде подзапросов) по каждому полю.

Сугубо ИМХО.
7 окт 21, 18:16    [22380855]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с JOIN  [new]
Rom1
Member

Откуда:
Сообщений: 33
shalamyansky
У дубликатов значений полей в результирующем наборе видны 2 причины:
1. Таковы данные, как мы видим в топике (похоже, ошибочные, но весьма показательные)

GOODSID = 123; ABOUNT = 301
GOODSID = 123; ABOUNT = 302
GOODSID = 123; ABOUNT = 301
Значение ABOUNT=301 повторяется два раза.
Это синтаксическая ошибка в топике, тут синтетические данные же. Но чтобы не быть голословным при написании вопроса, создал все базы таблицы (тут по ошибке GOODS превратилась в GOODS1), но наборы данных уже корректные, я вывод вставлял настоящий.

Сообщение было отредактировано: 7 окт 21, 18:19
7 окт 21, 18:28    [22380876]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с JOIN  [new]
shalamyansky
Member

Откуда:
Сообщений: 290
Значит, вам подходит решение (а). Добавьте слово distinct в агрегатную функцию, и довольно будет.
7 окт 21, 18:34    [22380880]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с JOIN  [new]
Dimitry Sibiryakov
Member

Откуда:
Сообщений: 54807

Rom1
Но чтобы не быть голословным при написании вопроса, создал все базы таблицы

Чтобы не быть голословным это надо было делать в
https://dbfiddle.uk/?rdbms=firebird_3.0 а сюда постить линк на уже готовые данные.

Posted via ActualForum NNTP Server 1.5

7 окт 21, 18:34    [22380881]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с JOIN  [new]
m7m
Member

Откуда: Украина, Мариуполь
Сообщений: 1460
Rom1
Dimitry Sibiryakov

Слишком кривая схема данных, слишком странное требование "одним запросом".

Ничего кривого не вижу.
Требование простое "отдать всю работу с клиента на сервер", в идеале одним запросом.

Симонов Денис
Rom1,
причём тут GOODS1, если в запросе KreatorXXI он вычитывается ровно один раз. Это к его результатам добавляются результаты 3-х подзапросов

Я не знаю как СУБД устроена внутри, возможно будет основной запрос товаров, после идентификаторы сериализуются и передаются в те три запроса, в результате имеем четыре запроса. Может быть и так, я не знаю, пусть эксперты подтвердят.
Лично я смотрю "в лоб" и вижу в этом примере, что на каждый товар по три запроса, 1000 товаром, 3000 запросов. Но может это и не так, я же написал - "насколько понимаю".

и чего это тебя смущает, проверь и посмотри планы и время выполнения
и пользы больше и времени потраченного немного
А уж если время выполнения не устроит то и приходи, помогут тут
7 окт 21, 18:36    [22380884]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с JOIN  [new]
KreatorXXI
Member

Откуда: Москва
Сообщений: 1119
Rom1

Да, сам уже дошел до такого:


Где же дошёл? Union all скорее всего будет невыгодным в Вашем случае. Если как у меня не получается, то есть "Select from select", есть CTE.
8 окт 21, 09:53    [22381058]     Ответить | Цитировать Сообщить модератору
 Re: Прошу помощи с JOIN  [new]
Rom1
Member

Откуда:
Сообщений: 33
KreatorXXI

Где же дошёл?есть CTE.

Я имел ввиду "докатился" в опытах.

Сообщение было отредактировано: 8 окт 21, 16:13
8 окт 21, 16:23    [22381295]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Firebird, InterBase Ответить