9. Hive-3

使用 beeline 和 hiveserver2 连接

在一个窗口中使用 hiveserver2,让窗口一直不关闭,在另一个窗口进入 beeline 后使用 JDBC 进行连接,使用 beeline 的好处是查询结果格式以更方便的形式的显示

常用函数

nvl

NVL( string1, replace_with): 判断 string1 是否为null,如果为null,使用 replace_with 替换null,否则就返回 string1

select avg(nvl(comm, 0)) from emp1;

例如上述例子中,对 comm 列计算平均值,如果 comm 为 null,则使用 0 代替。如果这里不使用 nvl 函数的话,那么计算平均值是不算上 null 的个数的,数据如下所示,不使用 nvl ,平均值为 (300+500+1400+0)/ 4 = 550。使用 nvl 平均值就是 146.66666666666666

concat

字符串拼接。 可以在参数中传入多个 string 类型的字符串,但是一旦有一个参数为 null,则返回 null

select concat("a","v"); -- av
select concat("a","v",null); -- NULL

concat_ws

使用指定的分隔符完成字符串拼接,格式为 concat_ws(分隔符,[string | array<string>]+),第一个参数为分隔符,后面为要拼接的字符串或者数组。

select concat_ws(".","zhansan",array("lisi","wangwu"));  -- zhansan.lisi.wangwu

collect_set

collect_set(列名) 作用是将此列的多行记录合并为一个 set 集合,去重

collect_list

collect_list(列名) 作用是将此列的多行记录合并为一个 set 集合,不去重

explode

explode(列名) 参数只能是 array 或 map, 将 array 类型参数转为1列N行, 将 map 类型参数转为2列N行

练习

练习一

有以下数据,求出不同部门男女各多少人

期望查询结果如下

A     2       1
B     1       2

方法1,按照部门分组,单独查询出男女的个数,然后在将结果进行汇总。下面方式 HQL 性能不高,有两个字查询,一个汇总,所以需要三个 job 才能运行完成。

 select a.dept_id, male_count, female_count from
 (select dept_id, count(1) male_count from emp_sex where sex="男" group by dept_id) a
 join
 (select dept_id, count(1) female_count from emp_sex where sex="女" group by dept_id) b
 on a.dept_id = b.dept_id;

方法2,利用 sum 函数,在求男性总人数时,如果当前人的性别为男,记1,否则记 0,sum 该字段就得到了男性的值。在求女性总人数时,如果当前人的性别为女,记1,否则记0,sum 该字段就得到了男性的值。

case when 的语法如下,这一列中出现的 值1 会被替换为 值2 , 值3 会被替换为 值4, 其他的替换为值5

case  列名 
    when  值1  then  值2
    when  值3  then  值4
    ...
    else 值5
end
select dept_id,
sum(case sex when "男" then 1 else 0 end) male_count,
sum(case sex when "女" then 1 else 0 end) female_count
from emp_sex group by dept_id;

使用 case when 的优化 HQL 如上,只需要一个 job 就能完成。

HQL 中除了 case when 能做判断外,if 也能做判断,语法为 if(判断表达式,值1,值2),值1当表达式为 true 时获取,值2当表达式为 false 时获取。实现 HQL 如下:

select dept_id,
sum(if(sex == "男", 1, 0)) male_count,
sum(if(sex == "女", 1, 0)) female_count
from emp_sex group by dept_id;

这个 HQL 也是只需要一个 job 就能完成。

练习二

把星座和血型一样的人归类到一起。结果如下:

射手座,A            大海|凤姐
白羊座,A            孙悟空|猪八戒
白羊座,B            宋宋

这里需要使用函数 concat_ws 进行字符串的拼接,还需要使用 collect_list 将某列的多行记录合并为一个 list 集合,不去重。

select  concat(constellation,',',blood_type),concat_ws('|',collect_list(name))
from person_info
group by constellation,blood_type

练习三

将电影分类中的数组数据展开。结果如下:

