SQL Server Outer Apply聚合含外部引用报错及解决方案问询

场景说明 #

拥有people表，需查询人员是否在registry中及其首次加入registry的时间；另有visit表记录人员前往clinic（诊所）或hospital（医院）的就诊记录。需求为统计：

• 人员加入registry前12个月内，各类型就诊的次数
• 人员加入registry后，逐月累计的各类型就诊次数（假设就诊次数随时间递减）

问题与报错 #

最初使用Outer Apply编写SQL时触发以下错误：

Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression.

尝试在Outer Apply内部关联外层的rgstry表时，又提示找不到该外部表。最终通过改用Left Join并在外层进行聚合的方式解决问题。

原始报错SQL #

select
    people_table.person_id
    ,rgstry.start_dttm
    ,cast(datediff(day,rgstry.start_dttm,getdate())/30.0 as float) as MonthsSinceEnrollment
    ,coalesce(visits_12m.clinic_12m_Before,0) as clinic_12m_Before
    ,coalesce(visits_12m.hospital_12m_Before,0) as hospital_12m_Before
    ,coalesce(visits_12m.clinic_1m_after,0) as clinic_1m_after
    ,coalesce(visits_12m.hospital_1m_after,0) as hospital_1m_after
    ,coalesce(visits_12m.clinic_2m_after,0) as clinic_2m_after
    ,coalesce(visits_12m.hospital_2m_after,0) as hospital_2m_after
--... thru 12 months after

from people_table

join
    (
    select registry.person_id, registry.start_dttm
    from registry
    where
    registry.registry_id = 12345    --ROSTER
    and registry.registry_status = '1' --active
    ) as rgstry on people_table.person_id = rgstry.person_id

outer apply
    (
    select visits.person_id
    ,sum(case when visits.visit_dttm between dateadd(month,-12, rgstry.start_dttm) and rgstry.start_dttm and visits.visit_type = 'hospital' then 1 else 0 end) as clinic_12m_Before
    ,sum(case when visits.visit_dttm between dateadd(month,-12, rgstry.start_dttm) and rgstry.start_dttm and visits.visit_type = 'clinic' then 1 else 0 end) as hospital_12m_Before
    ,sum(case when visits.visit_dttm between rgstry.start_dttm and dateadd(month,1,rgstry.start_dttm) and visits.visit_type = 'hospital' then 1 else 0 end) as clinic_1m_After
    ,sum(case when visits.visit_dttm between rgstry.start_dttm and dateadd(month,1,rgstry.start_dttm) and visits.visit_type = 'clinic' then 1 else 0 end) as hospital_1m_After
    ,sum(case when visits.visit_dttm between rgstry.start_dttm and dateadd(month,2,rgstry.start_dttm) and visits.visit_type = 'hospital' then 1 else 0 end) as clinic_2m_After
    ,sum(case when visits.visit_dttm between rgstry.start_dttm and dateadd(month,2,rgstry.start_dttm) and visits.visit_type = 'clinic' then 1 else 0 end) as hospital_2m_After
    ,sum(case when visits.visit_dttm between rgstry.start_dttm and dateadd(month,3,rgstry.start_dttm) and visits.visit_type = 'hospital' then 1 else 0 end) as clinic_3m_After
    ,sum(case when visits.visit_dttm between rgstry.start_dttm and dateadd(month,3,rgstry.start_dttm) and visits.visit_type = 'clinic' then 1 else 0 end) as hospital_3m_After
    --... thru 12 months after

    from visits
    where
    people_table.person_id = visits.person_id
    and visits.visit_dttm >= dateadd(month,-12, rgstry.start_dttm) --get count of visits for preceding 12 months
    and visits.visit_dttm < dateadd(month,12, rgstry.start_dttm) --get count of visits 12 months following
    and visits.visit_type in
        (   
        'clinic'
        ,'hospital'         
        )
    group by visits.person_id
    ) as visits_12m

修正后的可行SQL #

select
    people_table.person_id
    ,rgstry.start_dttm
    ,cast(datediff(day,rgstry.start_dttm,getdate())/30.0 as float) as MonthsSinceEnrollment
    ,sum(case when visits_12m.visit_type = 'clinic' and visits_12m.visit_dttm between dateadd(month,-12, rgstry.start_dttm) and rgstry.start_dttm then 1 else 0 end) as clinic_12m_Before
    ,sum(case when visits_12m.visit_type = 'hospital' and visits_12m.visit_dttm between dateadd(month,-12, rgstry.start_dttm) and rgstry.start_dttm then 1 else 0 end) as hospital_12m_Before
    ,sum(case when visits_12m.visit_type = 'clinic' and visits_12m.visit_dttm between rgstry.start_dttm and dateadd(month,1,rgstry.start_dttm) then 1 else 0 end) as clinic_1m_after
    ,sum(case when visits_12m.visit_type = 'hospital' and visits_12m.visit_dttm between rgstry.start_dttm and dateadd(month,1,rgstry.start_dttm) then 1 else 0 end) as hospital_1m_after
    ,sum(case when visits_12m.visit_type = 'clinic' and visits_12m.visit_dttm between rgstry.start_dttm and dateadd(month,2,rgstry.start_dttm) then 1 else 0 end) as clinic_2m_after
    ,sum(case when visits_12m.visit_type = 'hospital' and visits_12m.visit_dttm between rgstry.start_dttm and dateadd(month,2,rgstry.start_dttm) then 1 else 0 end) as hospital_2m_after
---.. thru 12 months after
from people_table

join
    (
    select registry.person_id, registry.start_dttm
    from registry
    where
    registry.registry_id = 12345    --ROSTER
    and registry.registry_status = '1' --active
    ) as rgstry on people_table.person_id = rgstry.person_id

left join
    (
    select visits.person_id,visits.visit_dttm ,visits.visit_type
    from visits
    where
    visits.visit_type in ('clinic','hospital')
    ) as visits_12m on people_table.person_id = visits_12m.person_id and datediff(month, rgstry.start_dttm, visits_12m.visit_dttm) between -12 and 12

group by
    people_table.person_id
    ,rgstry.start_dttm
    ,cast(datediff(day,rgstry.start_dttm,getdate())/30.0 as float)

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