sql-server - 这是否需要递归 CTE，只是创造性的窗口函数，一个循环？

我一生都无法弄清楚如何获得 X 类别分数的加权排名。例如，学生需要回答 3 个类别的 10 个问题(问题数量和类别数量最终都是可变的)。要获得总分，X (3) 个类别中的每个类别的前 1 分将被添加到剩下的任何部分，以加总 10 个问题的总分。

这是数据。我使用 CASE WHEN Row_Number() 来获取 TopInCat

http://sqlfiddle.com/#!6/e6e9f/1

fiddle 有更多的学生。

|问题 |学生 |分类 |得分 | TopInCat |
|----------|---------|----------|-------|--------- -|
| 120149 | 125 | 6 | 1 | 1 |
| 120127 | 125 | 6 | 0.9 | 0 |
| 120124 | 125 | 6 | 0.8 | 0 |
| 120125 | 125 | 6 | 0.7 | 0 |
| 120130 | 125 | 6 | 0.6 | 0 |
| 120166 | 125 | 6 | 0.5 | 0 |
| 120161 | 125 | 6 | 0.4 | 0 |
| 120138 | 125 | 4 | 0.15 | 1 |
| 120069 | 125 | 4 | 0.15 | 0 |
| 120022 | 125 | 4 | 0.15 | 0 |
| 120002 | 125 | 4 | 0.15 | 0 |
| 120068 | 125 | 2 | 0.01 | 1 |
| 120050 | 125 | 3 | 0.05 | 1 |
| 120139 | 125 | 2 | 0 | 0 |
| 120156 | 125 | 2 | 0 | 0 |

这就是我设想它需要看起来的样子，但它不必完全是这样。我只需要按 3 个类别的详细数据提出 10 个问题，这样我就可以对下面的 Sort 1-10 列求和和求平均值。 999's 可以是 null 或其他任何东西，只要我能总结什么是重要的并呈现细节。

|问题 |学生 |分类 |得分 | TopInCat |排序 |
|----------|---------|----------|-------|--------- -|------|
| 120149 | 125 | 6 | 1 | 1 | 1 |
| 120138 | 125 | 4 | 0.15 | 1 | 2 |
| 120068 | 125 | 2 | 0.01 | 1 | 3 |
| 120127 | 125 | 6 | 0.9 | 0 | 4 |
| 120124 | 125 | 6 | 0.8 | 0 | 5 |
| 120125 | 125 | 6 | 0.7 | 0 | 6 |
| 120130 | 125 | 6 | 0.6 | 0 | 7 |
| 120166 | 125 | 6 | 0.5 | 0 | 8 |
| 120161 | 125 | 6 | 0.4 | 0 | 9 |
| 120069 | 125 | 4 | 0.15 | 0 | 10 |
| 120022 | 125 | 4 | 0.15 | 0 | 999 |
| 120002 | 125 | 4 | 0.15 | 0 | 999 |
| 120050 | 125 | 3 | 0.05 | 1 | 999 |
| 120139 | 125 | 2 | 0 | 0 | 999 |
| 120156 | 125 | 2 | 0 | 0 | 999 |

最后一件事，一旦满足 X (3) 阈值，类别就不再重要。因此，第 4 类将正常排序。

|问题 |学生 |分类 |得分 | TopInCat |排序 |
|----------|---------|----------|-------|--------- -|------|
| 120149 | 126 | 6 | 1 | 1 | 1 |
| 120138 | 126 | 4 | 0.75 | 1 | 2 |
| 120068 | 126 | 2 | 0.50 | 1 | 3 |
| 120127 | 126 | 6 | 0.9 | 0 | 4 |
| 120124 | 126 | 6 | 0.8 | 0 | 5 |
| 120125 | 126 | 6 | 0.7 | 0 | 6 |
| 120130 | 126 | 6 | 0.6 | 0 | 7 |
| 120166 | 126 | 6 | 0.5 | 0 | 8 |
| 120050 | 126 | 3 | 0.45 | 1 | 9 |********
| 120161 | 126 | 6 | 0.4 | 0 | 10 |
| 120069 | 126 | 4 | 0.15 | 0 | 999 |
| 120022 | 126 | 4 | 0.15 | 0 | 999 |
| 120002 | 126 | 4 | 0.15 | 0 | 999 |
| 120139 | 126 | 2 | 0 | 0 | 999 |
| 120156 | 126 | 2 | 0 | 0 | 999 |

我真的很感激任何帮助。这几天一直在敲我的头。

最佳答案

对于此类问题，我喜欢采用“积木式”方法。遵循 的格言，首先让它工作，然后如果你需要让它更快 ，这第一步通常就足够了。

所以，给定

CREATE TABLE WeightedScores
    ([Question] int, [Student] int, [Category] int, [Score] dec(3,2))
;

和您的样本数据

INSERT INTO WeightedScores
    ([Question], [Student], [Category], [Score])
