时间戳

首页 > 解决方案 > 计算加权分数内的加权分数

sql - 计算加权分数内的加权分数

问题描述

我有一个一直在努力解决的数学/SQL 问题。

我有两个具有以下结构的表：

    CREATE TABLE Exams
(
ExamID INT PRIMARY KEY,
ExamName VARCHAR(100),
CourseID INT,
RelatedExamID INT NULL,
Weighting DECIMAL (5,3)
)

CREATE TABLE ExamMarks
(
ExamMarkID INT IDENTITY PRIMARY KEY,
StudentID VARCHAR(8),
ExamID INT FOREIGN KEY REFERENCES Exams(ExamID),
ExamMark DECIMAL (5,4)
)

检查表包含以下数据：

INSERT INTO Exams (ExamID, ExamName, CourseID, RelatedExamID, Weighting)
VALUES (1, 'English',1,NULL,1),
(2, 'French',2,NULL,1),
(3, 'Maths',3,NULL,0.6),
(4, 'Statistics',3,NULL,0.4),
(5, 'Physics Part 1',4,NULL,0.5),
(6, 'Physics Part 2',4,NULL,0.5),
(7, 'Heat and Mass',4,6,0.25)

考试成绩表包含以下数据：

INSERT INTO ExamMarks (StudentID, ExamID, ExamMark)
VALUES ('00112233', 1, 0.75),
('00112233', 2, 0.52),
('00112233', 3, 0.68),
('00112233', 4, 0.8),
('00112233', 5, 0.50),
('00112233', 6, 0.66),
('00112233', 7, 0.45)

这里的想法是给定的课程可能有

如果我想获得每个考试的每个候选人的加权总分 - 暂时忘记课程 4 - 我会执行以下操作：

SELECT em.StudentID,e.CourseID, SUM(em.ExamMark * e.Weighting)/SUM(e.Weighting)
FROM Exams e
INNER JOIN ExamMarks em ON e.ExamID = em.ExamID
GROUP BY em.StudentID,e.CourseID

但是，课程 4 由 3 个部分组成：

需要明确的是，热量和质量占物理第 2 部分的 25%，而物理第 2 部分本身占课程的 50%。

我已将这些数字放入 Excel 电子表格中，经过一番摸索后，我得出结论，我们的学生应该在课程 4 中获得 55.375% 的分数。

然而，不幸的是，我的 SQL（和数学/逻辑）技能不足以在 SQL 查询中得到这个结果。

上面的数据代表了某种简化。事实上，有大约 10000 分需要考虑（涉及大约 500 名学生），大约有 200 种不同的考试，其中可能有 30 种是“子考试”。考虑到这些权重，每年都必须将这些汇总起来，以便为学生提供每门课程的分数。

标签： sqlmathazure-sql-database

解决方案

好的，所以我设法找到了解决方案。我仍然会感谢那些比我了解更多的人可能更有效或更强大的任何其他人。

--Get SubComponent marks
WITH SubComponents
AS
(SELECT
        em.StudentID
       ,em.ExamID
       ,e.RelatedExamID
       ,e.Weighting
       ,e.Weighting * em.ExamMark AS WeightedMark
    FROM Exams e
    INNER JOIN ExamMarks em
        ON e.ExamID = em.ExamID
    WHERE e.RelatedExamID IS NOT NULL),
--Get marks for those components which have subcomponents
ParentComponents
AS
(SELECT
        em.StudentID
       ,e.CourseID
       ,em.ExamID
       ,e.RelatedExamID
       ,e.Weighting
       ,((1 - SubComponents.Weighting) * em.ExamMark) 
            + SubComponents.WeightedMark AS OverallComponentMark
    FROM Exams e
    INNER JOIN ExamMarks em
        ON e.ExamID = em.ExamID
    INNER JOIN SubComponents
        ON SubComponents.RelatedExamID = e.ExamID
        AND SubComponents.StudentID=em.StudentID),

--Get marks for those components which are neither parent nor child components
StandaloneComponents
AS
(SELECT
        em.StudentID
       ,e2.CourseID
       ,em.ExamID
       ,e2.RelatedExamID
       ,e2.Weighting
       ,em.ExamMark
    FROM Exams e2
    INNER JOIN ExamMarks em
        ON e2.ExamID = em.ExamID
    WHERE NOT EXISTS (SELECT
            *
        FROM Exams
        WHERE RelatedExamID = e2.ExamID)
    AND e2.RelatedExamID IS NULL),

-- Bring all the above together
ComponentMarks
AS
(SELECT
        StudentID
       ,CourseID
       ,ExamID
       ,Weighting
       ,ExamMark
    FROM StandaloneComponents
    UNION
    SELECT
        StudentID
       ,CourseID
       ,ExamID
       ,Weighting
       ,OverallComponentMark
    FROM ParentComponents)

-- Finally group and combine marks at course level
SELECT
    StudentID
   ,CourseID
   ,SUM(ExamMark * Weighting) / SUM(Weighting)
FROM ComponentMarks
GROUP BY StudentID
        ,CourseID

推荐阅读