ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / хитрый join и group by
17 сообщений из 17, страница 1 из 1
хитрый join и group by
    #39177987
Фотография Legushka
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Legushka
Участник
можно ли построить join так что бы не используя group by в with b as ... и в with c получить результат не 1, 12, 9 а 1, 6 и 3
хочу начать с неправильного запроса:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
with a as (select 1 as a),
b as (select 1 as a, 1 b 
union select 1 as a, 2 b 
union select 1 as a, 3 b 
),
c as (select 1 as a, 1 c 
union select 1 as a, 2
)
select a.a, sum(b), sum(c) from a 
left join b on a.a=b.a 
left join c on a.a=c.a
group by a.a

который дает неправильный результат такой:aсумма bсумма С1129
видно что в B задвоилось на количество строк в С
и в С задвоилось на количество строк в Б

НАДО что бы получился правильный результат без задвоений
aсумма bсумма С163
пример:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
with a as (select 1 as a),
b as (
	select a, sum(b) as b 
	from (
		select 1 as a, 1 b 
		union select 1 as a, 2 b 
		union select 1 as a, 3 b 
	     )b
	group by a
),
c as (
	select a, sum(c) as c
	from (
		select 1 as a, 1 c 
		union select 1 as a, 2
	     )c
	group by a
)
select a.a, sum(b), sum(c) from a 
left join b on a.a=b.a 
left join c on a.a=c.a
group by a.a



но в очень большом примере каждый лишний group by съедает много actual time (таблицы очень большие)

может быть есть такой вариант join что бы группировка и агрегатные вычисления делались только 1 раз и дали правильный результат?
...
Рейтинг: 0 / 0
24.02.2016, 17:11
| Ответить | Цитировать | Написать  
хитрый join и group by
    #39178016
Alexius
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alexius
Участник
Legushka,

подзапросы никто не отменял:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
with a as (select 1 as a),
b as (select 1 as a, 1 b 
union select 1 as a, 2 b 
union select 1 as a, 3 b 
),
c as (select 1 as a, 1 c 
union select 1 as a, 2
)
select a.a, (select sum(b) from b where b.a = a.a), (select sum(c) from c where c.a = a.a)
from a;
...
Рейтинг: 0 / 0
24.02.2016, 17:41
| Ответить | Цитировать | Написать  
хитрый join и group by
    #39178316
Фотография Legushka
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Legushka
Участник
Alexius, решение гуд, но хочется узнать есть ли решение на джоинах?
...
Рейтинг: 0 / 0
25.02.2016, 08:02
| Ответить | Цитировать | Написать  
хитрый join и group by
    #39178380
Фотография Legushka
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Legushka
Участник
пробовал через подзапросы, в итоге даже намного дольше стало, решил вернуться к первоначальному и еще раз проанализировать почему я хотел уйти от группировок

может быть дело не в запросе а в выделенной памяти?, как можно посмотреть сколько памяти отведено для работы со временными таблицами?

часть запроса и план:

есть временная таблица wpc_per где куча полей с ИД и суммовых полей
и есть wpc_per_sum которая на основе собирает суммы в разрезе одного параметра
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
with 
Woperation as (select operation_id from operation where ....),
wpc_per AS (
         SELECT pa_1.personal_account_id,
            pa_1.personal_record_id,
            pa_1.personal_record_order_id,
            pcp_1.calculation_per_id,
            pcp_1.sum1,
            pcp_1.sum2,
            pcp_1.sum3,
            pcp_1.sum4
           FROM Woperation pa_1
             JOIN calculation pc ON pc.operation_id = pa_1.operation_id
             JOIN calculation_per pcp_1 ON pcp_1.calculation_id = pc.calculation_id
        ), wpc_per_sum AS (
         SELECT pcp_1.personal_account_id,
            sum(pcp_1.sum1) AS sum1,
            sum(pcp_1.sum2) AS sum2,
            sum(pcp_1.sum3) AS sum3,
            sum(pcp_1.sum4) AS sum4
           FROM wpc_per pcp_1
          GROUP BY pcp_1.personal_account_id
        ),

  CTE wpc_per
    ->  Hash Join  (cost=62851.34..113431.18 rows=1244880 width=77) (actual time=7338.802..10511.509 rows=658452 loops=1)
          Hash Cond: (pa_1_2.operation_id = pc.operation_id)
          ->  CTE Scan on Woperation pa_1_2  (cost=0.00..14641.42 rows=732071 width=32) (actual time=3114.840..4681.331 rows=387214 loops=1)
          ->  Hash  (cost=47546.69..47546.69 rows=658452 width=61) (actual time=4214.726..4214.726 rows=658452 loops=1)
                Buckets: 2048  Batches: 64  Memory Usage: 638kB
                ->  Merge Join  (cost=1.00..47546.69 rows=658452 width=61) (actual time=0.159..3294.815 rows=658452 loops=1)
                      Merge Cond: (pc.calculation_id = pcp_1.calculation_id)
                      ->  Index Scan using pk_c on calculation pc  (cost=0.42..14250.88 rows=387213 width=16) (actual time=0.083..770.819 rows=387208 loops=1)
                      ->  Index Scan using ix_c_per on calculation_per pcp_1  (cost=0.42..24097.71 rows=658452 width=61) (actual time=0.059..1125.851 rows=658452 loops=1)
  CTE wpc_per_sum
    ->  HashAggregate  (cost=43570.80..43572.80 rows=200 width=108) (actual time=13485.463..13577.981 rows=58779 loops=1)
          ->  CTE Scan on wpc_per pcp_1_1  (cost=0.00..24897.60 rows=1244880 width=108) (actual time=7338.822..11868.782 rows=658452 loops=1)



