窗口函数和分组-如何在分区中将数据从第1行传播到第3行？

Question

我的查询如下：

select
    Contact.IndividualID,
    Contact.IndividualID as ContactId,
    Contact.CaseNumber as CaseID,
    [Case].ProgramCode as Benefit,
    Contact.Email as EmailAddress,
    '' as EmailTo,
    Contact.FirstName,
    [Case].CaseProgramIndividualStatusCode,
    [Case].ReviewDueDate as RenewDueDate,
    [Case].ReviewDueDate as BenefitExpirationDate,
    [Case].ProgramCode as ProgramCode,
    pref.Phone as MobileNumber,
    Contact.IsHeadOfHousehold,
    row_number() over (partition by Contact.CaseNumber order by Contact.IsHeadOfHousehold desc) as row
from
    SOMETABLE_Contact_Dev Contact    
inner join
    SOMETABLE_Case_Dev [Case] on Contact.IndividualID = [Case].IndividualID and Contact.CaseNumber = [Case].CaseNumber
left join
    [SSP RE Preferences] pref on Contact.IndividualID = pref.ContactId
where 
    (([Case].RenewalTypeCode = 'AC' and [Case].ReviewStatusCode in ('RI','RR')) or 
    ([Case].RenewalTypeCode = 'PS' and [Case].ReviewStatusCode = 'RI')) and 
        DateDiff(day, getdate(), [Case].ReviewDueDate) = 40 and 
        Contact.Email is not null and
        [Case].ProgramCode in ('KC','KT','CC','MA')

下面是运行此查询的结果集：

​

这就是我遇到麻烦的地方。我想要做的是，当有分区定义的分组时，我希望将该组的顶部记录(第1行)的电子邮件地址作为EmailTo字段：

​

​

如果没有分组，只需使用EmailTo字段的电子邮件地址。做这件事最好的方法是什么？

Answer 1

您可以在相关分区上使用first_value()。

例如，假设您指的是与用于row_number的分区相同的分区，请在row_number行之后添加以下行

FIRST_VALUE(Contact.Email) OVER (partition by Contact.CaseNumber order by Contact.IsHeadOfHousehold desc) AS first_email