Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 формирование строк по условию  [new]
,,,^..^,,,
Guest
доброго всем дня!
есть таблицы вида
create table #t1(id int identity(1, 1), name varchar(10))

insert #t1(name)
values	('10'), ('максим'), ('56'), ('вася'), ('13'), ('даша'), ('8000'), ('76'), ('123'), ('12222'), ('44'), ('нет'), ('20'), ('14444'), ('1444455')

create table #t2(id int identity(1, 1), list varchar(50))


необходимо в порядке возрастания id из таблицы #t1 "склеивать" поле name через разделитель (например, пробел), до тех пор, пока сформированная склеенная строка не превышает размерность поля list из #t2, при этом "огрызков" строк быть не должно, т.е. если очередное name не помещается целиком, с него следует начать формировать следующую строку для #t2.

возможно ли реализовать такое без CTE, на одних джойнах?
21 апр 15, 10:05    [17542570]     Ответить | Цитировать Сообщить модератору
 Re: формирование строк по условию  [new]
Gviber
Member

Откуда:
Сообщений: 124
Возможно.

Через xml:
select
STUFF(ISNULL(CAST(( 
	SELECT ','+cast(Col as varchar)
	FROM mytable p
	order by col
	for xml path('') 
) as xml).value('.', 'varchar(max)'),''),1,1,'')
21 апр 15, 10:45    [17542770]     Ответить | Цитировать Сообщить модератору
 Re: формирование строк по условию  [new]
,,,^..^,,,
Guest
основная задача - разбить на группы перед склейкой строк. а склеить можно хоть через for xml path, хоть курсором, хоть через собственную агрегирующую CLR-функцию.

суть задачи - верно распределить на группы, которые бы помещались в поле list
21 апр 15, 10:52    [17542794]     Ответить | Цитировать Сообщить модератору
 Re: формирование строк по условию  [new]
invm
Member

Откуда: Москва
Сообщений: 9646
,,,^..^,,,
суть задачи - верно распределить на группы
select
 *,
 sum(len(name) + <длина разделителя>) over (order by id) / <размерность поля list> as grp
from
 #t1;
21 апр 15, 11:02    [17542834]     Ответить | Цитировать Сообщить модератору
 Re: формирование строк по условию  [new]
Gviber
Member

Откуда:
Сообщений: 124
Вам нужно для начала определиться какой алгоритм разбиения на группы.

Есть стандартная оконная функция:
select Name,NTILE(ZN) OVER(order by id) from table

где ZN количество элементов в группе. Подходит ли это разбиение?
21 апр 15, 11:04    [17542844]     Ответить | Цитировать Сообщить модератору
 Re: формирование строк по условию  [new]
ЦБ
Member [заблокирован]

Откуда:
Сообщений: 2773
invm
,,,^..^,,,
суть задачи - верно распределить на группы
select
 *,
 sum(len(name) + <длина разделителя>) over (order by id) / <размерность поля list> as grp
from
 #t1;
Имхо, в общем случае, будет "лажать"
Вот, все слова - 4 символа, "размерность поля list"=5, разделителя - нет
select 4/5, (4+4)/5, (4+4+4)/5, (4+4+4+4)/5, (4+4+4+4+4)/5, (4+4+4+4+4+4)/5

----------- ----------- ----------- ----------- ----------- -----------
0 1 2 3 4 4

А по алгоритму ТС, все слова должны попадать в разные группы
21 апр 15, 11:13    [17542879]     Ответить | Цитировать Сообщить модератору
 Re: формирование строк по условию  [new]
,,,^..^,,,
Guest
ЦБ,

да, я простым делением тоже на эти грабли наступил, поэтому сюда и написал
21 апр 15, 11:38    [17543038]     Ответить | Цитировать Сообщить модератору
 Re: формирование строк по условию  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
,,,^..^,,,, попробовал я реализовать вариант, и как то пока не получается через джойны. А почему нельзя CTE использовать? И, если уж нет возможности использовать CTE, возможно через цикл while сделать?
21 апр 15, 11:39    [17543047]     Ответить | Цитировать Сообщить модератору
 Re: формирование строк по условию  [new]
