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

Откуда:
Сообщений: 52
есть такая табличка:

F1 | F2
dat1 | Gdat1
dat2 | Gdat2
dat3 | Gdat2
dat4 | Gdat1
dat5 | Gdat2

требуется по столбцу F2
собрать строку, но при этом удалять смежные повторяющиеся значения
т.е. на выходе должна получиться строка вида: Gdat1-Gdat2-Gdat1-Gdat2

сервер 2005
23 май 11, 17:10    [10695862]     Ответить | Цитировать Сообщить модератору
 Re: Курсор или distinct по group by?  [new]
Начинающий SQL 2008
Member

Откуда:
Сообщений: 438
chamanakol,

???

declare @s varchar(max), @xml_str xml

set @s = 'USD,EUR,RUB,USD,EUR,RUB,USD'

set @xml_str = '<root><row val="' + replace(@s, ',', ',"/><row val="') + ',"/></root>'

;with cte as
(
 select row_number() over(order by (select 0)) [num], f.value('.', 'varchar(max)') [data()] from @xml_str.nodes('/root/row/@val') R(f)
) 
select @s = (select [data()] from cte group by [data()] order by min(num) for xml path(''))

select left(@s,len(@s)-1)

Это можно обернуть в InLine - функцию
23 май 11, 17:12    [10695874]     Ответить | Цитировать Сообщить модератору
 Re: Курсор или distinct по group by?  [new]
chamanakol
Member

Откуда:
Сообщений: 52
похоже на раздувание из мухи слона
23 май 11, 17:26    [10695971]     Ответить | Цитировать Сообщить модератору
 Re: Курсор или distinct по group by?  [new]
Начинающий SQL 2008
Member

Откуда:
Сообщений: 438
chamanakol
похоже на раздувание из мухи слона

Почему?


F1 | F2
dat1 | Gdat1
dat2 | Gdat2
dat3 | Gdat2
dat4 | Gdat1
dat5 | Gdat2

напишите в виде
declare @t table...
insert @t values ...
23 май 11, 17:40    [10696072]     Ответить | Цитировать Сообщить модератору
 Re: Курсор или distinct по group by?  [new]
chamanakol
Member

Откуда:
Сообщений: 52
Начинающий SQL 2008,
спасибо за ответ, но я уже наткнулся на такой же вопрос:
https://www.sql.ru/forum/actualthread.aspx?tid=376632&hl=%f1%f2%ee%eb%e1%e5%f6%20%f1%f2%f0%ee%ea%f3

я думал можно как нибудь обойтись без сбора-разбора строк.
23 май 11, 17:58    [10696204]     Ответить | Цитировать Сообщить модератору
 Re: Курсор или distinct по group by?  [new]
Prolog
Member

Откуда: Москва
Сообщений: 2793
;with t as (select * from @t t1 where F2 <> coalesce((select top 1 F2 from @t t2 where t2.F1 < t1.F1 order by t2.F1 desc),''))
 select stuff((select '-'+F2 from t order by F1 for xml path('')),1,1,'') 
23 май 11, 17:59    [10696215]     Ответить | Цитировать Сообщить модератору
 Re: Курсор или distinct по group by?  [new]
iljy
Member

Откуда:
Сообщений: 8711
chamanakol,

ну да, собрать строку без сбора строк - это круто придумано
declare @t table (F1 varchar(30), F2 varchar(30))
insert @t
select 'dat1', 'Gdat1' union all
select 'dat2', 'Gdat2' union all
select 'dat3', 'Gdat2' union all
select 'dat4', 'Gdat1' union all
select 'dat5', 'Gdat2'

;with cte as
(select *, ROW_NUMBER() over(order by F1) RN from @t)
select stuff((select '-' + t1.F1 from
			cte t1 left join cte t2 on t1.RN+1 = t2.RN
			where t2.F2 != t1.F2 or t2.F2 is null
			for xml path('')),1,1,'')
23 май 11, 18:14    [10696311]     Ответить | Цитировать Сообщить модератору
 Re: Курсор или distinct по group by?  [new]
chamanakol
Member

Откуда:
Сообщений: 52
iljy,
угу, и получилось: dat1-dat3-dat4-dat5
а должно: Gdat1-Gdat2-Gdat1-Gdat2
всего одна цифра неверная.

но вопрос не в этом.

я проверил какой метод окажется более производительным (метод с множественными CASEами и два предложенных с CTE)
результат практически одинаков.

вот и зачем тогда городить CTE?

использую так: есть порядка 5 тыщ деталей с техпроцессами. нужно по информации из техпроцессов состряпать маршут изготовления по цехам. я создаю скалярную функцию, которая мне по номеру техпроцесса возвращает строку (типа того как в шапке) с маршрутом. в разных хранимых процедурах я цепляю эту функцию и получаю что хочу.
проблема в том, что для 5000 деталей все методы работают медленно (около минуты), что меня как-то не совсем устраивает.
24 май 11, 12:11    [10699112]     Ответить | Цитировать Сообщить модератору
 Re: Курсор или distinct по group by?  [new]
