Как правильно применить GROUP BY / PostgreSQL

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Как правильно применить GROUP BY

20 сообщений из 20, страница 1 из 1

Как правильно применить GROUP BY

#32676530

alvlnik

Гость

Есть таблица svalue. В ней три поля id, value, time
Делаю запрос
select id, max(time) as mtime from svalue group by id;
Все выводится нормально, групируется по id и в каждой строчке максимальное значение
time для этого поля. Но мне нужно еще и какое значение value было в это время,
делаю запрос
select id, value, max(time) as mtime from svalue group by id;
на что psql сообщает
ERROR: column "svalue.value" must appear in the GROUP BY clause or be used in an aggregate function
если вставляю value в group by получается полная несгруппированная ерудна :-(
Как же получить значения value по максимальному времени сгруппированые по id?

Да, psql 7.4.3

...

Рейтинг:

0 / 0

02.09.2004, 06:58

| Ответить | Цитировать | Написать

Как правильно применить GROUP BY

#32676553

PJG

Гость

Код: plaintext

1.
2.
3.

select *
from svalue a
where a.time=(select max(time) from svalue where id=a.id)

...

Рейтинг:

0 / 0

02.09.2004, 07:28

| Ответить | Цитировать | Написать

Как правильно применить GROUP BY

#32676584

alvlnik

Гость

To PJG, не-е, что-то тут не так. Похоже на бесконечный цикл. На тестовой базе в 44000 записей не дождался ответа в течении 15 минут ;(, что уж говорить про боевую базу с 8-мя миллионами записей. IMHO, надо всетаки копать в сторону group by.

...

Рейтинг:

0 / 0

02.09.2004, 08:32

| Ответить | Цитировать | Написать

Как правильно применить GROUP BY

#32676632

Sad Spirit

Участник

Сообщения: 576

Рейтинг: 0 / 0

я бы начал копать примерно отсюда:

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.

SELECT s.*
FROM svalue s, (
    SELECT id, max(time) AS maxtime
    FROM svalue
    GROUP BY id
) AS agg
WHERE s.id = agg.id AND
      s.time = agg.maxtime;

...

Рейтинг:

0 / 0

02.09.2004, 09:20

| Ответить | Цитировать | Написать

Как правильно применить GROUP BY

#32676681

alvlnik

Гость

О! То что надо. Все замечательно работает.
Огромное спасибо.

...

Рейтинг:

0 / 0

02.09.2004, 09:51

| Ответить | Цитировать | Написать

Как правильно применить GROUP BY

#32676861

nevermind

Гость

Эх, не успел написать то же самое :) ну да ладно, в след. раз :))

...

Рейтинг:

0 / 0

02.09.2004, 11:07

| Ответить | Цитировать | Написать

Как правильно применить GROUP BY

#32677436

alvlnik

Гость

nevermindЭх, не успел написать то же самое :) ну да ладно, в след. раз :))Зачем же в следующий раз, можно и сейчас проявить себя ;-).
Агрегатная функция max() исполняется жутко долго ;(, в известном документе про оптимизацию работы PostgreSQL, рекомендуется ее заменить на select ... order by limit 1, но что-то ощутимого быстродействия мне это не принисло. Вроде как Order by использует сортировку, а не index scan. Хотя index по полю time есть.

...

Рейтинг:

0 / 0

02.09.2004, 13:36

| Ответить | Цитировать | Написать

Как правильно применить GROUP BY

#32677530

nevermind

Гость

Напиши, что у тебя explain analyze выдает на запросе с order by

...

Рейтинг:

0 / 0

02.09.2004, 14:11

| Ответить | Цитировать | Написать

Как правильно применить GROUP BY

#32677574

alvlnik

Гость

nevermindНапиши, что у тебя explain analyze выдает на запросе с order byЗапрос
EXPLAIN analyze SELECT time from svalue order by time desc limit 1
Ответ

Limit (cost=4310.15..4310.15 rows=1 width=8) (actual time=1143.622..1143.625 rows=1 loops=1)
-> Sort (cost=4310.15..4419.05 rows=43561 width=8) (actual time=1143.611..1143.611 rows=1 loops=1)
Sort Key: "time"
-> Seq Scan on svalue (cost=0.00..953.61 rows=43561 width=8) (actual time=0.156..423.181 rows=50060 loops=1)
Total runtime: 1161.689 ms
(5 rows)

...

Рейтинг:

0 / 0

02.09.2004, 14:24

| Ответить | Цитировать | Написать

Как правильно применить GROUP BY

#32677630

alvlnik

Гость

Во е-мое, вот это на грабли я наступил! У меня был совмещенный индекс по двум полям сразу (id,time). Когда я создал раздельные индексы, тот же запрос с order by стал использовать индекс и выполниля за 0.2 msec ;-)
Как бы теперь этот select с order by вместо max() вписать?

