开天窗与否的区别

关于开天窗与不开天窗的区别

使用 over()是对最终结果集进行开天窗。

如果不使用，即不开天窗。受到group by等函数影响。

example:

hql

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

terminal:

name _c1
jack 1
mart 4

hql

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

terminal:

name count_window_0
mart 2
jack 2

business表

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

建表语句

hql

create table business(
name string,
orderdate string,
cost int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';