Glory
Member

Откуда:
Сообщений: 104751
chamanakol

вот и зачем тогда городить CTE?

Для того, чтобы получить результат в одной строке ?
24 май 11, 12:19    [10699169]     Ответить | Цитировать Сообщить модератору
 Re: Курсор или distinct по group by?  [new]
chamanakol
Member

Откуда:
Сообщений: 52
щас крутится мысль как нибудь заджойнить копии таблицы со сдвигом на одно значение, получить булевый столбец (равно не равно) и потом по нему фильтрануть и собрать в строку наставив разделителей
24 май 11, 12:21    [10699180]     Ответить | Цитировать Сообщить модератору
 Re: Курсор или distinct по group by?  [new]
iljy
Member

Откуда:
Сообщений: 8711
chamanakol
я проверил какой метод окажется более производительным (метод с множественными CASEами и два предложенных с CTE)
результат практически одинаков.

вот и зачем тогда городить CTE?

С какими еще CASEами?? И чем вам СТЕ не угодили? В моем примере он вообще используется, чтоб два раза одно и то же не писать, запросто убрать можно.
chamanakol
щас крутится мысль как нибудь заджойнить копии таблицы со сдвигом на одно значение, получить булевый столбец (равно не равно) и потом по нему фильтрануть и собрать в строку наставив разделителей

Если вы внимательно посмотрите на мой вариант, то увидите, что он ровно это и делает. А 5000 записей - это вообще слезы, при нормальных индексах и планах должно мгновенно отрабатывать. Счас запустил подобное на своем домашнем компе, 10к строк прожевала за 100мс.
24 май 11, 12:28    [10699239]     Ответить | Цитировать Сообщить модератору
 Re: Курсор или distinct по group by?  [new]
chamanakol
Member

Откуда:
Сообщений: 52
Glory
chamanakol
вот и зачем тогда городить CTE?

Для того, чтобы получить результат в одной строке ?

в основном CTE часто используют чтобы из столбцов собирать строки, т.к. легко получать что-то типа рекурсии. поэтому, я думаю, мне его и предложили как вариант.
24 май 11, 12:29    [10699249]     Ответить | Цитировать Сообщить модератору
 Re: Курсор или distinct по group by?  [new]
Glory
Member

Откуда:
Сообщений: 104751
chamanakol
щас крутится мысль как нибудь заджойнить копии таблицы со сдвигом на одно значение,

На одно значение в каком поле ? А "сдвиг" - это что за операция ?
24 май 11, 12:30    [10699251]     Ответить | Цитировать Сообщить модератору
 Re: Курсор или distinct по group by?  [new]
Glory
Member

Откуда:
Сообщений: 104751
chamanakol
Glory
пропущено...

Для того, чтобы получить результат в одной строке ?

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

Ну так ваша постановка задачи "т.е. на выходе должна получиться строка вида: Gdat1-Gdat2-Gdat1-Gdat2" ?
24 май 11, 12:31    [10699260]     Ответить | Цитировать Сообщить модератору
 Re: Курсор или distinct по group by?  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
chamanakol
в основном CTE часто используют чтобы из столбцов собирать строки, т.к. легко получать что-то типа рекурсии. поэтому, я думаю, мне его и предложили как вариант.
Вам никто ни разу не предлагал рекурсивное CTE
Что-то Вы не то говорите
24 май 11, 12:34    [10699287]     Ответить | Цитировать Сообщить модератору
 Re: Курсор или distinct по group by?  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
chamanakol
iljy,
угу, и получилось: dat1-dat3-dat4-dat5
а должно: Gdat1-Gdat2-Gdat1-Gdat2



;with cte as
(select *, ROW_NUMBER() over(order by F1) RN from @t)
select stuff((select '-' + t1.F2 from
			cte t1 left join cte t2 on t1.RN+1 = t2.RN
			where t2.F2 != t1.F2 or t2.F2 is null
			for xml path('')),1,1,'')
а самомму проверить ? все корректно получаеться
24 май 11, 12:45    [10699385]     Ответить | Цитировать Сообщить модератору
 Re: Курсор или distinct по group by?  [new]
chamanakol
Member

Откуда:
Сообщений: 52
iljy,
извиняюсь, видимо про сдвиг на +1 у меня мысль закрутилась, когда я Ваш запрос разбирал.
видимо дело не только в производительности запроса. приложение на WinForms долго отрисовывает полученные данные. ещё один столбец с довольно длинными строками его сильно напрягает.
24 май 11, 12:53    [10699452]     Ответить | Цитировать Сообщить модератору
 Re: Курсор или distinct по group by?  [new]
chamanakol
Member

Откуда:
Сообщений: 52
iljy
А 5000 записей - это вообще слезы, при нормальных индексах и планах должно мгновенно отрабатывать. Счас запустил подобное на своем домашнем компе, 10к строк прожевала за 100мс.

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

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

p.s. а про рекурсию я просто в контексте упомянул. хорошая вещь при работе с деревьями всякими
24 май 11, 13:11    [10699618]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить