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

Откуда: Харьков
Сообщений: 301
Трудолюбивый Незнайка, спрашивает всех самых сильных сиквелистов
Есть вот такая табличка #T. Очень хочется получить ответ как из #R то есть сгруппировать по первым двум полям и набрать по разбиению некоторую динамическую переменную. Можно ли сделать в один запрос? В запасе есть курсор и временная табличка но хотелось бы изящнее.

IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
IF OBJECT_ID('tempdb.dbo.#R') IS NOT NULL DROP TABLE #R

CREATE TABLE #T ( O INT, T INT, C NCHAR(3))
INSERT INTO #T (O,T,C) VALUES (3,4,'RUS')
INSERT INTO #T (O,T,C) VALUES (1,2,'CAN')
INSERT INTO #T (O,T,C) VALUES (3,4,'KAZ')
INSERT INTO #T (O,T,C) VALUES (1,2,'USA')
INSERT INTO #T (O,T,C) VALUES (1,4,'BEL')
INSERT INTO #T (O,T,C) VALUES (3,4,'LAT')
INSERT INTO #T (O,T,C) VALUES (1,4,'GOR')
INSERT INTO #T (O,T,C) VALUES (1,4,'UKR')

--SELECT * FROM #T
CREATE TABLE #R (O INT, T INT, CL NVARCHAR(MAX))
INSERT INTO #R (O,T,CL) VALUES (1, 2, 'CAN USA')
INSERT INTO #R (O,T,CL) VALUES (3, 4, 'RUS KAZ LAT')
INSERT INTO #R (O,T,CL) VALUES (1, 4, 'UKR BEL GOR')
--Вот такой ответ хотелось бы получить. Как результат запроса. Одного запроса.
SELECT * FROM #R

--Это размышления вслух
SELECT O,T,C FROM #T ORDER BY ROW_NUMBER() OVER (PARTITION BY O,T,C ORDER BY C)

IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
IF OBJECT_ID('tempdb.dbo.#R') IS NOT NULL DROP TABLE #R

Заранее, спасибо.
4 авг 11, 23:13    [11073684]     Ответить | Цитировать Сообщить модератору
 Re: Почти олимпиадная задача по PARTITION BY  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
albert t, FAQ. Если версия сервера позволяет, а она судя по PARTITION BY - позволяет, то одним запросом, с использованием
... for xml path('')
5 авг 11, 00:29    [11073911]     Ответить | Цитировать Сообщить модератору
 Re: Почти олимпиадная задача по PARTITION BY  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
albert t, на олимпиадную не тянет. Делов на 5-10 минут, если не спешить.
5 авг 11, 00:33    [11073916]     Ответить | Цитировать Сообщить модератору
 Re: Почти олимпиадная задача по PARTITION BY  [new]
Elena_HB
Member

Откуда:
Сообщений: 6
Доброй ночи,

так можно получить #R из #T

select distinct O,T,NEWDATA.C from #T t1
CROSS APPLY (select C+' ' from #T t2
where t1.O=t2.O and t1.T=t2.T
order by O,T
FOR XML PATH('')) AS NEWDATA(C)
5 авг 11, 00:41    [11073932]     Ответить | Цитировать Сообщить модератору
 Re: Почти олимпиадная задача по PARTITION BY  [new]
albert t
Member

Откуда: Харьков
Сообщений: 301
Elena_HB, kDnZP

Спасибо. Новое и полезное детектед.
5 авг 11, 02:12    [11073995]     Ответить | Цитировать Сообщить модератору
 Re: Почти олимпиадная задача по PARTITION BY  [new]
Andrey Sribnyak
Member

Откуда: Киев
Сообщений: 600
Для коллекции, через cte


IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
IF OBJECT_ID('tempdb.dbo.#R') IS NOT NULL DROP TABLE #R

