我正在开发一个学生平均成绩的SQL排名系统.目标是根据平均范围分配职位,而不仅仅是那些平均水平完全相同的职位.
例如,平均分在90到90.9之间的学生应该排在第四位.如果第4位之后最接近的平均值是85,那么85到85.9之间的平均值将是第5位.
我曾try 将Dense_Rank()窗口函数与Case语句一起使用,但我在实现基于范围的排名逻辑时遇到了问题.
这是我当前的SQL查询:
WITH RankedAverages AS (
SELECT
CAST(AVG(ft_tot_score) AS DECIMAL(10, 2)) AS unique_average,
DENSE_RANK() OVER (ORDER BY AVG(ft_tot_score) DESC) AS dense_rank
FROM
ftscores_primary
WHERE
class_id = 9 AND
section_id = 3 AND
session_id = 19
GROUP BY
student_id
ORDER BY
unique_average DESC
)
SELECT
unique_average,
CASE
WHEN dense_rank = 1 THEN '1st'
WHEN dense_rank = 2 THEN '2nd'
WHEN dense_rank = 3 THEN '3rd'
ELSE
CASE
WHEN RIGHT(dense_rank, 1) = 1 AND dense_rank != 11 THEN CONCAT(dense_rank, 'st')
WHEN RIGHT(dense_rank, 1) = 2 AND dense_rank != 12 THEN CONCAT(dense_rank, 'nd')
WHEN RIGHT(dense_rank, 1) = 3 AND dense_rank != 13 THEN CONCAT(dense_rank, 'rd')
ELSE CONCAT(dense_rank, 'th')
END
END AS position
FROM
RankedAverages
ORDER BY
unique_average DESC;
该查询根据指定的标准输出学生的唯一平均值及其相应的位置.它计算每个学生的平均值,根据平均值的降序分配一个密集排名,然后确定每个唯一平均值的位置. 虽然这很好,但它还没有实现,因为我还想根据平均范围分配等级.
为了更好地理解,请参阅图像ranking 这个逻辑是从第四个位置开始计算的.
在图中,从第四位开始,我们得到了91.80,而第五位是91.10. 由于它们在相同的范围内,即91范围内,它们应该都在第4位. 91.80-4 91.10-4 89.20-5 86.90-6 86.80-6 86.00-6日...
我将感谢任何关于如何实现这一目标的指导