在加权标记中计算加权标记

x0fgdtte 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(250)

我有一个数学/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)

这里的想法是，给定的课程可能
一次考试（如英语和法语）
多重考试（比如课程3，有两个考试叫做“数学”和“物理”），它们有独立的权重——在这种情况下，课程的结构使得数学考试占总数的60%，而物理考试占40%
有分考的考试，如课程4，稍后会有更多内容。
如果我想得到每个考生每次考试的加权总分——暂时忘记课程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部分组成：
物理第一部分-占总数的50%，以及
物理第2部分-也占总数的50%
热量和质量占物理学第2部分的25%（因此其id在“relatedexamid”列中）
很明显，热量和质量占物理第二部分的25%，而物理第二部分本身占课程的50%。
我把这些数据放进了一个excel电子表格，经过一番苦思冥想，我终于得出结论，我们的学生第四门课的分数应该是55.375%。
然而，不幸的是，我的sql（和数学/逻辑）技能不足以在sql查询中得到这个结果。
上面的数据有些简化。事实上，大约有10000个分数需要考虑（大约500个学生参与），大约200个不同的考试，其中可能有30个是“分考”。每年，考虑到这些权重，这些数据必须合计起来，以给学生每门课程的分数。

sql azure-sql-database Math

来源：https://stackoverflow.com/questions/62898317/calculating-weighted-marks-within-weighted-marks

1条答案

按热度按时间

cgfeq70w1#

好吧，我找到了解决办法。我仍然会感激那些比我了解得多的人能给予我更有效或更有力的帮助。

--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

赞(0）回复(0）举报 2021-07-26

我来回答

在加权标记中计算加权标记

1条答案

相关问题

热门标签

最新问答