《疑犯追踪》      悬疑
《疑犯追踪》      动作
《疑犯追踪》      科幻
《疑犯追踪》      剧情
《Lie to me》   悬疑
《Lie to me》   警匪
《Lie to me》   动作
《Lie to me》   心理
《Lie to me》   剧情
《战狼2》        战争
《战狼2》        动作
《战狼2》        灾难

将 category 列进行 explode,即转换为 1列N行

0: jdbc:hive2://hadoop10:10000> select explode(category) from movie_info;
+------+--+
| col  |
+------+--+
| 悬疑   |
| 动作   |
| 科幻   |
| 剧情   |
| 悬疑   |
| 警匪   |
| 动作   |
| 心理   |
| 剧情   |
| 战争   |
| 动作   |
| 灾难   |
+------+--+
select movie,col1
from movie_info lateral view explode(category) tmp1 as col1

lateral view 的作用是将炸裂后的1列N行,在逻辑上依然视作1列1行,实际是1列N行,然后查询时和movie进行笛卡尔集这个操作在hive中称为侧写(lateral view)

练习四

有以下数据

person_info2.names      person_info2.tags       person_info2.hobbys
["jack","tom","jerry"]  ["阳光男孩","肌肉男孩","直男"]  ["晒太阳","健身","说多喝热水"]
["marry","nancy"]       ["阳光女孩","肌肉女孩","腐女"]  ["晒太阳","健身","看有内涵的段子"]

希望得到以下查询结果

期望结果:
jack    阳光男孩    晒太阳
jack    阳光男孩    健身
jack    阳光男孩    说多喝热水
jack    肌肉男孩    晒太阳
jack    肌肉男孩    健身
jack    肌肉男孩    说多喝热水
.....

查询 SQL

select name,tag,hobby
from person_info2
lateral view explode(names) tmp1 as name
lateral view explode(tags) tmp1 as tag
lateral view explode(hobbys) tmp1 as hobby;

窗口函数

即可以在函数在运行时通过改变窗口的大小,来控制计算的数据集的范围。

文档地址 https://cwiki.apache.org/confluence/display/Hive/LanguageManual+WindowingAndAnalytics

常用函数

窗口函数有以下三类

第一类

LEAD: LEAD (scalar_expression [,offset] [,default]): 返回当前行以下N行的指定列的列值,如果找不到,就采用默认值

LAG: LAG (scalar_expression [,offset] [,default]): 返回当前行以上N行的指定列的列值,如果找不到,就采用默认值

FIRST_VALUE: FIRST_VALUE(列名,[false(默认)]):  返回当前窗口指定列的第一个值,第二个参数如果为true,代表加入第一个值为null,跳过空值,继续寻找!

LAST_VALUE: LAST_VALUE(列名,[false(默认)]):  返回当前窗口指定列的最后一个值,第二个参数如果为true,代表加入第一个值为null,跳过空值,继续寻找!

第二类

统计类的函数(一般都需要结合over使用): min,max,avg,sum,count

第三类

排名分析函数:RANK,ROW_NUMBER,DENSE_RANK,CUME_DIST,PERCENT_RANK,NTILE

语法

相关语法解释

OVER():指定分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变而变化
CURRENT ROW:当前行
n PRECEDING:往前n行数据
n FOLLOWING:往后n行数据
UNBOUNDED:起点,UNBOUNDED PRECEDING 表示从前面的起点, UNBOUNDED FOLLOWING表示到后面的终点
LAG(col,n):往前第n行数据
LEAD(col,n):往后第n行数据
NTILE(n):把有序分区中的行分发到指定数据的组中,各个组有编号,编号从1开始,对于每一行,NTILE返回此行所属的组的编号。注意:n必须为int类型。

窗口函数的使用格式为 函数 over( partition by 字段 ,order by 字段 window_clause ) window_clause 为窗口的大小,窗口大小常用格式如下

(rows | range) between (unbounded | [num]) preceding and ([num] preceding | current row | (unbounded | [num]) following)

(rows | range) between current row and (current row | (unbounded | [num]) following)

(rows | range) between [num] following and (unbounded | [num]) following

特殊情况:

  1. 在 over() 中既没有出现 windows_clause,也没有出现 order by,窗口默认为 rows between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING
  2. 在over()中没有出现 windows_clause,但指定了 order by,窗口默认为 rows between UNBOUNDED PRECEDING and CURRENT ROW

窗口函数和分组有什么区别:

  1. 如果是分组操作,select 后只能写分组后的字段
  2. 如果是窗口函数,窗口函数是在指定的窗口内,对每条记录都执行一次函数
  3. 如果是分组操作,有去重效果,而窗口函数中的 partition不去重

注意⚠️:不是所有的函数在运行都是可以通过改变窗口的大小,来控制计算的数据集的范围,所有的排名函数和LAG,LEAD,支持使用 over(),但是在 over() 中不能定义 window_clause。

查询示例

例如有以下数据

0: jdbc:hive2://hadoop10:10000> select * from business;
+----------------+---------------------+----------------+--+
| business.name  | business.orderdate  | business.cost  |
+----------------+---------------------+----------------+--+
| jack           | 2017-01-01          | 10             |
| tony           | 2017-01-02          | 15             |
| jack           | 2017-02-03          | 23             |
| tony           | 2017-01-04          | 29             |
| jack           | 2017-01-05          | 46             |
| jack           | 2017-04-06          | 42             |
| tony           | 2017-01-07          | 50             |
| jack           | 2017-01-08          | 55             |
| mart           | 2017-04-08          | 62             |
| mart           | 2017-04-09          | 68             |
| neil           | 2017-05-10          | 12             |
| mart           | 2017-04-11          | 75             |
| neil           | 2017-06-12          | 80             |
| mart           | 2017-04-13          | 94             |
+----------------+---------------------+----------------+--+

需要进行以下查询

(1)查询在2017年4月份购买过的顾客及总人数

select name,count(*) over(rows between UNBOUNDED  PRECEDING and UNBOUNDED  FOLLOWING)
from business
where substring(orderdate,1,7)='2017-04'
group by name

上面的写法等价于

select name,count(*) over()
from business
where substring(orderdate,1,7)='2017-04'
group by name

查询结果为

+-------+-----------------+--+
| name  | count_window_0  |
+-------+-----------------+--+
| mart  | 2               |
| jack  | 2               |
+-------+-----------------+--+

(2)查询顾客的购买明细及月购买总额

 select name,orderdate,cost,sum(cost) over(partition by name,substring(orderdate,1,7) ) 
 from business

查询 name, orderdate, cost,并对 cost 进行按月份的累加求和, 结果如下

+-------+-------------+-------+---------------+--+
| name  |  orderdate  | cost  | sum_window_0  |
+-------+-------------+-------+---------------+--+
| jack  | 2017-01-05  | 46    | 111           |
| jack  | 2017-01-08  | 55    | 111           |
| jack  | 2017-01-01  | 10    | 111           |
| jack  | 2017-02-03  | 23    | 23            |
| jack  | 2017-04-06  | 42    | 42            |
| mart  | 2017-04-13  | 94    | 299           |
| mart  | 2017-04-11  | 75    | 299           |
| mart  | 2017-04-09  | 68    | 299           |
| mart  | 2017-04-08  | 62    | 299           |
| neil  | 2017-05-10  | 12    | 12            |
| neil  | 2017-06-12  | 80    | 80            |
| tony  | 2017-01-04  | 29    | 94            |
| tony  | 2017-01-02  | 15    | 94            |
| tony  | 2017-01-07  | 50    | 94            |
+-------+-------------+-------+---------------+--+

(3)查询顾客的购买明细, 并将cost按照日期进行累加

 select name,orderdate,cost,sum(cost) over(partition by name order by orderdate ) 
 from business

查询 name, orderdate, cost,并对 cost 进行按名称的累加,结果如下

+-------+-------------+-------+---------------+--+
| name  |  orderdate  | cost  | sum_window_0  |
+-------+-------------+-------+---------------+--+
| jack  | 2017-01-01  | 10    | 10            |
| jack  | 2017-01-05  | 46    | 56            |
| jack  | 2017-01-08  | 55    | 111           |
| jack  | 2017-02-03  | 23    | 134           |
| jack  | 2017-04-06  | 42    | 176           |
| mart  | 2017-04-08  | 62    | 62            |
| mart  | 2017-04-09  | 68    | 130           |
| mart  | 2017-04-11  | 75    | 205           |
| mart  | 2017-04-13  | 94    | 299           |
| neil  | 2017-05-10  | 12    | 12            |
| neil  | 2017-06-12  | 80    | 92            |
| tony  | 2017-01-02  | 15    | 15            |
| tony  | 2017-01-04  | 29    | 44            |
| tony  | 2017-01-07  | 50    | 94            |
+-------+-------------+-------+---------------+--+

(4)查询顾客的购买明细及顾客上次的购买时间

select name,orderdate,cost,lag(orderdate,1,'无数据') over(partition by name order by orderdate) from business

LAG(col,n) 表示取之前的第n行的 col 列的数据

上面查询代表查询 name,orderdate,cost 信息,并且取 orderdate 的上一条数据,如果没数据就显示 “无数据”,并按 name 分组,orderdate 排序

+-------+-------------+-------+---------------+--+
| name  |  orderdate  | cost  | lag_window_0  |
+-------+-------------+-------+---------------+--+
| jack  | 2017-01-01  | 10    | 无数据           |
| jack  | 2017-01-05  | 46    | 2017-01-01    |
| jack  | 2017-01-08  | 55    | 2017-01-05    |
| jack  | 2017-02-03  | 23    | 2017-01-08    |
| jack  | 2017-04-06  | 42    | 2017-02-03    |
| mart  | 2017-04-08  | 62    | 无数据           |
| mart  | 2017-04-09  | 68    | 2017-04-08    |
| mart  | 2017-04-11  | 75    | 2017-04-09    |
| mart  | 2017-04-13  | 94    | 2017-04-11    |
| neil  | 2017-05-10  | 12    | 无数据           |
| neil  | 2017-06-12  | 80    | 2017-05-10    |
| tony  | 2017-01-02  | 15    | 无数据           |
| tony  | 2017-01-04  | 29    | 2017-01-02    |
| tony  | 2017-01-07  | 50    | 2017-01-04    |
+-------+-------------+-------+---------------+--+

(5)查询顾客的购买明细及顾客下次的购买时间

和上面差不多,只不过需要将函数 lag 换为 lead

select name,orderdate,cost,lead(orderdate,1,'无数据') over(partition by name order by orderdate ) from business

结果如下

+-------+-------------+-------+----------------+--+
| name  |  orderdate  | cost  | lead_window_0  |
+-------+-------------+-------+----------------+--+
| jack  | 2017-01-01  | 10    | 2017-01-05     |
| jack  | 2017-01-05  | 46    | 2017-01-08     |
| jack  | 2017-01-08  | 55    | 2017-02-03     |
| jack  | 2017-02-03  | 23    | 2017-04-06     |
| jack  | 2017-04-06  | 42    | 无数据            |
| mart  | 2017-04-08  | 62    | 2017-04-09     |
| mart  | 2017-04-09  | 68    | 2017-04-11     |
| mart  | 2017-04-11  | 75    | 2017-04-13     |
| mart  | 2017-04-13  | 94    | 无数据            |
| neil  | 2017-05-10  | 12    | 2017-06-12     |
| neil  | 2017-06-12  | 80    | 无数据            |
| tony  | 2017-01-02  | 15    | 2017-01-04     |
| tony  | 2017-01-04  | 29    | 2017-01-07     |
| tony  | 2017-01-07  | 50    | 无数据            |
+-------+-------------+-------+----------------+--+

(6)查询顾客的购买明细及顾客本月第一次购买的时间

第一次购买的时间只需要按照购买时间拍正序,然后取第一条记录即可,这里使用函数 FIRST_VALUE 取第一条记录。

select name,orderdate,cost,FIRST_VALUE(orderdate,true) over(partition by name,substring(orderdate,1,7) order by orderdate ) from business

查询结果如下

+-------+-------------+-------+-----------------------+--+
| name  |  orderdate  | cost  | first_value_window_0  |
+-------+-------------+-------+-----------------------+--+
| jack  | 2017-01-01  | 10    | 2017-01-01            |
| jack  | 2017-01-05  | 46    | 2017-01-01            |
| jack  | 2017-01-08  | 55    | 2017-01-01            |
| jack  | 2017-02-03  | 23    | 2017-02-03            |
| jack  | 2017-04-06  | 42    | 2017-04-06            |
| mart  | 2017-04-08  | 62    | 2017-04-08            |
| mart  | 2017-04-09  | 68    | 2017-04-08            |
| mart  | 2017-04-11  | 75    | 2017-04-08            |
| mart  | 2017-04-13  | 94    | 2017-04-08            |
| neil  | 2017-05-10  | 12    | 2017-05-10            |
| neil  | 2017-06-12  | 80    | 2017-06-12            |
| tony  | 2017-01-02  | 15    | 2017-01-02            |
| tony  | 2017-01-04  | 29    | 2017-01-02            |
| tony  | 2017-01-07  | 50    | 2017-01-02            |
+-------+-------------+-------+-----------------------+--+

(7)查询顾客的购买明细及顾客本月最后一次购买的时间

和上面类似,只是使用函数不同, 因为是取本月最后一次购买的时间,所以这里要限制窗口的位置,窗口的位置就是当前行到本次分组的最后一行。

select name,orderdate,cost,LAST_VALUE(orderdate,true) over(partition by name,substring(orderdate,1,7) order by orderdate rows between CURRENT row and UNBOUNDED  FOLLOWING) from business 

查询结果如下

+-------+-------------+-------+----------------------+--+
| name  |  orderdate  | cost  | last_value_window_0  |
+-------+-------------+-------+----------------------+--+
| jack  | 2017-01-01  | 10    | 2017-01-08           |
| jack  | 2017-01-05  | 46    | 2017-01-08           |
| jack  | 2017-01-08  | 55    | 2017-01-08           |
| jack  | 2017-02-03  | 23    | 2017-02-03           |
| jack  | 2017-04-06  | 42    | 2017-04-06           |
| mart  | 2017-04-08  | 62    | 2017-04-13           |
| mart  | 2017-04-09  | 68    | 2017-04-13           |
| mart  | 2017-04-11  | 75    | 2017-04-13           |
| mart  | 2017-04-13  | 94    | 2017-04-13           |
| neil  | 2017-05-10  | 12    | 2017-05-10           |
| neil  | 2017-06-12  | 80    | 2017-06-12           |
| tony  | 2017-01-02  | 15    | 2017-01-07           |
| tony  | 2017-01-04  | 29    | 2017-01-07           |
| tony  | 2017-01-07  | 50    | 2017-01-07           |
+-------+-------------+-------+----------------------+--+

(8)查询顾客的购买明细及顾客最近三次cost花费

最近三次可以是 当前和之前两次 或 当前+前一次+后一次

当前和之前两次:

select name,orderdate,cost,sum(cost) over(partition by name order by orderdate rows between 2 PRECEDING and CURRENT row) from business

当前+前一次+后一次:

select name,orderdate,cost,sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and 1 FOLLOWING) from business

查询结果如下

+-------+-------------+-------+---------------+--+
| name  |  orderdate  | cost  | sum_window_0  |
+-------+-------------+-------+---------------+--+
| jack  | 2017-01-01  | 10    | 56            |
| jack  | 2017-01-05  | 46    | 111           |
| jack  | 2017-01-08  | 55    | 124           |
| jack  | 2017-02-03  | 23    | 120           |
| jack  | 2017-04-06  | 42    | 65            |
| mart  | 2017-04-08  | 62    | 130           |
| mart  | 2017-04-09  | 68    | 205           |
| mart  | 2017-04-11  | 75    | 237           |
| mart  | 2017-04-13  | 94    | 169           |
| neil  | 2017-05-10  | 12    | 92            |
| neil  | 2017-06-12  | 80    | 92            |
| tony  | 2017-01-02  | 15    | 44            |
| tony  | 2017-01-04  | 29    | 94            |
| tony  | 2017-01-07  | 50    | 79            |
+-------+-------------+-------+---------------+--+

(9)查询前20%时间的订单信息

排名函数

RANK: 允许并列,一旦有并列跳号! 
ROW_NUMBER: 行号! 连续的,每个号之间差1!
DENSE_RANK: 允许并列,一旦有并列不跳号!
CUME_DIST:  从排序后的第一行到当前值之间数据 占整个数据集的百分比!
PERCENT_RANK:  rank-1/ 总数据量-1   
NTILE(x):  将数据集均分到X个组中,返回每条记录所在的组号

示例数据如下

+-------------+----------------+--------------+--+
| score.name  | score.subject  | score.score  |
+-------------+----------------+--------------+--+
| 孙悟空         | 语文             | 87           |
| 孙悟空         | 数学             | 95           |
| 孙悟空         | 英语             | 68           |
| 大海          | 语文             | 94           |
| 大海          | 数学             | 56           |
| 大海          | 英语             | 84           |
| 宋宋          | 语文             | 64           |
| 宋宋          | 数学             | 86           |
| 宋宋          | 英语             | 84           |
| 婷婷          | 语文             | 65           |
| 婷婷          | 数学             | 85           |
| 婷婷          | 英语             | 78           |
+-------------+----------------+--------------+--+

查询示例如下

select  *,rank() over(order by score) ranknum,
ROW_NUMBER() over(order by score) rnnum,
DENSE_RANK() over(order by score) drnum,
CUME_DIST() over(order by score) cdnum,
PERCENT_RANK() over(order by score) prnum
from score;

查询结果

score.name      score.subject   score.score     ranknum rnnum   drnum   cdnum   prnum
大海    数学    56      1       1       1       0.08333333333333333     0.0
宋宋    语文    64      2       2       2       0.16666666666666666     0.09090909090909091
婷婷    语文    65      3       3       3       0.25    0.18181818181818182
孙悟空  英语    68      4       4       4       0.3333333333333333      0.2727272727272727
婷婷    英语    78      5       5       5       0.4166666666666667      0.36363636363636365
宋宋    英语    84      6       6       6       0.5833333333333334      0.45454545454545453
大海    英语    84      6       7       6       0.5833333333333334      0.45454545454545453
婷婷    数学    85      8       8       7       0.6666666666666666      0.6363636363636364
宋宋    数学    86      9       9       8       0.75    0.7272727272727273
孙悟空  语文    87      10      10      9       0.8333333333333334      0.8181818181818182
大海    语文    94      11      11      10      0.9166666666666666      0.9090909090909091
孙悟空  数学    95      12      12      11      1.0     1.0
Time taken: 38.666 seconds, Fetched: 12 row(s)

查询示例

(1) 按照科目进行排名

select *,rank() over(partition by subject order by score desc) from score

查询结果如下

+-------------+----------------+--------------+----------------+--+
| score.name  | score.subject  | score.score  | rank_window_0  |
+-------------+----------------+--------------+----------------+--+
| 孙悟空         | 数学             | 95           | 1              |
| 宋宋          | 数学             | 86           | 2              |
| 婷婷          | 数学             | 85           | 3              |
| 大海          | 数学             | 56           | 4              |
| 宋宋          | 英语             | 84           | 1              |
| 大海          | 英语             | 84           | 1              |
| 婷婷          | 英语             | 78           | 3              |
| 孙悟空         | 英语             | 68           | 4              |
| 大海          | 语文             | 94           | 1              |
| 孙悟空         | 语文             | 87           | 2              |
| 婷婷          | 语文             | 65           | 3              |
| 宋宋          | 语文             | 64           | 4              |
+-------------+----------------+--------------+----------------+--+

