hive 开窗自我总结

原始数据:

name,orderdate,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

不能执行,聚合函数和普通字段同时使用,需要该字段为分组后的字段

select name,count(*)
from business
where date_format(orderdate,'Y-MM')='2017-04'

结果: Expression not in GROUP BY key 'name'

#使用group by之后,函数聚合函数是对分组内的数据进行聚合

select name,count(*)
from business
where date_format(orderdate,'Y-MM')='2017-04'
group by name;

结果:

name,count(*)
jack, 1
mart, 4

使用over()开窗之后,聚合函数是以组为基本单位进行聚合

select name,count(*) over()
from business
where date_format(orderdate,'Y-MM')='2017-04'
group by name;

结果: 以组为单位,所以统计的是组的个数

name,count(*)
jack,2
mart,2

使用over()开窗+聚合函数,但是没有group by 分组;窗口范围为整个表

select name,count(*) over()
from business
where date_format(orderdate,'Y-MM')='2017-04'

结果: 因为没有使用group by 分组,但使用了开窗,所以每行后面都是聚合函数的结果

name,count(*)
jack,5
mart,5
mart,5
mart,5
mart,5

使用聚合函数+over()开窗,使用partition by,窗口范围为 partition by划分后的区域内

select name,count(*) over(partition by name)
from business
where date_format(orderdate,'Y-MM')='2017-04'

结果: # 不使用group by ,则数据没有分组,但是聚合函数的应用以partition by分区后的范围内

name,count(*)
jack,1
mart,4
mart,4
mart,4
mart,4

使用聚合函数+over(),同时使用group by + partition by //数据实际分组,聚合函数也分区计算

select name,count(*) over(partition by name)
from business
where date_format(orderdate,'Y-MM')='2017-04'
group by name;

结果: !!! group by 分组后,count(*) 只是统计了组的个数?
思考：为什么不是 (jack,1)(mart,4)

name,count(*)
jack,1
mart,1

总结: 数据实际分组,看有没有group by,
窗口实际作用范围,看partition by,
聚合函数是对每个组内的数据进行聚合,
开窗之后,函数的计算范围是以 窗口大小来定.

补充：:group by将数据分组后,如果再对分组字段开窗,每组的数据会被压平成一条数据,部分聚合函数失效(仅count有效)

开窗函数lag(),lead()和proceding，following的区别？

lag()本身是一个开窗函数，类似rank，用于将某列结果上移。lead()将结果下移。
而proceding是指定窗口的移动，将当前窗口位置向上移动，following反之

– lead开窗函数

select studentId,math,departmentId,classId,
--窗口内 往下取第二个 取不到时赋默认值60
lead(math,2,60) over(partition by classId order by math) as lead1,
--窗口内 往下取第二个 取不到时赋默认值NULL
lead(math,2) over(partition by classId order by math) as lead2
from student_scores where departmentId='department1';

结果

studentid   math    departmentid    classid lead1   lead2
111         69      department1     class1  80      80
113         74      department1     class1  93      93
112         80      department1     class1  94      94
115         93      department1     class1  60      NULL
114         94      department1     class1  60      NULL
124         70      department1     class2  78      78
121         74      department1     class2  86      86
123         78      department1     class2  60      NULL
122         86      department1     class2  60      NULL

结果解释:
第4行lead1 窗口内向下第二个值为空，赋值60

follwing+preceding:
– sum开窗函数

select studentId,math,departmentId,classId,

– 以符合条件的所有行作为窗口

sum(math) over() as sum1,

– 以按classId分组的所有行作为窗口

sum(math) over(partition by classId) as sum2,

– 以按classId分组、按math排序后、按到当前行(含当前行)的所有行作为窗口

sum(math) over(partition by classId order by math) as sum3,

– 以按classId分组、按math排序后、按当前行+往前1行+往后2行的行作为窗口

sum(math) over(partition by classId order by math rows between 1 preceding and 2 following) as sum4
from student_scores where departmentId='department1';

结果

studentid   math    departmentid    classid sum1    sum2    sum3    sum4
111         69      department1     class1  718     410     69      223
113         74      department1     class1  718     410     143     316
112         80      department1     class1  718     410     223     341
115         93      department1     class1  718     410     316     267
114         94      department1     class1  718     410     410     187
124         70      department1     class2  718     308     70      222
121         74      department1     class2  718     308     144     308
123         78      department1     class2  718     308     222     238
122         86      department1     class2  718     308     308     164

结果解释:
同count开窗函数