单查询获取会员最新月份Score及总Salary的优化SQL方案

数据表格 #

需求说明 #

需通过单条SQL查询，为指定会员（例如M1）返回单行结果，包含三个字段：会员ID、该会员的总薪资、该会员最新月份的分数。示例结果为：M1 1800 32。

当前实现的SQL #

用户目前使用以下SQL实现需求，但由于表字段较多时需要大量MAX函数，希望得到更简洁高效的方案：

SELECT MEMBER_ID,SUM(SALARY) AS SALARY,SUM(SCORE) AS SCORE 
FROM(
    SELECT MEMBER_ID,SALARY, CASE WHEN RANK=1 THEN SCORE ELSE 0 END AS SCORE 
    FROM (
        SELECT A.*, RANK() OVER (PARTITION BY MEMBER_ID ORDER BY MONTH DESC) AS RANK 
        FROM table A 
        WHERE MONTH BETWEEN '2023-01' and '2023-08' 
    ) A 
)
GROUP BY 1;

优化方案 #

方案1：结合窗口函数与条件聚合 #

无需嵌套多层子查询，直接在聚合时通过窗口函数标记最新记录，再用条件聚合提取最新分数：

SELECT 
    MemberID,
    SUM(Salary) AS TotalSalary,
    MAX(CASE WHEN is_latest = 1 THEN Score END) AS LatestMonthScore
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER(PARTITION BY MemberID ORDER BY Month DESC) AS is_latest
    FROM YourTableName
    WHERE MemberID = 'M1' 
      AND Month BETWEEN '2023-01' AND '2023-08'
) t
GROUP BY MemberID;

这里用ROW_NUMBER()代替RANK()，如果同一会员有多个相同的最新月份，ROW_NUMBER()会只取其中一条；若需保留多条的分数，可改用RANK()并调整聚合逻辑。

方案2：使用JOIN分离聚合与最新记录查询 #

将总薪资的聚合和最新分数的查询分开，再通过会员ID关联，逻辑更清晰：

SELECT 
    agg.MemberID,
    agg.TotalSalary,
    latest.Score AS LatestMonthScore
FROM (
    SELECT 
        MemberID,
        SUM(Salary) AS TotalSalary
    FROM YourTableName
    WHERE MemberID = 'M1' 
      AND Month BETWEEN '2023-01' AND '2023-08'
    GROUP BY MemberID
) agg
JOIN (
    SELECT MemberID, Score
    FROM YourTableName
    WHERE MemberID = 'M1' 
      AND Month = (
          SELECT MAX(Month) 
          FROM YourTableName 
          WHERE MemberID = 'M1' 
            AND Month BETWEEN '2023-01' AND '2023-08'
      )
) latest ON agg.MemberID = latest.MemberID;

这种方式适合对窗口函数不太熟悉的场景，且在部分数据库中性能可能更优——尤其是当表有MemberID + Month的复合索引时。

方案3：支持FILTER子句的数据库（如PostgreSQL） #

如果使用支持FILTER子句的数据库，可进一步简化为单层级查询：

SELECT 
    MemberID,
    SUM(Salary) AS TotalSalary,
    MAX(Score) FILTER (WHERE Month = MAX(Month) OVER(PARTITION BY MemberID)) AS LatestMonthScore
FROM YourTableName
WHERE MemberID = 'M1' 
  AND Month BETWEEN '2023-01' AND '2023-08'
GROUP BY MemberID;

这种写法直接在聚合层完成所有逻辑，无需子查询，代码最简洁。

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