VALUES
    (120161, 123, 6, 1),    (120166, 123, 6, 0.64),    (120138, 123, 4, 0.57),    (120069, 123, 4, 0.5),
    (120068, 123, 2, 0.33),    (120022, 123, 4, 0.18),    (120061, 123, 6, 0),    (120002, 123, 4, 0),
    (120124, 123, 6, 0),    (120125, 123, 6, 0),    (120137, 123, 6, 0),    (120154, 123, 6, 0),
    (120155, 123, 6, 0),   (120156, 123, 6, 0),    (120139, 124, 2, 1),    (120156, 124, 2, 1),
    (120050, 124, 3, 0.88),    (120068, 124, 2, 0.87),    (120161, 124, 6, 0.87),    (120138, 124, 4, 0.85),
    (120069, 124, 4, 0.51),    (120166, 124, 6, 0.5),    (120022, 124, 4, 0.43),    (120002, 124, 4, 0),
    (120130, 124, 6, 0),    (120125, 124, 6, 0),    (120124, 124, 6, 0),    (120127, 124, 6, 0),
    (120149, 124, 6, 0),    (120149, 125, 6, 1),    (120127, 125, 6, 0.9),    (120124, 125, 6, 0.8),
    (120125, 125, 6, 0.7),    (120130, 125, 6, 0.6),    (120166, 125, 6, 0.5),    (120161, 125, 6, 0.4),
    (120138, 125, 4, 0.15),    (120069, 125, 4, 0.15),    (120022, 125, 4, 0.15),    (120002, 125, 4, 0.15),
    (120068, 125, 2, 0.01),    (120050, 125, 3, 0.05),    (120139, 125, 2, 0),    (120156, 125, 2, 0),
    (120149, 126, 6, 1),    (120138, 126, 4, 0.75),    (120068, 126, 2, 0.50),    (120127, 126, 6, 0.9),
    (120124, 126, 6, 0.8),    (120125, 126, 6, 0.7),    (120130, 126, 6, 0.6),    (120166, 126, 6, 0.5),
    (120050, 126, 3, 0.45),    (120161, 126, 6, 0.4),    (120069, 126, 4, 0.15),    (120022, 126, 4, 0.15),
    (120002, 126, 4, 0.15),    (120139, 126, 2, 0),    (120156, 126, 2, 0)
;

让我们继续。

这里的复杂部分是确定前三个顶级问题；每个学生感兴趣的十个问题中的其他问题只是按分数排序，这很容易。因此，让我们从确定前三个顶级问题开始。

首先，为每一行分配一个行号，给出该学生在该类别中的分数排序:

;WITH Numbered1 ( Question, Student, Category, Score, SeqInStudentCategory ) AS
(
    SELECT Question, Student, Category, Score
        , ROW_NUMBER() OVER (PARTITION BY Student, Category ORDER BY Score DESC) SeqInStudentCategory
    FROM WeightedScores
)

现在我们只对 SeqInStudentCategory 是 1 的行感兴趣。仅考虑这些行，让我们按学生内的分数对它们进行排序，并对这些行进行编号:

-- within the preceding WITH
, Numbered2 ( Question, Student, Category, Score, SeqInStudent ) AS
(
    SELECT
        Question, Student, Category, Score
        , ROW_NUMBER() OVER (PARTITION BY Student ORDER BY Score DESC) SeqInStudent
    FROM
        Numbered1
    WHERE
        SeqInStudentCategory = 1
)

现在我们只对 SeqInStudent 最多为 3 的行感兴趣。让我们把它们拉出来，这样我们就知道要包含它(并将它从简单的按分数排序中排除，我们将用它来构成剩余的七行):

-- within the preceding WITH
, TopInCat ( Question, Student, Category, Score, SeqInStudent ) AS
(
     SELECT Question, Student, Category, Score, SeqInStudent FROM Numbered2 WHERE SeqInStudent <= 3
)

现在我们为每个学生准备了三个顶级问题。我们现在需要为每个学生识别和排序不是排名靠前的问题:

-- within the preceding WITH
, NotTopInCat ( Question, Student, Category, Score, SeqInStudent ) AS
(
    SELECT
        Question, Student, Category, Score
        , ROW_NUMBER() OVER (PARTITION BY Student ORDER BY Score DESC) SeqInStudent
    FROM
        WeightedScores WS
    WHERE
        NOT EXISTS ( SELECT 1 FROM TopInCat T WHERE T.Question = WS.Question AND T.Student = WS.Student )
)

最后，我们将 TopInCat 与 NotTopInCat 结合，对 NotTopInCat.SeqInStudent 应用适当的偏移量和限制——我们需要将 3 添加到原始值，并取顶部 7 (即 10 - 3 ):

-- within the preceding WITH
, Combined ( Question, Student, Category, Score, CombinedSeq ) AS
(
    SELECT
        Question, Student, Category, Score, SeqInStudent AS CombinedSeq
    FROM
        TopInCat
    UNION
    SELECT
        Question, Student, Category, Score, SeqInStudent + 3 AS CombinedSeq
    FROM
        NotTopInCat
    WHERE
        SeqInStudent <= 10 - 3
)

要获得我们的最终结果:

SELECT * FROM Combined ORDER BY Student, CombinedSeq
;

您可以看到结果 on sqlfiddle 。

请注意，这里我假设每个学生总是至少从三个类别中得到答案。此外，最终输出没有 TopInCat 列，但希望您能看到如果需要，如何重新获得它。

此外，“(问题数量和类别数量最终都将是可变的)”在这里处理起来应该相对简单。但是请注意我的假设(在这种情况下)每个学生的答案中肯定会出现 3 个类别。

关于sql-server - 这是否需要递归 CTE，只是创造性的窗口函数，一个循环？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/35329282/