Используйте OVER (PARTITION BY) вместо Group By

прямо сейчас я использую временную таблицу в своем sql-запросе, но вместо этого хочу использовать функцию Partition By.

Мой запрос временной таблицы приведен ниже:

drop table #Temp;

create table #Temp
(
  NAME varchar(50),
  EMPID varchar(50),
  SS MONEY,
  PP MONEY
);

insert into #Temp
 select * From
 (  
  select
   p1.NAME,
   p1.EMPID,
   case when p1.AmtPayer = 'SELF' then sum(p1.Salary) else 0 end as S,
   case when p1.AmtPayer = 'MANAGER' then sum(p1.Salary) else 0 end as P
  from Candidate p1 
  group by p1.Name, p1.EMPID, p1.AmtPayer
 ) as P;

select 
 t.NAME,
 t.EMPID,
 sum(t.SS) as 'SELF PAID',
 sum(t.PP) as 'PARTY PAID' 
from #Temp t
group by t.NAME, t.EMPID;

Я также получаю ожидаемый результат, но я хочу выполнить эту операцию с помощью функции разделения, я пытался это сделать, но результат не точен -

select
 NAME,
 EMPID,
 sum(Salary) over (partition by AmtPayer) as Total
from dbo.Candidate

Выход:

Vivek   0001    300.00
Vivek   0001    300.00
Vivek   0001    6200.00
Vivek   0001    6200.00
Vivek   0001    6200.00

Но мне нужно:

Vivek   0001    6200.00 300.00

sql-server window-functions
person user3169167    schedule 07.01.2014    source источник
comment
Пожалуйста, сделайте отступ в своем коде, чтобы он отображался как код, и отформатируйте его с некоторой степенью здравомыслия... это действительно трудно читать.   -  person John Chrysostom    schedule 07.01.2014
comment
Если вы хотите, чтобы результат был сгруппирован, вам нужно использовать group by. Вся цель оконных функций не не уменьшить результирующий набор.   -  person a_horse_with_no_name    schedule 07.01.2014
comment
возможный дубликат Объединить несколько результатов в подзапрос в одно значение, разделенное запятыми   -  person a_horse_with_no_name    schedule 07.01.2014
comment
В любом случае вам не нужна временная таблица. Посмотрите что-нибудь об операторе with, который позволяет вам выполнять группу по группе по (что также решит вашу проблему).   -  person Luaan    schedule 07.01.2014

Ответы (2)


arrow_upward
9
arrow_downward

Чтобы сделать именно то, что вы хотите, попробуйте следующее:

select
 Name, EmpId,
 sum(case when AmtPayer = 'SELF' then Salary else 0 end) as [Self],
 sum(case when AmtPayer = 'MANAGER' then Salary else 0 end) as [Manager]
from dbo.Candidate
group by Name, EmpId;

Вы можете использовать операторы case в агрегатных функциях, что позволяет вам делать много сумасшедших вещей :)

Однако, как отмечалось в моем комментарии к вашему вопросу, это полезно только в том случае, если у вас есть фиксированное количество вариантов AmtPayer, которые вы знаете заранее.

Чтобы уточнить: partition by явно разработан не для сокращения набора результатов. Он по-прежнему будет возвращать одну строку на строку, и вы ничего не можете сделать, чтобы изменить это - если вы хотите уменьшить набор результатов, вы используете вместо этого group by. В сочетании со всеми сложными вещами, которые вы можете делать с агрегатными функциями, это на самом деле очень мощный инструмент, и это относится как к partition by, так и к group by. Также обратите внимание, что partition by может быть намного медленнее, чем group by. На самом деле я обнаружил, что использование partition by для получения количества результатов (например, count over (partition by NULL) или что-то подобное) намного медленнее, чем простое выполнение двух запросов, один только для подсчета, а другой для фактических результатов.

Не думайте, что ваш способ лучше, потому что он выглядит умнее — всегда измеряйте. Профилирование — ваш друг. Такие системы, как SQL Server, делают множество оптимизаций, которые постоянно пытаются дать вам отличную производительность для, казалось бы, глупых запросов :)

person Luaan    schedule 07.01.2014

arrow_upward
0
arrow_downward

Я использовал ниже запрос:

DROP TABLE #Temp
CREATE TABLE #Temp(
 NAME VARCHAR(50),
 EMPID VARCHAR(50),
 SS MONEY,
 PP MONEY
)
INSERT INTO #Temp
Select * From(
SELECT DISTINCT
  NAME,EMPID, 
  SUM(CASE WHEN AmtPayer='SELF' then Salary ELSE 0 end) OVER (PARTITION BY AmtPayer) AS SS ,
  SUM(CASE WHEN AmtPayer='MANAGER' THEN  Salary ELSE 0 end) OVER (PARTITION BY AmtPayer) AS PP
FROM dbo.Candidate 
)AS P
SELECT DISTINCT t.NAME ,t.EMPID ,SUM(t.SS) OVER(PARTITION BY t.NAME,t.EMPID) AS 'SELF PAID',
SUM(t.PP)  OVER(PARTITION BY t.NAME,t.EMPID) AS 'PARTY PAID' FROM #Temp t
--GROUP BY t.NAME ,t.EMPID
person user3169167    schedule 07.01.2014