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

Откуда: From Russia
Сообщений: 146
Всем привет,
Есть вот такое дело:
declare @t1 table(id int, source int, name nvarchar(32))
insert into @t1 values
	(1, 3, 'product1'),(1, 3, 'product2'),(2, 3, 'product3'),
	(4, 4, 'product4'),(5, 4, 'product5'),(6, 5, 'product6'),
	(6, 5, 'product7'),(7, 5, 'product8'),(8, 5, 'product9')

select 
	t.source, 
	COUNT(*),
	(
		select 
			case when MAX(g1.first_id) = MAX(g1.second_id) then 1 else 0 end as is_duplicate
			--, MAX(g1.first_name)
			--, MAX(g1.second_name)
		from (
			select 
				case when g.n = 1 then g.id else 0 end as first_id,
				case when g.n = 1 then g.name else '' end as first_name,
				case when g.n = 2 then g.id else 0 end as second_id,
				case when g.n = 2 then g.name else '' end as second_name,
				1 as for_group
			from (
				select t2.id, t2.name, ROW_NUMBER() over (order by id) as n from @t1 t2
				where t2.source = t.source
			) g
		) g1
	)
from @t1 t
group by t.source

Смысл такой, я группирую по полю source, и дальше мне нужно дополнительно обработать каждую группу, а именно: понять равны ли id первой и второй записи в группе. С этим мой запрос сейчас справляется, хотя наверняка можно проще сделать. Но теперь мне еще надо выводить name первой и второй записи из каждой группы в отдельных колонках. По идеи если раскомментировать MAX(g1.first_name) и MAX(g1.second_name), то это будет то что надо. Однако сиквел, разумеется, не дает раскомментировать эти строки, так как можно только одно поле из такой конструкции вернуть. Подскажите, как решить проблему?
27 дек 12, 10:58    [13693682]     Ответить | Цитировать Сообщить модератору
 Re: Дополнительно обработать каждую группу  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
;with numberedGr as
(
select 
	id
	,source
	,name
	,ROW_NUMBER() over (partition by source order by id) rn
from @t1

)	

select
	n1.source 
	,(select COUNT(*) from @t1 where source = n1.source) countInGr
	,n1.name name1
	,n2.name name2
	,case when n1.id<>n2.id OR n2.id IS null then 0 else 1 end isDuplicate
from	numberedGr n1
		left join numberedGr n2
		on	n2.source = n1.source
			and n2.rn=2
where n1.rn =1	
27 дек 12, 11:52    [13694049]     Ответить | Цитировать Сообщить модератору
 Re: Дополнительно обработать каждую группу  [new]
Добрый Э - Эх
Guest
Kudep,

Сортировка у тебя недетерминирована, если что. Кроме того, больше двух одинаковых ID в пределах одной группы не бывает?


Ну и версия сервера какая? А то, если что, можно всякие там LEAD/LAG/MAX over() привлекать.
27 дек 12, 12:50    [13694445]     Ответить | Цитировать Сообщить модератору
 Re: Дополнительно обработать каждую группу  [new]
Kudep
Member

Откуда: From Russia
Сообщений: 146
Мистер Хенки, работает, спасибо.
Добрый Э - Эх, сортировку я в реальности по полю created_dt использую, меня это устраивает. Больше двух может конечно. Sql Server 2008 R2.
27 дек 12, 13:24    [13694707]     Ответить | Цитировать Сообщить модератору
 Re: Дополнительно обработать каждую группу  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Kudep
Но теперь мне еще надо выводить name первой и второй записи из каждой группы в отдельных колонках. По идеи если раскомментировать MAX(g1.first_name) и MAX(g1.second_name), то это будет то что надо. Однако сиквел, разумеется, не дает раскомментировать эти строки, так как можно только одно поле из такой конструкции вернуть. Подскажите, как решить проблему?

Для решения этой проблемы был придуман оператор APPLY. Раскомментируем строки, передвигаем подзапрос внутрь cross apply, и готово:
declare @t1 table(id int, source int, name nvarchar(32))
insert into @t1 values
    (1, 3, 'product1'),(1, 3, 'product2'),(2, 3, 'product3'),
    (4, 4, 'product4'),(5, 4, 'product5'),(6, 5, 'product6'),
    (6, 5, 'product7'),(7, 5, 'product8'),(8, 5, 'product9')

select 
    t.source, 
    COUNT(*),
    x.is_duplicate, x.first_name, x.second_name
from @t1 t
    cross apply
    (
        select 
            case when MAX(g1.first_id) = MAX(g1.second_id) then 1 else 0 end as is_duplicate
            , MAX(g1.first_name) first_name
            , MAX(g1.second_name) second_name
        from (
            select 
                case when g.n = 1 then g.id else 0 end as first_id,
                case when g.n = 1 then g.name else '' end as first_name,
                case when g.n = 2 then g.id else 0 end as second_id,
                case when g.n = 2 then g.name else '' end as second_name,
                1 as for_group
            from (
                select t2.id, t2.name, ROW_NUMBER() over (order by id) as n from @t1 t2
                where t2.source = t.source
            ) g
        ) g1
    ) x
group by t.source, x.is_duplicate, x.first_name, x.second_name


Планы выполнения сравните сами и выберите оптимальное решение :-)
27 дек 12, 13:42    [13694832]     Ответить | Цитировать Сообщить модератору
 Re: Дополнительно обработать каждую группу  [new]
Kudep
Member

Откуда: From Russia
Сообщений: 146
Гость333, точно, сенкс.
Вариант с cte выполняется на несколько порядков быстрей. Смотрю планы, пытаюсь понять, в чем принципиальное отличие...
27 дек 12, 16:13    [13696090]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить