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

    2019-01-09 08:33:38
    hive窗口系列函数
  • Hive 窗口函数

    2021-01-07 14:25:24
    over():指定分析函数工作的数据窗口的大小,这个数据窗口大小会随着行的变而变化。over跟在聚合函数后面,只对聚合函数生效。 current row:当前行 n preceding:往前n行数据 n following:往后n行数据 unbounded:...
  • 文末有赠书福利目录:一、hive窗口函数语法----over()窗口函数的语法结构----常与over()一起使用的分析函数二、hive窗口函数练习28道题前言:我们在学习hive窗口函...

    文末有赠书福利

    目录:
    一、hive窗口函数语法
    ----over()窗口函数的语法结构
    ----常与over()一起使用的分析函数
    二、hive窗口函数练习28道题

    前言:我们在学习hive窗口函数的时候,一定要先了解窗口函数的结构。而不是直接百度sum() over()、row_number() over()、或者count() over()的用法,如果这样做,永远也掌握不到窗口函数的核心,当然我刚开始的时候也是这样做的,包括去年自己在接触ORACLE分析函数时也是这样搜索。

    还好我比较顽强,在HIVE窗口函数问题上折腾了半个月、看了很多文章后才知道over()才是窗口函数,而sum、row_number、count只是与over()搭配的分析函数,当然除了这三个函数还有其他的函数。

    一、hive窗口函数语法

    在前言中我们已经说了avg()、sum()、max()、min()是分析函数,而over()才是窗口函数,下面我们来看看over()窗口函数的语法结构、及常与over()一起使用的分析函数

    1、over()窗口函数的语法结构
    2、常与over()一起使用的分析函数
    3、窗口函数总结

    1、over()窗口函数的语法结构

    分析函数 over(partition by 列名 order by 列名 rows between 开始位置 and 结束位置)
    

    over()函数中包括三个函数:包括分区partition by 列名、排序order by 列名、指定窗口范围rows between 开始位置 and 结束位置。我们在使用over()窗口函数时,over()函数中的这三个函数可组合使用也可以不使用。

    over()函数中如果不使用这三个函数,窗口大小是针对查询产生的所有数据,如果指定了分区,窗口大小是针对每个分区的数据。

    1.1、over()函数中的三个函数讲解

    order by
    order by是排序的意思,是该窗口中的


    A、partition by
    partition by可理解为group by 分组。over(partition by 列名)搭配分析函数时,分析函数按照每一组每一组的数据进行计算的。

    B、rows between 开始位置 and 结束位置
    是指定窗口范围,比如第一行到当前行。而这个范围是随着数据变化的。over(rows between 开始位置 and 结束位置)搭配分析函数时,分析函数按照这个范围进行计算的。


    窗口范围说明:
    我们常使用的窗口范围是ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW(表示从起点到当前行),常用该窗口来计算累加。

    PRECEDING:往前

    FOLLOWING:往后
    CURRENT ROW:当前行
    UNBOUNDED:起点(一般结合PRECEDING,FOLLOWING使用)
    UNBOUNDED PRECEDING 表示该窗口最前面的行(起点)
    UNBOUNDED FOLLOWING:表示该窗口最后面的行(终点)
    
    比如说:
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW(表示从起点到当前行)
    ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING(表示往前2行到往后1行)
    ROWS BETWEEN 2 PRECEDING AND 1 CURRENT ROW(表示往前2行到当前行)
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING(表示当前行到终点)
    

    2、常与over()一起使用的分析函数:

    2.1、聚合类

    avg()、sum()、max()、min()

    2.1、排名类

    row_number()按照值排序时产生一个自增编号,不会重复(如:1、2、3、4、5、6)
    rank() 按照值排序时产生一个自增编号,值相等时会重复,会产生空位(如:1、2、3、3、3、6)
    dense_rank() 按照值排序时产生一个自增编号,值相等时会重复,不会产生空位(如:1、2、3、3、3、4)

    2.1、其他类

    lag(列名,往前的行数,[行数为null时的默认值,不指定为null]),以计算用户上次购买时间,或者用户下次购买时间。
    lead(列名,往后的行数,[行数为null时的默认值,不指定为null])
    ntile(n) 把有序分区中的行分发到指定数据的组中,各个组有编号,编号从1开始,对于每一行,ntile返回此行所属的组的编号

    3、窗口函数总结:

    其实窗口函数逻辑比较绕,我们可以把窗口理解为对表中的数据进行分组,排序等计算。要真正的理解HIVE窗口函数,还是要结合练习题才行。下面我们开始HIVE窗口函数的练习吧!

    二、hive窗口函数练习28道题

    第一套练习:hive之简单窗口函数 over()


    1、使用 over() 函数进行数据统计, 统计每个用户及表中数据的总数

    2、求用户明细并统计每天的用户总数
    3、计算从第一天到现在的所有 score 大于80分的用户总数
    4、计算每个用户到当前日期分数大于80的天数
    

    测试数据

    20191020,11111,85
    20191020,22222,83
    20191020,33333,86
    20191021,11111,87
    20191021,22222,65
    20191021,33333,98
    20191022,11111,67
    20191022,22222,34
    20191022,33333,88
    20191023,11111,99
    20191023,22222,33
    

    建表并导入数据:

    create table test_window
    (logday string,    #logday时间
    userid string,
    score int)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
    
    #加载数据
    load data local inpath '/home/xiaowangzi/hive_test_data/test_window.txt' into table test_window;
    
    

    我们先看下表中的数据:

    select  *  from test_window;
    

    test_window

    1、使用 over() 函数进行数据统计, 统计每个用户及表中数据的总数
    select *, count(userid)over() as total  from  test_window;
    

    这里使用 over() 与 select count(*) 有相同的作用,好处就是,在需要计算总数时不用再进行一次关联。

    2、求用户明细并统计每天的用户总数

    可以使用 partition by 按日期列对数据进行分区处理,如:over(partition by logday)

    select  *,count()over(partition by logday)as day_total from  test_window;
    

    求每天的用户数可以使用select logday, count(userid) from recommend.test_window group by logday,但是当想要得到 userid 信息时,这种用法的优势就很明显。

    3、计算从第一天到现在的所有 score 大于80分的用户总数

    此时简单的分区不能满足需求,需要将 order by 和 窗口定义结合使用。

    select  *,count()over(order by logday rows between unbounded preceding and current row)as total
    from  test_window
    where score > 80;
    


    通过 over() 计算出按日期的累加值。
    其实自己刚开始的时候就计算我思路是错了,我就想的是不用累加,直接select *,count()over()as total from test_window where score > 80;这样计算,如果这样计算的话只会显示表中所有大于80的人数,如果我想看20191021或者看20191022的人数看不见。

    4、计算每个用户到当前日期分数大于80的天数
    select *,
    count()over(partition  by userid order by logday rows between unbounded preceding and current row) as total
    from test_window
    where score > 80  order by logday, userid;
    


    第二套练习

    1、查询在2017年4月份购买过的顾客及总人数
    2、查询顾客的购买明细及月购买总额
    3、查询顾客的购买明细及到目前为止每个顾客购买总金额
    4、查询顾客上次的购买时间----lag()over()偏移量分析函数的运用
    5、查询前20%时间的订单信息
    

    测试数据

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

    建表并加载数据

    create table business
    (
    name string,
    orderdate string,
    cost int
    )ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
    
    #加载数据
    load  data local inpath "/home/fengGG/hive_test_data/business.txt" into table business;
    

    查看表数据

    select   *  from  business;
    

    查看表数据

    1、查询在2017年4月份购买过的顾客及总人数

    分析:按照日期过滤、分组count求总人数(分组为什么不是用group by?自己思考)

    select   *,count()over() as total  from  business
    where substr(orderdate,1,7) = '2017-04';
    


    2、查询顾客的购买明细及月购买总额

    是计算每月每个用户的消费金额

    select
    *,
    sum(cost) over(partition by name,substr(orderdate,1,7)) total_amount
    from
    business;
    


    3、查询顾客的购买明细及到目前为止每个顾客购买总金额

    分析:按照顾客分组、日期升序排序、组内每条数据将之前的金额累加

    select
    *,
    sum(cost) over(partition by name order  by  orderdate
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) total_amount
    from
    business;
    


    4、查询顾客上次的购买时间----lag()over()偏移量分析函数的运用

    lag()over()偏移量分析函数的运用

    select
    name,
    orderdate,
    cost,
    lag(orderdate,1) over(partition by name order by orderdate) last_date
    from
    business;
    

    5、查询前20%时间的订单信息

    select  *
    from
    (select   *,
    ntile(5)over(order  by  cost)sortgroup_num from  business)t
    where t.sortgroup_num = 1;
    


    第三套练习

    1、每门学科学生成绩排名(是否并列排名、空位排名三种实现)
    2、每门学科成绩排名top n的学生
    

    原始数据(学生成绩信息)

    name    subject score
    孙悟空 语文  87
    孙悟空 数学  95
    孙悟空 英语  68
    大海  语文  94
    大海  数学  56
    大海  英语  84
    宋宋  语文  64
    宋宋  数学  86
    宋宋  英语  84
    婷婷  语文  65
    婷婷  数学  85
    婷婷  英语  78
    

    建表并加载数据

    create table score
    (
    name string,
    subject string,
    score int
    ) row format delimited fields terminated by "\t";
    
    #加载数据
    load data local inpath '/home/fengGG/hive_test_data/score.txt' into table score;
    

    查看数据

    select  *  from  score;
    

    1、每门学科学生成绩排名(是否并列排名、空位排名三种实现)

    select  *,
    row_number()over(partition by subject order by score desc),
    rank()over(partition by subject order by score desc),
    dense_rank()over(partition by subject order by score desc)
    from score
    


    2、每门学科成绩排名top n的学生

    select
    *
    from
    (
    select
    *,
    row_number() over(partition by subject order by score desc) rmp
    from score
    ) t
    where t.rmp<=3;
    

    点击上方连接即可购买

    1. 重点介绍了Hive性能调优所涉及的Hadoop组件和Hive工具

    2. 站在工程的角度介绍Hive性能调优,注重调优方法的可落地性

    3. 从语法、表模型设计、执行计划和计算引擎等多个角度进行讲解

    4. 注重实例演示和调优方法的总结,给出近百个实例带领读者实际操练

    粉丝福利

    留言说说你为什么要学习Hive,留言点赞前五(刷赞无效)的5名读者可获赠正版图书 ---《Hive性能调优实战图书一本。

    --end--

    扫描下方二维码

    添加好友,备注【交流群

    拉你到学习路线和资源丰富的交流群

    展开全文
  • Hive 窗口函数详解

    千次阅读 2020-09-04 14:42:47
    目录1、hive窗口函数2、在日常的开发中常用那些类型函数?3、了解哪些窗口函数,都是什么意思?窗口函数功能over语法1、物理窗口(真实往上下移动多少行rows between):2、 逻辑窗口(满足条件上下多少行):(金融行业...

    1、hive窗口函数

    窗口函数是什么鬼?
    窗口函数指定了函数工作的数据窗口大小(当前行的上下多少行),这个数据窗口大小可能会随着行的变化而变化。

    窗口函数和聚合函数区别?
    窗口函数对于每个组返回多行,组内每一行对应返回一行值。
    聚合函数对于每个组只返回一行。

    2、在日常的开发中常用那些类型函数?

    • 1、字符串操作函数?split、concat、ifnull、substr、substring、cast
    • 2、聚合函数 : hive适用于分析,所以常用
    • 3、时间函数 : 数仓的特征随时间变化而变化,所以时间也特别多
    • 4、窗口函数:sum() over() 、 count() over() 、 排名函数

    接下来,着重讲解hive的窗口函数。

    3、了解哪些窗口函数,都是什么意思?

    窗口函数功能

    • 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

    over语法

    over(分组 排序 窗口) 中的order by后的语法:

    (ROWS | RANGE) BETWEEN (UNBOUNDED | [num]) PRECEDING AND ([num] PRECEDING | CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
    (ROWS | RANGE) BETWEEN CURRENT ROW AND (CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
    (ROWS | RANGE) BETWEEN [num] FOLLOWING AND (UNBOUNDED | [num]) FOLLOWING
    
    1、物理窗口(真实往上下移动多少行rows between):
    CURRENT ROW | UNBOUNDED PRECEDING | [num] PRECEDING AND UNBOUNDED FOLLOWING | [num] FOLLOWING| CURRENT ROW
    

    如: over(partition by col order by 排序字段 rows between 1 preceding and 1 fllowing)
    物理窗口

    2、 逻辑窗口(满足条件上下多少行):(金融行业、保险行业、p2p等)
    range between [num] PRECEDING AND [num] FOLLOWING
    

    如: over(partition by col order by 排序字段 range between 5 preceding and 5 fllowing)
    逻辑窗口
    注意: 窗口函数一般不和group by搭配使用。

    应用: 某天某产品的累计销售额。
    物理窗口:
    求多维度累计(累计退款金额、累计交易额、累计订单数量)
    
    逻辑窗口:
    范围累计环比情况、某值上下加减。
    

    4、窗口聚合函数

    创建测试表,存放当天每半小时的店铺销售数据

    CREATE TABLE IF NOT EXISTS shop_data(
    	shop_id INT comment '店铺id', 
    	stat_date STRING comment '时间', 
    	ordamt DOUBLE comment '销售额'
    )
    ROW FORMAT DELIMITED 
    FIELDS TERMINATED BY '\t' 
    LINES TERMINATED BY '\n'
    STORED AS TEXTFILE;
    

    插入数据:

    insert into shop_data values 
    (10026,'201901230030',5170),
    (10026,'201901230100',5669),
    (10026,'201901230130',2396),
    (10026,'201901230200',1498),
    (10026,'201901230230',1997),
    (10026,'201901230300',1188),
    (10026,'201901230330',598),
    (10026,'201901230400',479),
    (10026,'201901230430',1587),
    (10026,'201901230530',799),
    (10027,'201901230030',2170),
    (10027,'201901230100',1623),
    (10027,'201901230130',3397),
    (10027,'201901230200',1434),
    (10027,'201901230230',1001),
    (10028,'201901230300',1687),
    (10028,'201901230330',1298),
    (10028,'201901230400',149),
    (10029,'201901230430',2587),
    (10029,'201901230530',589);
    

    count开窗函数

    select shop_id,stat_date,ordamt,
    -- 以符合条件的所有行作为窗口
    count(shop_id) over() as count1,
     -- 以按shop_id分组的所有行作为窗口
    count(shop_id) over(partition by shop_id) as count2,
     -- 以按shop_id分组、按stat_date排序的所有行作为窗口
    count(shop_id) over(partition by shop_id order by stat_date) as count3,
     -- 以按shop_id分组、按stat_date排序、按当前行+往前1行+往后2行的行作为窗口
    count(ordamt) over(partition by shop_id order by stat_date rows between 1 preceding and 2 following) as count4,
     -- 以按shop_id分组、按stat_date排序、按从起点到末尾,默认从起点到末尾和count2结果相同
    count(ordamt) over(partition by shop_id order by stat_date rows between unbounded preceding and unbounded following) as count5,
     -- 以按shop_id分组、按stat_date排序、按从起点到当前行的前一行
    count(ordamt) over(partition by shop_id order by stat_date rows between unbounded preceding and 1 preceding) as count6,
     -- 以按shop_id分组、按stat_date排序、按从起点到当前行
    count(ordamt) over(partition by shop_id order by stat_date rows between unbounded preceding and current row) as count7,
     -- 以按shop_id分组、按stat_date排序、按从当前行到末尾
    count(ordamt) over(partition by shop_id order by stat_date rows between current row and unbounded following) as count8,
     -- 以按shop_id分组、按stat_date排序、按从当前行往后一行到末尾
    count(ordamt) over(partition by shop_id order by stat_date rows between 1 following and unbounded following) as count9,
     -- 以按shop_id分组、按stat_date排序、按从当前行往后一行到当前行往后2行
    count(ordamt) over(partition by shop_id order by stat_date rows between 1 following and 2 following) as count10
    from shop_data;
    
    OK
    shop_id	stat_date	ordamt	count1	count2	count3	count4	count5	count6	count7	count8	count9	count10
    10026	201901230030	5170.0	20	10	1	3	10	0	1	10	9	2
    10026	201901230100	5669.0	20	10	2	4	10	1	2	9	8	2
    10026	201901230130	2396.0	20	10	3	4	10	2	3	8	7	2
    10026	201901230200	1498.0	20	10	4	4	10	3	4	7	6	2
    10026	201901230230	1997.0	20	10	5	4	10	4	5	6	5	2
    10026	201901230300	1188.0	20	10	6	4	10	5	6	5	4	2
    10026	201901230330	598.0	20	10	7	4	10	6	7	4	3	2
    10026	201901230400	479.0	20	10	8	4	10	7	8	3	2	2
    10026	201901230430	1587.0	20	10	9	3	10	8	9	2	1	1
    10026	201901230530	799.0	20	10	10	2	10	9	10	1	0	0
    10027	201901230030	2170.0	20	5	1	3	5	0	1	5	4	2
    10027	201901230100	1623.0	20	5	2	4	5	1	2	4	3	2
    10027	201901230130	3397.0	20	5	3	4	5	2	3	3	2	2
    10027	201901230200	1434.0	20	5	4	3	5	3	4	2	1	1
    10027	201901230230	1001.0	20	5	5	2	5	4	5	1	0	0
    10028	201901230300	1687.0	20	3	1	3	3	0	1	3	2	2
    10028	201901230330	1298.0	20	3	2	3	3	1	2	2	1	1
    10028	201901230400	149.0	20	3	3	2	3	2	3	1	0	0
    10029	201901230430	2587.0	20	2	1	2	2	0	1	2	1	1
    10029	201901230530	589.0	20	2	2	2	2	1	2	1	0	0
    

    sum开窗函数

    select
    shop_id, stat_date, ordamt,
      -- 以按shop_id分组、按stat_date排序、统计每个商品截止到当前时间的销售总额,默认从起点到当前行
    sum(ordamt) over(partition by shop_id order by stat_date) as sum_amt1,
      -- 以按shop_id分组、按stat_date排序、统计每个商品前半小时到后一小时的销售额(按当前行+往前1行+往后2行的行作为窗口)
    sum(ordamt) over(partition by shop_id order by stat_date rows between 1 preceding and 2 following) as sum_amt2,
     -- 以按shop_id分组、按stat_date排序、统计每个商品的销售总额(从起点到末尾)
    sum(ordamt) over(partition by shop_id order by stat_date rows between unbounded preceding and unbounded following) as sum_amt3,
     -- 以按shop_id分组、按stat_date排序、统计截止到前半小时的销售总额(从起点到当前行的前一行)
    sum(ordamt) over(partition by shop_id order by stat_date rows between unbounded preceding and 1 preceding) as sum_amt4,
     -- 以按shop_id分组、按stat_date排序、统计每个商品截止到当前时间的销售总额,默认从起点到当前行(从起点到当前行)
    sum(ordamt) over(partition by shop_id order by stat_date rows between unbounded preceding and current row) as sum_amt5,
     -- 以按shop_id分组、按stat_date排序、统计当前时间及之后的销售总额(从当前行的末尾)
    sum(ordamt) over(partition by shop_id order by stat_date rows between current row and unbounded following) as sum_amt6,
     -- 以按shop_id分组、按stat_date排序、统计当前时间的后半小时及之后的销售额(当前行后一行到末尾)
    sum(ordamt) over(partition by shop_id order by stat_date rows between 1 following and unbounded following) as sum_amt7,
     -- 以按shop_id分组、按stat_date排序、统计当前时间后半小时到后一小时之间的销售额(按从当前行往后一行到当前行往后2行)
    sum(ordamt) over(partition by shop_id order by stat_date rows between 1 following and 2 following) as sum_amt8
    from shop_data;
    
    OK
    shop_id	stat_date	ordamt	sum_amt1	sum_amt2	sum_amt3	sum_amt4	sum_amt5	sum_amt6	sum_amt7	sum_amt8
    10026	201901230030	5170.0	5170.0	13235.0	21381.0	NULL	5170.0	21381.0	16211.0	8065.0
    10026	201901230100	5669.0	10839.0	14733.0	21381.0	5170.0	10839.0	16211.0	10542.0	3894.0
    10026	201901230130	2396.0	13235.0	11560.0	21381.0	10839.0	13235.0	10542.0	8146.0	3495.0
    10026	201901230200	1498.0	14733.0	7079.0	21381.0	13235.0	14733.0	8146.0	6648.0	3185.0
    10026	201901230230	1997.0	16730.0	5281.0	21381.0	14733.0	16730.0	6648.0	4651.0	1786.0
    10026	201901230300	1188.0	17918.0	4262.0	21381.0	16730.0	17918.0	4651.0	3463.0	1077.0
    10026	201901230330	598.0	18516.0	3852.0	21381.0	17918.0	18516.0	3463.0	2865.0	2066.0
    10026	201901230400	479.0	18995.0	3463.0	21381.0	18516.0	18995.0	2865.0	2386.0	2386.0
    10026	201901230430	1587.0	20582.0	2865.0	21381.0	18995.0	20582.0	2386.0	799.0	799.0
    10026	201901230530	799.0	21381.0	2386.0	21381.0	20582.0	21381.0	799.0	NULL	NULL
    10027	201901230030	2170.0	2170.0	7190.0	9625.0	NULL	2170.0	9625.0	7455.0	5020.0
    10027	201901230100	1623.0	3793.0	8624.0	9625.0	2170.0	3793.0	7455.0	5832.0	4831.0
    10027	201901230130	3397.0	7190.0	7455.0	9625.0	3793.0	7190.0	5832.0	2435.0	2435.0
    10027	201901230200	1434.0	8624.0	5832.0	9625.0	7190.0	8624.0	2435.0	1001.0	1001.0
    10027	201901230230	1001.0	9625.0	2435.0	9625.0	8624.0	9625.0	1001.0	NULL	NULL
    10028	201901230300	1687.0	1687.0	3134.0	3134.0	NULL	1687.0	3134.0	1447.0	1447.0
    10028	201901230330	1298.0	2985.0	3134.0	3134.0	1687.0	2985.0	1447.0	149.0	149.0
    10028	201901230400	149.0	3134.0	1447.0	3134.0	2985.0	3134.0	149.0	NULL	NULL
    10029	201901230430	2587.0	2587.0	3176.0	3176.0	NULL	2587.0	3176.0	589.0	589.0
    10029	201901230530	589.0	3176.0	3176.0	3176.0	2587.0	3176.0	589.0	NULL	NULL
    

    avg开窗函数

    select
    shop_id, stat_date, ordamt,
      -- 以按shop_id分组、按stat_date排序、
    round(avg(ordamt) over(partition by shop_id order by stat_date),2) as avg_amt1,
      -- 以按shop_id分组、按stat_date排序、按当前行+往前1行+往后2行的行作为窗口的平均值
    round(avg(ordamt) over(partition by shop_id order by stat_date rows between 1 preceding and 2 following), 2) as avg_amt2,
     -- 以按shop_id分组、按stat_date排序、从起点到末尾的平均值
    round(avg(ordamt) over(partition by shop_id order by stat_date rows between unbounded preceding and unbounded following), 2) as avg_amt3,
     -- 以按shop_id分组、按stat_date排序、从起点到当前行的前一行的平均值
    round(avg(ordamt) over(partition by shop_id order by stat_date rows between unbounded preceding and 1 preceding), 2) as avg_amt4,
     -- 以按shop_id分组、按stat_date排序、从起点到当前行的平均值
    round(avg(ordamt) over(partition by shop_id order by stat_date rows between unbounded preceding and current row), 2) as avg_amt5,
     -- 以按shop_id分组、按stat_date排序、从当前行的末尾的平均值
    round(avg(ordamt) over(partition by shop_id order by stat_date rows between current row and unbounded following), 2) as avg_amt6,
     -- 以按shop_id分组、按stat_date排序、当前行后一行到末尾的平均值
    round(avg(ordamt) over(partition by shop_id order by stat_date rows between 1 following and unbounded following), 2) as avg_amt7,
     -- 以按shop_id分组、按stat_date排序、按从当前行往后一行到当前行往后2行的平均值
    round(avg(ordamt) over(partition by shop_id order by stat_date rows between 1 following and 2 following), 2) as avg_amt8
    from shop_data;
    
    OK
    shop_id	stat_date	ordamt	avg_amt1	avg_amt2	avg_amt3	avg_amt4	avg_amt5	avg_amt6	avg_amt7	avg_amt8
    10026	201901230030	5170.0	5170.0	4411.67	2138.1	NULL	5170.0	2138.1	1801.22	4032.5
    10026	201901230100	5669.0	5419.5	3683.25	2138.1	5170.0	5419.5	1801.22	1317.75	1947.0
    10026	201901230130	2396.0	4411.67	2890.0	2138.1	5419.5	4411.67	1317.75	1163.71	1747.5
    10026	201901230200	1498.0	3683.25	1769.75	2138.1	4411.67	3683.25	1163.71	1108.0	1592.5
    10026	201901230230	1997.0	3346.0	1320.25	2138.1	3683.25	3346.0	1108.0	930.2	893.0
    10026	201901230300	1188.0	2986.33	1065.5	2138.1	3346.0	2986.33	930.2	865.75	538.5
    10026	201901230330	598.0	2645.14	963.0	2138.1	2986.33	2645.14	865.75	955.0	1033.0
    10026	201901230400	479.0	2374.38	865.75	2138.1	2645.14	2374.38	955.0	1193.0	1193.0
    10026	201901230430	1587.0	2286.89	955.0	2138.1	2374.38	2286.89	1193.0	799.0	799.0
    10026	201901230530	799.0	2138.1	1193.0	2138.1	2286.89	2138.1	799.0	NULL	NULL
    10027	201901230030	2170.0	2170.0	2396.67	1925.0	NULL	2170.0	1925.0	1863.75	2510.0
    10027	201901230100	1623.0	1896.5	2156.0	1925.0	2170.0	1896.5	1863.75	1944.0	2415.5
    10027	201901230130	3397.0	2396.67	1863.75	1925.0	1896.5	2396.67	1944.0	1217.5	1217.5
    10027	201901230200	1434.0	2156.0	1944.0	1925.0	2396.67	2156.0	1217.5	1001.0	1001.0
    10027	201901230230	1001.0	1925.0	1217.5	1925.0	2156.0	1925.0	1001.0	NULL	NULL
    10028	201901230300	1687.0	1687.0	1044.67	1044.67	NULL	1687.0	1044.67	723.5	723.5
    10028	201901230330	1298.0	1492.5	1044.67	1044.67	1687.0	1492.5	723.5	149.0	149.0
    10028	201901230400	149.0	1044.67	723.5	1044.67	1492.5	1044.67	149.0	NULL	NULL
    10029	201901230430	2587.0	2587.0	1588.0	1588.0	NULL	2587.0	1588.0	589.0	589.0
    10029	201901230530	589.0	1588.0	1588.0	1588.0	2587.0	1588.0	589.0	NULL	NULL
    

    min开窗函数

    select
    shop_id, stat_date, ordamt,
      -- 以按shop_id分组、按stat_date排序、
    min(ordamt) over(partition by shop_id order by stat_date) as min_amt1,
      -- 以按shop_id分组、按stat_date排序、按当前行+往前1行+往后2行的行作为窗口的最小数
    min(ordamt) over(partition by shop_id order by stat_date rows between 1 preceding and 2 following) as min_amt2,
     -- 以按shop_id分组、按stat_date排序、从起点到末尾的最小数
    min(ordamt) over(partition by shop_id order by stat_date rows between unbounded preceding and unbounded following) as min_amt3,
     -- 以按shop_id分组、按stat_date排序、从起点到当前行的前一行的最小数
    min(ordamt) over(partition by shop_id order by stat_date rows between unbounded preceding and 1 preceding) as min_amt4,
     -- 以按shop_id分组、按stat_date排序、从起点到当前行的最小数
    min(ordamt) over(partition by shop_id order by stat_date rows between unbounded preceding and current row) as min_amt5,
     -- 以按shop_id分组、按stat_date排序、从当前行的末尾的最小数
    min(ordamt) over(partition by shop_id order by stat_date rows between current row and unbounded following) as min_amt6,
     -- 以按shop_id分组、按stat_date排序、当前行后一行到末尾的最小数
    min(ordamt) over(partition by shop_id order by stat_date rows between 1 following and unbounded following) as min_amt7,
     -- 以按shop_id分组、按stat_date排序、按从当前行往后一行到当前行往后2行的最小数
    min(ordamt) over(partition by shop_id order by stat_date rows between 1 following and 2 following) as min_amt8
    from shop_data;
    
    OK
    shop_id	stat_date	ordamt	min_amt1	min_amt2	min_amt3	min_amt4	min_amt5	min_amt6	min_amt7	min_amt8
    10026	201901230030	5170.0	5170.0	2396.0	479.0	NULL	5170.0	479.0	479.0	2396.0
    10026	201901230100	5669.0	5170.0	1498.0	479.0	5170.0	5170.0	479.0	479.0	1498.0
    10026	201901230130	2396.0	2396.0	1498.0	479.0	5170.0	2396.0	479.0	479.0	1498.0
    10026	201901230200	1498.0	1498.0	1188.0	479.0	2396.0	1498.0	479.0	479.0	1188.0
    10026	201901230230	1997.0	1498.0	598.0	479.0	1498.0	1498.0	479.0	479.0	598.0
    10026	201901230300	1188.0	1188.0	479.0	479.0	1498.0	1188.0	479.0	479.0	479.0
    10026	201901230330	598.0	598.0	479.0	479.0	1188.0	598.0	479.0	479.0	479.0
    10026	201901230400	479.0	479.0	479.0	479.0	598.0	479.0	479.0	799.0	799.0
    10026	201901230430	1587.0	479.0	479.0	479.0	479.0	479.0	799.0	799.0	799.0
    10026	201901230530	799.0	479.0	799.0	479.0	479.0	479.0	799.0	NULL	NULL
    10027	201901230030	2170.0	2170.0	1623.0	1001.0	NULL	2170.0	1001.0	1001.0	1623.0
    10027	201901230100	1623.0	1623.0	1434.0	1001.0	2170.0	1623.0	1001.0	1001.0	1434.0
    10027	201901230130	3397.0	1623.0	1001.0	1001.0	1623.0	1623.0	1001.0	1001.0	1001.0
    10027	201901230200	1434.0	1434.0	1001.0	1001.0	1623.0	1434.0	1001.0	1001.0	1001.0
    10027	201901230230	1001.0	1001.0	1001.0	1001.0	1434.0	1001.0	1001.0	NULL	NULL
    10028	201901230300	1687.0	1687.0	149.0	149.0	NULL	1687.0	149.0	149.0	149.0
    10028	201901230330	1298.0	1298.0	149.0	149.0	1687.0	1298.0	149.0	149.0	149.0
    10028	201901230400	149.0	149.0	149.0	149.0	1298.0	149.0	149.0	NULL	NULL
    10029	201901230430	2587.0	2587.0	589.0	589.0	NULL	2587.0	589.0	589.0	589.0
    10029	201901230530	589.0	589.0	589.0	589.0	2587.0	589.0	589.0	NULL	NULL
    

    max开窗函数

    select
    shop_id, stat_date, ordamt,
      -- 以按shop_id分组、按stat_date排序、
    max(ordamt) over(partition by shop_id order by stat_date) as max_amt1,
      -- 以按shop_id分组、按stat_date排序、按当前行+往前1行+往后2行的行作为窗口的最大数
    max(ordamt) over(partition by shop_id order by stat_date rows between 1 preceding and 2 following) as max_amt2,
     -- 以按shop_id分组、按stat_date排序、从起点到末尾的最大数
    max(ordamt) over(partition by shop_id order by stat_date rows between unbounded preceding and unbounded following) as max_amt3,
     -- 以按shop_id分组、按stat_date排序、从起点到当前行的前一行的最大数
    max(ordamt) over(partition by shop_id order by stat_date rows between unbounded preceding and 1 preceding) as max_amt4,
     -- 以按shop_id分组、按stat_date排序、从起点到当前行的最大数
    max(ordamt) over(partition by shop_id order by stat_date rows between unbounded preceding and current row) as max_amt5,
     -- 以按shop_id分组、按stat_date排序、从当前行的末尾的最大数
    max(ordamt) over(partition by shop_id order by stat_date rows between current row and unbounded following) as max_amt6,
     -- 以按shop_id分组、按stat_date排序、当前行后一行到末尾的最大数
    max(ordamt) over(partition by shop_id order by stat_date rows between 1 following and unbounded following) as max_amt7,
     -- 以按shop_id分组、按stat_date排序、按从当前行往后一行到当前行往后2行的最大数
    max(ordamt) over(partition by shop_id order by stat_date rows between 1 following and 2 following) as max_amt8
    from shop_data;
    
    OK
    shop_id	stat_date	ordamt	max_amt1	max_amt2	max_amt3	max_amt4	max_amt5	max_amt6	max_amt7	max_amt8
    10026	201901230030	5170.0	5170.0	5669.0	5669.0	NULL	5170.0	5669.0	5669.0	5669.0
    10026	201901230100	5669.0	5669.0	5669.0	5669.0	5170.0	5669.0	5669.0	2396.0	2396.0
    10026	201901230130	2396.0	5669.0	5669.0	5669.0	5669.0	5669.0	2396.0	1997.0	1997.0
    10026	201901230200	1498.0	5669.0	2396.0	5669.0	5669.0	5669.0	1997.0	1997.0	1997.0
    10026	201901230230	1997.0	5669.0	1997.0	5669.0	5669.0	5669.0	1997.0	1587.0	1188.0
    10026	201901230300	1188.0	5669.0	1997.0	5669.0	5669.0	5669.0	1587.0	1587.0	598.0
    10026	201901230330	598.0	5669.0	1587.0	5669.0	5669.0	5669.0	1587.0	1587.0	1587.0
    10026	201901230400	479.0	5669.0	1587.0	5669.0	5669.0	5669.0	1587.0	1587.0	1587.0
    10026	201901230430	1587.0	5669.0	1587.0	5669.0	5669.0	5669.0	1587.0	799.0	799.0
    10026	201901230530	799.0	5669.0	1587.0	5669.0	5669.0	5669.0	799.0	NULL	NULL
    10027	201901230030	2170.0	2170.0	3397.0	3397.0	NULL	2170.0	3397.0	3397.0	3397.0
    10027	201901230100	1623.0	2170.0	3397.0	3397.0	2170.0	2170.0	3397.0	3397.0	3397.0
    10027	201901230130	3397.0	3397.0	3397.0	3397.0	2170.0	3397.0	3397.0	1434.0	1434.0
    10027	201901230200	1434.0	3397.0	3397.0	3397.0	3397.0	3397.0	1434.0	1001.0	1001.0
    10027	201901230230	1001.0	3397.0	1434.0	3397.0	3397.0	3397.0	1001.0	NULL	NULL
    10028	201901230300	1687.0	1687.0	1687.0	1687.0	NULL	1687.0	1687.0	1298.0	1298.0
    10028	201901230330	1298.0	1687.0	1687.0	1687.0	1687.0	1687.0	1298.0	149.0	149.0
    10028	201901230400	149.0	1687.0	1298.0	1687.0	1687.0	1687.0	149.0	NULL	NULL
    10029	201901230430	2587.0	2587.0	2587.0	2587.0	NULL	2587.0	2587.0	589.0	589.0
    10029	201901230530	589.0	2587.0	2587.0	2587.0	2587.0	2587.0	589.0	NULL	NULL
    

    5、窗口分析函数

    first_value开窗函数

    select
    shop_id, stat_date, ordamt,
      -- 以按shop_id分组、按stat_date排序、
    first_value(ordamt) over(partition by shop_id order by stat_date) as first_amt1,
      -- 以按shop_id分组、按stat_date排序、按当前行+往前1行+往后2行的行作为窗口的第一个值
    first_value(ordamt) over(partition by shop_id order by stat_date rows between 1 preceding and 2 following) as first_amt2,
     -- 以按shop_id分组、按stat_date排序、从起点到末尾的第一个值
    first_value(ordamt) over(partition by shop_id order by stat_date rows between unbounded preceding and unbounded following) as first_amt3,
     -- 以按shop_id分组、按stat_date排序、从起点到当前行的前一行的第一个值
    first_value(ordamt) over(partition by shop_id order by stat_date rows between unbounded preceding and 1 preceding) as first_amt4,
     -- 以按shop_id分组、按stat_date排序、从起点到当前行的第一个值
    first_value(ordamt) over(partition by shop_id order by stat_date rows between unbounded preceding and current row) as first_amt5,
     -- 以按shop_id分组、按stat_date排序、从当前行的末尾的第一个值
    first_value(ordamt) over(partition by shop_id order by stat_date rows between current row and unbounded following) as first_amt6,
     -- 以按shop_id分组、按stat_date排序、当前行后一行到末尾的第一个值
    first_value(ordamt) over(partition by shop_id order by stat_date rows between 1 following and unbounded following) as first_amt7,
     -- 以按shop_id分组、按stat_date排序、按从当前行往后一行到当前行往后2行的第一个值
    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;
    
    OK
    shop_id	stat_date	ordamt	first_amt1	first_amt2	first_amt3	first_amt4	first_amt5	first_amt6	first_amt7	first_amt8
    10026	201901230030	5170.0	5170.0	5170.0	5170.0	NULL	5170.0	5170.0	5669.0	5669.0
    10026	201901230100	5669.0	5170.0	5170.0	5170.0	5170.0	5170.0	5669.0	2396.0	2396.0
    10026	201901230130	2396.0	5170.0	5669.0	5170.0	5170.0	5170.0	2396.0	1498.0	1498.0
    10026	201901230200	1498.0	5170.0	2396.0	5170.0	5170.0	5170.0	1498.0	1997.0	1997.0
    10026	201901230230	1997.0	5170.0	1498.0	5170.0	5170.0	5170.0	1997.0	1188.0	1188.0
    10026	201901230300	1188.0	5170.0	1997.0	5170.0	5170.0	5170.0	1188.0	598.0	598.0
    10026	201901230330	598.0	5170.0	1188.0	5170.0	5170.0	5170.0	598.0	479.0	479.0
    10026	201901230400	479.0	5170.0	598.0	5170.0	5170.0	5170.0	479.0	1587.0	1587.0
    10026	201901230430	1587.0	5170.0	479.0	5170.0	5170.0	5170.0	1587.0	799.0	799.0
    10026	201901230530	799.0	5170.0	1587.0	5170.0	5170.0	5170.0	799.0	NULL	NULL
    10027	201901230030	2170.0	2170.0	2170.0	2170.0	NULL	2170.0	2170.0	1623.0	1623.0
    10027	201901230100	1623.0	2170.0	2170.0	2170.0	2170.0	2170.0	1623.0	3397.0	3397.0
    10027	201901230130	3397.0	2170.0	1623.0	2170.0	2170.0	2170.0	3397.0	1434.0	1434.0
    10027	201901230200	1434.0	2170.0	3397.0	2170.0	2170.0	2170.0	1434.0	1001.0	1001.0
    10027	201901230230	1001.0	2170.0	1434.0	2170.0	2170.0	2170.0	1001.0	NULL	NULL
    10028	201901230300	1687.0	1687.0	1687.0	1687.0	NULL	1687.0	1687.0	1298.0	1298.0
    10028	201901230330	1298.0	1687.0	1687.0	1687.0	1687.0	1687.0	1298.0	149.0	149.0
    10028	201901230400	149.0	1687.0	1298.0	1687.0	1687.0	1687.0	149.0	NULL	NULL
    10029	201901230430	2587.0	2587.0	2587.0	2587.0	NULL	2587.0	2587.0	589.0	589.0
    10029	201901230530	589.0	2587.0	2587.0	2587.0	2587.0	2587.0	589.0	NULL	NULL
    

    last_value开窗函数

    select
    shop_id, stat_date, ordamt,
      -- 以按shop_id分组、按stat_date排序、
    last_value(ordamt) over(partition by shop_id order by stat_date) as last_amt1,
      -- 以按shop_id分组、按stat_date排序、按当前行+往前1行+往后2行的行作为窗口的最后一个值
    last_value(ordamt) over(partition by shop_id order by stat_date rows between 1 preceding and 2 following) as last_amt2,
     -- 以按shop_id分组、按stat_date排序、从起点到末尾的最后一个值
    last_value(ordamt) over(partition by shop_id order by stat_date rows between unbounded preceding and unbounded following) as last_amt3,
     -- 以按shop_id分组、按stat_date排序、从起点到当前行的前一行的最后一个值
    last_value(ordamt) over(partition by shop_id order by stat_date rows between unbounded preceding and 1 preceding) as last_amt4,
     -- 以按shop_id分组、按stat_date排序、从起点到当前行的最后一个值
    last_value(ordamt) over(partition by shop_id order by stat_date rows between unbounded preceding and current row) as last_amt5,
     -- 以按shop_id分组、按stat_date排序、从当前行的末尾的最后一个值
    last_value(ordamt) over(partition by shop_id order by stat_date rows between current row and unbounded following) as last_amt6,
     -- 以按shop_id分组、按stat_date排序、当前行后一行到末尾的最后一个值
    last_value(ordamt) over(partition by shop_id order by stat_date rows between 1 following and unbounded following) as last_amt7,
     -- 以按shop_id分组、按stat_date排序、按从当前行往后一行到当前行往后2行的最后一个值
    last_value(ordamt) over(partition by shop_id order by stat_date rows between 1 following and 2 following) as last_amt8
    from shop_data;
    
    OK
    shop_id	stat_date	ordamt	last_amt1	last_amt2	last_amt3	last_amt4	last_amt5	last_amt6	last_amt7	last_amt8
    10026	201901230030	5170.0	5170.0	2396.0	799.0	NULL	5170.0	799.0	799.0	2396.0
    10026	201901230100	5669.0	5669.0	1498.0	799.0	5170.0	5669.0	799.0	799.0	1498.0
    10026	201901230130	2396.0	2396.0	1997.0	799.0	5669.0	2396.0	799.0	799.0	1997.0
    10026	201901230200	1498.0	1498.0	1188.0	799.0	2396.0	1498.0	799.0	799.0	1188.0
    10026	201901230230	1997.0	1997.0	598.0	799.0	1498.0	1997.0	799.0	799.0	598.0
    10026	201901230300	1188.0	1188.0	479.0	799.0	1997.0	1188.0	799.0	799.0	479.0
    10026	201901230330	598.0	598.0	1587.0	799.0	1188.0	598.0	799.0	799.0	1587.0
    10026	201901230400	479.0	479.0	799.0	799.0	598.0	479.0	799.0	799.0	799.0
    10026	201901230430	1587.0	1587.0	799.0	799.0	479.0	1587.0	799.0	799.0	799.0
    10026	201901230530	799.0	799.0	799.0	799.0	1587.0	799.0	799.0	NULL	NULL
    10027	201901230030	2170.0	2170.0	3397.0	1001.0	NULL	2170.0	1001.0	1001.0	3397.0
    10027	201901230100	1623.0	1623.0	1434.0	1001.0	2170.0	1623.0	1001.0	1001.0	1434.0
    10027	201901230130	3397.0	3397.0	1001.0	1001.0	1623.0	3397.0	1001.0	1001.0	1001.0
    10027	201901230200	1434.0	1434.0	1001.0	1001.0	3397.0	1434.0	1001.0	1001.0	1001.0
    10027	201901230230	1001.0	1001.0	1001.0	1001.0	1434.0	1001.0	1001.0	NULL	NULL
    10028	201901230300	1687.0	1687.0	149.0	149.0	NULL	1687.0	149.0	149.0	149.0
    10028	201901230330	1298.0	1298.0	149.0	149.0	1687.0	1298.0	149.0	149.0	149.0
    10028	201901230400	149.0	149.0	149.0	149.0	1298.0	149.0	149.0	NULL	NULL
    10029	201901230430	2587.0	2587.0	589.0	589.0	NULL	2587.0	589.0	589.0	589.0
    10029	201901230530	589.0	589.0	589.0	589.0	2587.0	589.0	589.0	NULL	NULL
    

    lag开窗函数

    注意:
    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
    shop_id, stat_date, ordamt,
    -- 以按shop_id分组、按stat_date排序、注意第三个参数默认值的类型需要和列类型匹配,否则不生效
    lag(ordamt, 1, 0) over(partition by shop_id order by stat_date) as last_amt1,
    lag(ordamt, 2, 'NA') over(partition by shop_id order by stat_date rows between unbounded preceding and unbounded following) as last_amt2 
    from shop_data;
    
    OK
    shop_id     stat_date   ordamt  last_amt1   last_amt2 
    10026   201901230030    5170    0       0
    10026   201901230100    5669    5170    0
    10026   201901230130    2396    5669    5170
    10026   201901230200    1498    2396    5669
    10026   201901230230    1997    1498    2396
    10026   201901230300    1188    1997    1498
    10026   201901230330    598     1188    1997
    10026   201901230400    479     598     1188
    10026   201901230430    1587    479     598
    10026   201901230530    799     1587    479
    10027   201901230030    2170    0       0
    10027   201901230100    1623    2170    0
    10027   201901230130    3397    1623    2170
    10027   201901230200    1434    3397    1623
    10027   201901230230    1001    1434    3397
    10028   201901230300    1687    0       0
    10028   201901230330    1298    1687    0
    10028   201901230400    149     1298    1687
    10029   201901230430    2587    0       0
    10029   201901230530    589     2587    0
    

    lead开窗函数

    select
    shop_id, stat_date, ordamt,
    -- 以按shop_id分组、按stat_date排序、注意第三个参数默认值的类型需要和列类型匹配,否则不生效
    lead(ordamt, 1, 0) over(partition by shop_id order by stat_date) as last_amt1,
    lead(ordamt, 2, 'NA') over(partition by shop_id order by stat_date rows between unbounded preceding and unbounded following) as last_amt2 
    from shop_data;
    
    OK
    shop_id     stat_date   ordamt  last_amt1   last_amt2 
    10026   201901230030    5170    5669    2396
    10026   201901230100    5669    2396    1498
    10026   201901230130    2396    1498    1997
    10026   201901230200    1498    1997    1188
    10026   201901230230    1997    1188    598
    10026   201901230300    1188    598     479
    10026   201901230330    598     479     1587
    10026   201901230400    479     1587    799
    10026   201901230430    1587    799     0
    10026   201901230530    799     0       0
    10027   201901230030    2170    1623    3397
    10027   201901230100    1623    3397    1434
    10027   201901230130    3397    1434    1001
    10027   201901230200    1434    1001    0
    10027   201901230230    1001    0       0
    10028   201901230300    1687    1298    149
    10028   201901230330    1298    149     0
    10028   201901230400    149     0       0
    10029   201901230430    2587    589     0
    10029   201901230530    589     0       0
    

    cume_dist开窗函数

    计算某个窗口或分区中某个值的累积分布。假定升序排序,则使用以下公式确定累积分布:
    小于等于当前值x的行数 / 窗口或partition分区内的总行数。其中,x 等于 order by 子句中指定的列的当前行中的值。

    select 
    shop_id, stat_date, ordamt,
    -- 统计小于等于当前销售额的订单占总订单的比例
    cume_dist() over(order by ordamt) as cume_dist1,
    -- 统计大于等于当前销售额的订单占总订单的比例
    cume_dist() over(order by ordamt desc) as cume_dist2,
    -- 统计分区内小于等于当前销售额的订单占总订单的比例
    round(cume_dist() over(partition by shop_id order by ordamt), 2) as cume_dist3
    from shop_data;
    
    shop_id	stat_date	ordamt	cume_dist1	cume_dist2	cume_dist3
    10026	201901230400	479.0	0.1	0.95	0.1
    10026	201901230330	598.0	0.2	0.85	0.2
    10026	201901230530	799.0	0.25	0.8	0.3
    10026	201901230300	1188.0	0.35	0.7	0.4
    10026	201901230200	1498.0	0.5	0.55	0.5
    10026	201901230430	1587.0	0.55	0.5	0.6
    10026	201901230230	1997.0	0.7	0.35	0.7
    10026	201901230130	2396.0	0.8	0.25	0.8
    10026	201901230030	5170.0	0.95	0.1	0.9
    10026	201901230100	5669.0	1.0	0.05	1.0
    10027	201901230230	1001.0	0.3	0.75	0.2
    10027	201901230200	1434.0	0.45	0.6	0.4
    10027	201901230100	1623.0	0.6	0.45	0.6
    10027	201901230030	2170.0	0.75	0.3	0.8
    10027	201901230130	3397.0	0.9	0.15	1.0
    10028	201901230400	149.0	0.05	1.0	0.33
    10028	201901230330	1298.0	0.4	0.65	0.67
    10028	201901230300	1687.0	0.65	0.4	1.0
    10029	201901230530	589.0	0.15	0.9	0.5
    10029	201901230430	2587.0	0.85	0.2	1.0
    

    6、窗口排序函数

    rank开窗函数

    rank 开窗函数基于 over 子句中的 order by 确定一组值中一个值的排名。
    如果存在partition by ,则为每个分区组中的每个值排名。排名可能不是连续的。例如,如果两个行的排名为 1,则下一个排名为 3。

    select
    shop_id, stat_date, ordamt,
      -- 以按shop_id排序
    rank() over(order by shop_id) as rank_amt1,
      -- 以按shop_id分区、按stat_date排序
    rank() over(partition by shop_id order by stat_date) as rank_amt2
    from shop_data;
    
    OK
    shop_id	stat_date	ordamt	rank_amt1	rank_amt2
    10026	201901230030	5170.0	1	1
    10026	201901230100	5669.0	1	2
    10026	201901230130	2396.0	1	3
    10026	201901230200	1498.0	1	4
    10026	201901230230	1997.0	1	5
    10026	201901230300	1188.0	1	6
    10026	201901230330	598.0	1	7
    10026	201901230400	479.0	1	8
    10026	201901230430	1587.0	1	9
    10026	201901230530	799.0	1	10
    10027	201901230030	2170.0	11	1
    10027	201901230100	1623.0	11	2
    10027	201901230130	3397.0	11	3
    10027	201901230200	1434.0	11	4
    10027	201901230230	1001.0	11	5
    10028	201901230300	1687.0	16	1
    10028	201901230330	1298.0	16	2
    10028	201901230400	149.0	16	3
    10029	201901230430	2587.0	19	1
    10029	201901230530	589.0	19	2
    

    dense_rank开窗函数

    dense_rank与rank有一点不同,当排名一样的时候,接下来的行是连续的。如两个行的排名为 1,则下一个排名为 2。

    select
    shop_id, stat_date, ordamt,
      -- 以按shop_id排序
    dense_rank() over(order by shop_id) as dense_amt1,
      -- 以按shop_id分区、按stat_date排序
    dense_rank() over(partition by shop_id order by stat_date) as dense_amt2
    from shop_data;
    
    OK
    shop_id	stat_date	ordamt	dense_amt1	dense_amt2
    10026	201901230030	5170.0	1	1
    10026	201901230100	5669.0	1	2
    10026	201901230130	2396.0	1	3
    10026	201901230200	1498.0	1	4
    10026	201901230230	1997.0	1	5
    10026	201901230300	1188.0	1	6
    10026	201901230330	598.0	1	7
    10026	201901230400	479.0	1	8
    10026	201901230430	1587.0	1	9
    10026	201901230530	799.0	1	10
    10027	201901230030	2170.0	2	1
    10027	201901230100	1623.0	2	2
    10027	201901230130	3397.0	2	3
    10027	201901230200	1434.0	2	4
    10027	201901230230	1001.0	2	5
    10028	201901230300	1687.0	3	1
    10028	201901230330	1298.0	3	2
    10028	201901230400	149.0	3	3
    10029	201901230430	2587.0	4	1
    10029	201901230530	589.0	4	2
    

    ntile开窗函数

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

    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;
    
    OK
    shop_id	stat_date	ordamt	ntile_amt1	ntile_amt2
    10026	201901230030	5170.0	1	1
    10026	201901230100	5669.0	1	1
    10026	201901230130	2396.0	1	1
    10026	201901230200	1498.0	1	1
    10026	201901230230	1997.0	1	2
    10026	201901230300	1188.0	2	2
    10026	201901230330	598.0	2	2
    10026	201901230400	479.0	2	3
    10026	201901230430	1587.0	2	3
    10026	201901230530	799.0	2	3
    10027	201901230030	2170.0	1	1
    10027	201901230100	1623.0	1	1
    10027	201901230130	3397.0	1	2
    10027	201901230200	1434.0	2	2
    10027	201901230230	1001.0	2	3
    10028	201901230300	1687.0	1	1
    10028	201901230330	1298.0	1	2
    10028	201901230400	149.0	2	3
    10029	201901230430	2587.0	1	1
    10029	201901230530	589.0	2	2
    

    row_number开窗函数

    从1开始对分区内的数据排序。

    select
    shop_id, stat_date, ordamt,
      -- 以按shop_id分区、按stat_date排序
    row_number() over(partition by shop_id order by stat_date) as row_amt
    from shop_data;
    
    OK
    shop_id	stat_date	ordamt	row_amt
    10026	201901230030	5170.0	1
    10026	201901230100	5669.0	2
    10026	201901230130	2396.0	3
    10026	201901230200	1498.0	4
    10026	201901230230	1997.0	5
    10026	201901230300	1188.0	6
    10026	201901230330	598.0	7
    10026	201901230400	479.0	8
    10026	201901230430	1587.0	9
    10026	201901230530	799.0	10
    10027	201901230030	2170.0	1
    10027	201901230100	1623.0	2
    10027	201901230130	3397.0	3
    10027	201901230200	1434.0	4
    10027	201901230230	1001.0	5
    10028	201901230300	1687.0	1
    10028	201901230330	1298.0	2
    10028	201901230400	149.0	3
    10029	201901230430	2587.0	1
    10029	201901230530	589.0	2
    

    percent_rank开窗函数

    计算给定行的百分比排名。可以用来计算超过了百分之多少的人。如360小助手开机速度超过了百分之多少的人。
    (当前行的rank值-1)/(分组内的总行数-1)

    select
    shop_id, stat_date, ordamt,
      -- 以按shop_id分区、按stat_date排序
    row_number() over(partition by shop_id order by stat_date) as row_amt,
    round(percent_rank() over(partition by shop_id order by stat_date), 2) as percent_amt
    from shop_data;
    
    OK
    shop_id	stat_date	ordamt	row_amt	percent_amt
    10026	201901230030	5170.0	1	0.0
    10026	201901230100	5669.0	2	0.11
    10026	201901230130	2396.0	3	0.22
    10026	201901230200	1498.0	4	0.33
    10026	201901230230	1997.0	5	0.44
    10026	201901230300	1188.0	6	0.56
    10026	201901230330	598.0	7	0.67
    10026	201901230400	479.0	8	0.78
    10026	201901230430	1587.0	9	0.89
    10026	201901230530	799.0	10	1.0
    10027	201901230030	2170.0	1	0.0
    10027	201901230100	1623.0	2	0.25
    10027	201901230130	3397.0	3	0.5
    10027	201901230200	1434.0	4	0.75
    10027	201901230230	1001.0	5	1.0
    10028	201901230300	1687.0	1	0.0
    10028	201901230330	1298.0	2	0.5
    10028	201901230400	149.0	3	1.0
    10029	201901230430	2587.0	1	0.0
    10029	201901230530	589.0	2	1.0
    
    展开全文
  • DA_2 Hive_3 Hive窗口函数基础 001 Hive窗口函数基础巩固.pdf
  • hadoop hive窗口函数求和

    千次阅读 2020-02-07 21:01:19
    聚合函数:例如sum()、avg()、max()等,这类函数...窗口函数又叫OLAP函数/分析函数,窗口函数兼具分组和排序功能。 ​ 窗口函数最重要的关键字是 partition by 和 order by。 核心语法:over (partition by xxx...

    聚合函数:例如sum()、avg()、max()等,这类函数可以将多行数据按照规则聚集为一行,一般来讲聚集后的行数是要少于聚集前的行数的。

    窗口函数:既能显示聚集前的数据,又能显示聚集后的数据。窗口函数又叫OLAP函数/分析函数,窗口函数兼具分组和排序功能

    ​ 窗口函数最重要的关键字是 partition by 和 order by。

    核心语法:over (partition by xxx order by xxx)

    案例【求和】

    原始数据:

    --只使用聚合函数sum来对数据进行操作

    select  sum(pv) from itcast_t1; 

    结果如下:

    --sum函数搭配窗口函数的使用【根据cookieid分组,根据createtime排序】

    select cookieid,createtime,pv,
    sum(pv) over(partition by cookieid order by createtime) as pv1 
    from itcast_t1;

    结果:

    --此时sum聚合:默认是第一行到当前行聚合
    --还可以通过window子句 来控制聚合的行的范围:rows between含义,也叫做window子句
    --除了sum之外 avg max min都可以配合窗口函数使用

    展开全文
  • Hive窗口函数

    2020-09-19 17:10:32
    Hive窗口函数窗口函数概述排序聚合分析窗口定义 窗口函数 概述 窗口函数是一组特殊函数 扫描多个输入行来计算每个输出值,为每行数据生成一行结果 可以通过窗口函数来实现复杂的计算和聚合 语法 function (arg1,.....


    可参考博客: https://zhuanlan.zhihu.com/p/113245904

    窗口函数

    概述

    • 窗口函数是一组特殊函数
      扫描多个输入行来计算每个输出值,为每行数据生成一行结果
      可以通过窗口函数来实现复杂的计算和聚合
    • 语法
    function (arg1,..., arg n) over ([partition by <...>] [order by <....>] [<window_clause>])
    
      • partition by类似于group by,未指定则按整个结果集
      • 只有指定group by子句之后才能进行窗口定义
      • 可同时使用多个窗口函数
      • 过滤窗口函数计算结果必须在外面一层
    • 按功能可划分为:排序,聚合,分析

    案例:

    create database wintest;
    use wintest;
    create table test(name string,buydate string,num int)
    row format delimited
    fields terminated by ',';
    

    在这里插入图片描述
    在这里插入图片描述

    select * from test;
    

    在这里插入图片描述

    select *,count(*) over() from test;
    

    在这里插入图片描述
    在这里插入图片描述

    select *,count(*) over(partition by name) from test;
    

    在这里插入图片描述
    在这里插入图片描述

    select *,count(*) over(partition by name order by num) from test;
    

    在这里插入图片描述
    在这里插入图片描述

    select *,count(*) over(partition by name order by buydate desc) from test;
    

    在这里插入图片描述
    在这里插入图片描述

    select * from (select *,count(*) over(partition by name order by buydate desc) win1 from test) a where a.win1<3;
    

    在这里插入图片描述
    在这里插入图片描述

    select *,count(1) over() from test where buydate like "2018-01-%";
    

    在这里插入图片描述
    在这里插入图片描述

    排序

    • row_number()
      对所有数值输出不同的序号,序号唯一连续
    select *,row_number() over() from test;
    

    在这里插入图片描述
    在这里插入图片描述

    select *,row_number() over(partition by name) from test;
    

    在这里插入图片描述
    在这里插入图片描述

    • rank()
      对相同数值,输出相同的序号,下一个序号跳过(1,1,3)
    select * ,rank() over() win1 from test;
    

    在这里插入图片描述
    在这里插入图片描述

    select * ,rank() over(partition by name) win1 from test;
    

    在这里插入图片描述
    在这里插入图片描述

    select * ,rank() over(partition by name order by buydate) win1 from test;
    

    在这里插入图片描述
    在这里插入图片描述

    • dense_rank()
      对相同数值,输出相同的序号,下一个序号连续(1,1,2)
    select * ,dense_rank() over(partition by name order by buydate) win1 from test;
    

    在这里插入图片描述
    在这里插入图片描述

    • ntile(n)
      用于对分组数据按照顺序切片分成n片,返回当前切片值
    select *,ntile(4) over(partition by name) from test;
    

    在这里插入图片描述
    在这里插入图片描述

    • percent_rank()
      (目前排名- 1)/(总行数- 1),值相对于一组值的百分比排名

    聚合

    • count()
      计数,可以和distinct一起用
    • sum():求和
    • avg():平均值
    • max()/min(): 最大/小值
      从Hive 2.1.0开始在over子句中支持聚合函数

    分析

    • cume_dist
      小于等于当前值的行数/分组内总行数
    select *,cume_dist() over(partition by name order by buydate) from test;
    

    在这里插入图片描述
    在这里插入图片描述

    • lead/lag(col,n)
      某一列进行往前/后取第n行值(n可选,默认为1)
    select *,lag(buydate) over(partition by name order by buydate) from test;
    

    在这里插入图片描述
    在这里插入图片描述

    select *,lag(buydate) over(partition by name order by buydate) lastdate,lag(num) over(partition by name order by buydate) from test;
    

    在这里插入图片描述
    在这里插入图片描述

    • first_value
      对该列到目前为止的首个值
    select *,lag(buydate) over(partition by name order by buydate) lastdate,lag(num) over(partition by name order by buydate),first_value(num) over(partition by name order by buydate) from test;
    

    在这里插入图片描述
    在这里插入图片描述

    • last_value
      到目前行为止的最后一个值
    select *,lag(buydate) over(partition by name order by buydate) lastdate,lag(num) over(partition by name order by buydate),last_value(num) over(partition by name order by buydate) from test;
    

    在这里插入图片描述
    在这里插入图片描述

    窗口定义

    • 窗口定义由[<window_clause>]子句描述
      用于进一步细分结果并应用分析函数

    • 支持两类窗口定义
      行类型窗口
      范围类型窗口

    • rank、ntile、dense_rank、cume_dist、percent_rank、lead、lag和row_number函数不支持与窗口子句一起使用

    • 行窗口:根据当前行之前或之后的行号确定的窗口
      rows between <start_expr> and <end_expr>

    • <start_expr>可以为下列值
      unbounded preceding: 窗口起始位置(分组第一行)
      current row:当前行
      n preceding/following:当前行之前/之后n行

    • <end_expr>可以为下列值
      unbounded following : 窗口结束位置(分组最后一行)
      current row:当前行
      n preceding/following:当前行之前/之后n行

    案例:

    select *,count(1) over(partition by name order by buydate desc rows between unbounded preceding and current row ) from test;
    

    在这里插入图片描述
    在这里插入图片描述

    select *,count(1) over(partition by name order by buydate desc rows between 1 preceding and current row) from test;
    

    在这里插入图片描述
    在这里插入图片描述

    select *,count(1) over(partition by name order by buydate desc rows between 1 preceding and 1 following) from test;
    

    在这里插入图片描述
    在这里插入图片描述

    • 范围窗口是取分组内的值在指定范围区间内的行
      range between <start_expr> and <end_expr>
      该范围值/区间必须是数字或日期类型
      目前只支持一个order by列
    select *,count(num) over(partition by name order by num range between 20 preceding and 10 following) rst from test;
    

    在这里插入图片描述
    在这里插入图片描述

    展开全文
  • selectname,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 ...
  • 1. 窗口函数和普通聚合函数的区别 聚合函数是将多条记录合并为一条;窗口函数是每条记录都会执行,原来有几条记录最终执行完还是几条 聚合函数也可以用于窗口函数窗口函数在逻辑上的执行顺序是在FROM、JOIN、...
  • Hive窗口函数入门使用

    2020-07-25 10:39:17
    查询结果如下: 2、查询在2020年4月份购买过的顾客及总人数 先找出四月份购买过的用户,这里用到了substring()函数,然后再使用窗口函数,统计总人数用count() with a as (select distinct name from tb_orders ...
  • 文章目录over子句介绍1.... over子句默认值4.1 order by4.2 partition by4.3 partition by + order by几个常用开窗函数案例引出row_numberrankdense_rank案例答案 over子句介绍 over子句参考链接 https://blog.csdn....
  • 一篇搞定hive窗口函数

    2020-06-15 21:07:56
    分析函数 over(partition by分组列 order by排序列rows between 开始位置 and 结束位置) 常用分析函数: 聚合类 avg()、sum()、max()、min() 排名类 row_number() 按照值排序时产生一个自增编号,不会...
  • 前言:要先了解窗口函数的结构,over()才是窗口函数,而avg()、sum()、max()、min()等是与over()分析函数 一,hive窗口函数语法 首先,avg()、sum()、max()、min()等是分析函数,而over()才是窗口函数 ...
  • select city,year,taxes, sum(money) over() as sample1,--所有行相加 sum(money) over(partition by city) as sample2,--按city分组,组内数据相加 sum(money) over(partition by city order by year) as ...
  • 目录 窗口函数 排序 聚合 分析 窗口定义 窗口函数 窗口函数是一组特殊的函数,通过扫描多个输入行来计算输出值,为每行数据生成一个结果,我们可以通过窗口函数实现复杂的计算和聚合。 语法格式: Function (arg1,…...
  • hive窗口函数必备宝典

    万次阅读 多人点赞 2018-11-06 17:24:40
    1.Row_Number,Rank,Dense_Rank 这三个窗口函数的使用场景非常多   row_number():从1开始,按照顺序,生成分组内记录的序列,row_number()的值不会存在重复,当排序的值相同时,按照表中记录的顺序进行...
  • hive窗口函数实战(2)

    2020-04-08 17:50:26
    1.什么是窗口函数 在明白窗口函数的用途之前,我们先稍微提一下聚合函数,比如sum, count等常用的聚合函数,作用是将一列中多行的值合并为一行。与之对应的是,窗口函数完成的功能是本行内运算,从而多行的运算结果...
  • hive窗口函数.docx

    2021-07-08 13:26:13
    数据蛙hive窗口函数 - 精心总结
  • Hive窗口函数常用记录

    2021-01-04 16:40:59
    窗口函数通常是对组内数据进行处理,所以一定需要分组操作(partition by) 通常的语法是 select WINDOW(a.val)over(PARTITION by a.col1 order by a.col2 asc [rows BETWEEN xxx PRECEDING and CURRENT ROW]) from a...
  • - 目录 1、聚合函数+over 2、partition by子句 3、order by子句 4、★window子句(里面包含) - PRECEDING:往前 - ...hive中的窗口函数和sql中的窗口函数相类似,都是用来做一些数据分析类的工作,一般用于ola.
  • 一、Hive 窗口函数(OLAP函数\分析函数) 窗口函数最重要的关键字是 partition by 和 order by 具体用法如下:over (parttion by xxx order by xxx) 1、SUM、AVG、MIN、MAX 数据: cookie1,2018-04-10,1 ...
  • Hive窗口函数之preceding and following

    千次阅读 2020-04-07 18:33:29
    Hive窗口函数中,有一个功能是统计当前行之前或之后指定行作为一个聚合,关键字是 preceding 和 following,举例说明其使用方法。 一、加载测试数据 在 hive 环境中创建临时表: create table tmp_student ( name ...
  • 窗口函数是一种不需要使用group by也能进行聚合计算的强大分析函数 以下是经过自己使用的一些感悟: 1),窗口函数不是某个函数,它是 “一批” 的函数的总称 2),窗口函数适用于增量写入的计算场景 3),窗口函数...
  • 窗口函数over,其内部分为partition by 和order by 以及windos子句这三个自居; 而外部分别和序列函数以及聚合函数相组合。 Over 子句 Over中有partition by 和order by 以及windos字句 Patition by 是用来分组...
  • Hive窗口函数Over和排序函数Rank

    千次阅读 2018-08-13 20:33:39
    本文主要介绍hive中的窗口函数.hive中的窗口函数和sql中的窗口函数相类似,都是用来做一些数据分析类的工作,一般用于olap分析 概念 我们都知道在sql中有一类函数叫做聚合函数,例如sum()、avg()、max()等等,这类...
  • hive窗口函数(over)详解

    千次阅读 2019-08-01 11:25:26
    hive窗口函数: 一.函数说明: OVER():指定分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变而变化 CURRENT ROW:当前行 n PRECEDING:往前n行数据 n FOLLOWING:往后n行数据 UNBOUNDED:起点,UNBOUNDED ...
  • Hive】(九)Hive 窗口函数总结

    千次阅读 2019-12-16 21:25:22
    文章目录一、简介二、概念三、数据准备四、聚合函数+over()五、partition by...本文主要介绍Hive中的窗口函数Hive中的窗口函数和SQL中的窗口函数相类似,都是用来做一些数据分析类的工作,一般用于olap分析(在线分...
  • hive窗口函数+分位数

    2019-09-19 11:03:57
    original_id, stage hive 计算千分位数: percentile函数和percentile_approx函数: (percentile要求输入的字段必须是int类型的,而percentile_approx则是数值类似型的都可以 ) 其使用方式为percentile(col, p)、...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 9,989
精华内容 3,995
关键字:

hive窗口函数