...

Рейтинг:

0 / 0

02.09.2004, 14:41

| Ответить | Цитировать | Написать

Как правильно применить GROUP BY

#32677755

assa

Участник

Откуда: мы сами не местные

Сообщения: 3 467

Рейтинг: 0 / 0

авторУ меня был совмещенный индекс по двум полям сразу (id,time). Когда я создал раздельные индексы, тот ...
так все праильно. если бы был индекс (time,id), тогда бы он использовался при order by time. Индексы (обычных видов) не аддитивны. В т.ч. 2 отдельных индекса не заменяют составной при поиске по 2-м полям .

...

Рейтинг:

0 / 0

02.09.2004, 15:15

| Ответить | Цитировать | Написать

Как правильно применить GROUP BY

#32678554

alvlnik

Гость

Господа, тема не закрыта ;-)
Пытаюсь заменить тормозной max() на вроде более шустрый select... order by и вот что у меня получается.
Запрос
EXPLAIN analyze
SELECT v.id, (
SELECT s.time
FROM svalue AS s
WHERE s.id = v.id
ORDER BY time DESC LIMIT 1) AS maxtime
FROM svalue as v
GROUP BY v.id
Ответ
HashAggregate (cost=1208.21..1216.82 rows=13 width=4) (actual time=564.285..662.280 rows=13 loops=1)
-> Seq Scan on svalue v (cost=0.00..1070.17 rows=55217 width=4) (actual time=0.108..337.782 rows=55247 loops=1)
SubPlan
-> Limit (cost=0.00..0.66 rows=1 width=8) (actual time=9.813..9.815 rows=1 loops=13)
-> Index Scan Backward using time_idx on svalue s (cost=0.00..2812.66 rows=4248 width=8) (actual time=9.800..9.800 rows=1 loops=13)
Filter: (id = $0)
Total runtime: 663.270 ms
(7 rows)
Видим, что в подзапросе (где вычисляется максимальное поле time) индекс используется, а при выбре id нет ;-(.
Вот созданные индексы для данной таблицы
psql=# \d svalue
Table "public.svalue"
Column | Type | Modifiers
--------+-----------------------------+-----------
id | integer | not null
value | text |
time | timestamp without time zone |
alarm | smallint |
Indexes:
"alarm_idx" btree (alarm)
"id_idx" btree (id)
"id_time_idx" btree (id, "time")
"time_id_idx" btree ("time", id)
"time_idx" btree ("time")

Как же можно ускорить запрос?

...

Рейтинг:

0 / 0

03.09.2004, 08:27

| Ответить | Цитировать | Написать

Как правильно применить GROUP BY

#32678998

nevermind

Гость

Потому что запрос переписал неправильно и индексов у тебя туева хуча не понятно зачем. Оставь только те, которыми пользуешься - остальные грохни нафиг. Насчет запроса: тебе всего лишь надо было переписать внутр. часть того, что писал Sad spirit. Это будет что-то вроде

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.

SELECT s.*
FROM svalue s, (
	SELECT ss.id AS id, tmp.maxtime AS maxtime
	FROM svalue ss, (
		select id, time AS maxtime
		FROM svalue sss
		WHERE sss.id = ss.id
		ORDER BY time DESC
		LIMIT  1 
	) AS tmp
) AS agg
WHERE	s.id = agg.id AND
	s.time = agg.maxtime;

писал быстро, поэтому возможны ошибки. Проверяй

...

Рейтинг:

0 / 0

03.09.2004, 11:49

| Ответить | Цитировать | Написать

Как правильно применить GROUP BY

#32679117

alvlnik

Гость

nevermindПотому что запрос переписал неправильно и индексов у тебя туева хуча не понятно зачем. Оставь только те, которыми пользуешься - остальные грохни нафиг.Конечно я специально насоздавал излишние индексы, чтобы посмотреть какими реально будет пользоваться оптимизатор, остальные удалю.
nevermind Насчет запроса: тебе всего лишь надо было переписать внутр. часть того, что писал Sad spirit. Это будет что-то вродеКак ты предложил я тоже пробовал. На это psql выдает

Код: plaintext

ERROR:  subquery in FROM may not refer to other relations of same query level

...

Рейтинг:

0 / 0

03.09.2004, 12:25

| Ответить | Цитировать | Написать

Как правильно применить GROUP BY

#32679150

alvlnik

Гость

Хм-м-м. так что GROUP BY тоже не использует индексы ? Вот пример

Код: plaintext

1.
2.

EXPLAIN analyze 
select id from svalue group by id

Вот ответ

Код: plaintext

1.
2.
3.
4.

 HashAggregate  (cost= 1208 . 21 .. 1208 . 21  rows= 13  width= 4 ) (actual time= 516 . 238 .. 516 . 276  rows= 13  loops= 1 )
   ->  Seq Scan on svalue  (cost= 0 . 00 .. 1070 . 17  rows= 55217  width= 4 ) (actual time= 0 . 149 .. 303 . 352  rows= 59235  loops= 1 )
 Total runtime:  516 . 514  ms
( 3  rows)

Индексами и не пахнет ;-(.

...

Рейтинг:

0 / 0

03.09.2004, 12:38

| Ответить | Цитировать | Написать

Как правильно применить GROUP BY

#32679355

assa

Участник

Откуда: мы сами не местные

Сообщения: 3 467

Рейтинг: 0 / 0

в лоб можно и так

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.

SELECT s.*
FROM svalue s
WHERE	s.time = 
(select time AS maxtime
	FROM svalue ss
	WHERE ss.id = s.id
	ORDER BY time DESC
	LIMIT  1 )
ORDER BY id;

,но мне не кажется, что это будет быстро - условие Where будет проверяться построчно.

еще один, думается плохой, вариант

Код: plaintext

1.
2.
3.
4.
5.
6.
7.

SELECT t.* FROM  svalue t INNER JOIN
(SELECT DISTINCT id, (Select time From svalue s 
	WHERE  s.id=g.id
	ORDER BY time Desc limit  1 ) AS time
    FROM svalue g   -- group by id 
) q ON t.id = q.id AND t.time=q.time

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

чтобы считать тайм только один раз для группы (id) придется как-то так:

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.

SELECT s.* FROM svalue s INNER JOIN
	(SELECT gr.id, 
		(SELECT t.time 
		FROM svalue t
		WHERE t.id = gr.id 
		ORDER BY time DESC
		LIMIT  1  ) AS  time
	FROM  (SELECT  g.id FROM svalue g
	 group by id) gr) agg
ON s.id = agg.id AND s.time=agg.time;

(т.е. :берем группировку по id, вместо max берем подзапрос, на результат - вяжемся). Будет ли это быстро?

...

Рейтинг:

0 / 0

03.09.2004, 13:51

| Ответить | Цитировать | Написать

Как правильно применить GROUP BY

#32679490

alvlnik

Гость

assaв лоб можно и так
,но мне не кажется, что это будет быстро - условие Where будет проверяться построчно. Да, в лоб получилось очень долго.

assa
еще один, думается плохой, вариант

2-й вариант тоже очень долго.

assaчтобы считать тайм только один раз для группы (id) придется как-то так:
(т.е. :берем группировку по id, вместо max берем подзапрос, на результат - вяжемся). Будет ли это быстро?3-й вариант показал такую же скорость как и в приведенном мной варианте, хотя план запроса более навороченный. Вот анализ плана запроса

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.

 Nested Loop  (cost= 1208 . 21 .. 1303 . 39  rows= 1  width= 21 ) (actual time= 526 . 371 .. 639 . 217  rows= 13  loops= 1 )
   Join Filter: ("inner".id = "outer".id)
   ->  Subquery Scan gr  (cost= 1208 . 21 .. 1208 . 34  rows= 13  width= 4 ) (actual time= 507 . 842 .. 508 . 016  rows= 13  loops= 1 )
         ->  HashAggregate  (cost= 1208 . 21 .. 1208 . 21  rows= 13  width= 4 ) (actual time= 507 . 829 .. 507 . 932  rows= 13  loops= 1 )
               ->  Seq Scan on svalue g  (cost= 0 . 00 .. 1070 . 17  rows= 55217  width= 4 ) (actual time= 0 . 156 .. 286 . 543  rows= 61460  loops= 1 )
   ->  Index Scan using time_idx on svalue s  (cost= 0 . 00 .. 6 . 60  rows= 1  width= 21 ) (actual time= 0 . 046 .. 0 . 054  rows= 1  loops= 13 )
         Index Cond: (s."time" = (subplan))
         SubPlan
           ->  Limit  (cost= 0 . 00 .. 0 . 66  rows= 1  width= 8 ) (actual time= 9 . 989 .. 9 . 991  rows= 1  loops= 13 )
                 ->  Index Scan Backward using time_idx on svalue t  (cost= 0 . 00 .. 2812 . 66  rows= 4248  width= 8 ) (actual time= 9 . 977 .. 9 . 977  rows= 1  loops= 13 )
                       Filter: (id = $ 0 )
           ->  Limit  (cost= 0 . 00 .. 0 . 66  rows= 1  width= 8 ) (never executed)
                 ->  Index Scan Backward using time_idx on svalue t  (cost= 0 . 00 .. 2812 . 66  rows= 4248  width= 8 ) (never executed)
                       Filter: (id = $ 0 )
           ->  Limit  (cost= 0 . 00 .. 0 . 66  rows= 1  width= 8 ) (never executed)
                 ->  Index Scan Backward using time_idx on svalue t  (cost= 0 . 00 .. 2812 . 66  rows= 4248  width= 8 ) (never executed)
                       Filter: (id = $ 0 )
 Total runtime:  639 . 896  ms
( 18  rows)

...

Рейтинг:

0 / 0

03.09.2004, 14:52

| Ответить | Цитировать | Написать

Как правильно применить GROUP BY

#32679652

assa

Участник

Откуда: мы сами не местные

Сообщения: 3 467

Рейтинг: 0 / 0

если речь об этом:

Код: plaintext

1.
2.
3.
4.
5.
6.

SELECT v.id, (
	SELECT s.time 
	FROM svalue AS s 
	WHERE s.id = v.id 
	ORDER BY time DESC LIMIT  1 ) AS maxtime
FROM svalue as v
GROUP BY v.id ;

то
7.3.4
в PgAdmin

Код: plaintext

ERROR:  Sub-SELECT uses un-GROUPed attribute atable.atext from outer query

или речь еще об чем?

...

Рейтинг:

0 / 0

03.09.2004, 16:02

| Ответить | Цитировать | Написать

Как правильно применить GROUP BY

#32679660

assa

Участник

Откуда: мы сами не местные

Сообщения: 3 467

Рейтинг: 0 / 0

тьфу
atable.atext - энто вылезло у меня из определения svalue.id во вью "svalue" (не стал я таблу заводить для тестов), но, мне кааца также должно отругаться и на табличном наборе (если нет - это очередной баг постнре, на).

...

Рейтинг:

0 / 0

03.09.2004, 16:06

| Ответить | Цитировать | Написать

Как правильно применить GROUP BY

#32679721

assa

Участник

Откуда: мы сами не местные

Сообщения: 3 467

Рейтинг: 0 / 0

действительно, запрос на таблице проходит. На вью - них.
Баг работы со вью?

(идеалогически-то ему (постгресу, т.е.) должно быть пох (Select one_field From bla-bla limit 1) as anyval - это попросту выражение, и никакой группировкой тут не пахнет, т.ч. должно пропускать и вью и таблицу в кач-ве bla-bla). Недосмотр тут у них.

...

Рейтинг:

0 / 0

03.09.2004, 16:27

| Ответить | Цитировать | Написать

20 сообщений из 20, страница 1 из 1

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Как правильно применить GROUP BY

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=53&msg=32678554&tid=2007691]:	0ms
get settings:	8ms
get forum list:	13ms
check forum access:	2ms
check topic access:	2ms
track hit:	207ms
get topic data:	7ms
get forum data:	3ms
get page messages:	54ms
get tp. blocked users:	1ms
others:	224ms

total:	521ms