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

Откуда: Киев
Сообщений: 77
 		народ, подскажите, как можно агрегировать записи таким образом:
есть таблица myTable(id identity(1,1),value). в ней 1млн записей. для каждого Id нужно записать SUM(Value) из трех строк, но нужно пропустить 2строки.
То есть для Id = 1 нужно сложить 1ю, 4ю и 7ю запись
для Id = 2 соответственно 2ю, 5ю и 8ю
для Id = 4 нужны (!) 4я, 7я и 10я строка.
Вот как примерно выглядит исходная таблица и должна выглядеть результирующая:
--Входные
SELECT 1 id,200 VALUE UNION
SELECT 2 id,300 VALUE UNION
SELECT 3 id,200 VALUE UNION
SELECT 4 id,600 VALUE UNION
SELECT 5 id,800 VALUE UNION
SELECT 6 id,300 VALUE UNION
SELECT 7 id,800 VALUE UNION
SELECT 8 id,200 VALUE UNION
SELECT 9 id,100 VALUE UNION
SELECT 10 id,200 VALUE UNION
SELECT 11 id,400 VALUE UNION
SELECT 12 id,200 VALUE UNION
SELECT 13 id,400 VALUE 

--Результат
SELECT 1 id,1600 Aggregated_VALUE UNION
SELECT 2 id,1300 VALUE UNION
SELECT 3 id,600 VALUE UNION
SELECT 4 id,1600 VALUE UNION
SELECT 5 id,1400 VALUE UNION
SELECT 6 id,600 VALUE UNION
SELECT 7 id,800 VALUE UNION
SELECT 8 id,1400 VALUE
2 июл 14, 19:45    [16251704]     Ответить | Цитировать Сообщить модератору
 Re: [help] Хитрая агрегация  [new]
msLex
Member

Откуда:
Сообщений: 8696
 		если сервер 2012, смотрите в сторону lag

если нет - то 


SELECT 1 id,200 VALUE 
into #t
UNION
SELECT 2 id,300 VALUE UNION
SELECT 3 id,200 VALUE UNION
SELECT 4 id,600 VALUE UNION
SELECT 5 id,800 VALUE UNION
SELECT 6 id,300 VALUE UNION
SELECT 7 id,800 VALUE UNION
SELECT 8 id,200 VALUE UNION
SELECT 9 id,100 VALUE UNION
SELECT 10 id,200 VALUE UNION
SELECT 11 id,400 VALUE UNION
SELECT 12 id,200 VALUE UNION
SELECT 13 id,400 VALUE 

create unique clustered index i on  #t(id)



select 
	id,
	t.value + x.value
from #t t
cross apply (
	select 
		value = sum(case when rn in (3,6) then value else 0 end)
		, cnt  = count(*)
	from (
		select top 6 
			value
			, rn = row_number() over (order by id)
		from #t t1 
		where 
			t1.id > t.id
		order by  
			id 
	) x
) x
where 
	x.cnt = 6
2 июл 14, 20:08    [16251767]     Ответить | Цитировать Сообщить модератору
 Re: [help] Хитрая агрегация  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
 		msLex, чет как то сложно, а нельзя проще сделать? Например, так:

select t.id, t.value + t1.value + t2.value
from   #t t
         inner join #t t1
                  on t.id + 3 = t1.id
         inner join #t t2
                  on t.td +6 = t2.id


В случае, если для последней миллионной строки тоже нужно вернуть результат, заменяем inner на left и t1.value на isnull(t1.value, 0) и t2.value на isnull(t2.value, 0)
3 июл 14, 10:23    [16253142]     Ответить | Цитировать Сообщить модератору
 Re: [help] Хитрая агрегация  [new]
msLex
Member

Откуда:
Сообщений: 8696
Minamoto
msLex, чет как то сложно, а нельзя проще сделать? Например, так:

select t.id, t.value + t1.value + t2.value
from   #t t
         inner join #t t1
                  on t.id + 3 = t1.id
         inner join #t t2
                  on t.td +6 = t2.id



В случае, если для последней миллионной строки тоже нужно вернуть результат, заменяем inner на left и t1.value на isnull(t1.value, 0) и t2.value на isnull(t2.value, 0)

1. Кто вам сказал, что в id нет дырок?
2. Не факт, что два джойна будут лучше одного, можете проверить на миллионной таблице.
3 июл 14, 10:34    [16253264]     Ответить | Цитировать Сообщить модератору
 Re: [help] Хитрая агрегация  [new]
Minamoto
Member

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

1) если есть, тогда да, мой вариант не пройдет.

2) Да легко:

select ROW_NUMBER() over (order by c0.number, c1.number) as id, cast(RAND(c0.number + 1000 * c1.number)* 1000 as int) as value
into #t
from master.dbo.spt_values c0
cross join master.dbo.spt_values c1
where c0.type = 'p' and c1.type = 'p'
and c0.number < 1000 and c1.number < 1000;


Ваш вариант:
(строк обработано: 999994)

(строк обработано: 1)

 SQL Server Execution Times:
   CPU time = 18547 ms,  elapsed time = 7907 ms.

Мой вариант:
(строк обработано: 999994)

(строк обработано: 1)

 SQL Server Execution Times:
   CPU time = 10531 ms,  elapsed time = 7324 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
3 июл 14, 10:59    [16253478]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить