oracle、hive中的分析函数详解

分析函数又名窗口函数，在oracle、hive中使用比较多，包括flink等也会有分析函数

什么是分析函数

分析函数基于一组行来计算总值。 与聚合函数不同之处在于它们为每个组返回多行。 这组行被称为一个窗口，并由分析子句定义。 对于每一行可以定义滑动窗口，滑动窗口可以动态的定义使用多少行或者范围来进行计算。 窗口大小可以基于物理行数或逻辑间隔（如时间）

除了最后的order by子句，分析函数是查询中执行的最后一组操作。 在处理分析函数之前，所有连接以及所有where，group by和having子句都已完成。 因此，分析函数只能出现在选择列表或order by子句中。

分析函数通常用于计算累计，移动，居中和汇总汇总。

语法介绍

分析函数

  
  analytic_function([ arguments ]) 
    over (partition by { expr[, expr ]...}
   order [ siblings ] by { expr | position | c_alias } [ asc | desc ] [ nulls first | nulls last ]
    { rows | range }
    { between { unbounded preceding|current row|value_expr{ preceding | following }} 
  and {unbounded following| current row| value_expr { preceding | following }}
  | { unbounded preceding| current row| value_expr preceding}})

arguments [参数]

分析函数传入0~3个参数，参数可以是任何数字类型或者任何可以隐式转换成数字数据类型的非数字数据类型。oracle确定具有最高数字优先级的参数，并将其余参数隐式转换为该数据类型。 返回类型也是该数据类型，除非单个函数另有说明。

analytic_clause [分析子句]

通过over 分析子句可以指示分析函数在查询结果集上进行操作。 该子句在from，where，group和having子句之后计算。 你可以在选择列表或order by子句中使用此子句指定分析函数。 要根据分析函数筛选查询结果，需将这些函数嵌套在父查询中，然后筛选嵌套子查询的结果。

无法在分子函数任何部分中指定任何分析函数来嵌套分析函数。但是可以在子查询中指定分析函数，然后可以在上面指定另外的分析函数

和内建分析函数一样，你可以在用户自定义分析函数上指定over 分析子句

query_partition_clause [分组子句]

使用partition by子句根据一个或多个value_expr将查询结果集分成多个组。 如果省略此子句，则该函数将查询结果集的所有行视为单个组。

你可以在同一个查询中指定多个分析函数，每个分析函数都具有相同或不同的partition by键。

如果被查询的对象具有并行属性，并且使用query_partition_clause指定了分析函数，那么函数计算也是并行化的。

value_expr的有效值是常量，列，非分析函数，函数表达式或涉及这些函数的表达式

order_by_clause [排序子句]

使用order_by_clause指定数据在分区内的排序方式。 对于所有分析函数，您可以对多个键上的分区中的值进行排序，每个键由value_expr定义，并且每个键都按排序顺序进行限定。

在每个函数中，您可以指定多个排序表达式。 这样做在使用排序值的函数时特别有用，因为第二个表达式可以解析第一个表达式的相同值之间的关系。

windowing_clause [窗口子句]

rows | rangerows|range为每行定义用于计算函数结果的窗口（物理或逻辑行集合）。 该函数然后应用于窗口中的所有行, 窗口从上到下遍历查询结果集或分区。

between … and

使用between ... and子句指定窗口的起点和终点。 第一个表达式（在and之前）定义起始点，第二个表达式（在and之后）定义结束点。如果省略between并只指定一个端点，则oracle将其视为起点，并且终点默认为当前行.

unbounded preceding

unbounded following

current row

other

如果指定了rows：

value_expr是一个物理偏移量。 它必须是常数或表达式，并且必须评估为正数值。

如果value_expr是开始点的一部分，则它必须计算到结束点之前的一行。

如果指定了range：

value_expr是一个逻辑偏移量。 它必须是一个常数或表达式，其值为正数值或区间文字

您只能在order_by_clause中指定一个表达式。

如果value_expr的计算结果为数字值，那么order by expr必须是数字或date数据类型。

如果value_expr的计算结果为间隔值，那么order by expr必须是date数据类型。

other

如果完全忽略windowing_clause，则默认值为range between unbounded preceding and current row

分析函数通常用于数据仓库环境。 在下面的分析函数列表中，函数后跟星号（*），表示完整的语法，包括windowing_clause。

分析函数列表

avg *cluster_detailscluster_distancecluster_idcluster_probabilitycluster_setcorr *count *covar_pop *covar_samp *cume_distdense_rankfeature_detailsfeature_idfeature_setfeature_valuefirstfirst_value *laglastlast_value *leadlistaggmax *medianmin *nth_value *ntilepercent_rankpercentile_contpercentile_discpredictionprediction_costprediction_detailsprediction_probabilityprediction_setrankratio_to_reportregr_ (linear regression) functions *row_numberstddev *stddev_pop *stddev_samp *sum *var_pop *var_samp *variance *

上述有很多分析函数，这里主要讲解个人比较常用的分析函数

rank 、row_number、dense_rank

三者都用于排序,主要区别为，如果两个值相等，row_number会按照输入顺序给予排序，rank和densk_rank会给予相同排序，rank中接下来的排序会跳一级。

  
  select col as 值
     , row_number() over(order by col) as  row_number
     , rank() over(order by col) as  rank
     , dense_rank() over(order by col) as  dense_rank
   from (
  select 'a' col
  union all
  select 'b' col
  union all
  select 'b' col
    )t

结果如下:

值	row_number	rank	dense_rank
a	1	1	1
a	2	1	1
b	3	3	2

lag和lead

得到上一个值的值和下一个的值

  
  select col as 值
     , lag(col) over(order by col) as  lag
     , lead(col) over(order by col) as  lead
   from (
  select 'a' col
  union all
  select 'b' col
  union all
  select 'c' col
    )t

结果如下

值	lag	lead
a	\n	b
b	a	c
c	b	\n

另外还有lag(col,n,默认值)和lag(col,n)两种写法，n为位移位数，默认值为上n个值没有取到数据情况下置默认值。lag(col)相当于lag(col,1,null)

max,sum,min,count,last_value

这些和聚合函数中类似，分别为求最大、求和、最小、计数、第一个值

实战

上面讲了一堆语法，可能大家比较懵逼。接下来，根据实际情况使用分析函数

create table user_consume_log
(
user_id varchar(10),
consume_time date,
money  int
);

insert into user_consume_log(user_id,consume_time,money) values(1000198010,to_date('2018-06-09 01:17:22','yyyy-mm-dd hh24:mi:ss'),110);
insert into user_consume_log(user_id,consume_time,money) values(1000198010,to_date('2018-06-09 09:17:22','yyyy-mm-dd hh24:mi:ss'),200);
insert into user_consume_log(user_id,consume_time,money) values(1000198010,to_date('2018-06-09 02:17:22','yyyy-mm-dd hh24:mi:ss'),340);
insert into user_consume_log(user_id,consume_time,money) values(1000198010,to_date('2018-06-09 07:17:22','yyyy-mm-dd hh24:mi:ss'),400);
insert into user_consume_log(user_id,consume_time,money) values(1000198010,to_date('2018-06-09 03:17:22','yyyy-mm-dd hh24:mi:ss'),520);
insert into user_consume_log(user_id,consume_time,money) values(1000198010,to_date('2018-06-09 04:17:22','yyyy-mm-dd hh24:mi:ss'),630);
insert into user_consume_log(user_id,consume_time,money) values(1000198010,to_date('2018-06-09 05:17:22','yyyy-mm-dd hh24:mi:ss'),750);
insert into user_consume_log(user_id,consume_time,money) values(1000198010,to_date('2018-06-09 06:17:22','yyyy-mm-dd hh24:mi:ss'),880);
insert into user_consume_log(user_id,consume_time,money) values(1000198010,to_date('2018-06-09 10:17:22','yyyy-mm-dd hh24:mi:ss'),934);
insert into user_consume_log(user_id,consume_time,money) values(1000198010,to_date('2018-06-09 08:17:22','yyyy-mm-dd hh24:mi:ss'),1023);


