基于SQL分区后创建的列的条件

我使用over partition创建了一个包含SalespersonID, AvgLineProfit, AvgLineProfitPerSalesPerson的表。

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

现在我想添加另一个列，如果AvgLineProfitPerSalesPerson>AvgLineProfit，否则为NULL。

如何在我的条件中使用创建的列?

我尝试创建新的选择查询或使用'case when'

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

但是它不工作。

下面是输出

AvgProfitPerSalesPerson

不能在同一个select中引用别名。所以你必须重复整件事:

select ...
, case when avg(invoicelines.LineProfit) over(partition by invoices.SalespersonPersonID) > avg(invoicelines.LineProfit) over() then SalespersonPersonID end as new_column

或者在子查询中包装你的原始选择，然后它工作:

select *, case when case when AvgProfitPerSalesPerson > AvgLineProfit...
from (
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
) x
order by SalespersonPersonID

就是这样的

select 
new_table.SalespersonPersonID,
new_table.QuantityPerSalesPerson,
new_table.AvgLineProfit,
new_table.AvgProfitPerSalesPerson,
case when new_table.AvgProfitPerSalesPerson > new_table.AvgLineProfit then new_table.SalespersonPersonID
end as succesfull_salesperson
from
(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) as new_table
order by SalespersonPersonID

相关内容

最新更新

热门标签：