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

Была задача: есть некоторое количество заказов, которым оказались не назначены менеджеры, есть 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;

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

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;

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.

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

Similar Posts

LEAVE A COMMENT Отменить ответ