Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 наведите на мысль. разбить множество групп на примерно одинакового размера  [new]
дмитрий_099098
Guest
Трудно получается оформ словами.
declare  @i int, @qty int,@j int
set @i=98;
select char(97) as entity,1 as etype into ttb
union
select char(97) as entity,2 as etype
while(@i<110)
 begin
  set @qty=1+cast(10*rand() as int)
  set @j=1;
  while(@j<=@qty)
    begin
	 insert ttb values(char(@i),@j)
	 set @j=@j+1
	end
  set @i=@i+1
 end


есть сущности : a,b,c и тд.
у каждой сущности есть некий набор атрибутов. 1,2,3...10.
для примера выборка нарастающим итогом кол-ва атрибутов
entityrunningcount
entity p_total
a2
b5
c10
d17
e27
f37
g45
h48
i55
j57
k60
l67
m77

Что пытаюсь получить новую группу, в которой не больше, допустим(параметр изменяемый) 20 элементов:
newgroupentityrunningcount
1a2
1b5
1c10
1d17
2e27
2f37
3g45
3h48
3i55
3j57
4k60
4l67
4m77

тут получилось первая группа 17 элементов(сущности a,b,c,d), остальные по 20.
как это можно сделать?
Группы создаются по порядку сортировки entity.
10 май 16, 12:25    [19152461]     Ответить | Цитировать Сообщить модератору
 Re: наведите на мысль. разбить множество групп на примерно одинакового размера  [new]
iap
Member

Откуда: Москва
Сообщений: 47049
NTILE
10 май 16, 12:30    [19152492]     Ответить | Цитировать Сообщить модератору
 Re: наведите на мысль. разбить множество групп на примерно одинакового размера  [new]
митрий_099098
Guest
iap,спасибо
автор
NTILE (integer_expression) OVER ( [ <partition_by_clause> ] < order_by_clause > )

но кол-во групп не известно в принципе. Известен только размер группы - не больше 20. можно менше, но ровным счетом на элементы из предшествующей группы

есть чисто реляционный способ БЕЗ функций mssql2008+
только запросами?
10 май 16, 12:49    [19152609]     Ответить | Цитировать Сообщить модератору
 Re: наведите на мысль. разбить множество групп на примерно одинакового размера  [new]
iap
Member

Откуда: Москва
Сообщений: 47049
митрий_099098
iap,спасибо
автор
NTILE (integer_expression) OVER ( [ <partition_by_clause> ] < order_by_clause > )

но кол-во групп не известно в принципе. Известен только размер группы - не больше 20. можно менше, но ровным счетом на элементы из предшествующей группы

есть чисто реляционный способ БЕЗ функций mssql2008+
только запросами?
Разве нельзя общее количество поделить на количество строк в группе
и найденное число подставить в качестве аргумента NTILE()?
10 май 16, 12:52    [19152632]     Ответить | Цитировать Сообщить модератору
 Re: наведите на мысль. разбить множество групп на примерно одинакового размера  [new]
iap
Member

Откуда: Москва
Сообщений: 47049
Версия сервера неизвестна. Но если >= SQL2012, то OFFSET
10 май 16, 12:56    [19152653]     Ответить | Цитировать Сообщить модератору
 Re: наведите на мысль. разбить множество групп на примерно одинакового размера  [new]
Дмитрий_099098
Guest
iap
Разве нельзя общее количество поделить на количество строк в группе
?

в entity может быть разное кол-во строк.
пример грубо
entity а 5 строк, b 17, c 19, d 12, e 10,
все эти entity попадут в разные группы
1-a
2-b
3-c
4-d
5-e
это не ранжирование численного значения.важен порядок. a,b,c,d...
нельзя объединить в одной группе entity a и d из приведенного ряда выше.
засада какая то.

либо я толком сформулировать не могу.
еще у меня некоторые есть ограничения среды из которой отсылаются запросы к сиквелу(запросы "транслируются"), но это наверное не критично, можно пошаманить.
10 май 16, 13:05    [19152732]     Ответить | Цитировать Сообщить модератору
 Re: наведите на мысль. разбить множество групп на примерно одинакового размера  [new]