CREATE TABLE #T ( O INT, T INT, C NCHAR(3))
INSERT INTO #T (O,T,C) VALUES (3,4,'RUS')
INSERT INTO #T (O,T,C) VALUES (1,2,'CAN')
INSERT INTO #T (O,T,C) VALUES (3,4,'KAZ')
INSERT INTO #T (O,T,C) VALUES (1,2,'USA')
INSERT INTO #T (O,T,C) VALUES (1,4,'BEL')
INSERT INTO #T (O,T,C) VALUES (3,4,'LAT')
INSERT INTO #T (O,T,C) VALUES (1,4,'GOR')
INSERT INTO #T (O,T,C) VALUES (1,4,'UKR')

--SELECT * FROM #T
CREATE TABLE #R (O INT, T INT, CL NVARCHAR(MAX))
INSERT INTO #R (O,T,CL) VALUES (1, 2, 'CAN USA')
INSERT INTO #R (O,T,CL) VALUES (3, 4, 'RUS KAZ LAT')
INSERT INTO #R (O,T,CL) VALUES (1, 4, 'UKR BEL GOR')
--Вот такой ответ хотелось бы получить. Как результат запроса. Одного запроса.
SELECT * FROM #R

;with c0 as 
(select O,T,C, ROW_NUMBER() over (PARTITION  by O, T order by (select 1)) rnk from #t)
,
c1 as (select O,T,cast(C as nvarchar(50)) c, rnk from c0 where rnk=1
union all
select c0.O, c0.T , cast(c1.c +' ' +c0.c  as nvarchar (50)) as c, c0.rnk from c0 join c1 on c0.O=c1.O and c0.t=c1.t and c0.rnk=c1.rnk+1
)
,c2 as
(select *, MAX(rnk) over (partition by O, T) rm from c1)

select O, T, C from c2 where rm=rnk



IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
IF OBJECT_ID('tempdb.dbo.#R') IS NOT NULL DROP TABLE #R
5 авг 11, 11:53    [11075392]     Ответить | Цитировать Сообщить модератору
 Re: Почти олимпиадная задача по PARTITION BY  [new]
albert t
Member

Откуда: Харьков
Сообщений: 301
Ещё раз всем спасибо. Как побочный результат добиваю белые пятна по T-SQL with XML


Очень изящный вариант, на мой взгляд, с FOR XML PATH, но к сожалению беглый анализ производительности выявил победу за функцией. Да, да, я помню что в вопросе было про "одним запросом"

Последний же ответ заставляет восхититься устройством мозга автора, который способен держать такое в голове.
Производительность же последнего метода просто ужасающая Из 24 секунд все были за ним :)

USE [master]
IF OBJECT_ID('dbo.T') IS NOT NULL DROP TABLE T


CREATE TABLE T ( O INT, T INT, C NCHAR(3))
GO
DECLARE @curr INT SET @curr=0
WHILE @curr<300 BEGIN
INSERT INTO T (O,T,C) VALUES (FLOOR(RAND()*10),FLOOR(RAND()*10),'RUS')
INSERT INTO T (O,T,C) VALUES (FLOOR(RAND()*10),FLOOR(RAND()*10),'CAN')
INSERT INTO T (O,T,C) VALUES (FLOOR(RAND()*10),FLOOR(RAND()*10),'KAZ')
INSERT INTO T (O,T,C) VALUES (FLOOR(RAND()*10),FLOOR(RAND()*10),'USA')
INSERT INTO T (O,T,C) VALUES (FLOOR(RAND()*10),FLOOR(RAND()*10),'BEL')
INSERT INTO T (O,T,C) VALUES (FLOOR(RAND()*10),FLOOR(RAND()*10),'LAT')
INSERT INTO T (O,T,C) VALUES (FLOOR(RAND()*10),FLOOR(RAND()*10),'GOR')
INSERT INTO T (O,T,C) VALUES (FLOOR(RAND()*10),FLOOR(RAND()*10),'UKR')
SET @curr=@curr+1
END


IF OBJECT_ID('dbo.listfunc') IS NOT NULL DROP FUNCTION dbo.listfunc
GO
CREATE FUNCTION dbo.listfunc(@O INT, @T INT)
RETURNS NVARCHAR(100)
AS
BEGIN
DECLARE @list NVARCHAR(100)
SET @list=''
SELECT @list=@list+tab.C+' ' FROM dbo.T tab WHERE tab.O=@O AND tab.T=@T
RETURN @list
END

GO
DECLARE @s1 DATETIME, @s2 DATETIME, @s3 DATETIME, @s4 DATETIME
SET  @s1=GETDATE()
--Это размышления вслух
SELECT TOP 1 WITH TIES  O,T,dbo.listfunc(O,T) AS C FROM T ORDER BY ROW_NUMBER() OVER (PARTITION BY O,T ORDER BY C)

SET @s2=GETDATE()
SELECT DISTINCT O,T,NEWDATA.C FROM T T1 CROSS APPLY (SELECT C+' ' FROM T T2 WHERE T1.O=T2.O AND T1.T=T2.T FOR XML PATH('')) AS NEWDATA(C)

SET @s3=GETDATE()
;with c0 as 
(select O,T,C, ROW_NUMBER() over (PARTITION  by O, T order by (select 1)) rnk from t)
,
c1 as (select O,T,cast(C as nvarchar(50)) c, rnk from c0 where rnk=1
union all
select c0.O, c0.T , cast(c1.c +' ' +c0.c  as nvarchar (50)) as c, c0.rnk from c0 join c1 on c0.O=c1.O and c0.t=c1.t and c0.rnk=c1.rnk+1
)
,c2 as
(select *, MAX(rnk) over (partition by O, T) rm from c1)

select O, T, C from c2 where rm=rnk

SET @s4=GETDATE()

SELECT @s2-@s1
SELECT @s3-@s2
SELECT @s4-@s3


IF OBJECT_ID('dbo.T') IS NOT NULL DROP TABLE T
--IF OBJECT_ID('dbo.R') IS NOT NULL DROP TABLE R
IF OBJECT_ID('dbo.listfunc') IS NOT NULL DROP FUNCTION dbo.listfunc
IF OBJECT_ID('dbo.randintfunc') IS NOT NULL DROP FUNCTION dbo.randintfunc
9 авг 11, 22:40    [11094440]     Ответить | Цитировать Сообщить модератору
 Re: Почти олимпиадная задача по PARTITION BY  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
albert t, а чего рекурсивный CTE не потестили? ;) Потому как его забыли упомянуть?

* Работа с XML действительно не супер-быстрая, но как один из вариантов - вполне годится.
9 авг 11, 22:46    [11094454]     Ответить | Цитировать Сообщить модератору
 Re: Почти олимпиадная задача по PARTITION BY  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
kDnZP
albert t, а чего рекурсивный CTE не потестили? ;) Потому как его забыли упомянуть?

* Работа с XML действительно не супер-быстрая, но как один из вариантов - вполне годится.

Прошу прощения, внимательно вчитался и все нашел))).
9 авг 11, 22:47    [11094457]     Ответить | Цитировать Сообщить модератору
 Re: Почти олимпиадная задача по PARTITION BY  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
Кстати, вродь такой вариант работы с XML получается чувствительно пошустрее:
SELECT T1.O, T1.T, (SELECT T2.C [data()] FROM T T2 WHERE T1.O=T2.O AND T1.T=T2.T FOR XML PATH('')) C
FROM T T1
GROUP BY T1.O,T1.T
9 авг 11, 23:49    [11094622]     Ответить | Цитировать Сообщить модератору
 Re: Почти олимпиадная задача по PARTITION BY  [new]
iljy
Member

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

вы просто абсолютно некорректно тестируете. У вас запросы совершенно разные. Для начала их надо привести к одному виду, например так:
DECLARE @s1 DATETIME, @s2 DATETIME, @s3 DATETIME, @s4 DATETIME
SET  @s1=GETDATE()
--Запрос 1
SELECT O, T, dbo.listfunc(O,T) c
FROM T T1
GROUP BY O,T

SET @s2=GETDATE()
--Запрос 2
SELECT O, T, (SELECT C + ' ' FROM T T2 WHERE T1.O=T2.O AND T1.T=T2.T FOR XML PATH('')) c
FROM T T1
GROUP BY O,T

SET @s3=GETDATE()

SELECT @s2-@s1
SELECT @s3-@s2
Далее - проделайте простенький эксперимент и поменяйте запросы местами. Забавно, правда? Вы не учитываете время получения данных клиентом, а оно во время выполнения запроса входит.Убрать этот эффект можно так:
declare @O int, @t int, @c nvarchar(max)
DECLARE @s1 DATETIME, @s2 DATETIME, @s3 DATETIME, @s4 DATETIME
SET  @s1=GETDATE()
--Запрос 1
SELECT @O = O, @t = T, @c = dbo.listfunc(O,T)
FROM T T1
GROUP BY O,T

SET @s2=GETDATE()
--Запрос 2
SELECT @O = O, @t = T, @c = (SELECT C + ' ' FROM T T2 WHERE T1.O=T2.O AND T1.T=T2.T FOR XML PATH(''))
FROM T T1
GROUP BY O,T

SET @s3=GETDATE()

SELECT @s2-@s1
SELECT @s3-@s2
И получаем
Запрос 1 - 70мс, Запрос 2 - 27мс.
Но на самом деле это тоже некорректные данные - взгляните на планы. При работе с хмулем оптимизатор добавляет операцию Index Spool, и все объединения, кроме первого, выполняет уже над индексом, а не сканируя таблицу. Чтобы убрать этот эффект, создадим индекс сами:
create clustered index IX_T on T (O,T)
И получаем быстродействие обоих запросов порядка 20мс. На самом деле вы далеко не первый, кто этими вещами озаботился. А если еще и увеличить количество строк, скажем, в 10 раз (WHILE @curr<3000), не забыв изменить функцию на
IF OBJECT_ID('dbo.listfunc') IS NOT NULL DROP FUNCTION dbo.listfunc
GO
CREATE FUNCTION dbo.listfunc(@O INT, @T INT)
RETURNS NVARCHAR(max)
AS
BEGIN
DECLARE @list NVARCHAR(max)
SET @list=''
SELECT @list=@list+tab.C+' ' FROM dbo.T tab WHERE tab.O=@O AND tab.T=@T
RETURN @list
END
GO
, то результат получится и совсем даже противоположный. Запрос 1 - 167мс, Запрос 2 - 60мс. Вот так то
10 авг 11, 01:26    [11094801]     Ответить | Цитировать Сообщить модератору
 Re: Почти олимпиадная задача по PARTITION BY  [new]
albert t
Member

Откуда: Харьков
Сообщений: 301
Справедливые замечания. XML победил.

USE [master]
IF OBJECT_ID('dbo.T') IS NOT NULL DROP TABLE T
IF OBJECT_ID('dbo.listfunc') IS NOT NULL DROP FUNCTION dbo.listfunc
GO
CREATE FUNCTION dbo.listfunc(@O INT, @T INT)
RETURNS NVARCHAR(max)
AS
BEGIN
DECLARE @list NVARCHAR(max)
SET @list=''
SELECT @list=@list+tab.C+' ' FROM dbo.T tab WHERE tab.O=@O AND tab.T=@T
RETURN @list
END
GO

CREATE TABLE T ( O INT, T INT, C NCHAR(3))
CREATE CLUSTERED INDEX IX_T ON T (O,T)
DECLARE @curr INT SET @curr=0
WHILE @curr<3000 BEGIN
INSERT INTO T (O,T,C) VALUES (FLOOR(RAND()*10),FLOOR(RAND()*10),'RUS')
INSERT INTO T (O,T,C) VALUES (FLOOR(RAND()*10),FLOOR(RAND()*10),'CAN')
INSERT INTO T (O,T,C) VALUES (FLOOR(RAND()*10),FLOOR(RAND()*10),'KAZ')
INSERT INTO T (O,T,C) VALUES (FLOOR(RAND()*10),FLOOR(RAND()*10),'USA')
INSERT INTO T (O,T,C) VALUES (FLOOR(RAND()*10),FLOOR(RAND()*10),'BEL')
INSERT INTO T (O,T,C) VALUES (FLOOR(RAND()*10),FLOOR(RAND()*10),'LAT')
INSERT INTO T (O,T,C) VALUES (FLOOR(RAND()*10),FLOOR(RAND()*10),'GOR')
INSERT INTO T (O,T,C) VALUES (FLOOR(RAND()*10),FLOOR(RAND()*10),'UKR')
SET @curr=@curr+1
END

DECLARE @s1 DATETIME, @s2 DATETIME, @s3 DATETIME
DECLARE @oo INT, @tt INT, @cc NVARCHAR(MAX)

--SELECT @oo=O, @tt=T, @cc=C FROM T

SET  @s1=GETDATE()
--Запрос 1
SELECT @oo=O, @tt=T, @cc=dbo.listfunc(O,T)
FROM T
GROUP BY O,T

SET @s2=GETDATE()
--Запрос 2
SELECT @oo=O, @tt=T, @cc=(SELECT C + ' ' FROM T T2 WHERE T1.O=T2.O AND T1.T=T2.T FOR XML PATH(''))
FROM T T1
GROUP BY O,T

SET @s3=GETDATE()

SELECT @s2-@s1
SELECT @s3-@s2

SET  @s1=GETDATE()
--Запрос 1
SELECT @oo=O, @tt=T, @cc=(SELECT C + ' ' FROM T T2 WHERE T1.O=T2.O AND T1.T=T2.T FOR XML PATH(''))
FROM T T1
GROUP BY O,T

SET @s2=GETDATE()
--Запрос 2

SELECT @oo=O, @tt=T, @cc=dbo.listfunc(O,T) FROM T GROUP BY O,T

SET @s3=GETDATE()

SELECT @s2-@s1
SELECT @s3-@s2

IF OBJECT_ID('dbo.T') IS NOT NULL DROP TABLE T
10 авг 11, 14:12    [11097863]     Ответить | Цитировать Сообщить модератору
 Re: Почти олимпиадная задача по PARTITION BY  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
albert t, вместо USE [master] лучше писать USE [tempdb], т.к. устраивать свалку в мастере ИМХО не есть хорошо.
10 авг 11, 14:16    [11097914]     Ответить | Цитировать Сообщить модератору
 Re: Почти олимпиадная задача по PARTITION BY  [new]
albert t
Member

Откуда: Харьков
Сообщений: 301
kDnZP,
согласен.

Это не влезло
IF OBJECT_ID('dbo.listfunc') IS NOT NULL DROP FUNCTION dbo.listfunc

Индекс надо удалять, интересно?
10 авг 11, 14:20    [11097948]     Ответить | Цитировать Сообщить модератору
 Re: Почти олимпиадная задача по PARTITION BY  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
albert t, ну какбы при удалении таблиц - улетят и индексы с триггерами)))). Вам чуть ниже пример:
BEGIN TRANSACTION

CREATE TABLE test(id INT IDENTITY PRIMARY KEY, f INT)
CREATE INDEX idx ON test(f)
go

INSERT INTO test(f)
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
go
CREATE TRIGGER dbo.tstTrg ON test
    INSTEAD OF INSERT
AS
BEGIN
	INSERT INTO test(f) SELECT -1*f FROM INSERTED	
END
go
INSERT INTO test(f)
SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
go
SELECT * FROM test
-- Раскоментировать, чтобы проверить удаление всего, что висит на таблице (можно поглядев в системных объектах)
--DROP TABLE test
go
ROLLBACK TRANSACTION -- Тут окончательно все почитим
-- SELECT * FROM test -- Уже ее нет.
10 авг 11, 14:38    [11098108]     Ответить | Цитировать Сообщить модератору
 Re: Почти олимпиадная задача по PARTITION BY  [new]
albert t
Member

Откуда: Харьков
Сообщений: 301
kDnZP,
Спасибо. Оригинальный способ.
10 авг 11, 14:43    [11098160]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить