Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / хитрый join и group by / 17 сообщений из 17, страница 1 из 1
24.02.2016, 17:11
    #39177987
Legushka
Legushka
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
хитрый join и group by
можно ли построить 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:41
    #39178016
Alexius
Alexius
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
хитрый join и group by
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
| Ответить | Цитировать | Написать  
25.02.2016, 08:02
    #39178316
Legushka
Legushka
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
хитрый join и group by
Alexius, решение гуд, но хочется узнать есть ли решение на джоинах?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.02.2016, 09:53
    #39178380
Legushka
Legushka
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
хитрый join и group by
пробовал через подзапросы, в итоге даже намного дольше стало, решил вернуться к первоначальному и еще раз проанализировать почему я хотел уйти от группировок

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

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

есть временная таблица 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, 10:21
    #39178404
p2.
p2.
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
хитрый join и group by
Legushka,

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

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

и эта вторая таблица в итоге работает в 2 раза дольше чем формирование первой? что можно сделать?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.02.2016, 10:36
    #39178420
vyegorov
vyegorov
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
хитрый join и group by
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, 11:02
    #39178457
qwwq
qwwq
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
хитрый join и group by
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:07
    #39178467
qwwq
qwwq
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
хитрый join и group by
p2.Legushka,

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

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

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

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

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

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

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

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

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

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

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

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

понятно, что чем меньше вам надо разместить где--то строк -- тем лучше.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / хитрый join и group by / 17 сообщений из 17, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 486ms
Закрыть
start [/forum/topic.php?fid=53&mobile=1&tid=1997407]:
 0ms
get settings:
 6ms
get forum list:
 14ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 174ms
get topic data:
 9ms
get forum data:
 3ms
get page messages:
 52ms
get tp. blocked users:
 1ms
others: 221ms
total:  486ms
x
x
Закрыть


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