Добрый Э - Эх
Guest
,,,^..^,,,,

задача - боян, решений - есть несколько. искать в тырнете по фразе "Reset Running Total"
21 апр 15, 11:40    [17543052]     Ответить | Цитировать Сообщить модератору
 Re: формирование строк по условию  [new]
,,,^..^,,,
Guest
Gviber
Вам нужно для начала определиться какой алгоритм разбиения на группы.

Есть стандартная оконная функция:
select Name,NTILE(ZN) OVER(order by id) from table

где ZN количество элементов в группе. Подходит ли это разбиение?


нет, эта функция не подходит вообще. изначально неизвестно сколько элементов в группу попадает ведь. группа может быть неполной, если следующее слово целиком не помещается и т.д.
21 апр 15, 11:41    [17543057]     Ответить | Цитировать Сообщить модератору
 Re: формирование строк по условию  [new]
,,,^..^,,,
Guest
Добрый Э - Эх
искать в тырнете по фразе "Reset Running Total"


все эти задачи подразумевают изменение значения в определенном уже существующем столбце в таблице.
в моей задаче этот столбец вычисляется.
CTE использовать нельзя.
21 апр 15, 11:55    [17543143]     Ответить | Цитировать Сообщить модератору
 Re: формирование строк по условию  [new]
Добрый Э - Эх
Guest
,,,^..^,,,
все эти задачи подразумевают .
значит ты, всё же, НЕ ВСЕ решения посмотрел... Было там от Ицека Бен Гана. Вот только с ходу найти его не могу, по памяти воспроизвести - тоже.
21 апр 15, 12:00    [17543183]     Ответить | Цитировать Сообщить модератору
 Re: формирование строк по условию  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
Добрый Э - Эх, вот об этом речь?

http://stackoverflow.com/questions/22235959/window-functions-running-total-with-reset
21 апр 15, 12:13    [17543265]     Ответить | Цитировать Сообщить модератору
 Re: формирование строк по условию  [new]
Gviber
Member

Откуда:
Сообщений: 124
Сложная задача.

Тянет на 4-5 балов на известном сайте.

Думаю тут нужно отталкиваться от этого:


	select t1.id i1,t2.id i2,sm
	from #t1 t1 join #t1 t2 on t1.id<t2.id
	outer apply (select SUM(LEN(name)+1)-1 from #t1 where id between t1.id and t2.id) S(sm)
	where sm<=10

Где i1 начало последовательности, i2 конец.

И нужно найти множества которые не пересекаются.
21 апр 15, 12:57    [17543502]     Ответить | Цитировать Сообщить модератору
 Re: формирование строк по условию  [new]
Добрый Э - Эх
Guest
,,,^..^,,,
возможно ли реализовать такое без CTE?
чем вызвано стремление решить задачу без СТЕ ?
21 апр 15, 13:50    [17543747]     Ответить | Цитировать Сообщить модератору
 Re: формирование строк по условию  [new]
Добрый Э - Эх
Guest
Minamoto,

речь об этом, но сейчас вижу - не совсем тот случай, что у ТС...
21 апр 15, 13:50    [17543750]     Ответить | Цитировать Сообщить модератору
 Re: формирование строк по условию  [new]
o-o
Guest
Добрый Э - Эх
,,,^..^,,,
возможно ли реализовать такое без CTE?
чем вызвано стремление решить задачу без СТЕ ?

наверное, имеется в виду без рекурсии,
потому как нет никаких проблем позаменять CTE на соответствующие подзапросы
21 апр 15, 13:56    [17543784]     Ответить | Цитировать Сообщить модератору
 Re: формирование строк по условию  [new]
,,,^..^,,,
Guest
o-o
наверное, имеется в виду без рекурсии

верно, без рекурсии. под CTE я подразумевал возможность рекурсии, а не упрощение записи :-)
21 апр 15, 14:11    [17543893]     Ответить | Цитировать Сообщить модератору
 Re: формирование строк по условию  [new]
Gviber
Member