по плану видно что группировка на основе полученных данных выполняется даже больше чем получение данных
может быть памяти мало?
...
Рейтинг: 0 / 0
25.02.2016, 09:53
| Ответить | Цитировать | Написать  
хитрый join и group by
    #39178404
p2.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
p2.
Участник
Legushka,

много лишних букв. за лесом сложно разгледеть, что собствнно, нужно.
джоин двух подзапоосов с груп бай?
...
Рейтинг: 0 / 0
25.02.2016, 10:21
| Ответить | Цитировать | Написать  
хитрый join и group by
    #39178418
Фотография Legushka
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Legushka
Участник
p2.,

понять почему очень долго выполняется запрос где сперва во временную таблицу накидали значений
а потом создаем еще одну временную таблицу куда кидаем группировку и суммы на основании первой временной таблицы

и эта вторая таблица в итоге работает в 2 раза дольше чем формирование первой? что можно сделать?
...
Рейтинг: 0 / 0
25.02.2016, 10:35
| Ответить | Цитировать | Написать  
хитрый join и group by
    #39178420
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
Legushka,

SQL исполняется в определённой последовательности, которая совпадает с порядком следования основных слов в синтаксисе:
Код: plaintext
1.
2.
3.
4.
5.
  FROM ...
 WHERE
 GROUP BY
HAVING
 UNION
 ORDER BY

Таблицы сначала связываются, потом результат группруется. Вот данные после связки:
Код: sql
1.
2.
3.
4.
5.
6.
7.
WITH a(a)   AS (SELECT 1),
     b(a,b) AS (VALUES(1,1),(1,2),(1,3)),
     c(a,c) AS (VALUES(1,1),(1,2))
SELECT a.a, b, c
  FROM a 
  LEFT JOIN b ON a.a=b.a 
  LEFT JOIN c ON a.a=c.a;


Как видно, дубликаты появляются по результатам связывания таблиц и избавиться от них только связками не выйдет.
Тут можно только один агрегат засунуть в подзапрос, скажем в `c`, возможно станет быстрее:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
WITH a(a)   AS (SELECT 1),
     b(a,b) AS (VALUES(1,1),(1,2),(1,3)),
     c(a,c) AS (VALUES(1,1),(1,2))
SELECT a.a, sum(b), sum(c)
  FROM a 
  LEFT JOIN b ON a.a=b.a 
  LEFT JOIN (SELECT a, sum(c) c FROM c GROUP BY a) c ON a.a=c.a
 GROUP BY a.a;



В приведённом плане большую часть времени занимает плучение данных, т.к. начало группировки начинается через 13.4 секунды:
Код: sql
1.
2.
...
    ->  HashAggregate  (cost=43570.80..43572.80 rows=200 width=108) (actual time=13485.463..13577.981 rows=58779 loops=1)
...
Рейтинг: 0 / 0
25.02.2016, 10:36
| Ответить | Цитировать | Написать  
хитрый join и group by
    #39178457
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
vyegorovLegushka,
<>
В приведённом плане большую часть времени занимает плучение данных, т.к. начало группировки начинается через 13.4 секунды:
 
Код: sql
1.
2.
...
    ->  HashAggregate  (cost=43570.80..43572.80 rows=200 width=108) (actual time=13485.463..13577.981 rows=58779 loops=1)


как мне недавно объяснили, возможно даже вы -- это время до отдания первой строки хеш--агрегатом.
Условно можно считать , что первичная (не из СТЕ) вычитка данных завершилась одновременно с мерджем. (если все там сделано по уму --- оно должно и мерджить и начинать размещать в хеш таблицу "на проходе" -- т.е. в момент профетчивания -- сиречь -- вычитки). что тоже неверно. А так там прямо написано -- последняя запись индекс сканами получена в 1125.851 , а мердж задержался до 3294.815, потом началось сканирование материализованного СТЕ, с параллельным (конвейер, как я догадываюсь) хеш--джойном, которое завершилось в 10511.509, с момента отдачи первой записи хеш--джойном [7338.802] _уже_ (по уму) должно начаться хеш--агрегироование (как процесс), которое просто по определению не могло _вернуть_ первую запись раньше, чем к нему пришла последняя из конвеера -- 10511.509 .

как--то так.
...
Рейтинг: 0 / 0
25.02.2016, 11:02
| Ответить | Цитировать | Написать  
хитрый join и group by
    #39178467
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
p2.Legushka,

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