Дмитрий_099098
Guest
или еще пример
a 9
b 7
c 9
d 3
e 7
f 4
g 5

В итоге идем сверху, наполняем группы:
1a9
1b7
2c9
2d3
2e7
3f4
3g5

итого в 1 группе оказались entity a,b и 16 элементов(строк)
во второй cde и их 19 элементов
в третьей fg и колво элементов 9, после чего, допустим, конец таблицы...
10 май 16, 14:12    [19153157]     Ответить | Цитировать Сообщить модератору
 Re: наведите на мысль. разбить множество групп на примерно одинакового размера  [new]
invm
Member

Откуда: Москва
Сообщений: 9633
дмитрий_099098
тут получилось первая группа 17 элементов(сущности a,b,c,d), остальные по 20.
как это можно сделать?
select
 *, runningcount / 20 + 1
from
 Таблица
10 май 16, 14:37    [19153290]     Ответить | Цитировать Сообщить модератору
 Re: наведите на мысль. разбить множество групп на примерно одинакового размера  [new]
Konst_One
Member

Откуда:
Сообщений: 11562
а почему 1 группа 16 элементов, а 2-я 19, а 3-я вообще всего 9?
у вас есть какая то математика для этого?
10 май 16, 14:39    [19153307]     Ответить | Цитировать Сообщить модератору
 Re: наведите на мысль. разбить множество групп на примерно одинакового размера  [new]
Дмитрий_099098
Guest
Konst_One,
Математика предельно простая. на тскл делается на раз, два, три... но вот запросами... хз.
еще раз поясню

в начале код - делает тестовую таблицу.

есть сущности A B C..
У каждой есть несколько элементов.
то есть первая сущность А
у нее два элемента: 1 и 2.
Далее сущность Б у нее рандомное кол-во элементов от 1 до 10.
сами элементы - это типы. их всегда 10 шт(но это не важно,т.к. они "занимают место", можем считать что у каждой сущности свой уникальный небор уникальных элементов). но у какой-то сущности ОДИН тип. у другой ДВА, а у третей их ДЕСЯТЬ.

сущности идут по порядку - в виде очереди и это важно.

Эту очередь с ее начала мы должны разбить на группы по 20 элементов.

берем первую сущность, там 2 элемента. берем вторую, там 9, третью там 7. итого 18 элементов в первых трех сущностях.
Дальше идет четвертая сущность и в ней 7 элементов.
18+7 > 20. поэтому четвертую сущность мы уже не можем упаковать в группу не больше 20 элементов.
поэтому первая группа будет из трех сущностей с 18 их элементами.
вторая группа начнется с четвертой сущности и закончится на той, которая в свою очередь также переполнить ограничение в 20.

аналогия реальной жизни:
очередь на аттракцион, причем в очереди нельзя обогнать/пропустить.
подходит к кабинке человек, садиться, второй и третий - парень с девушкой, садяться, в люльке остается 1 место. следующей идет семейная пара с ребенком. место одно, а их трое. люлька уезжает, подъезжает следующая, они садяться, а к ним садится еще один человек ТОЛЬКО при условии, что он - одиночка... если не одиночка, места нет и семья уезжает в люльке с одним пустым местом.

в этом примере размер группы 4. Сущность и элемент - одно и то же - человек. Если в этот пример кроме кол-ва мест добавить ограничение по весу, то человек станет сущностью, а вес - элементом.
10 май 16, 14:57    [19153452]     Ответить | Цитировать Сообщить модератору
 Re: наведите на мысль. разбить множество групп на примерно одинакового размера  [new]
Konst_One
Member

Откуда:
Сообщений: 11562
какая то странная задача, это случаем не комбинаторика разбиений?
10 май 16, 15:01    [19153489]     Ответить | Цитировать Сообщить модератору
 Re: наведите на мысль. разбить множество групп на примерно одинакового размера  [new]
Konst_One
Member

Откуда:
Сообщений: 11562
делайте курсором с проверкой на текущую накопительную сумму, может потом кто и в один запрос засунет
10 май 16, 15:03    [19153502]     Ответить | Цитировать Сообщить модератору
 Re: наведите на мысль. разбить множество групп на примерно одинакового размера  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4807
