hive 窗口函数使用

参考教程

窗口函数的使用：(over就是使用窗口函数）

具体语法如下：over (partition by xxx order by xxx rows between xxx and xxx )

设置窗口大小：rows between xx and xx

preceding:往前 3 preceding

following：往后 3 following

current row:当前行

unbounded:起点

unbounded preceding:从前面的起点

unbouned following :表示到后面的终点

#先前3个窗口

over(partition by xx order by xxx rows between 3 preceding and current row)

#向后3个窗口

over(partition by xx order by xxx rows between 3 following and current row)

或者over(partition by xx order by xxx rows between current row and 3 following)



#开始到当前组

over(partition by xx order by xxx row between unbounded preceding and current row)

#当前到结束

over(partition by xx order by xxx row between  current row and  unbounded following)

设定好窗口之后，就是各种函数的使用

#sum,avg,min,max 函数


#row_number() 12345
#rank() 12234
#dense_rank() 122345

#ntile(x):x表示分组的组数

#
percent_rank()  :row_number-1/total_number-1

cume_dist : row_number/total_number

# lead() leg() first_value() last_value()

参考教程#聚合 grouping sets with rollup with cube

#()表示所有

group by a,b,c grouping sets((a,b,c),(a,b),())

# with rollup 相当于((a,b,c),(a,b),(a),())

group by a,b,c with rollup

#with cube 相单与 ((a),(b),(c),(a,b),(a,c),(b,c),(a,b,c),())

参考教程

# 并集

#并集并去重

union [distinct]

union all

#交集

intersect [distinct]

intersect all

#补集

except

except [all]

minus

minus [all]

参考教程.

语法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)