精华内容
下载资源
问答
  • Hive窗口函数

    万次阅读 多人点赞 2017-02-08 13:34:15
    Hive窗口函数

    简介

    本文主要介绍hive中的窗口函数.hive中的窗口函数和sql中的窗口函数相类似,都是用来做一些数据分析类的工作,一般用于olap分析

    概念

    我们都知道在sql中有一类函数叫做聚合函数,例如sum()、avg()、max()等等,这类函数可以将多行数据按照规则聚集为一行,一般来讲聚集后的行数是要少于聚集前的行数的.但是有时我们想要既显示聚集前的数据,又要显示聚集后的数据,这时我们便引入了窗口函数.

    在深入研究Over字句之前,一定要注意:在SQL处理中,窗口函数都是最后一步执行,而且仅位于Order by字句之前.

    数据准备

    我们准备一张order表,字段分别为name,orderdate,cost.数据内容如下:

    jack,2015-01-01,10
    tony,2015-01-02,15
    jack,2015-02-03,23
    tony,2015-01-04,29
    jack,2015-01-05,46
    jack,2015-04-06,42
    tony,2015-01-07,50
    jack,2015-01-08,55
    mart,2015-04-08,62
    mart,2015-04-09,68
    neil,2015-05-10,12
    mart,2015-04-11,75
    neil,2015-06-12,80
    mart,2015-04-13,94

    在hive中建立一张表t_window,将数据插入进去.

    实例

    聚合函数+over

    假如说我们想要查询在2015年4月份购买过的顾客及总人数,我们便可以使用窗口函数去去实现

    select name,count(*) over ()
    from t_window
    where substring(orderdate,1,7) = '2015-04'

    得到的结果如下:

    name    count_window_0
    mart    5
    mart    5
    mart    5
    mart    5
    jack    5

    可见其实在2015年4月一共有5次购买记录,mart购买了4次,jack购买了1次.事实上,大多数情况下,我们是只看去重后的结果的.针对于这种情况,我们有两种实现方式

    第一种:distinct

    select distinct name,count(*) over ()
    from t_window
    where substring(orderdate,1,7) = '2015-04'

    第二种:group by

    select name,count(*) over ()
    from t_window
    where substring(orderdate,1,7) = '2015-04'
    group by name

    执行后的结果如下:
    name count_window_0
    mart 2
    jack 2

    partition by子句

    Over子句之后第一个提到的就是Partition By.Partition By子句也可以称为查询分区子句,非常类似于Group By,都是将数据按照边界值分组,而Over之前的函数在每一个分组之内进行,如果超出了分组,则函数会重新计算.

    实例

    我们想要去看顾客的购买明细及月购买总额,可以执行如下的sql

    select name,orderdate,cost,sum(cost) over(partition by month(orderdate))
    from t_window

    执行结果如下:

    name    orderdate   cost    sum_window_0
    jack    2015-01-01  10  205
    jack    2015-01-08  55  205
    tony    2015-01-07  50  205
    jack    2015-01-05  46  205
    tony    2015-01-04  29  205
    tony    2015-01-02  15  205
    jack    2015-02-03  23  23
    mart    2015-04-13  94  341
    jack    2015-04-06  42  341
    mart    2015-04-11  75  341
    mart    2015-04-09  68  341
    mart    2015-04-08  62  341
    neil    2015-05-10  12  12
    neil    2015-06-12  80  80

    可以看出数据已经按照月进行汇总了.

    order by子句

    上述的场景,假如我们想要将cost按照月进行累加.这时我们引入order by子句.

    order by子句会让输入的数据强制排序(文章前面提到过,窗口函数是SQL语句最后执行的函数,因此可以把SQL结果集想象成输入数据)。Order By子句对于诸如Row_Number(),Lead(),LAG()等函数是必须的,因为如果数据无序,这些函数的结果就没有任何意义。因此如果有了Order By子句,则Count(),Min()等计算出来的结果就没有任何意义。

    我们在上面的代码中加入order by

    select name,orderdate,cost,sum(cost) over(partition by month(orderdate) order by orderdate )
    from t_window

    得到的结果如下:(order by默认情况下聚合从起始行当当前行的数据)

    name    orderdate   cost    sum_window_0
    jack    2015-01-01  10  10
    tony    2015-01-02  15  25
    tony    2015-01-04  29  54
    jack    2015-01-05  46  100
    tony    2015-01-07  50  150
    jack    2015-01-08  55  205
    jack    2015-02-03  23  23
    jack    2015-04-06  42  42
    mart    2015-04-08  62  104
    mart    2015-04-09  68  172
    mart    2015-04-11  75  247
    mart    2015-04-13  94  341
    neil    2015-05-10  12  12
    neil    2015-06-12  80  80

    window子句

    我们在上面已经通过使用partition by子句将数据进行了分组的处理.如果我们想要更细粒度的划分,我们就要引入window子句了.

    我们首先要理解两个概念:
    - 如果只使用partition by子句,未指定order by的话,我们的聚合是分组内的聚合.
    - 使用了order by子句,未使用window子句的情况下,默认从起点到当前行.

    当同一个select查询中存在多个窗口函数时,他们相互之间是没有影响的.每个窗口函数应用自己的规则.

    window子句:
    - PRECEDING:往前
    - FOLLOWING:往后
    - CURRENT ROW:当前行
    - UNBOUNDED:起点,UNBOUNDED PRECEDING 表示从前面的起点, UNBOUNDED FOLLOWING:表示到后面的终点

    我们按照name进行分区,按照购物时间进行排序,做cost的累加.
    如下我们结合使用window子句进行查询

    select name,orderdate,cost,
    sum(cost) over() as sample1,--所有行相加
    sum(cost) over(partition by name) as sample2,--按name分组,组内数据相加
    sum(cost) over(partition by name order by orderdate) as sample3,--按name分组,组内数据累加
    sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row )  as sample4 ,--和sample3一样,由起点到当前行的聚合
    sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING   and current row) as sample5, --当前行和前面一行做聚合
    sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING   AND 1 FOLLOWING  ) as sample6,--当前行和前边一行及后面一行
    sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING ) as sample7 --当前行及后面所有行
    from t_window;

    得到查询结果如下:

    name    orderdate   cost    sample1 sample2 sample3 sample4 sample5 sample6 sample7
    jack    2015-01-01  10  661 176 10  10  10  56  176
    jack    2015-01-05  46  661 176 56  56  56  111 166
    jack    2015-01-08  55  661 176 111 111 101 124 120
    jack    2015-02-03  23  661 176 134 134 78  120 65
    jack    2015-04-06  42  661 176 176 176 65  65  42
    mart    2015-04-08  62  661 299 62  62  62  130 299
    mart    2015-04-09  68  661 299 130 130 130 205 237
    mart    2015-04-11  75  661 299 205 205 143 237 169
    mart    2015-04-13  94  661 299 299 299 169 169 94
    neil    2015-05-10  12  661 92  12  12  12  92  92
    neil    2015-06-12  80  661 92  92  92  92  92  80
    tony    2015-01-02  15  661 94  15  15  15  44  94
    tony    2015-01-04  29  661 94  44  44  44  94  79
    tony    2015-01-07  50  661 94  94  94  79  79  50

    窗口函数中的序列函数

    主要序列函数是不支持window子句的.

    hive中常用的序列函数有下面几个:

    NTILE

    • NTILE(n),用于将分组数据按照顺序切分成n片,返回当前切片值

    • NTILE不支持ROWS BETWEEN,
      比如 NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)

    • 如果切片不均匀,默认增加第一个切片的分布

    这个函数用什么应用场景呢?假如我们想要每位顾客购买金额前1/3的交易记录,我们便可以使用这个函数.

    select name,orderdate,cost,
           ntile(3) over() as sample1 , --全局数据切片
           ntile(3) over(partition by name), -- 按照name进行分组,在分组内将数据切成3份
           ntile(3) over(order by cost),--全局按照cost升序排列,数据切成3份
           ntile(3) over(partition by name order by cost ) --按照name分组,在分组内按照cost升序排列,数据切成3份
    from t_window

    得到的数据如下:

    name    orderdate   cost    sample1 sample2 sample3 sample4
    jack    2015-01-01  10  3   1   1   1
    jack    2015-02-03  23  3   1   1   1
    jack    2015-04-06  42  2   2   2   2
    jack    2015-01-05  46  2   2   2   2
    jack    2015-01-08  55  2   3   2   3
    mart    2015-04-08  62  2   1   2   1
    mart    2015-04-09  68  1   2   3   1
    mart    2015-04-11  75  1   3   3   2
    mart    2015-04-13  94  1   1   3   3
    neil    2015-05-10  12  1   2   1   1
    neil    2015-06-12  80  1   1   3   2
    tony    2015-01-02  15  3   2   1   1
    tony    2015-01-04  29  3   3   1   2
    tony    2015-01-07  50  2   1   2   3

    如上述数据,我们去sample4 = 1的那部分数据就是我们要的结果

    row_number、rank、dense_rank

    这三个窗口函数的使用场景非常多
    - row_number()从1开始,按照顺序,生成分组内记录的序列,row_number()的值不会存在重复,当排序的值相同时,按照表中记录的顺序进行排列
    - RANK() 生成数据项在分组中的排名,排名相等会在名次中留下空位
    - DENSE_RANK() 生成数据项在分组中的排名,排名相等会在名次中不会留下空位

    **注意:
    rank和dense_rank的区别在于排名相等时会不会留下空位.**

    举例如下:

    SELECT 
    cookieid,
    createtime,
    pv,
    RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn1,
    DENSE_RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn2,
    ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn3 
    FROM lxw1234 
    WHERE cookieid = 'cookie1';
    
    cookieid day           pv       rn1     rn2     rn3 
    
    cookie1 2015-04-12      7       1       1       1
    cookie1 2015-04-11      5       2       2       2
    cookie1 2015-04-15      4       3       3       3
    cookie1 2015-04-16      4       3       3       4
    cookie1 2015-04-13      3       5       4       5
    cookie1 2015-04-14      2       6       5       6
    cookie1 2015-04-10      1       7       6       7
    rn1: 15号和16号并列第3, 13号排第5
    rn2: 15号和16号并列第3, 13号排第4
    rn3: 如果相等,则按记录值排序,生成唯一的次序,如果所有记录值都相等,或许会随机排吧。

    LAG和LEAD函数

    这两个函数为常用的窗口函数,可以返回上下数据行的数据.
    以我们的订单表为例,假如我们想要查看顾客上次的购买时间可以这样去查询

    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 t_window;

    查询后的数据为:

    name    orderdate   cost    time1   time2
    jack    2015-01-01  10  1900-01-01  NULL
    jack    2015-01-05  46  2015-01-01  NULL
    jack    2015-01-08  55  2015-01-05  2015-01-01
    jack    2015-02-03  23  2015-01-08  2015-01-05
    jack    2015-04-06  42  2015-02-03  2015-01-08
    mart    2015-04-08  62  1900-01-01  NULL
    mart    2015-04-09  68  2015-04-08  NULL
    mart    2015-04-11  75  2015-04-09  2015-04-08
    mart    2015-04-13  94  2015-04-11  2015-04-09
    neil    2015-05-10  12  1900-01-01  NULL
    neil    2015-06-12  80  2015-05-10  NULL
    tony    2015-01-02  15  1900-01-01  NULL
    tony    2015-01-04  29  2015-01-02  NULL
    tony    2015-01-07  50  2015-01-04  2015-01-02

    time1取的为按照name进行分组,分组内升序排列,取上一行数据的值.

    time2取的为按照name进行分组,分组内升序排列,取上面2行的数据的值,注意当lag函数为设置行数值时,默认为1行.未设定取不到时的默认值时,取null值.

    lead函数与lag函数方向相反,取向下的数据.

    first_value和last_value

    first_value取分组内排序后,截止到当前行,第一个值
    last_value取分组内排序后,截止到当前行,最后一个值

    select name,orderdate,cost,
    first_value(orderdate) over(partition by name order by orderdate) as time1,
    last_value(orderdate) over(partition by name order by orderdate) as time2
    from t_window

    查询结果如下:

    name    orderdate   cost    time1   time2
    jack    2015-01-01  10  2015-01-01  2015-01-01
    jack    2015-01-05  46  2015-01-01  2015-01-05
    jack    2015-01-08  55  2015-01-01  2015-01-08
    jack    2015-02-03  23  2015-01-01  2015-02-03
    jack    2015-04-06  42  2015-01-01  2015-04-06
    mart    2015-04-08  62  2015-04-08  2015-04-08
    mart    2015-04-09  68  2015-04-08  2015-04-09
    mart    2015-04-11  75  2015-04-08  2015-04-11
    mart    2015-04-13  94  2015-04-08  2015-04-13
    neil    2015-05-10  12  2015-05-10  2015-05-10
    neil    2015-06-12  80  2015-05-10  2015-06-12
    tony    2015-01-02  15  2015-01-02  2015-01-02
    tony    2015-01-04  29  2015-01-02  2015-01-04
    tony    2015-01-07  50  2015-01-02  2015-01-07
    

    参考内容:
    - SQL Server中的窗口函数
    - 分析函数——排序排列(rank、dense_rank、row_number)
    - SQL中的窗口函数 OVER窗口函数

    展开全文
  • hive窗口函数

    2021-01-19 11:58:49
    hive窗口函数前言hive窗口函数格式窗口函数over()应用窗口函数over(partition by)应用窗口函数rows between.... and....窗口函数first_value窗口函数之偏移函数排序函数 前言 拉钩教育 这是你第一次使用 Markdown...

    前言

    文章内容输出来源:拉勾教育数据分析训练营

    hive窗口函数格式

    分析函数(如:sum(), max(), row_number()…) + 窗⼝⼦句(over函数)

    窗口函数over()应用

    1.统计test_join_order表中的订单数

    select count(distinct order_id) from test_join_order ;  -- 不用开窗函数的情况下进行统计
    select distinct count(distinct order_id) over() from test_join_order ;  --over()所有的数据为一组即一个窗口
    

    结果如下:
    窗口函数

    注意:之所以使用两个distinct,是因为,over()函数会返回相同的重复的数据结果,因为窗口函数会为窗口内的每一行返回一个值。
    如下图所示:
    窗口函数over

    窗口函数over(partition by)应用

    2.统计test_join_order表中各商品数的订单数,总销售额,平均销售额,最⼤⾦额,最⼩⾦额

    select distinct round(count(distinct order_id)over(partition by quantity),2)count,
    round(sum(sales)over(partition by quantity),2)sum,
    round(avg(sales)over(partition by quantity),2)avg,
    round(max(sales)over(partition by quantity),2)max,
    round(min(sales)over(partition by quantity),2)min from test_join_order;
    

    结果如下:
    hive窗口函数
    分析test_join_order表中各订单,⾦额⼤于平均⾦额的为⾼,否则为低

    select distinct(order_id),
    if(sum(sales)over(partition by order_id)>(sum(sales)over()/count(distinct order_id)over()),"高","低") 
    from test_join_order;
    

    结果如下:
    在这里插入图片描述

    这里不用avg(sales)作为平均订单金额,是因为,一个订单会有多个购买商品,如果用avg(sales)over(),则会计算成所有商品的平均金额。

    窗口函数rows between… and…

    3.分析test_join_order表中各记录对销售额累计求和(保留两位⼩数点),销售量累计求最⼤
    unbounded preceding 表示该窗⼝最前⾯的⾏
    unbounded following 表示该窗⼝最后⾯的⾏
    往前 n preceding 从当前⾏向前n⾏
    往后 n following 从当前⾏向后n⾏

    select order_id,quantity,sales,
    round(sum(sales)over(rows between unbounded preceding and current row),2)heji,
    round(max(quantity)over(rows between unbounded preceding and current row),2)max
    from test_join_order;
    

    在这里插入图片描述

    4.分析overData表中每三天(前⼀天,当前天,后⼀天)的销售额合计(保留两位⼩数点)

    select order_date,sales,
    round(sum(sales)over(order by order_date rows between 1 preceding and 1 following ),2)from overdate;
    

    在这里插入图片描述
    5.统计Pianyi 表中每天⾦额最⾼,最低的订单号

    窗口函数first_value

    select order_id,order_date,sales,
    first_value(order_id)over(partition by order_date order by sales desc)max,
    first_value(order_id)over(partition by order_date order by sales)min from pianyi;
    

    在这里插入图片描述
    6.利⽤lead ,lag 找的每个⽤户上⼀次,下⼀次的购买时间

    窗口函数之偏移函数

    lag(字段,num),向上偏移num是偏移的行数,默认是1。
    lead(字段,num),向下偏移。

    select customer_id,order_date,
    lag(order_date,1) over(partition by customer_id order by customer_id,order_date ) ago,
    lead(order_date,1) over(partition by customer_id order by customer_id,order_date) next
    from Pianyi order by customer_id,order_date;
    

    在这里插入图片描述

    7.分析Pianyi表中 各记录, 按销量降序排序后标上上序号

    排序函数

    row_numer 无空位排序
    rank()排名相同,留下空位
    dense_rank()排名相同,不会留下排名空缺位

    select order_id,order_date,quantity,
    row_number()over(order by quantity desc)row_number,
    rank()over(order by quantity desc)rank,
    dense_rank()over(order by quantity desc)dense_rank
    from pianyi;
    

    在这里插入图片描述
    8.把Pianyi表的销量分为4份

    select * ,
    ntile(4) over(order by quantity ) zu
    from Pianyi;
    

    在这里插入图片描述
    9.统计⼩于等于当前⾦额的订单数 占总订单数的⽐例
    ⼩于等于当前值的⾏数/分组内总⾏数

    select * ,ntile(4) over(order by quantity ) zu
    from Pianyi
    order by quantity;
    

    在这里插入图片描述

    展开全文
  • Hive 窗口函数

    2020-06-01 19:45:25
    Hive 窗口函数简介概念数据准备实例聚合函数+overpartition by子句order by子句window子句窗口函数中的序列函数NTILELAG和LEAD函数first_value和last_value扩展:总结: 简介 本文主要介绍hive中的窗口函数.hive中的...

    简介

    本文主要介绍hive中的窗口函数.hive中的窗口函数和sql中的窗口函数相类似,都是用来做一些数据分析类的工作,一般用于olap分析(在线分析处理)。

    概念

    我们都知道在sql中有一类函数叫做聚合函数,例如sum()、avg()、max()等等,这类函数可以将多行数据按照规则聚集为一行,一般来讲聚集后的行数是要少于聚集前的行数的.但是有时我们想要既显示聚集前的数据,又要显示聚集后的数据,这时我们便引入了窗口函数.

    在深入研究Over字句之前,一定要注意:在SQL处理中,窗口函数都是最后一步执行,而且仅位于Order by字句之前。

    数据准备

    我们准备一张order表,字段分别为name,orderdate,cost.数据内容如下:

    jack,2015-01-01,10
    tony,2015-01-02,15
    jack,2015-02-03,23
    tony,2015-01-04,29
    jack,2015-01-05,46
    jack,2015-04-06,42
    tony,2015-01-07,50
    jack,2015-01-08,55
    mart,2015-04-08,62
    mart,2015-04-09,68
    neil,2015-05-10,12
    mart,2015-04-11,75
    neil,2015-06-12,80
    mart,2015-04-13,94
    

    实例

    聚合函数+over

    假如说我们想要查询在2015年4月份购买过的顾客及总人数,我们便可以使用窗口函数去去实现

    select name,count(*) over ()
    from t_window
    where substring(orderdate,1,7) = '2015-04'
    

    得到的结果如下:

    name    count_window_0
    mart    5
    mart    5
    mart    5
    mart    5
    jack    5
    

    可见其实在2015年4月一共有5次购买记录,mart购买了4次,jack购买了1次.事实上,大多数情况下,我们是只看去重后的结果的.针对于这种情况,我们有两种实现方式
    第一种:distinct

    select distinct name,count(*) over ()
    from t_window
    where substring(orderdate,1,7) = '2015-04'
    

    第二种:group by

    select name,count(*) over ()
    from t_window
    where substring(orderdate,1,7) = '2015-04'
    group by name
    

    执行后的结果如下:

    name count_window_0 
    mart 2 
    jack 2
    

    partition by子句

    Over子句之后第一个提到的就是Partition By.Partition By子句也可以称为查询分区子句,非常类似于Group By,都是将数据按照边界值分组,而Over之前的函数在每一个分组之内进行,如果超出了分组,则函数会重新计算.

    实例

    我们想要去看顾客的购买明细及月购买总额,可以执行如下的sql

    select name,orderdate,cost,sum(cost) over(partition by month(orderdate))
    from t_window
    

    执行结果如下:

    name    orderdate   cost    sum_window_0
    jack    2015-01-01  10  205
    jack    2015-01-08  55  205
    tony    2015-01-07  50  205
    jack    2015-01-05  46  205
    tony    2015-01-04  29  205
    tony    2015-01-02  15  205
    jack    2015-02-03  23  23
    mart    2015-04-13  94  341
    jack    2015-04-06  42  341
    mart    2015-04-11  75  341
    mart    2015-04-09  68  341
    mart    2015-04-08  62  341
    neil    2015-05-10  12  12
    neil    2015-06-12  80  80
    

    可以看出数据已经按照月进行汇总了.

    order by子句

    上述的场景,假如我们想要将cost按照月进行累加.这时我们引入order by子句.

    order by子句会让输入的数据强制排序(文章前面提到过,窗口函数是SQL语句最后执行的函数,因此可以把SQL结果集想象成输入数据)。Order By子句对于诸如Row_Number(),Lead(),LAG()等函数是必须的,因为如果数据无序,这些函数的结果就没有任何意义。因此如果有了Order By子句,则Count(),Min()等计算出来的结果就没有任何意义。

    我们在上面的代码中加入order by

    select name,orderdate,cost,sum(cost) over(partition by month(orderdate) order by orderdate )
    from t_window
    

    得到的结果如下:(order by默认情况下聚合从起始行到当前行的数据)

    name    orderdate   cost    sum_window_0
    jack    2015-01-01  10  10
    tony    2015-01-02  15  25 //10+15
    tony    2015-01-04  29  54 //10+15+29
    jack    2015-01-05  46  100 //10+15+29+46
    tony    2015-01-07  50  150
    jack    2015-01-08  55  205
    jack    2015-02-03  23  23
    jack    2015-04-06  42  42
    mart    2015-04-08  62  104
    mart    2015-04-09  68  172
    mart    2015-04-11  75  247
    mart    2015-04-13  94  341
    neil    2015-05-10  12  12
    neil    2015-06-12  80  80
    

    window子句

    我们在上面已经通过使用partition by子句将数据进行了分组的处理.如果我们想要更细粒度的划分,我们就要引入window子句了.

    我们首先要理解两个概念:

    • 如果只使用partition by子句,未指定order by的话,我们的聚合是分组内的聚合. 使用了order
    • by子句,未使用window子句的情况下,默认从起点到当前行.

    当同一个select查询中存在多个窗口函数时,他们相互之间是没有影响的.每个窗口函数应用自己的规则.

    • window子句:
    • PRECEDING:往前
    • FOLLOWING:往后
    • CURRENT ROW:当前行
    • UNBOUNDED:起点,UNBOUNDED PRECEDING 表示从前面的起点, UNBOUNDED
      FOLLOWING:表示到后面的终点

    我们按照name进行分区,按照购物时间进行排序,做cost的累加.
    如下我们结合使用window子句进行查询

    select name,orderdate,cost,
    sum(cost) over() as sample1,--所有行相加
    sum(cost) over(partition by name) as sample2,--按name分组,组内数据相加
    sum(cost) over(partition by name order by orderdate) as sample3,--按name分组,组内数据累加
    sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row )  as sample4 ,--和sample3一样,由起点到当前行的聚合
    sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING   and current row) as sample5, --当前行和前面一行做聚合
    sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING   AND 1 FOLLOWING  ) as sample6,--当前行和前边一行及后面一行
    sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING ) as sample7 --当前行及后面所有行
    from t_window;
    

    得到查询结果如下:

    name    orderdate   cost    sample1 sample2 sample3 sample4 sample5 sample6 sample7
    jack    2015-01-01  10  661 176 10  10  10  56  176
    jack    2015-01-05  46  661 176 56  56  56  111 166
    jack    2015-01-08  55  661 176 111 111 101 124 120
    jack    2015-02-03  23  661 176 134 134 78  120 65
    jack    2015-04-06  42  661 176 176 176 65  65  42
    mart    2015-04-08  62  661 299 62  62  62  130 299
    mart    2015-04-09  68  661 299 130 130 130 205 237
    mart    2015-04-11  75  661 299 205 205 143 237 169
    mart    2015-04-13  94  661 299 299 299 169 169 94
    neil    2015-05-10  12  661 92  12  12  12  92  92
    neil    2015-06-12  80  661 92  92  92  92  92  80
    tony    2015-01-02  15  661 94  15  15  15  44  94
    tony    2015-01-04  29  661 94  44  44  44  94  79
    tony    2015-01-07  50  661 94  94  94  79  79  50
    

    窗口函数中的序列函数

    主要序列函数是不支持window子句的.

    hive中常用的序列函数有下面几个:

    NTILE

    • NTILE(n),用于将分组数据按照顺序切分成n片,返回当前切片值
    • NTILE不支持ROWS BETWEEN, 比如 NTILE(2) OVER(PARTITION BY cookieid ORDER BY
      createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
    • 如果切片不均匀,默认增加第一个切片的分布

    这个函数用什么应用场景呢?假如我们想要每位顾客购买金额前1/3的交易记录,我们便可以使用这个函数.

    select name,orderdate,cost,
           ntile(3) over() as sample1 , --全局数据切片
           ntile(3) over(partition by name), -- 按照name进行分组,在分组内将数据切成3份
           ntile(3) over(order by cost),--全局按照cost升序排列,数据切成3份
           ntile(3) over(partition by name order by cost ) --按照name分组,在分组内按照cost升序排列,数据切成3份
    from t_window
    

    得到的数据如下:

    name    orderdate   cost    sample1 sample2 sample3 sample4
    jack    2015-01-01  10  3   1   1   1
    jack    2015-02-03  23  3   1   1   1
    jack    2015-04-06  42  2   2   2   2
    jack    2015-01-05  46  2   2   2   2
    jack    2015-01-08  55  2   3   2   3
    mart    2015-04-08  62  2   1   2   1
    mart    2015-04-09  68  1   2   3   1
    mart    2015-04-11  75  1   3   3   2
    mart    2015-04-13  94  1   1   3   3
    neil    2015-05-10  12  1   2   1   1
    neil    2015-06-12  80  1   1   3   2
    tony    2015-01-02  15  3   2   1   1
    tony    2015-01-04  29  3   3   1   2
    tony    2015-01-07  50  2   1   2   3
    

    如上述数据,我们去sample4 = 1的那部分数据就是我们要的结果

    row_number
    rank
    dense_rank

    • 这三个窗口函数的使用场景非常多
    • row_number()从1开始,按照顺序,生成分组内记录的序列,row_number()的值不会存在重复,当排序的值相同时,按照表中记录的顺序进行排列
    • RANK() 生成数据项在分组中的排名,排名相等会在名次中留下空位 DENSE_RANK()
      生成数据项在分组中的排名,排名相等会在名次中不会留下空位
      注意:
      rank和dense_rank的区别在于排名相等时会不会留下空位.

      举例如下:
    SELECT 
    cookieid,
    createtime,
    pv,
    RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn1,
    DENSE_RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn2,
    ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn3 
    FROM lxw1234 
    WHERE cookieid = 'cookie1';
     
    cookieid day           pv       rn1     rn2     rn3 
     
    cookie1 2015-04-12      7       1       1       1
    cookie1 2015-04-11      5       2       2       2
    cookie1 2015-04-15      4       3       3       3
    cookie1 2015-04-16      4       3       3       4
    cookie1 2015-04-13      3       5       4       5
    cookie1 2015-04-14      2       6       5       6
    cookie1 2015-04-10      1       7       6       7
    rn1: 15号和16号并列第3, 13号排第5
    rn2: 15号和16号并列第3, 13号排第4
    rn3: 如果相等,则按记录值排序,生成唯一的次序,如果所有记录值都相等,或许会随机排吧。
    

    LAG和LEAD函数

    这两个函数为常用的窗口函数,可以返回上下数据行的数据.
    以我们的订单表为例,假如我们想要查看顾客上次的购买时间可以这样去查询

    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 t_window;
    

    查询后的数据为:

    name    orderdate   cost    time1   time2
    jack    2015-01-01  10  1900-01-01  NULL
    jack    2015-01-05  46  2015-01-01  NULL
    jack    2015-01-08  55  2015-01-05  2015-01-01
    jack    2015-02-03  23  2015-01-08  2015-01-05
    jack    2015-04-06  42  2015-02-03  2015-01-08
    mart    2015-04-08  62  1900-01-01  NULL
    mart    2015-04-09  68  2015-04-08  NULL
    mart    2015-04-11  75  2015-04-09  2015-04-08
    mart    2015-04-13  94  2015-04-11  2015-04-09
    neil    2015-05-10  12  1900-01-01  NULL
    neil    2015-06-12  80  2015-05-10  NULL
    tony    2015-01-02  15  1900-01-01  NULL
    tony    2015-01-04  29  2015-01-02  NULL
    tony    2015-01-07  50  2015-01-04  2015-01-02
    

    time1取的为按照name进行分组,分组内升序排列,取上一行数据的值,见下图。

    time2取的为按照name进行分组,分组内升序排列,取上面2行的数据的值,注意当lag函数未设置行数值时,默认为1行.设定取不到时的默认值时,取null值.

    lead函数与lag函数方向相反,取向下的数据.
    在这里插入图片描述

    first_value和last_value

    first_value取分组内排序后,截止到当前行,第一个值
    last_value取分组内排序后,截止到当前行,最后一个值

    select name,orderdate,cost,
    first_value(orderdate) over(partition by name order by orderdate) as time1,
    last_value(orderdate) over(partition by name order by orderdate) as time2
    from t_window
    

    查询结果如下:

    name    orderdate   cost    time1   time2
    jack    2015-01-01  10  2015-01-01  2015-01-01
    jack    2015-01-05  46  2015-01-01  2015-01-05
    jack    2015-01-08  55  2015-01-01  2015-01-08
    jack    2015-02-03  23  2015-01-01  2015-02-03
    jack    2015-04-06  42  2015-01-01  2015-04-06
    mart    2015-04-08  62  2015-04-08  2015-04-08
    mart    2015-04-09  68  2015-04-08  2015-04-09
    mart    2015-04-11  75  2015-04-08  2015-04-11
    mart    2015-04-13  94  2015-04-08  2015-04-13
    neil    2015-05-10  12  2015-05-10  2015-05-10
    neil    2015-06-12  80  2015-05-10  2015-06-12
    tony    2015-01-02  15  2015-01-02  2015-01-02
    tony    2015-01-04  29  2015-01-02  2015-01-04
    tony    2015-01-07  50  2015-01-02  2015-01-07
    

    原文参考:https://blog.csdn.net/qq_26937525/article/details/54925827

    扩展:

    • row_number的用途非常广泛,排序最好用它,它会为查询出来的每一行记录生成一个序号,依次排序且不会重复,注意使用row_number函数时必须要用over子句选择对某一列进行排序才能生成序号。
    • rank函数用于返回结果集的分区内每行的排名,行的排名是相关行之前的排名数加一。简单来说rank函数就是对查询出来的记录进行排名,与row_number函数不同的是,rank函数考虑到了over子句中排序字段值相同的情况,如果使用rank函数来生成序号,over子句中排序字段值相同的序号是一样的,后面字段值不相同的序号将跳过相同的排名号排下一个,也就是相关行之前的排名数加一,可以理解为根据当前的记录数生成序号,后面的记录依此类推。
    • dense_rank函数的功能与rank函数类似,dense_rank函数在生成序号时是连续的,而rank函数生成的序号有可能不连续。dense_rank函数出现相同排名时,将不跳过相同排名号,rank值紧接上一次的rank值。在各个分组内,rank()是跳跃排序,有两个第一名时接下来就是第四名,dense_rank()是连续排序,有两个第一名时仍然跟着第二名。

    借助实例能更直观地理解:

    假设现在有一张学生表student,学生表中有姓名、分数、课程编号。

    select * from student;
    

    在这里插入图片描述
    现在需要按照课程对学生的成绩进行排序:

    --row_number() 顺序排序
    select name,course,row_number() over(partition by course order by score desc) rank from student;
    
    --rank() 跳跃排序,如果有两个第一级别时,接下来是第三级别
    select name,course,rank() over(partition by course order by score desc) rank from student;
    
    --dense_rank() 连续排序,如果有两个第一级别时,接下来是第二级别 
    select name,course,dense_rank() over(partition by course order by score desc) rank from student;
    

    取得每门课程的第一名:

    --每门课程第一名只取一个: 
    select * from (select name,course,row_number() over(partition by course order by score desc) rank from student) where rank=1;
    --每门课程第一名取所有: 
    select * from (select name,course,dense_rank() over(partition by course order by score desc) rank from student) where rank=1;
    --每门课程第一名取所有:
    select * from (select name,course,rank() over(partition by course order by score desc) rank from student) where rank=1;
    

    附:每门课程第一名取所有的其他方法(使用group by 而不是partition by):

    select s.* from student s
      inner join(select course,max(score) as score from student group by course) c
      on s.course=c.course and s.score=c.score; 
    --或者使用using关键字简化连接
    select * from student s
      inner join(select course,max(score) as score from student group by course) c
      using(course,score);
    

    关于Parttion by:

    Parttion by关键字是Oracle中分析性函数的一部分,用于给结果集进行分区。它和聚合函数Group by不同的地方在于它只是将原始数据进行名次排列,能够返回一个分组中的多条记录(记录数不变),而Group by是对原始数据进行聚合统计,一般只有一条反映统计值的结果(每组返回一条)。

    TIPS:

    使用rank over()的时候,空值是最大的,如果排序字段为null, 可能造成null字段排在最前面,影响排序结果。

    可以这样: rank over(partition by course order by score desc nulls last)

    总结:

    在使用排名函数的时候需要注意以下三点:

    1、排名函数必须有 OVER 子句。

    2、排名函数必须有包含 ORDER BY 的 OVER 子句。

    3、分组内从1开始排序。

    参考:https://www.cnblogs.com/qiuting/p/7880500.html

    展开全文
  • HIVE窗口函数

    2018-10-09 09:33:21
    HIVE窗口函数 1. Hive窗口函数 SUM,AVG,MIN,MAX 数据格式 zhm,2018-04-10,12 zhm,2018-04-11,51 zhm,2018-04-12,72 zhm,2018-04-13,31 zhm,2018-04-14,21 zhm,2018-04-15,14 zhm,2018-04-16,44 创建表 create ...

    HIVE窗口函数

    1. Hive窗口函数 SUM,AVG,MIN,MAX

    数据格式

    zhm,2018-04-10,12
    zhm,2018-04-11,51
    zhm,2018-04-12,72
    zhm,2018-04-13,31
    zhm,2018-04-14,21
    zhm,2018-04-15,14
    zhm,2018-04-16,44 
    

    创建表

    create table fun_table1
     (username string, createtime string, pv int)
     row format delimited
     fields terminated by ',';
    

    加入本地数据

    load data local inpath "/home/zhm/test1.txt" into table fun_table1;
    
    select 
       username, 
       createtime, 
       pv, 
       pv1: 分组内从起点到当前行的pv累积,如,11号的pv1=10号的pv+11号的pv, 12号=10号+11号+12号
       sum(pv) over (partition by username order by createtime rows between unbounded preceding and current row) as pv1, 
       pv2: 同pv1
       sum(pv) over (partition by username order by createtime) as pv2, 
       pv3: 分组内(cookie1)所有的pv累加
       sum(pv) over (partition by username) as pv3, 
       pv4: 分组内当前行+往前3行,如,11号=10号+11号, 12号=10号+11号+12号, 13号=10号+11号+12号+13号, 14号=11号+12号+13号+14号
       sum(pv) over (partition by username order by createtime rows between 3 preceding and current row) as pv4, 
       pv5: 分组内当前行+往前3行+往后1行,如,14号=11号+12号+13号+14号+15号=5+7+3+2+4=21
       sum(pv) over (partition by username order by createtime rows between 3 preceding and 1 following) as pv5, 
       pv6: 分组内当前行+往后所有行,如,13号=13号+14号+15号+16号=3+2+4+4=13,14号=14号+15号+16号=2+4+4=10
       sum(pv) over (partition by username order by createtime rows between current row and unbounded following) as pv6 
       from fun_table1;
    
    如果不指定ROWS BETWEEN,默认为从起点到当前行;
    
    如果不指定ORDER BY,则将分组内所有值累加;
    
    关键是理解ROWS BETWEEN含义,也叫做WINDOW子句:
    
    PRECEDING:往前
    
    FOLLOWING:往后
    
    CURRENT ROW:当前行
    
    UNBOUNDED:起点,
    
    UNBOUNDED PRECEDING 表示从前面的起点,
    
    UNBOUNDED FOLLOWING:表示到后面的终点
    
    其他AVG,MIN,MAX,和SUM用法一样
    

    2. Hive窗口序列函数 ROW_NUMBER,RANK,DENSE_RANK###

    ROW_NUMBER() 从1开始,按照顺序,生成分组内记录的序列
    比如,按照pv降序排列,生成分组内每天的pv名次
    
    分组排序
    select
      username,
      createtime,
      pv,
      row_number() over (partition by username order by pv desc) as rn
    from fun_table1;
    
    
    RANK 和 DENSE_RANK
    —RANK() 生成数据项在分组中的排名,排名相等会在名次中留下空位
    —DENSE_RANK() 生成数据项在分组中的排名,排名相等会在名次中不会留下空位
    
    select
      username,
      createtime,
      pv,
      rank() over (partition by username order by pv desc) as rn1,
      dense_rank() over (partition by username order by pv desc) as rn2,
      row_number() over (partition by username order by pv desc) as rn3
    from   fun_table1
    where cookieid='zhm';
    
    row_number: 按顺序编号,不留空位
    rank: 按顺序编号,相同的值编相同号,留空位
    dense_rank: 按顺序编号,相同的值编相同的号,不留空位
    

    3. Hive窗口函数 CUME_DIST

    CUME_DIST : 小于等于当前值的行数/分组内总行数
    比如,统计小于等于当前薪水的人数,所占总人数的比例
    create table cookie3(dept string, userid string, sal int) 
    row format delimited fields terminated by ',';
    
    select 
      dept,
      userid,
      sal,
      cume_dist() over (order by sal) as rn1,
      cume_dist() over (partition by dept order by sal) as rn2
      from cookie.cookie3;
    
    展开全文
  • 在上一篇HIVE 窗口函数详解 中,我们介绍了hive窗口函数的定义和语法,本篇中我们根据例子来进行实战。数据准备首先我们来定义测试表结构,建立一张student_score学生分数表,主要三个字段,学生,班级,成绩,如下...
  • hive 窗口函数

    2019-06-13 10:15:13
    本文主要介绍hive中的窗口函数.hive中的窗口函数和sql中的窗口函数相类似,都是用来做一些数据分析类的工作,一般用于olap分析(在线分析处理)。 概念 我们都知道在sql中有一类函数叫做聚合函数,例如sum()、avg()、...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 2,889
精华内容 1,155
关键字:

hive窗口函数