SQL中如何基于分区生成的列添加条件判断新列？

我用OVER PARTITION生成了包含SalespersonPersonID、AvgLineProfit、AvgProfitPerSalesPerson等列的表，对应的SQL语句如下：

select distinct
    invoices.SalespersonPersonID,
    sum(invoicelines.Quantity) over(partition by invoices.SalespersonPersonID) as QuantityPerSalesPerson,
    avg(invoicelines.LineProfit) over() as AvgLineProfit,
    avg(invoicelines.LineProfit) over(partition by invoices.SalespersonPersonID) as AvgProfitPerSalesPerson
from Sales.InvoiceLines as invoicelines
join Sales.Invoices as invoices on invoicelines.InvoiceID = invoices.InvoiceID
order by invoices.SalespersonPersonID

现在我想添加一个新列：当AvgProfitPerSalesPerson > AvgLineProfit时显示SalespersonPersonID，否则显示NULL。但尝试嵌套查询或CASE WHEN时写法有误，比如下面的代码无法生效：

case when AvgProfitPerSalesPerson > AvgLineProfit then SalespersonPersonID over() as new_column

当前输出示例：

解决方案 #

你不需要在CASE WHEN里给SalespersonPersonID加OVER()，窗口函数生成的列可以直接在同一条查询的CASE中引用，以下是两种可行写法：

写法1：直接在原SELECT中添加CASE语句

把CASE WHEN作为新列直接加入原查询，直接引用窗口函数的计算结果（或重复窗口函数逻辑）即可：

select distinct
    invoices.SalespersonPersonID,
    sum(invoicelines.Quantity) over(partition by invoices.SalespersonPersonID) as QuantityPerSalesPerson,
    avg(invoicelines.LineProfit) over() as AvgLineProfit,
    avg(invoicelines.LineProfit) over(partition by invoices.SalespersonPersonID) as AvgProfitPerSalesPerson,
    -- 新增的条件判断列
    case 
        when avg(invoicelines.LineProfit) over(partition by invoices.SalespersonPersonID) > avg(invoicelines.LineProfit) over() 
        then invoices.SalespersonPersonID 
        else null 
    end as HighPerformingSalespersonID
from Sales.InvoiceLines as invoicelines
join Sales.Invoices as invoices on invoicelines.InvoiceID = invoices.InvoiceID
order by invoices.SalespersonPersonID

写法2：使用CTE封装原结果（更简洁）

先通过公共表表达式（CTE）封装原查询的结果，再在外部查询中添加条件判断，避免重复写窗口函数逻辑：

with SalespersonMetrics as (
    select distinct
        invoices.SalespersonPersonID,
        sum(invoicelines.Quantity) over(partition by invoices.SalespersonPersonID) as QuantityPerSalesPerson,
        avg(invoicelines.LineProfit) over() as AvgLineProfit,
        avg(invoicelines.LineProfit) over(partition by invoices.SalespersonPersonID) as AvgProfitPerSalesPerson
    from Sales.InvoiceLines as invoicelines
    join Sales.Invoices as invoices on invoicelines.InvoiceID = invoices.InvoiceID
)
select 
    *,
    case 
        when AvgProfitPerSalesPerson > AvgLineProfit 
        then SalespersonPersonID 
        else null 
    end as HighPerformingSalespersonID
from SalespersonMetrics
order by SalespersonPersonID

原写法无效的原因 #

你之前的代码中，SalespersonPersonID over()是错误语法——OVER()仅能配合聚合函数使用，SalespersonPersonID是普通列，不需要添加OVER()，直接返回该列本身即可。

内容的提问来源于stack exchange，提问作者Nataliia Yefimova