使用OVER(PARTITION BY)代替Group By

Question

我现在在我的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

- user3169167

---

请缩进您的代码，以便它显示为代码，并使用某种程度的格式化使其更易于阅读。 - John Chrysostom

如果您想要结果分组，您需要使用group by。窗口函数的整个目的不是“减少”结果集。 - user330315

可能是重复的问题：将子查询中的多个结果合并为单个逗号分隔值 - user330315

无论如何，您都不需要临时表。查找有关“with”语句的信息，它允许您在一个分组上进行分组（这也将解决您的问题）。 - Luaan

Answer 1

要做到您想要的，可以尝试这样做：

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 这样的系统一直在尝试进行各种优化，以便针对看起来很愚蠢的查询提供出色的性能 :)

- Luaan

Answer 2

我使用了以下查询：

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 

- user3169167