Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Запрос. Аналог/замена sum(distinct Field) over (partition by)  [new]
user89
Member

Откуда:
Сообщений: 2083
Всем доброго дня! Есть таблица примерно такого содержания:
PodrRes_NameProj_NameCapacity
Отдел 1ИвановПроект 1160
Отдел 1ИвановПроект 2160
Отдел 1ПетровПроект 1159
Отдел 1ПетровПроект 123159
Отдел 1СидоровПроект 1160
Отдел 2КузнецовПроект 321155

declare @t table (Podr varchar(500), Res_Name varchar(500), Proj_Name varchar(500), Capacity int)
insert @t values ('Отдел 1','Иванов','Проект 1',160), ('Отдел 1','Иванов','Проект 2',160), ('Отдел 1','Петров','Проект 1',159), ('Отдел 1','Петров','Проект 123',159),
('Отдел 1','Сидоров','Проект 1',160), ('Отдел 2','Кузнецов','Проект 321',155)

select * from @t

Нужно получить сумму Capacity по каждому отделу без дубликатов
PodrRes_NameProj_NameCapacitySumByPodr
Отдел 1ИвановПроект 1160479.00
Отдел 1ИвановПроект 2160479.00
Отдел 1ПетровПроект 1159479.00
Отдел 1ПетровПроект 123159479.00
Отдел 1СидоровПроект 1160479.00
Отдел 2КузнецовПроект 321155155.00


У нас SQL 2008, и такая конструкция не работает
select *, sum(distinct Capacity) over(partition by Podr, Res_Name) [SumByPodr]
from @t

Уверен, что решение несложное, но никак не соображу :(
14 мар 13, 11:38    [14046546]     Ответить | Цитировать Сообщить модератору
 Re: Запрос. Аналог/замена sum(distinct Field) over (partition by)  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22550
коррелированным подзапросом?
14 мар 13, 11:41    [14046565]     Ответить | Цитировать Сообщить модератору
 Re: Запрос. Аналог/замена sum(distinct Field) over (partition by)  [new]
nizamovich
Member

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

что значит без дубликатов ?
я не вижу дубликатов, хотя судя по вашему запросу, разбиваете на res_name

если данных мало , то можно написать простой запрос

 select *, (select SUM(Capacity) from @t as t2 where t1.Podr = t2.Podr) 
from @t as t1 
14 мар 13, 11:46    [14046590]     Ответить | Цитировать Сообщить модератору
 Re: Запрос. Аналог/замена sum(distinct Field) over (partition by)  [new]
user89
Member

Откуда:
Сообщений: 2083
Паганель,

сообразил!
;with cte as (
  select *, case when row_number() over(partition by Podr, Res_Name order by Podr) = 1 then Capacity else 0 end [CapacityTmp]
  from @t
)
select Podr, Res_Name, Proj_Name, Capacity, sum(CapacityTmp) over(partition by Podr) [SumByPodr]
from cte
14 мар 13, 11:47    [14046593]     Ответить | Цитировать Сообщить модератору
 Re: Запрос. Аналог/замена sum(distinct Field) over (partition by)  [new]
user89
Member

Откуда:
Сообщений: 2083
nizamovich
что значит без дубликатов ?
я не вижу дубликатов, хотя судя по вашему запросу, разбиваете на res_name
тут повторяется Capacity для каждого человека. Сложить нужно цифры внутри отдела, которые я подкрасил зеленым цветом.

Я нашел решение, но если есть альтернативы, то с радостью возьму себе в копилку знаний, пригодится для других задач.
14 мар 13, 11:50    [14046614]     Ответить | Цитировать Сообщить модератору
 Re: Запрос. Аналог/замена sum(distinct Field) over (partition by)  [new]
user89
Member

Откуда:
Сообщений: 2083
На больших данных этот запрос выполнился чуть-чуть быстрее
;with cte as (
  select Podr, Res_Name, sum(sum(distinct Capacity)) over(partition by Podr) val
  from @t group by Podr, Res_Name
)
select t.*, cte.val
from @t t
inner join cte on t.Podr = cte.Podr and t.Res_Name = cte.Res_Name
14 мар 13, 12:27    [14046826]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить