Hive连续系列2

有score表，字段分别为年份，课程，学生，成绩

2020,语文,A,83
2020,数学,A,58
2020,英语,A,29
2020,语文,B,83
2020,数学,B,77
2020,英语,B,67
2021,语文,A,50
2021,数学,A,93
2021,英语,A,70
2021,语文,B,86
2021,数学,B,43
2021,英语,B,39
2022,语文,A,90
2022,数学,A,49
2022,英语,A,86
2022,语文,B,81
2022,数学,B,60
2022,英语,B,45

需求1：求每年每门课程排名第一的学生成绩

方式1：常规方式group by

SELECT
a.year,a.course,b.student
FROM
(--以年和课程为组,查找课程中最大的成绩
SELECT year,course,MAX(score) max_score FROM safety.tmp_scores
group by year,course
)a
JOIN safety.tmp_scores b 
ON a.year=b.year AND a.course=b.course AND a.max_score=b.score

注：结果含义，2020年英语最高成绩是为B的学生，67分

year    course  student 
2020    数学      B
2020    英语      B
2020    语文      A
2020    语文      B
2021    数学      A
2021    英语      A
2021    语文      B
2022    英语      A
2022    数学      B
2022    语文      A

方式二：使用开窗函数

SELECT
a.year,a.course,a.student
FROM
(
    SELECT 
    year,course,student,score,
    MAX(score)  OVER(partition by year,course) max_score
    FROM safety.tmp_scores
) a
WHERE a.score=a.max_score

结果如下：

year    course  student 
2020    数学      B
2020    英语      B
2020    语文      A
2020    语文      B
2021    数学      A
2021    英语      A
2021    语文      B
2022    数学      B
2022    英语      A
2022    语文      A

方式三：此方法只适合SparkSQL，Hive不支持。

SELECT
distinct
year,
course,
first_value(student)  OVER(partition by year,course order by score desc) student
FROM 
safety.tmp_scores
order by year

需求2：求每年总成绩有提升的学生

首先从'提升'可知这是一个求连续的问题，
1、首先求出每年的总成绩

SELECT
year,
student,
sum(score) sum_scote
FROM 
safety.tmp_scores
group by  year,student
ORDER BY student LIMIT 100

结果如下

year    student     sum_scote   
2020        A       170.0
2021        A       213.0
2022        A       225.0
2020        B       227.0
2021        B       168.0
2022        B       186.0

肉眼观察每年有提升的学生是A，关键是如何使用SQL得出

2、求连续，使用lag求出当前总成绩与前年总成绩，进行观察

SELECT 
year,student,sum_score,
--这相减就是求当前和去年的差值，更加好的找联系
sum_score - lag(sum_score,1,0)  OVER(partition by student order by year) f1
FROM 
(
SELECT
year,
student,
sum(score) sum_score
FROM 
safety.tmp_scores
group by  year,student
) t 

结果如下

year                student    sum_score     f1 
2020        A       170     170
2021        A       213     43
2022        A       225     12
2020        B       227     227
2021        B       168     -59
2022        B       186     18

3、lag得到的值都不一样没啥规律，所以可以使用if进行打标签

SELECT 
year,student,sum_score,
sum_score - lag(sum_score,1,0)  OVER(partition by student order by year) f1,
IF(sum_score - lag(sum_score,1,0)  OVER(partition by student order by year)>1,1,0) f2
FROM 
(
SELECT
year,
student,
sum(score) sum_score
FROM 
safety.tmp_scores
group by  year,student
) t 

结果如下，一眼可以得出取f2连续等于1的A学生就是我们要的结果了

year    student  sum_score  f1      f2  
2020        A       170     170     1
2021        A       213     43      1
2022        A       225     12      1
2020        B       227     227     1
2021        B       168     -59     0
2022        B       186     18      1

4、剩下的问题就是如何使用SQL得到A了,如果分别求A和B学生的f2平均值呢？平均值等于1学生就是我们要的了

SELECT
student
FROM
(
    SELECT 
    year,student,sum_score,
    sum_score - lag(sum_score,1,0)  OVER(partition by student order by year) f1,
    IF(sum_score - lag(sum_score,1,0)  OVER(partition by student order by year)>1,1,0) f2
    FROM 
    (
    SELECT
    year,
    student,
    sum(score) sum_score
    FROM 
    safety.tmp_scores
    group by  year,student
    ) t 
) t1
GROUP BY student HAVING AVG(f2)=1

这样就得到我们要的结果A了