insert into user_consume_log(user_id,consume_time,money) values(1000198010,to_date('2018-06-10 01:17:22','yyyy-mm-dd hh24:mi:ss'),110);
insert into user_consume_log(user_id,consume_time,money) values(1000198010,to_date('2018-06-10 09:17:22','yyyy-mm-dd hh24:mi:ss'),200);
insert into user_consume_log(user_id,consume_time,money) values(1000198010,to_date('2018-06-10 02:17:22','yyyy-mm-dd hh24:mi:ss'),340);
insert into user_consume_log(user_id,consume_time,money) values(1000198010,to_date('2018-06-10 07:17:22','yyyy-mm-dd hh24:mi:ss'),400);
insert into user_consume_log(user_id,consume_time,money) values(1000198010,to_date('2018-06-10 03:17:22','yyyy-mm-dd hh24:mi:ss'),520);
insert into user_consume_log(user_id,consume_time,money) values(1000198010,to_date('2018-06-10 04:17:22','yyyy-mm-dd hh24:mi:ss'),630);
insert into user_consume_log(user_id,consume_time,money) values(1000198010,to_date('2018-06-10 05:17:22','yyyy-mm-dd hh24:mi:ss'),750);
insert into user_consume_log(user_id,consume_time,money) values(1000198010,to_date('2018-06-10 06:17:22','yyyy-mm-dd hh24:mi:ss'),880);
insert into user_consume_log(user_id,consume_time,money) values(1000198010,to_date('2018-06-10 10:17:22','yyyy-mm-dd hh24:mi:ss'),934);
insert into user_consume_log(user_id,consume_time,money) values(1000198010,to_date('2018-06-10 08:17:22','yyyy-mm-dd hh24:mi:ss'),1023);

user_consume_log表为用户消费表，user_id为用户id，consume_time、money分别为消费时间和金额

求用户消费明细、消费金额升序列、降序、当前消费和前后消费差距不小于100的总金额、累计到当前和后消费差距不小于100的总金额

select user_id              as 用户id
   , to_char(consume_time,'yyyy-mm-dd') as 日期
   , consume_time            as 时间
   , money               as 消费金额
   , row_number() over(partition by user_id,to_char(consume_time,'yyyy-mm-dd') order by money )    rn     --按金额从小到大排序
   , row_number() over(partition by user_id,to_char(consume_time,'yyyy-mm-dd') order by money desc)  rn1    -- 按金额降序排列
   , sum(money) over(partition by user_id,to_char(consume_time,'yyyy-mm-dd') order by money  range between 100 preceding and 100 following) amt -- 当前消费和前后消费差距不小于100的总金额
   , sum(money) over(partition by user_id,to_char(consume_time,'yyyy-mm-dd') order by money  range between unbounded preceding and current row)  amt2 --累计到当前和后消费差距不小于100的总金额
 from user_consume_log

效果如下:

计算逻辑:

rn = 4的amt计算: rn = 4 消费金额为 400，前后金额差均为100，400 - 100 =300, 400 + 100 =500，所以

取值范围为300~500的所有值作为amt的值，最终结果为 340 + 400 = 740

rn = 4的amt2计算，从第一行到当前行计算

求消费明细及按照消费时间排序后的累计到当前总计、上两次和本次消费总计、本次和下一次消费总计、上一次消费金额、下一次消费金额、最大消费金额等值

select user_id              as user_id -- 用户id
   , to_char(consume_time,'yyyy-mm-dd') as tdate  --日期
   , consume_time            as time  --时间
   , money               as money  -- 消费金额
   , sum(money) over(partition by user_id,to_char(consume_time,'yyyy-mm-dd') order by consume_time) as amt --累计到当前总计
   , sum(money) over(partition by user_id,to_char(consume_time,'yyyy-mm-dd') order by consume_time rows between 2 preceding and current row) amt1 --上两次和本次消费总计
   , sum(money) over(partition by user_id,to_char(consume_time,'yyyy-mm-dd') order by consume_time rows between current row and 1 following) amt2 --本次和下一次消费总计
   , lag(money,1,0) over(partition by user_id,to_char(consume_time,'yyyy-mm-dd') order by consume_time) amt3 -- 上一次消费金额
   , lead(money) over(partition by user_id,to_char(consume_time,'yyyy-mm-dd') order by consume_time)   amt4 -- 下一次消费金额
   , max(money) over(partition by user_id,to_char(consume_time,'yyyy-mm-dd') order by consume_time rows between unbounded preceding and unbounded following) amt5--最大消费金额
 from user_consume_log