如何基于日期提取每个客户的首次与末次评估记录？

针对你的需求，这里提供两种高效的SQL实现方案，适合处理大量评估条目：

方案1：使用窗口函数（推荐，性能更优） #

通过窗口函数给每个客户的评估记录按日期排序，筛选出首次和末次的记录后聚合结果，仅需扫描一次表，适合大数据量场景：

WITH RankedAssessments AS (
    SELECT 
        clientID,
        AssessmentDate,
        TotalScore,
        -- 按客户分组，日期升序标记首次评估
        ROW_NUMBER() OVER (PARTITION BY clientID ORDER BY AssessmentDate ASC) AS first_rank,
        -- 按客户分组，日期降序标记末次评估
        ROW_NUMBER() OVER (PARTITION BY clientID ORDER BY AssessmentDate DESC) AS last_rank
    FROM Client.Assessments
)
SELECT 
    clientID,
    MAX(CASE WHEN first_rank = 1 THEN AssessmentDate END) AS FirstAssessmentDate,
    MAX(CASE WHEN first_rank = 1 THEN TotalScore END) AS FirstTotalScore,
    MAX(CASE WHEN last_rank = 1 THEN AssessmentDate END) AS LastAssessmentDate,
    MAX(CASE WHEN last_rank = 1 THEN TotalScore END) AS LastTotalScore
FROM RankedAssessments
GROUP BY clientID;

如果同一客户在同一天有多条评估记录，ROW_NUMBER()会随机选取一条；若需保留所有同日期记录，可替换为RANK()。

方案2：关联最小/最大日期子查询 #

先提取每个客户的首次、末次评估日期，再关联原表获取对应分数：

SELECT 
    a.clientID,
    first_assess.AssessmentDate AS FirstAssessmentDate,
    first_assess.TotalScore AS FirstTotalScore,
    last_assess.AssessmentDate AS LastAssessmentDate,
    last_assess.TotalScore AS LastTotalScore
FROM (
    SELECT 
        clientID,
        MIN(AssessmentDate) AS FirstDate,
        MAX(AssessmentDate) AS LastDate
    FROM Client.Assessments
    GROUP BY clientID
) a
JOIN Client.Assessments first_assess 
    ON a.clientID = first_assess.clientID AND a.FirstDate = first_assess.AssessmentDate
JOIN Client.Assessments last_assess 
    ON a.clientID = last_assess.clientID AND a.LastDate = last_assess.AssessmentDate;

注意：如果同一日期存在多条评估，该方案会返回多条对应客户的记录，需额外通过聚合函数（如MAX()/AVG()）处理重复数据。

性能优化建议 #

• 创建联合索引：CREATE INDEX idx_client_assessment_date ON Client.Assessments(clientID, AssessmentDate);，可大幅提升分组、排序的效率。
• 优先选择窗口函数方案，尤其是数据量较大时，避免多次扫描表带来的性能损耗。

内容的提问来源于stack exchange，提问作者Neri M.