важно понять, что именно вам нужно, и думать вокруг оптимизации именно этого.
...
Рейтинг: 0 / 0
25.02.2016, 11:07
| Ответить | Цитировать | Написать  
хитрый join и group by
    #39178468
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
qwwq,

Также как и в случае с HashJoin сначала строиться хэш таблица на все данные , так же и тут — пока не будет построена таблица по всем данным, аггрегация не начнётся. Разница между окончанием работы с данными `11868.782` и началом агрегации `13485.463` похожа на время создания таблицы.

P.S. Пруфов нет под рукой, так что могу и наврать.
...
Рейтинг: 0 / 0
25.02.2016, 11:07
| Ответить | Цитировать | Написать  
хитрый join и group by
    #39178480
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
vyegorovqwwq,

Также как и в случае с HashJoin сначала строиться хэш таблица на все данные , так же и тут — пока не будет построена таблица по всем данным, аггрегация не начнётся. Разница между окончанием работы с данными `11868.782` и началом агрегации `13485.463` похожа на время создания таблицы.

P.S. Пруфов нет под рукой, так что могу и наврать.не "началом агрегации" , а выдачей первой записи из хеш--агрегата.

PS вы руками когда--либо агрегировали в хеш таблице ? это типичное агрегирование на проходе -- вы при повторении хеш--ключа уже навариваете значение на ячейках -- агрегатах. Если по уму делать, конечно.
...
Рейтинг: 0 / 0
25.02.2016, 11:13
| Ответить | Цитировать | Написать  
хитрый join и group by
    #39178686
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
qwwq,

Я не уверен, что результаты `CTE Scan on wpc_per` можно сразу использовать как хэш-таблицу.

Мне кажется, что запрос исполняется так:
1. Хэш по результатам слияния
2. Хэш-объединение с `CTE Scan on Woperation` (до конца, без ковейера выше)
3. Хэш по результатам из #2 (в плане не показан)
4. Хэш-группировка
...
Рейтинг: 0 / 0
25.02.2016, 12:47
| Ответить | Цитировать | Написать  
хитрый join и group by
    #39178698
p2.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
p2.
Участник
vyegorovТут можно только один агрегат засунуть в подзапросчем второй не угодил?
...
Рейтинг: 0 / 0
25.02.2016, 12:54
| Ответить | Цитировать | Написать  
хитрый join и group by
    #39178724
Фотография vyegorov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
vyegorov
Участник
p2.vyegorovТут можно только один агрегат засунуть в подзапросчем второй не угодилОдин подзапрос с агрегатом, ещё один как есть. Внешний запрос тоже с агрегатом.
Либо два запроса с агрегатами, потом объединить.
...
Рейтинг: 0 / 0
25.02.2016, 13:07
| Ответить | Цитировать | Написать  
хитрый join и group by
    #39178735
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
vyegorov,

что показано, а что нет, и как производится хеш агрегирование (с исчислением хеша на том же проходе, что и агрегирование в ячейках хеш--агрегата) -- давайте оставим. тут надо в код лезть, а я в си не ходок.

я думаю проблема автора в том, что он материализует довольно большие наборы, не задумываясь о том, что это дисковые операции. а надо бы максимально их сворачивать при первой материализации, а то, что не сворачиваемо, писать подзапросом, а не ЦТЕ, который всегда материализует.
...
Рейтинг: 0 / 0
25.02.2016, 13:12
| Ответить | Цитировать | Написать  
хитрый join и group by
    #39178895
Фотография Legushka
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Legushka
Участник
qwwq, подскажите, плз, что понимается под сворачиваемыми, максимальным сворачиванием, материализацией.
...
Рейтинг: 0 / 0
25.02.2016, 15:10
| Ответить | Цитировать | Написать  
хитрый join и group by
    #39178910
qwwq
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
qwwq
Участник
Legushkaqwwq, подскажите, плз, что понимается под сворачиваемыми, максимальным сворачиванием, материализацией.
правильнее (про первые 2) сказать что я понимаю. сворачивание по измерению[ям] -- группировка по ним с результирующим уменьшением (обычно на несколько порядков) количества строк результата. [тут сворачиваются прочие измерения, если есть]

а материализация -- это материализация -- ваш набор строк становится не инструкцией, для его получения, а размещается материально либо в памяти, либо на диске (если не в какие ворота). текущая реализация СТЕ в пж реализовано именно через т.н. "темпл тейбл" -- материальный набор строк. Что плохо, когда он вам нужен просто как многокрано повторяемая в тексте одна и та же инструкция. (т.е. подзапрос).

понятно, что чем меньше вам надо разместить где--то строк -- тем лучше.
...
Рейтинг: 0 / 0
25.02.2016, 15:18
| Ответить | Цитировать | Написать  
17 сообщений из 17, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / хитрый join и group by
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&msg=39178480&tid=1997407]:
 0ms
get settings:
 6ms
get forum list:
 13ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 80ms
get topic data:
 13ms
get forum data:
 3ms
get page messages:
 68ms
get tp. blocked users:
 1ms
others: 234ms
total:  424ms
созд. / загр.: 424ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]