Hive sql及窗口函数

count () over() total, -- total 是 count () over() 的别名， count () over() 是分析函数求总数，OVER()是分析函数的格式

cast (count()over() as decimal)/2 mid --同上 mid 是别名，

ceiling(cast(count()over() as decimal)/2) next,

row_number() over(order by UID) --row_number() 行号

分析函数语法:

FUNCTION_NAME(<argument>,<argument>)

OVER

(<Partition-Clause><Order-by-Clause><Windowing Clause>)

例:

sum(sal) over (partition by deptno order by ename) new_alias

sum就是函数名

(sal)是分析函数的参数,每个函数有0~3个参数,参数可以是表达式,例如:sum(sal+comm)

over 是一个关键字,用于标识分析函数,否则查询分析器不能区别sum()聚集函数和sum()分析函数

partition by deptno 是可选的分区子句,如果不存在任何分区子句,则全部的结果集可看作一个单一的大区

order by ename 是可选的orderby 子句,有些函数需要它,有些则不需要依靠已排序数据的那些函数,如:用于访问结果集中前一行和后一行的LAG和LEAD,必须使用,其它函数,如AVG,则不需要在使用了任何排序的开窗函数时,该子句是强制性的,它指定了在计算分析函数时一组内的数据是如何排序的

1)FUNCTION子句

ORACLE提供了26个分析函数,按功能分5类

分析函数分类

等级(ranking)函数:用于寻找前N种查询

开窗(windowing)函数:用于计算不同的累计,如SUM,COUNT,AVG,MIN,MAX等,作用于数据的一个窗口上

例:

sum(tsal) over (order by tdeptno,tename) running_total,

sum(tsal) over (partition by tdeptno order by tename) department_total

制表(reporting)函数:与开窗函数同名,作用于一个分区或一组上的所有列

例:

sum(tsal) over () running_total2,

sum(tsal) over (partition by tdeptno ) department_total2

制表函数与开窗函数的关键不同之处在于OVER语句上缺少一个ORDER BY子句!

LAG,LEAD函数:这类函数允许在结果集中向前或向后检索值,为了避免数据的自连接,它们是非常用用的

VAR_POP,VAR_SAMP,STDEV_POPE及线性的衰减函数:计算任何未排序分区的统计值

2)PARTITION子句

按照表达式分区(就是分组),如果省略了分区子句,则全部的结果集被看作是一个单一的组

3)ORDER BY子句

分析函数中ORDER BY的存在将添加一个默认的开窗子句,这意味着计算中所使用的行的集合是当前分区中当前行和前面所有行,没有ORDERBY时,默认的窗口是全部的分区 在Order by 子句后可以添加nulls last,如:order by comm descnulls last 表示排序时忽略comm列为空的行

4)WINDOWING子句

用于定义分析函数将在其上 *** 作的行的集合

Windowing子句给出了一个定义变化或固定的数据窗口的方法,分析函数将对这些数据进行 *** 作

默认的窗口是一个固定的窗口,仅仅在一组的第一行开始,一直继续到当前行,要使用窗口,必须使用ORDER BY子句

根据2个标准可以建立窗口:数据值的范围(RANGES)或与当前行的行偏移量

5)Rang窗口

Range 5 preceding:将产生一个滑动窗口,他在组中拥有当前行以前5行的集合

ANGE窗口仅对NUMBERS和DATES起作用,因为不可能从VARCHAR2中增加或减去N个单元

另外的限制是ORDER BY中只能有一列,因而范围实际上是一维的,不能在N维空间中

例:

avg(tsal) over(order by thiredate asc range 100 preceding) 统计前100天平均工资

6)Row窗口

利用ROW分区,就没有RANGE分区那样的限制了,数据可以是任何类型,且ORDER BY 可以包括很多列

7)Specifying窗口

UNBOUNDED PRECEDING:这个窗口从当前分区的每一行开始,并结束于正在处理的当前行

CURRENT ROW:该窗口从当前行开始(并结束)

Numeric Expression PRECEDING:对该窗口从当前行之前的数字表达式(Numeric Expression)的行开始,对RANGE来说,从从行序值小于数字表达式的当前行的值开始

Numeric Expression FOLLOWING:该窗口在当前行Numeric Expression行之后的行终止(或开始),且从行序值大于当前行Numeric Expression行的范围开始(或终止)

range between 100 preceding and 100 following:当前行100前,当前后100后

注意:分析函数允许你对一个数据集进排序和筛选,这是SQL从来不能实现的除了最后的Order by子句之外,分析函数是在查询中执行的最后的 *** 作集,这样的话,就不能直接在谓词中使用分析函数,即不能在上面使用where或having子句!!!

/

oracle 和sqlserver中有个开窗函数 over可以实现这个功能

/

select from

(

select t1id,t1name,t1age,t1sex,t2title,t2adddate,

row_number() over(partition by t1id order by t2adddate desc) r1

from user t1,article t2

where t1id = t2id

) t

where r1 = 1

/

sqlserver 2000以前的话稍微有点麻烦

/

select t2id,t2name,t2age,t2sex,t3title,t3adddate

from (select id,max(adddate) adddate from article group by id ) t1,

user t2, article t3

where t1id = t2id

and t1id = t3id

and t1adddate = t3adddate

FlinkSQL

窗口：

TUMBLE(TABLE data, DESCRIPTOR(timecol), size)

HOP(TABLE data, DESCRIPTOR(timecol), slide, size [, offset ])

SESSION(<time-attr>, <gap-interval>)

<gap-interval>: INTERVAL 'string' timeUnit

累积窗口函数：CUMULATE(TABLE data, DESCRIPTOR(timecol), step, size)

窗口分组聚合GROUPING SETS

over函数

CUBE

Flink DataStream

增量聚合和全量聚合

增量聚合: 窗口不维护原始数据，只维护中间结果，每次基于中间结果和增量数据进行聚合。

如: ReduceFunction、AggregateFunction

全量聚合: 窗口需要维护全部原始数据，窗口触发进行全量聚合。如:ProcessWindowFunction

以上就是关于Hive sql及窗口函数全部的内容，包括:Hive sql及窗口函数、SQL server 开窗函数里可以加聚集函数嘛类似rank() over (order by sum(distinct a) )、这几句SQL麻烦高手仔细解答一下 意义以及用法 3KS等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！