Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Разложить выборку на группы  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4901
Есть такая задача.

Имеется 2 поля а) некий ключ ID и б) сумма неких значений VALUE

DECLARE @NBuckets INT = 32

SELECT SUM(VALUE) AS VALUE, ID
FROM 
(
	SELECT 
		CAST(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING(CAST(NEWID() AS CHAR(36)), 
				1, 5), 'A', ''), 'B', ''), 'C', ''), 'D', ''), 'E', ''), 'F', '') AS INT) AS VALUE, 
		CAST(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING(CAST(NEWID() AS CHAR(36)), 
				10, 3), 'A', ''), 'B', ''), 'C', ''), 'D', ''), 'E', ''), 'F', '') AS INT) AS ID 
	FROM master..spt_values
) T
GROUP BY ID 
ORDER BY ID DESC 


Нужно разложить ключи ID по диапазонам (бакетам) в порядке их возрастания, так чтобы суммы VALUE распределились насколько можно равномерно.

Результат примерно такой
BucketN, ID, VALUE, SUM(VALUE) OVER(PARTITION BY BUCKETN)

1 1 500 900
1 2 400 900
2 3 600 800
2 4 200 800
3 5 750 750
4 6 500 1100
4 7 600 1100

Скажу сразу, что функция NTILE не даст нужного результата для данной задачи. Но суть того, что она делает, схожа.

В общем задача стоит подобрать оптимальную функцию партиционирования для таблицы.
24 ноя 15, 13:41    [18464365]     Ответить | Цитировать Сообщить модератору
 Re: Разложить выборку на группы  [new]
Glory
Member

Откуда:
Сообщений: 104751
a_voronin
В общем задача стоит подобрать оптимальную функцию партиционирования для таблицы.

Это задача перебора. Она не решается упорядочиванием.
24 ноя 15, 13:43    [18464390]     Ответить | Цитировать Сообщить модератору
 Re: Разложить выборку на группы  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4901
Glory,

Ну не знаю, под SQL 2014 я бы подумал.
24 ноя 15, 13:50    [18464451]     Ответить | Цитировать Сообщить модератору
 Re: Разложить выборку на группы  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4901
У меня такой вариант родился, если кто лучше предложит пишите.

DECLARE @NBuckets INT = 32

SELECT BucketN, ID, VALUE, SumBucket = SUM(VALUE) OVER (PARTITION BY BucketN) FROM 
(
	SELECT 
		SUM(VALUE) AS VALUE, ID, 
		BucketN = CAST(0.999999999 * @NBuckets * PERCENT_RANK() OVER (ORDER BY ID, SUM(VALUE)) AS INT)
		--RunningTotal = SUM(SUM(VALUE)) OVER (ORDER BY ID DESC),
	FROM 
	(
		SELECT 
			CAST(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING(CAST(NEWID() AS CHAR(36)), 
					1, 5), 'A', ''), 'B', ''), 'C', ''), 'D', ''), 'E', ''), 'F', '') AS INT) AS VALUE, 
			CAST(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING(CAST(NEWID() AS CHAR(36)), 
					10, 3), 'A', ''), 'B', ''), 'C', ''), 'D', ''), 'E', ''), 'F', '') AS INT) AS ID 
		FROM master..spt_values
	) T
	GROUP BY ID 
) TT
ORDER BY ID DESC 
24 ноя 15, 14:02    [18464585]     Ответить | Цитировать Сообщить модератору
 Re: Разложить выборку на группы  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
a_voronin, вот такой вариант, только корзин почти никогда не получается ровно столько, сколько нужно.

Зато распределение ровнее.

+
DECLARE @NBuckets INT = 32;


SELECT SUM(VALUE) AS VALUE, ID
into #pretable
FROM 
(
	SELECT 
		CAST(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING(CAST(NEWID() AS CHAR(36)), 
				1, 5), 'A', ''), 'B', ''), 'C', ''), 'D', ''), 'E', ''), 'F', '') AS INT) AS VALUE, 
		CAST(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING(CAST(NEWID() AS CHAR(36)), 
				10, 3), 'A', ''), 'B', ''), 'C', ''), 'D', ''), 'E', ''), 'F', '') AS INT) AS ID 
	FROM master..spt_values
) T
GROUP BY ID  

declare @val_in_bucket int;
select @val_in_bucket = sum(value) / @NBuckets
from #pretable;

alter table #pretable add bucket int;

while (select count(*) from #pretable  where bucket is null) > 0
begin

with t as 
(
	select id, value, SUM(value) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as r_t
	from #pretable
	where bucket is null
), t1 as 
(select top 1 id 
 from t
 order by abs(r_t - @val_in_bucket)
)
update pt
set		bucket = (select isnull(max(bucket), 0) + 1 from #pretable)
from #pretable pt
where bucket is null and id <= (select id from t1)

end

select @val_in_bucket;

select id, value, bucket, sum(value) over (partition by bucket) from #pretable
order by id

drop table #pretable
24 ноя 15, 14:11    [18464667]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить