PostgreSQL — генерирование повторяющихся значений для неупорядоченных данных

• 03, 17, 2011
•  bullgare
•  Databases
• No Comments.

Была задача: есть некоторое количество заказов, которым оказались не назначены менеджеры, есть 3 менеджера, между которыми нужно распределить эти заказы равномерно. Легче всё сделать на php или python, но задача была сделать всё при помощи SQL-запроса без хранимых процедур и желательно одним запросом.

Пришлось немного подумать — в итоге сделал следующее (пусть id менеджеров 111, 222 и 333):

SELECT
	main.*,
	CASE 
		WHEN mod(number, 3) = 1 THEN 111 
		WHEN mod(number, 3) = 2 THEN 222 
		ELSE 333 
	END AS manager 
FROM
(
	SELECT
		m1.id,
		COUNT(m2.id) AS number 
	FROM
		main_training AS m1 
	INNER JOIN
		main_training AS m2 
	ON
		m1.id >= m2.id 
	WHERE
		m1.id IN (1, 2, 3, 5, 15, 20, 40) AND
		m2.id IN (1, 2, 3, 5, 15, 20, 40)
	GROUP BY
		m1.id 
	ORDER by
		number 
) AS main;

Основная проблема — пронумеровать последовательно все подпадающие под выборку строки. Делается это при помощи INNER JOIN на ту же таблицу. Условие ON m1.id >= m2.id и группировка по m1.id дают в COUNT(m2.id) порядковый номер (таблица m2 джойнится к m1 столько раз, сколько в m2 есть удовлетворяющих условию строк с id меньше m1.id).
После этого на основе модуля от деления порядкового номера на 3 определяем менеджера, которого назначим для сделки.

UPD:
Нашёл (вспомнил) формулу для аггрегирования значений:

formula for running aggregates

SELECT
	x1.key, 
	x1.some_column, 
	AGGREGATE_FN(x2.some_column) AS running_aggregate
FROM x AS x1
INNER JOIN x AS x2
ON x1.key >= x2.key
GROUP BY x1.key;

Формула взята из доклада Jay Pipes.