时间戳

首页 > 解决方案 > 按与组函数的最大值关联的值查找组

sql - 按与组函数的最大值关联的值查找组

问题描述

假设我有几个表来存储学生的考试成绩，就像这样

tests (
    testId INT,
    proctoredDate DATE,
    totalMark INT,
    weight FLOAT,
    CONSTRAINT test_id_primary_key PRIMARY KEY(testId),
    ...
)

student_test_marks (
    studentId INT,
    testId INT,
    awardedMark INT,
    CONSTRAINT student_test_composite_key PRIMARY KEY(studentId, testId),
    ...
)

我正在尝试确定在给定月份中哪个学生获得了最高的累积分数。所以过程是将每个学生在指定月份分组的所有测试分数和权重相加，并仅返回最大总和。我已经有一个有效的查询，但我认为它不是很有效。

SELECT studentId
FROM student_test_marks INNER JOIN tests
ON student_test_marks.testId = tests.testId
GROUP BY studentId, EXTRACT(MONTH FROM proctoredDate)
HAVING EXTRACT(MONTH FROM proctoredDate) = 4 AND 
SUM((awardedMark / totalMark) * weight) IN (
    SELECT MAX(SUM((awardedMark / totalMark) * weight))
    FROM student_test_marks INNER JOIN tests
    ON student_test_marks.testId = test.testId
    GROUP BY studentId, EXTRACT(MONTH FROM proctoredDate)
    HAVING EXTRACT(MONTH FROM proctoredDate) = 4
)

它确实有效，但我觉得它不是最优的，因为查询连接、分组和过滤两次。我考虑按组的 Sum 列排序，然后只获取第一行，但这感觉很笨重。

有没有更好的方法来解决这个问题？

标签： sqloracle

解决方案

我认为您只想使用分析函数：

SELECT m.*
FROM (SELECT TRUNC(proctoredDate, 'MON') as yyyymm,
             studentId, SUM((awardedMark / totalMark) * weight) as weighted_mark,
             RANK() OVER (PARTITION BY TRUNC(proctoredDate, 'MON')
                          ORDER BY SUM((awardedMark / totalMark) * weight)
                         ) as seqnum
      FROM student_test_marks stm INNER JOIN
           tests t
           ON stm.testId = t.testId
      GROUP BY TRUNC(proctoredDate, 'MON'), studentId
     ) m
WHERE seqnum = 1;

推荐阅读