hive复习篇：窗口函数（刷sql要一直使用！）

sum(col) over() : 分组对col累计求和
count(col) over() : 分组对col累计
min(col) over() : 分组对col求最小值
max(col) over() : 分组求col的最大值
avg(col) over() : 分组求col列的平均值
first_value(col) over() : 某分组排序后的第一个col值
last_value(col) over() : 某分组排序后的最后一个col值
lag(col,n,DEFAULT) : 统计往前n行的col值，n可选，默认为1，DEFAULT当往上第n行为NULL时候，取默认值，如不指定，则为NULL
lead(col,n,DEFAULT) : 统计往后n行的col值，n可选，默认为1，DEFAULT当往下第n行为NULL时候，取默认值，如不指定，则为NULL
ntile(n) : 用于将分组数据按照顺序切分成n片，返回当前切片值。注意：n必须为int类型
row_number() over() : 排名函数，不会重复，适合于生成主键或者不并列排名
rank() over() : 排名函数，有并列名次，名次不连续。如:1，1，3
dense_rank() over() : 排名函数，有并列名次，名次连续。如：1，1，2

1、排名函数

使用方法

排名函数+over(partition by name order by ctime )

总结:
1.row_number()的值不会存在重复,只排序
2.rank()：并列第一,则都为第一,没有第二名,直接第三名
3.dense_rank(): 并列第一,则都为第一,有第二名

一个复杂子查询嵌套

求连续4天有销售记录的店铺

先进行一个排名函数
对日期进行diff操作
对记录进行销售记录进行count，并且要group by name,diff having  cc >=4
最后对name去重

代码

select
  distinct name
from
(
  select
    name , 
    diff,
    count(*) cc
  from
  (
    select
      *,
      date_sub(ctime , n) diff
    from
    (
   select
        *,
      row_number() over(partition by name order by ctime) n
      from
      shop
 )t1
  )t2
  group by name , diff 
  having cc >=4
)t3 ;

2 SUM(), MIN(),MAX(),AVG()等聚合函数

就是控制运算范围

select
  * ,
  sum(cost) over() , -- 起点到终点  所有的行累加 
  sum(cost) over(partition by name),  --每个人累加
  sum(cost) over(partition by name order by  createtime  desc) -- 每个人按照时间累加
from
orders ;

3.ntile(n)+over

ntile函数可以用发来求 数据的前n% 数据,ntile(n)是把数据分成n等分,查询时用where n = ? 就可以取出对用的那份数据

子查询就是把数据分片，外部的查询就是加where n = ？

案例一求前20%的订单信息

-- 查询前20%时间的订单信息
select
   *
 from
 (
   select
     *,
     ntile(5) over(order by cday) n
   from orders
  )t
where t.n = 1;

案例二 将分区中已排序的行划分为大小尽可能相等的指定数量的排名的组，并返回给定行所在的组的排名。

select
shop_id, stat_date, ordamt,
  -- 以按shop_id分成两组、按stat_date排序
ntile(2) over(partition by shop_id order by stat_date) as ntile_amt1,
  -- 以按shop_id分成三组、按stat_date排序
ntile(3) over(partition by shop_id order by stat_date) as ntile_amt2
from shop_data;

4、lag(col,n)和lead(col,n)

lag(column , n , “默认值”) 参数一 数据 参数二 取前n行 参数三 没有数据时的默认值
lead(column , n , “默认值”) 后面的数据

注意:
1、使用第三个参数设置默认值时，默认值的数据类型需要和列的数据类型保持一致，否则设置不生效。
2、使用lag() 和 lead() 不能对窗口限定边界，必须是 unbounded 无界的，如果设置了边界，会出现如下报错信息。

Error while compiling statement: FAILED: SemanticException Failed to breakup Windowing invocations into Groups. 
At least 1 group must only depend on input columns. Also check for circular dependencies.

Underlying error: Expecting left window frame boundary for function lag((tok_table_or_col ordamt), 1, 'NA') 
Window Spec=[PartitioningSpec=[partitionColumns=[(tok_table_or_col shop_id)]orderColumns=[(tok_table_or_col 
stat_date) ASC]]window(start=range(1 FOLLOWING), end=range(Unbounded FOLLOWING))] as _wcol2 to be unbounded.

select 
  name,
  orderdate,
  cost, 
  lag(orderdate,1,'1900-01-01') over(partition by name order by orderdate ) as time1, 
  lag(orderdate,2) over (partition by name order by orderdate) as time2   
from business;

5、min函数和max函数 +over()

-- 以按shop_id分组、按stat_date排序、按从当前行往后一行到当前行往后2行的最小数
select min(ordamt) over(partition by shop_id order by stat_date rows between 1 following and 2 following) as min_amt8
from shop_data;

6 first_value和last_value+over

 -- 以按shop_id分组、按stat_date排序、按从当前行往后一行到当前行往后2行的第一个值
select first_value(ordamt) over(partition by shop_id order by stat_date rows between 1 following and 2 following) as first_amt8
from shop_data;

--排序、按从当前行往后一行到当前行往后2行的第一个值
select first_value(ordamt) over(partition by shop_id order by stat_date rows between 1 following and 2 following) as first_amt8
from shop_data;