Сортировка в агрегирующей функции в PostgreSQL
В PostgreSQL до 9-й версии не было сортировки внутри агрегирующей функции. То есть нельзя было написать как-нибудь вроде SELECT array_agg(price ORDER BY year)…
А как быть, если очень хочется и, в общем-то, нужно? А версия 8.4, и повысить её ну просто никак нельзя, ультимативное ограничение проекта.
Мэтью Вуд посоветовал использовать упорядоченный подзапрос. Да, собственно, официальный мануал так и говорит: «Обеспечение входных значений отсортированным подзапросом, однако, обычно будет работать». Умилительно написал об этом Том Лейн, один из главных разработчиков этой СУБД: «Ну, спека SQL этого не требует (на самом деле, я думаю, выражение ORDER BY внутри подзапроса даже вообще не правомерно по спеке SQL)… но мы обещаем это в текущей реализации, и сомневаюсь, что мы нарушим это обещание в будущем, ведь это мощно настраиваемое поведение для пользовательских агрегатов».
Но тут обнаружилась ещё одна засада. Если в главном запросе нужна группировка (GROUP BY), сортировка из подзапроса внезапно разрушается!
И тут уж я думал, придётся отказаться от агрегации, и разработчице, которой я этот метод тоже присоветовал, придётся сказать, что она потратила полдня зря из-за того, что я её направил на ложный путь. Ах-ах, потеря лица!
Но, хвала богам, благородный Лейн оговорился и на этот счёт: «Заметьте, что если вам нужно использовать выражение GROUP во внешнем запросе, лучше всего будет отсортировать выдачу внутреннего запроса сначала по полям группировки внешнего запроса». Лейн поясняет, отвечая на решение Михаэля Фура, участника дискуссии: «Этот способ будет работать и если планировщик решит использовать GroupAggregate (а он, вероятно, так и сделает, если увидит, что может избежать ещё одного шага сортировки). Способ же, указанный Михаэлем, будет работать только если план будет использовать HashAggregate,- но если планировщик решит, что во внешнем запросе нужно использовать Sort+GroupAggregate, пересортировка вероятно разрушит требуемый порядок…»