Дмитрий_099098,

Вы ABC анализ что ли делаете? Тут была тема про это
10 май 16, 15:07    [19153531]     Ответить | Цитировать Сообщить модератору
 Re: наведите на мысль. разбить множество групп на примерно одинакового размера  [new]
invm
Member

Откуда: Москва
Сообщений: 9633
Дмитрий_099098
берем первую сущность, там 2 элемента. берем вторую, там 9, третью там 7. итого 18 элементов в первых трех сущностях.
Дальше идет четвертая сущность и в ней 7 элементов.
18+7 > 20. поэтому четвертую сущность мы уже не можем упаковать в группу не больше 20 элементов.
поэтому первая группа будет из трех сущностей с 18 их элементами.
вторая группа начнется с четвертой сущности и закончится на той, которая в свою очередь также переполнить ограничение в 20.
Осмыслить не пробовали - 19153290?
10 май 16, 15:07    [19153535]     Ответить | Цитировать Сообщить модератору
 Re: наведите на мысль. разбить множество групп на примерно одинакового размера  [new]
Дмитрий_099098
Guest
Konst_One
какая то странная задача, это случаем не комбинаторика разбиений?

нет. это такой специфический отчет.
комбинаторики тут как раз нет. есть порядок очереди и просто отсекать."упаковывать" но это не задача о рюкзаке. это всего лишь нарезка.
tsql не подходит, (система только запросы понимает), а на сервере сиквельном серваке писать хранимки нельзя...
просто в явном виде это работа с множествами, вот чуется, но в упор ничего не придумывается.

в принципе отчет у меня уже лет 5 работал без особых изменений.
как раз на группы делил чисто математически играл с скользячими каунтами и тд.
приемлемый был результат, тк сущности были плюс-минус одного размера и в среднем 5-6 штук упаковывалось в необходимую "люльку". а сейчас кол-во типов увеличилось до 10 и группы в итоге именно по этой логике стали от 2 до 15 сущностей (у меня "люлька" на 42 элемента) и старый способ дает то пусто, то густо, то есть то в "люльку" из 42 мест засаживает 6 человек, а то 70...
ну как-то так.
очень заморочено я объясняю, должен извиниться, что приходиться чуть ли не ломать мозг чтобы понять эти абстракции(((
10 май 16, 15:22    [19153628]     Ответить | Цитировать Сообщить модератору
 Re: наведите на мысль. разбить множество групп на примерно одинакового размера  [new]
Konst_One
Member

Откуда:
Сообщений: 11562
Дмитрий_099098
tsql не подходит, (система только запросы понимает), а на сервере сиквельном серваке писать хранимки нельзя...


если ничего нельзя, то что вы хотите тут на форуме получить?
10 май 16, 15:28    [19153650]     Ответить | Цитировать Сообщить модератору
 Re: наведите на мысль. разбить множество групп на примерно одинакового размера  [new]
Дмитрий_099098
Guest
invm,да, пробовал. собственно так и считалось до того как кол-во типов внутри не стало прыгать сильно.
группа entity кол-во элементов колв-во нараст. Итогом runningcount / 20 + 1
1 a 5 5 1
2 b 17 22 2
3 c 19 41 3
4 d 12 53 3
5 e 10 63 4
5 f 4 67 4
5 g 5 72 4

Вот общий пример. выше.
да подход самый простой. и кстати работает на "случайной генерации". но на выборке выше уже не работает.e,f,g попали в одну группу.
спасибо за участие.
10 май 16, 16:17    [19154057]     Ответить | Цитировать Сообщить модератору
 Re: наведите на мысль. разбить множество групп на примерно одинакового размера  [new]
Дмитрий_099098
Guest
более того, чем больше таких сущность "a" как в примере будет зажато между крупными блоками, тем быстрее будет иди сбой в простыне(самих сущностей в принципе довольно)много. да, в моих условиях типов не больше 10(пока), но это ошибку переносит с 4 группы(ошибочно вычисленной) на 8ую...
мысли были как-то покрутить таблицу сджоиненую на себя три-4 раза(сущность на последующую сущность id=id-1 типа того), чтобы посчитать сумму элементов соседних сущностей... короче в эту сторону...будем думать дальше.
10 май 16, 16:25    [19154130]     Ответить | Цитировать Сообщить модератору
 Re: наведите на мысль. разбить множество групп на примерно одинакового размера  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4807
Дмитрий_099098
более того, чем больше таких сущность "a" как в примере будет зажато между крупными блоками, тем быстрее будет иди сбой в простыне(самих сущностей в принципе довольно)много. да, в моих условиях типов не больше 10(пока), но это ошибку переносит с 4 группы(ошибочно вычисленной) на 8ую...
мысли были как-то покрутить таблицу сджоиненую на себя три-4 раза(сущность на последующую сущность id=id-1 типа того), чтобы посчитать сумму элементов соседних сущностей... короче в эту сторону...будем думать дальше.


Я попытался набросать запрос для решения вашей задачи, но уперся вот в какую проблему: как только мы относим некоторое кол-во элементов в группу, расчет для последующих надо сдвигать. Тут возможен 1) курсор 2) рекурсивный запрос 3) сохранение промежуточного результата в таблицу в последующей серией обновлений в цикле,

