评级表中的数据如下所示:
| StudentID | Rating | ScoreType | Date |
|---|---|---|---|
| 1 | 12 | RAPID | 2023-01-01 |
| 1 | 15 | RAPID | 2023-01-10 |
| 1 | 20 | RAPID | 2023-02-15 |
| 1 | 25 | RAPID | 2023-02-20 |
| 1 | 25 | BLITZ | 2023-03-01 |
| 1 | 33 | BLITZ | 2023-03-20 |
| 2 | 17 | RAPID | 2023-01-15 |
| 2 | 19 | BLITZ | 2023-02-06 |
我正在try 构建的是这样的:
| StudentID | RAPID_Rating | RAPID_Games | BLITZ_Rating | BLITZ_Games | Total Games |
|---|---|---|---|---|---|
| 1 | 25 | 4 | 33 | 2 | 6 |
| 2 | 17 | 1 | 19 | 1 | 2 |
每个学生的数据应该是一行
预期输出的列定义:
RAPID_RATING:基于ScoreType=‘RAPID’的日期的最新评级值
Rapid_Games:每个学生玩的快速游戏的总数
Blitz_Rating:基于日期的最新评价值，其中ScoreType=‘blitz’
Flitz_Games:每个学生玩的BLIRZ游戏总数
游戏总数:每个学生玩的游戏总数
我try 过的SQL代码:
select
coalesce(a.StudentID,b.StudentID) as StudentID,
a.rating as RAPID_Rating,
RAPID_Games,
b.rating as BLITZ_Rating,
BLITZ_Games,
RAPID_Games + BLITZ_Games as Total_Games
from
(select
StudentID,
ScoreType,
rating,
count(rating) over(partition by StudentID,ScoreType) as RAPID_Games,
row_number() over(partition by StudentID,ScoreType order by date desc) as RNUM
from rating
where ScoreType ='RAPID'
) a join (
select
StudentID,
ScoreType,
rating,
count(rating) over(partition by StudentID,ScoreType) as BLITZ_Games,
row_number() over(partition by StudentID,ScoreType order by date desc) as RNUM
from rating
where ScoreType ='BLITZ'
) b on a.StudentID = b.StudentID
where a.RNUM = 1 and b.RNUM = 1
我必须为另外2个类别做这个计算，然后再加起来2个子查询.有什么办法可以优化这段SQL代码吗？