(2)给每个学生的总分进行排名

select name,sumscore,rank()  over( order by sumscore desc)
from
(select name,sum(score) sumscore
from  score
group by  name) tmp
+-------+-----------+----------------+--+
| name  | sumscore  | rank_window_0  |
+-------+-----------+----------------+--+
| 孙悟空   | 250       | 1              |
| 宋宋    | 234       | 2              |
| 大海    | 234       | 2              |
| 婷婷    | 228       | 4              |
+-------+-----------+----------------+--+

(3)求每个学生的成绩明细及给每个学生的总分和总分排名

select *,DENSE_RANK() over(order by tmp.sumscore desc)
from
(select *,sum(score) over(partition by name)  sumscore
from score) tmp
+-----------+--------------+------------+---------------+----------------------+--+
| tmp.name  | tmp.subject  | tmp.score  | tmp.sumscore  | dense_rank_window_0  |
+-----------+--------------+------------+---------------+----------------------+--+
| 孙悟空       | 语文           | 87         | 250           | 1                    |
| 孙悟空       | 数学           | 95         | 250           | 1                    |
| 孙悟空       | 英语           | 68         | 250           | 1                    |
| 宋宋        | 语文           | 64         | 234           | 2                    |
| 宋宋        | 数学           | 86         | 234           | 2                    |
| 宋宋        | 英语           | 84         | 234           | 2                    |
| 大海        | 语文           | 94         | 234           | 2                    |
| 大海        | 数学           | 56         | 234           | 2                    |
| 大海        | 英语           | 84         | 234           | 2                    |
| 婷婷        | 语文           | 65         | 228           | 3                    |
| 婷婷        | 数学           | 85         | 228           | 3                    |
| 婷婷        | 英语           | 78         | 228           | 3                    |
+-----------+--------------+------------+---------------+----------------------+--+

(4)只查询每个科目的成绩的前2名

select *
from
(select *,rank() over(partition by subject order by score desc) rn
from score) tmp
where rn<=2
+-----------+--------------+------------+---------+--+
| tmp.name  | tmp.subject  | tmp.score  | tmp.rn  |
+-----------+--------------+------------+---------+--+
| 孙悟空       | 数学           | 95         | 1       |
| 宋宋        | 数学           | 86         | 2       |
| 宋宋        | 英语           | 84         | 1       |
| 大海        | 英语           | 84         | 1       |
| 大海        | 语文           | 94         | 1       |
| 孙悟空       | 语文           | 87         | 2       |
+-----------+--------------+------------+---------+--+

(5)查询学生成绩明细,并显示当前科目最高分

select *,max(score) over(partition by subject)
from score

或者

select *,FIRST_VALUE(score) over(partition by subject order by score desc)
from score
+-------------+----------------+--------------+---------------+--+
| score.name  | score.subject  | score.score  | max_window_0  |
+-------------+----------------+--------------+---------------+--+
| 婷婷          | 数学             | 85           | 95            |
| 宋宋          | 数学             | 86           | 95            |
| 大海          | 数学             | 56           | 95            |
| 孙悟空         | 数学             | 95           | 95            |
| 婷婷          | 英语             | 78           | 84            |
| 宋宋          | 英语             | 84           | 84            |
| 大海          | 英语             | 84           | 84            |
| 孙悟空         | 英语             | 68           | 84            |
| 婷婷          | 语文             | 65           | 94            |
| 宋宋          | 语文             | 64           | 94            |
| 大海          | 语文             | 94           | 94            |
| 孙悟空         | 语文             | 87           | 94            |
+-------------+----------------+--------------+---------------+--+