Hive SQL开窗函数实战

公众号guangcity

发布于 2019-09-20 17:32:54

1.5K0

发布于 2019-09-20 17:32:54

文章被收录于专栏：光城(guangcity)光城(guangcity)

Hive SQL开窗函数实战

开窗函数是数据的一种查询统计语法糖，多是用于离线统计，这同时也是大数据技术栈的应用场景。今天学习Hive SQL的开窗(窗口)函数，对比与MySQL，在MySQL8.0以前是不支持窗口函数的，而Oracle支持。

下面进入实战练习模式：

数据字段描述：

class    student score
math       a      20

第一列为课程类型，比如math，第二列为学生的学号，假设是a、b、c、d这种，第三列为学生得分。下面对这个数据进行表构建与数据导入。

创建表及导入数据：

hive (cookie)> create database if not exists math;
OK
Time taken: 0.31 seconds
hive (cookie)> use math;
OK
Time taken: 0.01 seconds
hive (math)> drop table if exists math;
OK
Time taken: 0.007 seconds
hive (math)> create table math(class string,student string,score int) row format delimited fields terminated by ',';
OK
Time taken: 0.233 seconds
hive (math)> load data local inpath '/home/light/mysql/hive/math.txt' into table math;
Loading data to table math.math
OK
Time taken: 0.405 seconds
hive (math)> select * from math;
OK
math.class    math.student    math.score
math    a   20
math    b   70
math    c   100
math    d   30
math    e   60
math    f   50
math    g   50
Time taken: 0.17 seconds, Fetched: 7 row(s)

执行下面查询与语句：

select 
   class, 
   student, 
   score, 
   sum(score) over (partition by class order by student rows between unbounded preceding and current row) as score1, 
   sum(score) over (partition by class order by student) as score2, 
   sum(score) over (partition by class) as score3, 
   sum(score) over (partition by class order by student rows between 3 preceding and current row) as score4, 
   sum(score) over (partition by class order by student rows between 3 preceding and 1 following) as score5, 
   sum(score) over (partition by class order by student rows between current row and unbounded following) as score6 
from math;

上述的代码解释：

如果不指定ROWS BETWEEN,默认为从起点到当前行;如果不指定ORDER BY，则将分组内所有值累加;

ROWS BETWEEN含义,也叫做WINDOW子句(这就是开窗！)

PRECEDING：往前；FOLLOWING：往后；CURRENT ROW：当前行

UNBOUNDED：起点，

　　UNBOUNDED PRECEDING 表示从前面的起点，

　　UNBOUNDED FOLLOWING：表示到后面的终点

结果：