hql 计算连续10次之类的问题

今天看到一条hql题目如下：

核心思想：两个有序字段相减的值相同，小例子如下：
list a = [1,2,4,5,6,7,8,10,11,12] (看作 test_id)
list b = [1,2,3,4,5,6,7,8,9,10] （看作 row_number）
令list c = list a - list b
list c = [0,0,1,1,1,1,1,2,2,2]

问题解决（没有做测试哈，只是整理了思路写HQL，若有误或有更简单的方法可以在博客留言）：
1、求出各个test每个学生的总成绩
SELECT test_id,std_id,std_name,
SUM(score) OVER(PARTITION BY test_id,std_id) AS total_score
FROM test_tb

2、对学生总成绩进行排名
SELECT test_id,std_id,std_name,
DENSE_RANK() OVER(PARTITION BY test_id ORDER BY total_score) as d_rank
FROM
(SELECT test_id,std_id,std_name,
SUM(score) OVER(PARTITION BY test_id,std_id) AS total_score
FROM test_tb) t1

3、取各个test中排名前三的老哥
SELECT test_id,std_id,std_name
FROM
(SELECT test_id,std_id,std_name,
DENSE_RANK() OVER(PARTITION BY test_id ORDER BY total_score) as d_rank
FROM
(SELECT test_id,std_id,std_name,
SUM(score) OVER(PARTITION BY test_id,std_id) AS total_score
FROM test_tb) t1) t2
WHERE d_rank <= 3

4、根据学生做分区，按test_id排序，增加row_num字段
SELECT test_id,std_id,std_name,
ROW_NUMBER() OVER(PARTITION BY std_id ORDER BY test_id) as row_num
FROM
(SELECT test_id,std_id,std_name
FROM
(SELECT test_id,std_id,std_name,
DENSE_RANK() OVER(PARTITION BY test_id ORDER BY total_score) as d_rank
FROM
(SELECT test_id,std_id,std_name,
SUM(score) OVER(PARTITION BY test_id,std_id) AS total_score
FROM test_tb) t1) t2
WHERE d_rank <= 3) t3

5、根据row_num和test_id 获取差值(字段diff)
SELECT std_id,std_name,(row_num-test_id) as diff
FROM
(SELECT test_id,std_id,std_name,
ROW_NUMBER() OVER(PARTITION BY std_id ORDER BY test_id) as row_num
FROM
(SELECT test_id,std_id,std_name
FROM
(SELECT test_id,std_id,std_name,
DENSE_RANK() OVER(PARTITION BY test_id ORDER BY total_score) as d_rank
FROM
(SELECT test_id,std_id,std_name,
SUM(score) OVER(PARTITION BY test_id,std_id) AS total_score
FROM test_tb) t1) t2
WHERE d_rank <= 3) t3) t4

6、筛选出各个学生差值数量>=10的数据
SELECT std_id,std_name
FROM
(SELECT std_id,std_name,(row_num-test_id) as diff
FROM
(SELECT test_id,std_id,std_name,
ROW_NUMBER() OVER(PARTITION BY std_id ORDER BY test_id) as row_num
FROM
(SELECT test_id,std_id,std_name
FROM
(SELECT test_id,std_id,std_name,
DENSE_RANK() OVER(PARTITION BY test_id ORDER BY total_score) as d_rank
FROM
(SELECT test_id,std_id,std_name,
SUM(score) OVER(PARTITION BY test_id,std_id) AS total_score
FROM test_tb) t1) t2
WHERE d_rank <= 3) t3) t4) t5
GROUP BY std_id,std_name,diff
HAVING diff >= 10