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

alvlnik
Дата: 02.09.2004 06:58:58
Есть таблица 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
PJG
Дата: 02.09.2004 07:28:04
select *
from svalue a
where a.time=(select max(time) from svalue where id=a.id)
alvlnik
Дата: 02.09.2004 08:32:17
To PJG, не-е, что-то тут не так. Похоже на бесконечный цикл. На тестовой базе в 44000 записей не дождался ответа в течении 15 минут ;(, что уж говорить про боевую базу с 8-мя миллионами записей. IMHO, надо всетаки копать в сторону group by.
Sad Spirit
Дата: 02.09.2004 09:20:11
я бы начал копать примерно отсюда:

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;
alvlnik
Дата: 02.09.2004 09:51:44
О! То что надо. Все замечательно работает.
Огромное спасибо.
nevermind
Дата: 02.09.2004 11:07:22
Эх, не успел написать то же самое :) ну да ладно, в след. раз :))
alvlnik
Дата: 02.09.2004 13:36:13
nevermind
Эх, не успел написать то же самое :) ну да ладно, в след. раз :))
Зачем же в следующий раз, можно и сейчас проявить себя ;-).
Агрегатная функция max() исполняется жутко долго ;(, в известном документе про оптимизацию работы PostgreSQL, рекомендуется ее заменить на select ... order by limit 1, но что-то ощутимого быстродействия мне это не принисло. Вроде как Order by использует сортировку, а не index scan. Хотя index по полю time есть.
nevermind
Дата: 02.09.2004 14:11:02
Напиши, что у тебя explain analyze выдает на запросе с order by
alvlnik
Дата: 02.09.2004 14:24:53
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)
alvlnik
Дата: 02.09.2004 14:41:16
Во е-мое, вот это на грабли я наступил! У меня был совмещенный индекс по двум полям сразу (id,time). Когда я создал раздельные индексы, тот же запрос с order by стал использовать индекс и выполниля за 0.2 msec ;-)
Как бы теперь этот select с order by вместо max() вписать?