но вот так в лоб оконными функциями не решается.
10 май 16, 16:42    [19154251]     Ответить | Цитировать Сообщить модератору
 Re: наведите на мысль. разбить множество групп на примерно одинакового размера  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4807
Дмитрий_099098,

Если у вас размер группы небольшой (скажем 20), то можно написать попробовать запрос с 20 вложениями, но сами понимаете это имеет ограничения.
10 май 16, 16:44    [19154271]     Ответить | Цитировать Сообщить модератору
 Re: наведите на мысль. разбить множество групп на примерно одинакового размера  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
Дмитрий_099098,

create table #t (num varchar(1), cnt int)
insert #t values
('a',	9),
('b',	7),
('c',	9),
('d',	3),
('e',	7),
('f',	4),
('g',	5)

select *, 
sum(cnt) over(order by num) / 19 группа
from #t
10 май 16, 16:50    [19154330]     Ответить | Цитировать Сообщить модератору
 Re: наведите на мысль. разбить множество групп на примерно одинакового размера  [new]
a_voronin
Member

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

Ответ неверный
create table #t (num varchar(1), cnt int)
insert #t values
('a',	9),
('b',	7),
('c',	12),
('d',	9),
('e',	7),
('f',	4),
('g',	5)

select *, 
sum(cnt) over(order by num) / 19 группа,
SUM(cnt) OVER (ORDER BY cnt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal 
from #t
ORDER BY sum(cnt) over(order by num) / 19
GO 

DROP table #t
10 май 16, 17:04    [19154443]     Ответить | Цитировать Сообщить модератору
 Re: наведите на мысль. разбить множество групп на примерно одинакового размера  [new]
Konst_One
Member

Откуда:
Сообщений: 11562
это не будет работаь на 2008 сервере
10 май 16, 17:05    [19154459]     Ответить | Цитировать Сообщить модератору
 Re: наведите на мысль. разбить множество групп на примерно одинакового размера  [new]
Дмитрий_099098
Guest
группа 40-42 элемента.сущность из 1 до 10 элементов.
да... спасибо за внимание... похоже с кондачка решить не получится.
тскулем решается. просто. да. но у меня ограничения.
10 май 16, 17:31    [19154605]     Ответить | Цитировать Сообщить модератору
 Re: наведите на мысль. разбить множество групп на примерно одинакового размера  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
a_voronin
WarAnt,

Ответ неверный


Да согласен проглядел пару моментов:0

тогда вот так

create table #t (num varchar(1), cnt int)
insert #t values
('a',	5),
('b',	22),
('c',	41),
('d',	53),
('e',	63),
('f',	67),
('g',	72)
select *,
round ((((lag(cnt, 1, 0) over(order by num) * 1.0) % 20 + cnt * 1.0) / 20), 0) + 1, -- для 2008+
round ((((isnull((select top 1 cnt from #t where num < t.num order by num desc), 0) * 1.0) % 20 + cnt * 1.0) / 20), 0) + 1 --для всех
from #t t
10 май 16, 17:55    [19154758]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить