如何按周返回各测试的最近结果？SQL技术问询

我有两张表可用：

• 一张包含日期列表及其所属的对应周（Dates_table）
• 另一张记录了人员进行8项测试中任意一项的日期（每项测试对应一行，Tests_table）

我希望展示一年中每周各项测试的最近完成日期，无论测试实际何时进行。

期望输出示例 #

当前进展 #

目前我已统计出每周每位人员当周是否进行测试，结果如下：

使用的查询语句：

with wkref  as (
Select distinct 
    d.[DateKey]
,   d.FirstDayOfWeek
from Dates_table    d with(nolock)

where   d.CalendarYear  between 2018 and YEAR(getdate())
)
, checks as (
Select 
    Dateadd(d, 6, w.FirstDayOfWeek) 'WeekEnding'
,   t.PersonKey
,   MAX(case 
        when    t.Measurement   =   'Test1' then    t.EventDateKey
        else    null 
    end) 'Test1_Date'
,   MAX(case 
        when    t.Measurement   =   'Test2' then    t.EventDateKey
        else    null 
    end) 'Test2_Date'
from    wkref w with(nolock)

left    join    Tests_table t with(nolock)
    on  t.EventDateKey  =   w.DateKey
)

我尝试用ROW_NUMBER()计算非空值间距并结合LAG函数填充，但未成功，该函数仅填充已有值的行，未返回正确行数。尝试自连接、基于testdate <= weekending的连接等方案也无效。

我的问题：

1. 期望的输出是否可行？
2. 若可行，正确的实现方式是什么？

1. 可行性确认 #

完全可行，核心思路是先生成所有周+人员的完整笛卡尔积，再针对每个维度（人员、测试项、周）找到该周及之前的最近测试日期，最后通过透视转换为目标格式。

2. 具体实现方案 #

方案一：使用窗口函数实现

WITH WeekList AS (
    -- 提取所有需要统计的周结束日期
    SELECT DISTINCT
        DATEADD(DAY, 6, d.FirstDayOfWeek) AS WeekEnding
    FROM Dates_table d WITH(NOLOCK)
    WHERE d.CalendarYear BETWEEN 2018 AND YEAR(GETDATE())
),
PersonList AS (
    -- 提取所有涉及的人员
    SELECT DISTINCT PersonKey
    FROM Tests_table t WITH(NOLOCK)
),
WeekPerson AS (
    -- 生成周-人员的完整笛卡尔积，确保每个人员每周都有记录
    SELECT 
        w.WeekEnding,
        p.PersonKey
    FROM WeekList w
    CROSS JOIN PersonList p
),
TestPivot AS (
    -- 将测试表转换为宽表格式，保留每个人员每项测试的所有日期
    SELECT
        PersonKey,
        MAX(CASE WHEN Measurement = 'Test1' THEN EventDateKey END) AS Test1,
        MAX(CASE WHEN Measurement = 'Test2' THEN EventDateKey END) AS Test2
        -- 其他6项测试复制上述CASE语句即可
    FROM Tests_table WITH(NOLOCK)
    GROUP BY PersonKey, EventDateKey
),
LatestTestCTE AS (
    -- 关联周-人员表和测试表，计算每个维度的最近日期
    SELECT
        wp.WeekEnding,
        wp.PersonKey,
        -- 用窗口函数向前填充最近的非空测试日期
        LAST_VALUE(tp.Test1) OVER (
            PARTITION BY wp.PersonKey 
            ORDER BY wp.WeekEnding 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS Test1,
        LAST_VALUE(tp.Test2) OVER (
            PARTITION BY wp.PersonKey 
            ORDER BY wp.WeekEnding 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS Test2
        -- 其他测试项同理添加窗口函数
    FROM WeekPerson wp
    LEFT JOIN TestPivot tp
        ON tp.PersonKey = wp.PersonKey
        AND tp.Test1 <= wp.WeekEnding -- 限制测试日期在当前周及之前
)
-- 去重得到最终结果
SELECT DISTINCT
    WeekEnding,
    PersonKey,
    Test1,
    Test2
    -- 其他测试项
FROM LatestTestCTE
ORDER BY PersonKey, WeekEnding;

方案二：使用OUTER APPLY（更直观高效）

适用于SQL Server 2012及以上版本，逻辑更清晰，且在有合适索引的情况下性能更优：

WITH WeekList AS (
    SELECT DISTINCT
        DATEADD(DAY, 6, d.FirstDayOfWeek) AS WeekEnding
    FROM Dates_table d WITH(NOLOCK)
    WHERE d.CalendarYear BETWEEN 2018 AND YEAR(GETDATE())
),
PersonList AS (
    SELECT DISTINCT PersonKey
    FROM Tests_table t WITH(NOLOCK)
)
SELECT
    w.WeekEnding,
    p.PersonKey,
    t1.TestDate AS Test1,
    t2.TestDate AS Test2
    -- 其他测试项复制以下OUTER APPLY块即可
FROM WeekList w
CROSS JOIN PersonList p
-- 针对Test1取当前周及之前的最近日期
OUTER APPLY (
    SELECT TOP 1 EventDateKey AS TestDate
    FROM Tests_table
    WHERE PersonKey = p.PersonKey
      AND Measurement = 'Test1'
      AND EventDateKey <= w.WeekEnding
    ORDER BY EventDateKey DESC
) t1
-- 针对Test2取当前周及之前的最近日期
OUTER APPLY (
    SELECT TOP 1 EventDateKey AS TestDate
    FROM Tests_table
    WHERE PersonKey = p.PersonKey
      AND Measurement = 'Test2'
      AND EventDateKey <= w.WeekEnding
    ORDER BY EventDateKey DESC
) t2
ORDER BY p.PersonKey, w.WeekEnding;

关键注意点 #

• 必须生成周-人员的完整笛卡尔积，否则会缺失没有测试记录的周/人员组合
• 若存在某测试从未完成的情况，对应字段会显示NULL，可通过ISNULL()函数替换为默认值（比如最早日期或特定标记）
• 建议给Tests_table创建包含PersonKey、Measurement、EventDateKey的复合索引，提升查询性能

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