SQL - 使用窗口和案例语句平展表

Question

第一次提问者 -- 我在 SqlServer 2012 中结合案例逻辑和窗口时遇到了一些问题。我需要展平下面显示的数据结构，因此之后我将针对这些结果运行 MAX 语句。我正在使用大小写/何时逻辑为每个 xID 指定一个"所有者"。我的挑战是我总是得到错误的结果，我无法确定我做错了什么。

这是案例/时间逻辑和表结构。对于"所有者"列，我需要评估三个条件，并尝试使用 FIRST_VALUE() 来选择 case 语句的输出。case 语句中的逻辑工作正常，但是当我尝试使用窗口函数将值应用于每个 xID 时，它返回了错误的值。

FIRST_VALUE(case
when [partnerType] = 'Giver' and [partnerAgree] = 'True' then [partnerGroup]
when [partnerType] = 'Impacted' and [partnerAgree] = 'True' then [partnerGroup]
when [Stakeholder No#] = 1 and [partnerAgree] <> 'True' then [partnerGroup]
else Null end) over (partition by [xID] order by [yID])
as 'Owner'
Desired Results --------------------------
|xID|yID| Owner     |partnerType| partnergrp|partnerAgree
|100|  1| grp_Banana|Taker      |grp_Apple  |TRUE
|100|  2| grp_Banana|Giver      |grp_Banana |TRUE
|100|  3| grp_Banana|Taker      |grp_Banana |FALSE
|101|  1| grp_Carrot|Taker      |grp_Carrot |TRUE
|101|  2| grp_Carrot|Giver      |grp_Danish |FALSE
|101|  3| grp_Carrot|Taker      |grp_Banana |TRUE
|101|  4| grp_Carrot|Taker      |grp_Danish |FALSE
Results I'm getting --------------------------
|xID|yID| Owner     |partnerType| partnergrp|partnerAgree
|100|  1| grp_Apple |Taker      |grp_Apple  |TRUE
|100|  2| grp_Apple |Giver      |grp_Banana |TRUE
|100|  3| grp_Apple |Taker      |grp_Banana |FALSE
|101|  1| grp_Carrot|Taker      |grp_Carrot |TRUE
|101|  2| grp_Carrot|Giver      |grp_Danish |FALSE
|101|  3| grp_Carrot|Taker      |grp_Banana |TRUE
|101|  4| grp_Carrot|Taker      |grp_Danish |FALSE

第一个表显示我预期的结果，但代码生成第二个表中的值。示例：对于 xID=100，我希望所有者grp_Banana，但我的代码返回grp_Apple。对于 xID=101，我得到了正确的答案，但原因错误。窗口函数似乎正在获取任何结果集的第一个 yID。

谢谢，任何帮助不胜感激。另外，我愿意不使用窗口函数，这似乎是正确的方向。

Answer 1

除非您的数据库支持ignore nulls 参数，否则这很棘手。 您可以使用两个窗口函数执行此操作：

max(case when yid = yid_special then partnerGroup end) over (partition by xid) as Owner
from (select . . . ,
min(case when partnerType = 'Giver' and [partnerAgree] = 'True' then yid
when partnerType = 'Impacted' and [partnerAgree] = 'True' then yid
when [Stakeholder No#] = 1 and [partnerAgree] <> 'True' then yid
end) over (partition by xid) as yid_special

您也可以使用以下first_value()编写以下内容：

first_value(partnerGroup) over
(partition by xid
order by (case when partnerType = 'Giver' and [partnerAgree] = 'True' then yid
when partnerType = 'Impacted' and [partnerAgree] = 'True' then yid
when [Stakeholder No#] = 1 and [partnerAgree] <> 'True' then yid
else 999999
end) 
) as owner

Answer 2

使用@Gordon-Linoff的建议，我能够解决我的问题。这是修改后的代码。看到 Gordon在 Order By 中使用 case 语句让我意识到为什么我的代码有时会选择错误的值。我把答案归功于他，因为我只是在他的方法的基础上。非常感谢。

, FIRST_VALUE(
case
when [partnerType] = 'Remediator' and [partnerAgree] = 'True' then [partnerGroup]
when [partnerType] = 'Impacted' and [partnerAgree] = 'True' then [partnerGroup]
when [yID] = 1 and [partnerAgree] <> 'True' then [partnerGroup]
else [partnerGroup] end) 
over 
(partition by [Incident ID] order by
case 
when [partnerType] = 'Remediator' and [partnerAgree] = 'True' then 1
when [partnerType] = 'Impacted' and [partnerAgree] = 'True' then 2
when [yID] = 1 and [partnerAgree] <> 'True' then 3   
else 100
end
)
as 'Owner'