Откуда:
Сообщений: 124
Может правильно, а может и нет. Сложно сказать.
with T as
(
	select t1.id i1,MAX(t2.id) i2,MAX(sm) sm,ROW_NUMBER() OVER(order by t1.id,MAX(t2.id))%2 Ch
	from #t1 t1 join #t1 t2 on t1.id<=t2.id
	outer apply (select SUM(LEN(name)+1)-1 from #t1 where id between t1.id and t2.id) S(sm)
	where sm<=10
	group by t1.id
)
select * from
(
	select IIF( LAG(i2) OVER(order by i1,i2) >= i1,i1+1,i1) i1,i2 from
	(
		select i1,i2,sm,LEAD(NxtD) OVER(order by i1,i2) prevN,LAG(NxtD) OVER(order by i1,i2) nxtN,Ch from
		(
			select i1,i2,sm,IIF (LEAD(i1) OVER(order by i1,i2) between i1 and i2  ,1,0) NxtD,Ch 
			from T T1
		) Z3
	) Z4
	where not(prevN=1 and nxtN=1 and ch=0)
) Z4
where i2>=i1
order by i1,i2

Результат генерирует последовательности.
21 апр 15, 15:01    [17544263]     Ответить | Цитировать Сообщить модератору
 Re: формирование строк по условию  [new]
Gviber
Member

Откуда:
Сообщений: 124
Неправильно генерирует (похоже но не то).
21 апр 15, 15:11    [17544347]     Ответить | Цитировать Сообщить модератору
 Re: формирование строк по условию  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
,,,^..^,,,, в общем, пока вариантов без рекурсии нет ) Если версия сервера не позволяет использовать CTE, то можно использовать while.
23 апр 15, 15:14    [17554564]     Ответить | Цитировать Сообщить модератору
 Re: формирование строк по условию  [new]
ЦБ
Member [заблокирован]

Откуда:
Сообщений: 2773
,,,^..^,,,
основная задача - разбить на группы перед склейкой строк.

Если можно немного "подправить" структуру (а если нельзя, тогда через времянку с этими двумя доп.полями),
то через переменные можно справиться

use tempdb
go

IF OBJECT_ID('tempdb..#t1') IS NOT NULL
    DROP TABLE #t1
--

create table #t1(id int identity(1, 1), name varchar(10), gr int, TotalRunLen int)

insert #t1(name)values ('10')
insert #t1(name)values ('максим')
insert #t1(name)values ('56')
insert #t1(name)values ('вася')
insert #t1(name)values ('13')
insert #t1(name)values ('даша')
insert #t1(name)values ('8000')
insert #t1(name)values ('76')
insert #t1(name)values ('123')
insert #t1(name)values ('12222')
insert #t1(name)values ('44')
insert #t1(name)values ('нет')
insert #t1(name)values ('20')
insert #t1(name)values ('14444')
insert #t1(name)values ('1444455')

--------------------------------------
declare @ListLen int;		set @ListLen = 20	--	Размер поля "получателя"
declare @Delim varchar(10);	set @Delim = '_'	--	Разделитель	

declare @gr int;			set @gr = 0
declare @TotalRunLen int;	set @TotalRunLen = 0

--------------------------------------
;with cte as
	(select top(2147483647) * from #t1 order by id)

update cte
set 
	 @gr = gr = @gr +	
				case 
					when @TotalRunLen+len(name) = @ListLen then 0 
					when @TotalRunLen+len(name)+len(@Delim) > @ListLen then 1 
					else 0
				end

	,@TotalRunLen = TotalRunLen =	
				case 
					when @TotalRunLen+len(name) = @ListLen then @TotalRunLen+len(name)
					when @TotalRunLen+len(name)+len(@Delim) > @ListLen then len(name)+len(@Delim) 
					else @TotalRunLen+len(name)+len(@Delim) 
				end
--output inserted.*

select * from #t1

id          name       gr          TotalRunLen
----------- ---------- ----------- -----------
1           10         0           3
2           максим     0           10
3           56         0           13
4           вася       0           18
5           13         0           20
6           даша       1           5
7           8000       1           10
8           76         1           13
9           123        1           17
10          12222      2           6
11          44         2           9
12          нет        2           13
13          20         2           16
14          14444      3           6
15          1444455    3           14

(15 row(s) affected)
28 апр 15, 01:18    [17573043]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить