В ожидании 9.0 (8.5) - Агрегаты с упорядочиванием

Перевод Waiting for 8.5 – ordered aggregates с select * from depesz;

(В январе 2010 г. команда разработчиков решила, что следующая версия PostgreSQL будет нумероваться 9.0, а не 8.5)

15 декабря Tom Lane применил патч от Andrew Gierth (aka RhodiumToad), добавляющий интересную возможность:

Поддержка ORDER BY в агрегатных функциях, наконец-то не хак-решение того в каком порядке значения будут агрегироваться. На ряду с этим снимается ограничение использования DISTINCT в агрегатах с одним и только одним аргументом.

Возможно стоит упомянуть о изменении в поведении: ранее agg(DISTINCT x) всегда выкидывала null-значения. Теперь это происходит только тогда, когда transition-функция агрегата реализует это ограничение. Иначе null-значения обрабатываются так, как обычно делает DISTINCT, т.е. возвращается одна копия.

Andrew Gierth, проверено Hitoshi Harada

Описание вполне понятно, т.ч. давайти просто проведём тесты. Предположим, есть таблица заказов следующей структуры:

# \d orders
Table "public.orders"
Column | Type | Modifiers
---------+---------+-----------------------------------------------------
id | integer | not null default nextval('orders_id_seq'::regclass)
buyer | text | not null
ordered | date | not null
total | integer |
Indexes:
"orders_pkey" PRIMARY KEY, btree (id)

В этой таблице следующие данные:

# select * from orders;
id | buyer | ordered | total
----+-------+------------+-------
1 | Alice | 2009-05-12 | 57
2 | Eve | 2009-07-07 | 131
3 | Alice | 2009-08-15 | 177
4 | Carol | 2009-04-28 | 232
...
38 | Eve | 2009-02-01 | 225
(38 rows)

Они сгенерированы следующим запросом:

select n[j], now() - '1 year'::interval * random(), 50 + random() * 200
from
generate_series(1,10) i,
generate_series(1,5) j,
(select '{Alice,Bob,Carol,Dave,Eve}'::text[] as n) x
where random() < 0.6;

Сейчас, для каждого получателя получим сумму его заказов и список дат этих заказов. Это просто:

# select buyer, sum(total), array_agg( ordered)
from orders
group by buyer
order by buyer;
buyer | sum | array_agg
-------+------+------------------------------------------------------------------------------------------------------
Alice | 1057 | {2009-05-12,2009-08-15,2009-11-22,2009-05-08,2009-02-14,2009-03-25,2009-03-06,2009-08-16}
Bob | 905 | {2009-01-29,2009-08-22,2009-05-28,2009-05-12,2009-02-10,2009-08-17}
Carol | 1118 | {2009-04-28,2009-03-12,2009-01-10,2009-03-30,2009-06-27,2009-09-19,2009-12-14,2009-09-06,2009-09-01}
Dave | 1239 | {2009-07-15,2009-07-27,2009-02-07,2009-12-19,2009-01-13,2009-05-28,2009-08-27}
Eve | 1222 | {2009-07-07,2009-04-07,2009-10-02,2009-02-05,2009-09-26,2009-04-18,2009-08-14,2009-02-01}
(5 rows)

Проблема в том, что даты в массивах не упорядочены. Ранее нам приходилось делать так:

# select buyer, sum(total), array_agg( ordered)
from ( select * from orders order by buyer, ordered ) x
group by buyer
order by buyer;
buyer | sum | array_agg
-------+------+------------------------------------------------------------------------------------------------------
Alice | 1057 | {2009-02-14,2009-03-06,2009-03-25,2009-05-08,2009-05-12,2009-08-15,2009-08-16,2009-11-22}
Bob | 905 | {2009-01-29,2009-02-10,2009-05-12,2009-05-28,2009-08-17,2009-08-22}
Carol | 1118 | {2009-01-10,2009-03-12,2009-03-30,2009-04-28,2009-06-27,2009-09-01,2009-09-06,2009-09-19,2009-12-14}
Dave | 1239 | {2009-01-13,2009-02-07,2009-05-28,2009-07-15,2009-07-27,2009-08-27,2009-12-19}
Eve | 1222 | {2009-02-01,2009-02-05,2009-04-07,2009-04-18,2009-07-07,2009-08-14,2009-09-26,2009-10-02}
(5 rows)

Что работало, но порядок не гарантировался - тут порядок правильный потому что так это работает с таким планом. Если PG выберет другой план - массивы опять будут не упорядоченными.

Сейчас, благодаря автору патча, мы можем явно указывать какой должен быть порядок:

# select buyer, sum(total), array_agg( ordered order by ordered )
from orders
group by buyer;
buyer | sum | array_agg
-------+------+------------------------------------------------------------------------------------------------------
Alice | 1057 | {2009-02-14,2009-03-06,2009-03-25,2009-05-08,2009-05-12,2009-08-15,2009-08-16,2009-11-22}
Bob | 905 | {2009-01-29,2009-02-10,2009-05-12,2009-05-28,2009-08-17,2009-08-22}
Carol | 1118 | {2009-01-10,2009-03-12,2009-03-30,2009-04-28,2009-06-27,2009-09-01,2009-09-06,2009-09-19,2009-12-14}
Dave | 1239 | {2009-01-13,2009-02-07,2009-05-28,2009-07-15,2009-07-27,2009-08-27,2009-12-19}
Eve | 1222 | {2009-02-01,2009-02-05,2009-04-07,2009-04-18,2009-07-07,2009-08-14,2009-09-26,2009-10-02}
(5 rows)

Более того упорядочивать можно по другим полям:

# select buyer, sum(total), array_agg( ordered order by total desc )
from orders
group by buyer;
buyer | sum | array_agg
-------+------+------------------------------------------------------------------------------------------------------
Alice | 1057 | {2009-05-08,2009-08-15,2009-03-25,2009-08-16,2009-02-14,2009-11-22,2009-03-06,2009-05-12}
Bob | 905 | {2009-02-10,2009-01-29,2009-08-17,2009-05-12,2009-08-22,2009-05-28}
Carol | 1118 | {2009-04-28,2009-09-01,2009-03-30,2009-06-27,2009-12-14,2009-09-19,2009-03-12,2009-01-10,2009-09-06}
Dave | 1239 | {2009-05-28,2009-07-27,2009-02-07,2009-07-15,2009-08-27,2009-01-13,2009-12-19}
Eve | 1222 | {2009-02-01,2009-08-14,2009-09-26,2009-04-07,2009-07-07,2009-02-05,2009-04-18,2009-10-02}
(5 rows)

Проверим это:

# select *
from orders
where buyer = 'Bob'
order by total desc;
id | buyer | ordered | total
----+-------+------------+-------
24 | Bob | 2009-02-10 | 249
11 | Bob | 2009-01-29 | 215
26 | Bob | 2009-08-17 | 156
19 | Bob | 2009-05-12 | 105
12 | Bob | 2009-08-22 | 95
17 | Bob | 2009-05-28 | 85
(6 rows)

Великолепно. Работает как и должно. Конечно же порядок не имеет слысл для всех агрегатов - count(*) например.

Ещё один пример - вычисление медианы заказа на чистом SQL:

# WITH aggregated AS (
select
buyer,
array_agg(total order by total) as list,
count(*),
count(*) / 2 as half
from orders
group by buyer
)
select
buyer,
list,
case
when count % 2 = 1 then list[ half + 1 ]
else ( list[ half ] + list[ half + 1 ] ) / 2
end as median
FROM
aggregated;
buyer | list | median
-------+-----------------------------------+--------
Alice | {57,70,83,119,156,170,177,225} | 137
Bob | {85,95,105,156,215,249} | 130
Carol | {52,77,86,99,105,107,159,201,232} | 105
Dave | {98,131,154,155,219,241,241} | 155
Eve | {66,85,121,131,182,202,210,225} | 156
(5 rows)

Просто здорово!