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

    2020-03-17 17:56:19
    1.rank():相同排序跳序号 2.row_number():不存在重复序号 3.dense_rank():相同排序不跳序号(允许并排次序) 4.partition by:查询分区子句 ...窗口函数语法: 函数() over(partition by 列1,列2,orde...

    1.rank():相同排序跳序号
    2.row_number():不存在重复序号
    3.dense_rank():相同排序不跳序号(允许并排次序)
    4.partition by:查询分区子句
    5.order by:排序,默认升序
    6.lead(),lag():可以在当前行访问上N行(lag)或者下N行(lead)的数据
    窗口函数语法:

    函数() over(partition by 列1,列2,order by 列3,窗口子句) as 别名
    
    展开全文
  • SQL的窗口函数-简单易上手窗口函数的概念及作用举个例子:查看每一个班级的总分数。窗口函数语法(很简单哦)常用的窗口函数(也很简单哦)1.排序函数2.偏移函数3.聚合函数4.分布函数 窗口函数的概念及作用 窗口函数...

    窗口函数有啥用?

    窗口函数是分析函数,聚合函数是统计函数。
    普通常用的聚合函数只能返回一个值,而窗口函数每一行都能返回一个值。

    举个例子:查看每一个班级的总分数。

    首先表结构大概为这个样子:在这里插入图片描述

    普通的聚合函数实现为:
    在这里插入图片描述
    而窗口函数的实现为:
    在这里插入图片描述
    在这里插入图片描述
    通过这两种情况的比较,可以看出来:

    1. 普通的聚合函数侧重于统计出结果,假如你想分析一下前30名同学的总成绩的话是没有办法实现的,所以说普通函数最后生成一个值,也就是按照班级进行分组后取第一行。

    2. 聚合函数侧重于对过程的分析,对总成绩的变化分析等等,也就是每一行会生成一个值,在这里就是对总成绩的累加。

    窗口函数怎样写?(很简单哦)

    窗口函数+over(分区+排序+范围)
    例如:rank() over(partition by 班级 order by 合计) as 排名
    解释:首先是你需要用的窗口函数,其次在over子句中进行动态的分区或者排序(分区就相当于group by),所以窗口函数由三部分组成:分区,排序和范围。
    注意:分区、排序和范围为可选项,可以按照需求选择,而over子句一定要有,所以rank() over()也是正确的,对范围rows的介绍在聚合窗口函数部分着重介绍,像rank()这类的函数基本上不会用到rows,所以默认是表头到表尾,即整张表。

    窗口函数有哪些?(也很简单哦)

    1.排序函数

    排序函数分为四种排序函数,分别是row_number()、rank()、dense_rank()、ntile()。
    注意:over()里面的分组以及排序的执行晚于 where 、group by、 order by 的执行,同时要注意group by 只返回查询到的第一行,关于group by 和order by 一起使用的注意事项我会另外谈

    • row_number()
      在这里插入图片描述
      很容易看出规律吧,就是一直按照递增的顺序排下去。

    • rank()
      在这里插入图片描述
      从上图可以看出rank()函数并不是连续递增的,相同成绩的排名是一样的。

    • dense_rank()
      在这里插入图片描述
      从上图可以看出dense_rank()函数的排名是连续递增的,同时相同成绩的排名是一样的。

    • dense_rank()
      在这里插入图片描述
      在这里插入图片描述
      从上面结果可以看出ntile()是将数据平均分成n个组,由于在over()子句中使用了分区,所以先分区,然后将每一个分区中的数据大致分成n个组(这个n个组是抽象的概念,是指窗口函数生成的数字,而paitition by是真正的进行分组处理,这里要清楚)

    2.偏移函数
    • first_value()和last_value()
      在这里插入图片描述

    从图中可以看出来,first_value()的结果值是不变的,为什么不变呢,因为结果集是遍历表的过程中一行一行添加的,所以第一个值是不变的,因为后面遍历的结果都是添加在第一行的下面的。但是last_value()的结果值就不同了,遍历的过程中每次添加一行,这一行就是最后一行,所以last_value表示的就是当前行的合计成绩,因为当前行就是当前结果集的最后一行。(从这一点也可以看出窗口函数是注重过程的,过程都会记录,方便分析使用,同时也能看出over子句动态分区排序的特点)

    • lead()和lag()
      在这里插入图片描述
      lead表示向下取值,参数表示向下取值的行数,lag表示向上取值。向上或者向下取值没有的话就是null。
    3.聚合函数
    大家对over子句中的partition by 和order by 都有了解了吧,在介绍聚合函数			前,先说一下语法的最后一个部分-rows。
    格式为rows between···and···,也就是取一个范围,也就是所说的窗口。
    当前行-current row
    之前的行-preceding
    之后的行-following
    无界限-unbounded
    表示从前面的起点-unbounded preceding
    表示到后面的终点-unbounded following
    不理解的话没关系,举几个例子就容易理解了。
    取当前行和前五行:rows between 5 preceding and current row
    取当前行和后五行:rows between current row and 5 following
    取前五行和后五行:rows between 5 preceding and 5 folowing
    	注意:在前面的行要写在前面,假如取当前行和前五行改一下:rows between current row and  5 preceding 这样写就是错误的。
    下面介绍聚合函数。
    
    • avg()
      计算本行以及前后相邻两行得合计成绩的平均值
      在这里插入图片描述

    • count()

    • 按照班级进行分组,统计并列出每个班级中合计成绩>80的人数

    • 在这里插入图片描述

    • sun()

    • 按照班级分组,按照学号排序对学生的合计成绩进行累计求和
      (还是那句话,窗口函数是侧重过程的,侧重分析的,所以是对合计成绩的累加)在这里插入图片描述

    • max(),min()
      按照班级进行分组,统计每个班级的最高分和最低分
      (注意:求min最小值时不能desc逆序排序,因为窗口函数是注重过程的,当逆序排序的时候,会遍历表,判断当前行的合计成绩是不是当前行及之前行这个范围内的最小值,如果不是,那上一行一定是,拿过来,如果是,那就成为最小值填入当前行)
      在这里插入图片描述

    4.分布函数
    • CUME_DIST函数
      累计分布函数:小于等于当前值的行数/分组内总行数(注意asc和desc)
      合计成绩大于等于当前成绩的人数占总学生人数的比例(注意,一定是大于等于,大于等于,光大于也不行)
      在这里插入图片描述

    • percent_rank函数
      当前行-1/总行数-1
      其实可以这样理解:从名字就可以看出是百分比排名函数,比如说你的成绩在你们班能排名百分之多少?(我怎么感觉跟cume_dist没什么区别)
      在这里插入图片描述

    • 最后,窗口函数基本上介绍完了,如有错误,欢迎指正!

    展开全文
  • Hive SQL 中有很多窗口函数值得我们在平时数据开发处理中好好使用。通常包含排序类、聚合类、累计计算,等。在数据开发 此篇就简单罗列一些窗口函数的SQL例子,做一个复习回顾。 专用窗口函数 rank,dense_...

    Hive SQL 中有很多窗口函数值得我们在平时的数据开发处理中好好使用。通常包含排序类、聚合类、累计计算,等。在数据开发的

    此篇就简单罗列一些窗口函数的SQL例子,做一个复习回顾。

    专用窗口函数

    rank,dense_rank, row_number, ntile,等。
    RANK() 生成数据项在分组中的排名,排名相等会在名次中留下空位
    DENSE_RANK() 生成数据项在分组中的排名,排名相等会在名次中不会留下空位

    select 
        *
        ,rank() over(partition by `班级` order by `成绩` desc) as `班级排名`
        ,rank() over (order by `成绩` desc) as ranking -- 相同分数,相同排名,接下来的跳过+n排名(不连续排名)
        ,dense_rank() over (order by `成绩` desc) as dese_ranking -- 相同分数,相同排名,接下来的继续+1排名(连续排名)
        ,row_number() over (order by `成绩` desc) as row_num -- 按成绩倒序,编号(排序)
    from database_name.table_a;


    聚合函数作为窗口函数

    假设:按照学生学号 student_no(每个学生有唯一学号),统计每个学生的 总分、平均分、目前考试科目、最高分、最低分。

    select
        *
        ,sum(score) over (partition by student_no) as current_sum -- 每个学生总成绩
        ,avg(score) over (partition by student_no) as current_avg  -- 平均分
        ,count(score) over (partition by student_no) as current_cnt
        ,max(score) over (partition by student_no) as current_max
        ,min(score) over (partition by student_no) as current_min
    from database_name.table_a;

    按照学号升序排序,对成绩score统计一些累计指标。

    select
        *
        ,sum(score) over (order by student_no) as current_sum -- 累计求和
        ,avg(score) over (order by student_no) as current_avg
        ,count(score) over (order by student_no) as current_cnt
        ,max(score) over (order by student_no) as current_max
        ,min(score) over (order by student_no) as current_min
    from database_name.table_a;

    按照 成绩 倒序排序,rank()是连续排名, 1, 2, 3, 4,。。。遇到相同分数,继续排序下去。

    select
        *
        ,rank() over(order by `成绩` desc) as ranking
    from database_name.table_a;

    对数据进行偏移计算

    主要考虑关键词: rows n preceding, rows n following, rows between [n|unbounded] preceding and [n|current|unbounded] following。或者 lag(offset) over(partition by  order by ),lead(offset) over(partition by  order by )。

    指定最靠近的3行作为汇总对象: rows n preceding:包含本条记录的前n行记录

    select 
        *
        ,avg(sale_price) over(order by product_id rows 2 preceding) as moving_avg -- 移动平均
    from database_name.table_a;

     指定包含本条记录,前1行,后1行 记录的 sale_price平均值

    select
        *
        ,avg(sale_price) over(order by product_id rows 1 preceding and 1 following) as moving_avg -- 移动平均
    from database_name.table_a;
    SELECT 
    cookieid,
    createtime,
    pv,
    SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime) AS pv1, -- 默认为从起点到当前行
    SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pv2, -- 从起点到当前行,结果同pv1 
    SUM(pv) OVER(PARTITION BY cookieid) AS pv3,                                -- 分组内所有行
    SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pv4,   -- 当前行+往前3行
    SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS pv5,    -- 当前行+往前3行+往后1行
    SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS pv6   -- 当前行+往后所有行  
    FROM win1 
    order by createtime;

    对数据的偏移统计,还可以用到

    LAG(expr, offset, default_value) over([partition by col1] [order by col2] [desc|asc])  ,表示向前偏移offset行记录,按照over后面的条件,进行expr统计,如果偏移offset后越界,可以是null, 也可以是指定的default_value。

    LEAD(expr, offset, default_value) over([partition by col1] [order by col2] [desc|asc])  ,表示向后偏移offset行记录,按照over后面的条件,进行expr统计,如果偏移offset后越界,可以是null, 也可以是指定的default_value。

    LAG(字段,n,为空时的默认值)向上n行

    SELECT 
        cookieid,
        createtime,
        url,
        ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
        LAG(createtime, 1, '1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY createtime) AS last_1_time,
        LAG(createtime, 2) OVER(PARTITION BY cookieid ORDER BY createtime) AS last_2_time 
    FROM win4;

    LEAD(字段,n,为空时的默认值)向下n行

    SELECT 
        cookieid,
        createtime,
        url,
        ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
        LEAD(createtime,1,'1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY createtime) AS next_1_time,
        LEAD(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS next_2_time 
    FROM win4;
    select
    	seq,
    	LAG(seq+100, 1, -1) over (partition by window order by seq) as r1
    from sliding_window;
    -- 
    select 
    	c_Double_a,c_String_b,c_int_a,
    	lead(c_int_a, 1) over(partition by c_Double_a order by c_String_b) as r2
    from dual;
    
    select 
    	c_String_a,c_time_b,c_Double_a,
    	lead(c_Double_a,1) over(partition by c_String_a order by c_time_b) as r3
    from dual;
    
    select 
    	c_String_in_fact_num,c_String_a,c_int_a,
    	lead(c_int_a) over(partition by c_String_in_fact_num order by c_String_a) as r4
    from dual;

    分片函数

    NTILE分片函数,随机分配n个编号给相应的分组。

    ntile 用于将分组数据按照顺序切分成n片,并返回当前切片值。如果切片不均匀,默认增加第一个切片的分布。

    SELECT 
        cookieid,
        createtime,
        pv,
        NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn1,    -- 分组内将数据分成2片
        NTILE(3) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn2,  -- 分组内将数据分成3片
        NTILE(4) OVER(ORDER BY createtime) AS rn3        -- 将所有数据分成4片
    FROM win2 
    ORDER BY 
        cookieid,
        createtime;
    -- 现在需要将所有职工根据部门按sal高到低切分为3组,并获得职工自己组内的序号。
    select
    	deptno
    	,ename
    	,sal
    	,ntile(3)over(partition by deptno order by sal desc) as nt3
    from emp;

    PERCENT_RANK 返回位置百分数,按照rank进行排序之后,找出当前位置的rank所在的位置百分数

    SELECT 
    dept,
    userid,
    sal,
    PERCENT_RANK() OVER(ORDER BY sal) AS rn1,   -- 分组内
    RANK() OVER(ORDER BY sal) AS rn11,          -- 分组内RANK值
    SUM(1) OVER(PARTITION BY NULL) AS rn12,     -- 分组内总行数
    PERCENT_RANK() OVER(PARTITION BY dept ORDER BY sal) AS rn2 
    FROM win3;

    FIRST_VALUE()取分组后截止到当前行相应字段的第一个值

    SELECT cookieid,
        createtime,
        url,
        ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
        FIRST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS first1,
        LAST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime DESC)  AS first2,
        LAST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS last1
    FROM lxw1234;

    NTH_VALUE 用于计算第n个值。如果n超过窗口的最大长度,返回NULL。

    select 
        user_id, 
        price, 
        nth_value(price, 2) over(partition by user_id) as nth_value 
    from test_src;

    CLUSTER_SAMPLE 分组抽样

    -- 从每组中抽取10%的样本
    select
        key, value
    from (
        select
            key, value,
            cluster_sample(10, 1) over(partition by key) as flag
        from tbl
        ) sub
    where flag=true;

    CUME_DIST 累计分布

    -- 现在需要将所有职工根据部门分组,再求sal在同一组内的前百分之几。
    select
        deptno
        ,ename
        ,sal
        ,concat(round(cume_dist(sal) over(partition by deptno order by sal desc)*100, 2), '%') as cume_dist
    from emp;

    此外,还有一些针对字符串的常用操作,具体可以参考阿里云Maxcompute文档。

    WM_CONCAT 用指定的separator做分隔符,链接str中的值。

    -- 对表进行分组排序后合并: 对表test按照id列进行分组排序,并将同组的内容进行合并。   
    SELECT 
        id,
        WM_CONCAT('',alphabet) as res
    FROM test 
    GROUP BY 
        id 
    ORDER BY 
        id 
    LIMIT 100;

    -- collect_list(col) 在给定Group内,将col指定的表达式聚合为一个数组。

    需要对应的配置

    set project odps.sql.type.system.odps2=true; 
    或
    set odps.sql.type.system.odps2=true;

    COLLECT_SET(col) 在给定Group内,将col指定的表达式聚合为一个无重复元素的集合数组,输出类型是set(集合)。

    以下为一些常用的统计方差函数

    -- variance/var_pop(col) 计算 col 列方差
    -- var_samp 用于计算指定数字列的样本方差。
    -- covar_pop 用于计算指定两个数字列的总体协方差。
    -- COVAR_SAMP 用于计算指定两个数字列的样本协方差。

    PERCENTILE 返回指定列精确的第p位百分数。p必须在0和1之间。
    -- DOUBLE PERCENTILE(BIGINT col, p)

    SELECT 
        PERCENTILE(c1,0),
        PERCENTILE(c1,0.3),
        PERCENTILE(c1,0.5),
        PERCENTILE(c1,1) 
    FROM var_test;

    以上就是复习常用的窗口函数和一些字符串统计函数。还有比较实用的是 行转列(一行转多行)、列转行等数据处理,可以参考explode、lateral view 关键词,及 explode 与 split、lateral view 等 并用。

    以下图为 表 table_explode_lateral_view 的记录。

    需求1: 只拆解 goods_id 字段

    select 
        explode(split(goods_id, ',')) as new_goods_id 
    from table_explode_lateral_view;

    需求2: 只拆解area字段

    select 
        explode(split(area, ',')) as new_area
    from table_explode_lateral_view;

    LATERAL VIEW的使用:

    侧视图的意义是配合explode(或者其他的UDTF),一个语句生成把单行数据拆解成多行后的数据结果集。

    select 
        goods_id2,
        sale_info 
    from table_explode_lateral_view 
    LATERAL VIEW explode(split(goods_id,','))goods as goods_id2;

    其中LATERAL VIEW explode(split(goods_id,',')) goods相当于一个虚拟表,与原表explode_lateral_view笛卡尔积关联。

    也可以多重使用

    select 
        goods_id2,
        sale_info,
        area2
    from table_explode_lateral_view 
    LATERAL VIEW explode(split(goods_id,',')) goods as goods_id2 
    LATERAL VIEW explode(split(area,',')) area as area2;

    也是三个表笛卡尔积的结果。

    需求3: 解析以上sale_info成二维表。

    select 
        get_json_object(concat('{',sale_info_1,'}'),'$.source') as source,
        get_json_object(concat('{',sale_info_1,'}'),'$.monthSales') as monthSales,
        get_json_object(concat('{',sale_info_1,'}'),'$.userCount') as monthSales,
        get_json_object(concat('{',sale_info_1,'}'),'$.score') as monthSales
    from table_explode_lateral_view
    LATERAL VIEW explode(split(regexp_replace(regexp_replace(sale_info,'\\[\\{',''),'}]',''),'},\\{')) sale_info as sale_info_1;

    也可以使用json_tuple对一个json字段/或者数组,同时对多个key进行解析。

    select 
        sale_info_1
    from table_explode_lateral_view
    lateral view json_tuple(sale_info,'source','monthSales','userCount', 'score') sale_info_new as sale_info_1;

    更多细节,还需要根据实际情况进行处理。

    展开全文
  • Hive和SQL的窗口函数

    千次阅读 2018-05-30 11:42:20
    分析窗口函数应用场景:(1.2重要,其他了解就行)(1)用于分区排序(2)动态Group By(3)Top N(4)累计计算(5)层次查询Hive分析窗口函数(一) SUM,AVG,MIN,MAXHive中提供了越来越多分析函数,用于完成负责...

    分析窗口函数应用场景:(1.2重要,其他的了解就行)

    (1)用于分区排序

    (2)动态Group By

    (3)Top N

    (4)累计计算

    (5)层次查询


    Hive分析窗口函数(一) SUM,AVG,MIN,MAX

    Hive中提供了越来越多的分析函数,用于完成负责的统计分析。抽时间将所有的分析窗口函数理一遍,将陆续发布。

    今天先看几个基础的,SUM、AVG、MIN、MAX。

    用于实现分组内所有和连续累积的统计。

    数据准备:

    1. CREATE EXTERNAL TABLE lxw1234 (  
    2. cookieid string,  
    3. createtime string,   --day   
    4. pv INT  
    5. ) ROW FORMAT DELIMITED   
    6. FIELDS TERMINATED BY ','   
    7. stored as textfile location '/tmp/lxw11/';  
    8.    
    9. DESC lxw1234;  
    10. cookieid                STRING   
    11. createtime              STRING   
    12. pv INT   
    13.    
    14. hive> select * from lxw1234;  
    15. OK  
    16. cookie1 2015-04-10      1  
    17. cookie1 2015-04-11      5  
    18. cookie1 2015-04-12      7  
    19. cookie1 2015-04-13      3  
    20. cookie1 2015-04-14      2  
    21. cookie1 2015-04-15      4  
    22. cookie1 2015-04-16      4  

    SUM — 注意,结果和ORDER BY相关,默认为升序

    1. SELECT cookieid,  
    2. createtime,  
    3. pv,  
    4. SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime) AS pv1, -- 默认为从起点到当前行  
    5. SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pv2, --从起点到当前行,结果同pv1   
    6. SUM(pv) OVER(PARTITION BY cookieid) AS pv3, --分组内所有行  
    7. SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pv4,  --当前行+往前3行  
    8. SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS pv5,  --当前行+往前3行+往后1行  
    9. SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS pv6  ---当前行+往后所有行    
    10. FROM lxw1234;  
    11.    
    12. cookieid createtime     pv      pv1     pv2     pv3     pv4     pv5      pv6   
    13. -----------------------------------------------------------------------------  
    14. cookie1  2015-04-10      1       1       1       26      1       6       26  
    15. cookie1  2015-04-11      5       6       6       26      6       13      25  
    16. cookie1  2015-04-12      7       13      13      26      13      16      20  
    17. cookie1  2015-04-13      3       16      16      26      16      18      13  
    18. cookie1  2015-04-14      2       18      18      26      17      21      10  
    19. cookie1  2015-04-15      4       22      22      26      16      20      8  
    20. cookie1  2015-04-16      4       26      26      26      13      13      4  

    pv1: 分组内从起点到当前行的pv累积,如,11号的pv1=10号的pv+11号的pv, 12号=10号+11号+12号
    pv2: 同pv1
    pv3: 分组内(cookie1)所有的pv累加
    pv4: 分组内当前行+往前3行,如,11号=10号+11号, 12号=10号+11号+12号, 13号=10号+11号+12号+13号, 14号=11号+12号+13号+14号
    pv5: 分组内当前行+往前3行+往后1行,如,14号=11号+12号+13号+14号+15号=5+7+3+2+4=21
    pv6: 分组内当前行+往后所有行,如,13号=13号+14号+15号+16号=3+2+4+4=13,14号=14号+15号+16号=2+4+4=10

     

    总结:窗口帧(又叫window子句) 用于从分区中选择指定的多条记录,供窗口函数处理。Hive 提供了两种定义窗口帧的形式:ROWS 和 RANGE

    如果不指定ROWS BETWEEN(window子句),也没指定order by 子句,分组内所有值累加;

    如果不指定ROWS BETWEEN(window子句),但指定order by 子句,默认为从起点到当前行;

    如果指定ROWS BETWEEN(window子句), 没指定order by 自己,当然是按照rows between执行;

    关键是理解ROWS BETWEEN含义,也叫做WINDOW子句
    PRECEDING:往前
    FOLLOWING:往后
    CURRENT ROW:当前行
    UNBOUNDED:起点,UNBOUNDED PRECEDING 表示从前面的起点, UNBOUNDED FOLLOWING:表示到后面的终点

    –其他AVG,MIN,MAX,和SUM用法一样。

    1. --AVG  
    2. SELECT cookieid,  
    3. createtime,  
    4. pv,  
    5. AVG(pv) OVER(PARTITION BY cookieid ORDER BY createtime) AS pv1, -- 默认为从起点到当前行  
    6. AVG(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pv2, --从起点到当前行,结果同pv1   
    7. AVG(pv) OVER(PARTITION BY cookieid) AS pv3, --分组内所有行  
    8. AVG(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pv4, --当前行+往前3行  
    9. AVG(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS pv5, --当前行+往前3行+往后1行  
    10. AVG(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS pv6  ---当前行+往后所有行    
    11. FROM lxw1234;   
    12. cookieid createtime     pv      pv1     pv2     pv3     pv4     pv5      pv6   
    13. -----------------------------------------------------------------------------  
    14. cookie1 2015-04-10      1       1.0     1.0     3.7142857142857144      1.0     3.0     3.7142857142857144  
    15. cookie1 2015-04-11      5       3.0     3.0     3.7142857142857144      3.0     4.333333333333333       4.166666666666667  
    16. cookie1 2015-04-12      7       4.333333333333333       4.333333333333333       3.7142857142857144      4.333333333333333       4.0     4.0  
    17. cookie1 2015-04-13      3       4.0     4.0     3.7142857142857144      4.0     3.6     3.25  
    18. cookie1 2015-04-14      2       3.6     3.6     3.7142857142857144      4.25    4.2     3.3333333333333335  
    19. cookie1 2015-04-15      4       3.6666666666666665      3.6666666666666665      3.7142857142857144      4.0     4.0     4.0  
    20. cookie1 2015-04-16      4       3.7142857142857144      3.7142857142857144      3.7142857142857144      3.25    3.25    4.0  
    1. --MIN  
    2. SELECT cookieid,  
    3. createtime,  
    4. pv,  
    5. MIN(pv) OVER(PARTITION BY cookieid ORDER BY createtime) AS pv1, -- 默认为从起点到当前行  
    6. MIN(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pv2, --从起点到当前行,结果同pv1   
    7. MIN(pv) OVER(PARTITION BY cookieid) AS pv3,  --分组内所有行  
    8. MIN(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pv4,  --当前行+往前3行  
    9. MIN(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS pv5,  --当前行+往前3行+往后1行  
    10. MIN(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS pv6  ---当前行+往后所有行    
    11. FROM lxw1234;  
    12.    
    13. cookieid createtime     pv      pv1     pv2     pv3     pv4     pv5      pv6   
    14. -----------------------------------------------------------------------------  
    15. cookie1 2015-04-10      1       1       1       1       1       1       1  
    16. cookie1 2015-04-11      5       1       1       1       1       1       2  
    17. cookie1 2015-04-12      7       1       1       1       1       1       2  
    18. cookie1 2015-04-13      3       1       1       1       1       1       2  
    19. cookie1 2015-04-14      2       1       1       1       2       2       2  
    20. cookie1 2015-04-15      4       1       1       1       2       2       4  
    21. cookie1 2015-04-16      4       1       1       1       2       2       4  
    1. --MAX  
    2. SELECT cookieid,  
    3. createtime,  
    4. pv,  
    5. MAX(pv) OVER(PARTITION BY cookieid ORDER BY createtime) AS pv1, -- 默认为从起点到当前行  
    6. MAX(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pv2, --从起点到当前行,结果同pv1   
    7. MAX(pv) OVER(PARTITION BY cookieid) AS pv3, --分组内所有行  
    8. MAX(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pv4, --当前行+往前3行  
    9. MAX(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS pv5, --当前行+往前3行+往后1行  
    10. MAX(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS pv6  ---当前行+往后所有行    
    11. FROM lxw1234;  
    12.    
    13. cookieid createtime     pv      pv1     pv2     pv3     pv4     pv5      pv6   
    14. -----------------------------------------------------------------------------  
    15. cookie1 2015-04-10      1       1       1       7       1       5       7  
    16. cookie1 2015-04-11      5       5       5       7       5       7       7  
    17. cookie1 2015-04-12      7       7       7       7       7       7       7  
    18. cookie1 2015-04-13      3       7       7       7       7       7       4  
    19. cookie1 2015-04-14      2       7       7       7       7       7       4  
    20. cookie1 2015-04-15      4       7       7       7       7       7       4  
    21. cookie1 2015-04-16      4       7       7       7       4       4       4  

    Hive分析窗口函数(二) NTILE,ROW_NUMBER,RANK,DENSE_RANK

    本文中介绍前几个序列函数,NTILE,ROW_NUMBER,RANK,DENSE_RANK,下面会一一解释各自的用途。

    注意: 序列函数不支持WINDOW子句。(什么是WINDOW子句,点此查看前面的文章

    数据准备:

    1. CREATE EXTERNAL TABLE lxw1234 (  
    2. cookieid string,  
    3. createtime string,   --day   
    4. pv INT  
    5. ) ROW FORMAT DELIMITED   
    6. FIELDS TERMINATED BY ','   
    7. stored as textfile location '/tmp/lxw11/';  
    8.    
    9. DESC lxw1234;  
    10. cookieid                STRING   
    11. createtime              STRING   
    12. pv INT   
    13.    
    14. hive> select * from lxw1234;  
    15. OK  
    16. cookie1 2015-04-10      1  
    17. cookie1 2015-04-11      5  
    18. cookie1 2015-04-12      7  
    19. cookie1 2015-04-13      3  
    20. cookie1 2015-04-14      2  
    21. cookie1 2015-04-15      4  
    22. cookie1 2015-04-16      4  
    23. cookie2 2015-04-10      2  
    24. cookie2 2015-04-11      3  
    25. cookie2 2015-04-12      5  
    26. cookie2 2015-04-13      6  
    27. cookie2 2015-04-14      3  
    28. cookie2 2015-04-15      9  
    29. cookie2 2015-04-16      7  

    NTILE

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

    1. SELECT   
    2. cookieid,  
    3. createtime,  
    4. pv,  
    5. NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn1,    --分组内将数据分成2片  
    6. NTILE(3) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn2,  --分组内将数据分成3片  
    7. NTILE(4) OVER(ORDER BY createtime) AS rn3        --将所有数据分成4片  
    8. FROM lxw1234   
    9. ORDER BY cookieid,createtime;  
    10.    
    11. cookieid day           pv       rn1     rn2     rn3  
    12. -------------------------------------------------  
    13. cookie1 2015-04-10      1       1       1       1  
    14. cookie1 2015-04-11      5       1       1       1  
    15. cookie1 2015-04-12      7       1       1       2  
    16. cookie1 2015-04-13      3       1       2       2  
    17. cookie1 2015-04-14      2       2       2       3  
    18. cookie1 2015-04-15      4       2       3       3  
    19. cookie1 2015-04-16      4       2       3       4  
    20. cookie2 2015-04-10      2       1       1       1  
    21. cookie2 2015-04-11      3       1       1       1  
    22. cookie2 2015-04-12      5       1       1       2  
    23. cookie2 2015-04-13      6       1       2       2  
    24. cookie2 2015-04-14      3       2       2       3  
    25. cookie2 2015-04-15      9       2       3       4  
    26. cookie2 2015-04-16      7       2       3       4  

    –比如,统计一个cookie,pv数最多的前1/3的天

    1. SELECT   
    2. cookieid,  
    3. createtime,  
    4. pv,  
    5. NTILE(3) OVER(PARTITION BY cookieid ORDER BY pv DESCAS rn   
    6. FROM lxw1234;  
    7.    
    8. --rn = 1 的记录,就是我们想要的结果  
    9.    
    10. cookieid day           pv       rn  
    11. ----------------------------------  
    12. cookie1 2015-04-12      7       1  
    13. cookie1 2015-04-11      5       1  
    14. cookie1 2015-04-15      4       1  
    15. cookie1 2015-04-16      4       2  
    16. cookie1 2015-04-13      3       2  
    17. cookie1 2015-04-14      2       3  
    18. cookie1 2015-04-10      1       3  
    19. cookie2 2015-04-15      9       1  
    20. cookie2 2015-04-16      7       1  
    21. cookie2 2015-04-13      6       1  
    22. cookie2 2015-04-12      5       2  
    23. cookie2 2015-04-14      3       2  
    24. cookie2 2015-04-11      3       3  
    25. cookie2 2015-04-10      2       3  

    ROW_NUMBER

    ROW_NUMBER() –从1开始,按照顺序,生成分组内记录的序列
    –比如,按照pv降序排列,生成分组内每天的pv名次
    ROW_NUMBER() 的应用场景非常多,再比如,获取分组内排序第一的记录;获取一个session中的第一条refer等。

    1. SELECT   
    2. cookieid,  
    3. createtime,  
    4. pv,  
    5. ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv descAS rn   
    6. FROM lxw1234;  
    7.    
    8. cookieid day           pv       rn  
    9. -------------------------------------------   
    10. cookie1 2015-04-12      7       1  
    11. cookie1 2015-04-11      5       2  
    12. cookie1 2015-04-15      4       3  
    13. cookie1 2015-04-16      4       4  
    14. cookie1 2015-04-13      3       5  
    15. cookie1 2015-04-14      2       6  
    16. cookie1 2015-04-10      1       7  
    17. cookie2 2015-04-15      9       1  
    18. cookie2 2015-04-16      7       2  
    19. cookie2 2015-04-13      6       3  
    20. cookie2 2015-04-12      5       4  
    21. cookie2 2015-04-14      3       5  
    22. cookie2 2015-04-11      3       6  
    23. cookie2 2015-04-10      2       7  

    RANK 和 DENSE_RANK

    —RANK() 生成数据项在分组中的排名,排名相等会在名次中留下空位
    —DENSE_RANK() 生成数据项在分组中的排名,排名相等会在名次中不会留下空位

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

    Hive分析窗口函数(三) CUME_DIST,PERCENT_RANK

    这两个序列分析函数不是很常用,这里也介绍一下。

    注意: 序列函数不支持WINDOW子句。(什么是WINDOW子句,点此查看前面的文章

    数据准备:

    1. CREATE EXTERNAL TABLE lxw1234 (  
    2. dept STRING,  
    3. userid string,  
    4. sal INT  
    5. ) ROW FORMAT DELIMITED   
    6. FIELDS TERMINATED BY ','   
    7. stored as textfile location '/tmp/lxw11/';  
    8.    
    9.    
    10. hive> select * from lxw1234;  
    11. OK  
    12. d1      user1   1000  
    13. d1      user2   2000  
    14. d1      user3   3000  
    15. d2      user4   4000  
    16. d2      user5   5000  

    CUME_DIST

    –CUME_DIST 小于等于当前值的行数/分组内总行数
    –比如,统计小于等于当前薪水的人数,所占总人数的比例

    1. SELECT   
    2. dept,  
    3. userid,  
    4. sal,  
    5. CUME_DIST() OVER(ORDER BY sal) AS rn1,  
    6. CUME_DIST() OVER(PARTITION BY dept ORDER BY sal) AS rn2   
    7. FROM lxw1234;  
    8.    
    9. dept    userid   sal   rn1       rn2   
    10. -------------------------------------------  
    11. d1      user1   1000    0.2     0.3333333333333333  
    12. d1      user2   2000    0.4     0.6666666666666666  
    13. d1      user3   3000    0.6     1.0  
    14. d2      user4   4000    0.8     0.5  
    15. d2      user5   5000    1.0     1.0  
    16.    
    17. rn1: 没有partition,所有数据均为1组,总行数为5,  
    18.      第一行:小于等于1000的行数为1,因此,1/5=0.2  
    19.      第三行:小于等于3000的行数为3,因此,3/5=0.6  
    20. rn2: 按照部门分组,dpet=d1的行数为3,  
    21.      第二行:小于等于2000的行数为2,因此,2/3=0.6666666666666666  

    PERCENT_RANK

    –PERCENT_RANK 分组内当前行的RANK值-1/分组内总行数-1
    应用场景不了解,可能在一些特殊算法的实现中可以用到吧。

    1. SELECT   
    2. dept,  
    3. userid,  
    4. sal,  
    5. PERCENT_RANK() OVER(ORDER BY sal) AS rn1,   --分组内  
    6. RANK() OVER(ORDER BY sal) AS rn11,          --分组内RANK值  
    7. SUM(1) OVER(PARTITION BY NULLAS rn12,     --分组内总行数  
    8. PERCENT_RANK() OVER(PARTITION BY dept ORDER BY sal) AS rn2   
    9. FROM lxw1234;  
    10.    
    11. dept    userid  sal     rn1    rn11     rn12    rn2  
    12. ---------------------------------------------------  
    13. d1      user1   1000    0.0     1       5       0.0  
    14. d1      user2   2000    0.25    2       5       0.5  
    15. d1      user3   3000    0.5     3       5       1.0  
    16. d2      user4   4000    0.75    4       5       0.0  
    17. d2      user5   5000    1.0     5       5       1.0  
    18.    
    19. rn1: rn1 = (rn11-1) / (rn12-1)   
    20.      第一行,(1-1)/(5-1)=0/4=0  
    21.      第二行,(2-1)/(5-1)=1/4=0.25  
    22.      第四行,(4-1)/(5-1)=3/4=0.75  
    23. rn2: 按照dept分组,  
    24.      dept=d1的总行数为3  
    25.      第一行,(1-1)/(3-1)=0  
    26.      第三行,(3-1)/(3-1)=1  

    Hive分析窗口函数(四) LAG,LEAD,FIRST_VALUE,LAST_VALUE

    继续学习这四个分析函数。

    注意: 这几个函数不支持WINDOW子句。(什么是WINDOW子句,点此查看前面的文章

    数据准备:

    1. CREATE EXTERNAL TABLE lxw1234 (  
    2. cookieid string,  
    3. createtime string,  --页面访问时间  
    4. url STRING       --被访问页面  
    5. ) ROW FORMAT DELIMITED   
    6. FIELDS TERMINATED BY ','   
    7. stored as textfile location '/tmp/lxw11/';  
    8.    
    9.    
    10. hive> select * from lxw1234;  
    11. OK  
    12. cookie1 2015-04-10 10:00:02     url2  
    13. cookie1 2015-04-10 10:00:00     url1  
    14. cookie1 2015-04-10 10:03:04     1url3  
    15. cookie1 2015-04-10 10:50:05     url6  
    16. cookie1 2015-04-10 11:00:00     url7  
    17. cookie1 2015-04-10 10:10:00     url4  
    18. cookie1 2015-04-10 10:50:01     url5  
    19. cookie2 2015-04-10 10:00:02     url22  
    20. cookie2 2015-04-10 10:00:00     url11  
    21. cookie2 2015-04-10 10:03:04     1url33  
    22. cookie2 2015-04-10 10:50:05     url66  
    23. cookie2 2015-04-10 11:00:00     url77  
    24. cookie2 2015-04-10 10:10:00     url44  
    25. cookie2 2015-04-10 10:50:01     url55  

    LAG

    LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值
    第一个参数为列名,第二个参数为往上第n行(可选,默认为1),第三个参数为默认值(当往上第n行为NULL时候,取默认值,如不指定,则为NULL)

    1. SELECT cookieid,  
    2. createtime,  
    3. url,  
    4. ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,  
    5. LAG(createtime,1,'1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY createtime) AS last_1_time,  
    6. LAG(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS last_2_time   
    7. FROM lxw1234;  
    8.    
    9.    
    10. cookieid createtime             url    rn       last_1_time             last_2_time  
    11. -------------------------------------------------------------------------------------------  
    12. cookie1 2015-04-10 10:00:00     url1    1       1970-01-01 00:00:00     NULL  
    13. cookie1 2015-04-10 10:00:02     url2    2       2015-04-10 10:00:00     NULL  
    14. cookie1 2015-04-10 10:03:04     1url3   3       2015-04-10 10:00:02     2015-04-10 10:00:00  
    15. cookie1 2015-04-10 10:10:00     url4    4       2015-04-10 10:03:04     2015-04-10 10:00:02  
    16. cookie1 2015-04-10 10:50:01     url5    5       2015-04-10 10:10:00     2015-04-10 10:03:04  
    17. cookie1 2015-04-10 10:50:05     url6    6       2015-04-10 10:50:01     2015-04-10 10:10:00  
    18. cookie1 2015-04-10 11:00:00     url7    7       2015-04-10 10:50:05     2015-04-10 10:50:01  
    19. cookie2 2015-04-10 10:00:00     url11   1       1970-01-01 00:00:00     NULL  
    20. cookie2 2015-04-10 10:00:02     url22   2       2015-04-10 10:00:00     NULL  
    21. cookie2 2015-04-10 10:03:04     1url33  3       2015-04-10 10:00:02     2015-04-10 10:00:00  
    22. cookie2 2015-04-10 10:10:00     url44   4       2015-04-10 10:03:04     2015-04-10 10:00:02  
    23. cookie2 2015-04-10 10:50:01     url55   5       2015-04-10 10:10:00     2015-04-10 10:03:04  
    24. cookie2 2015-04-10 10:50:05     url66   6       2015-04-10 10:50:01     2015-04-10 10:10:00  
    25. cookie2 2015-04-10 11:00:00     url77   7       2015-04-10 10:50:05     2015-04-10 10:50:01  
    26.    
    27.    
    28. last_1_time: 指定了往上第1行的值,default'1970-01-01 00:00:00'    
    29.              cookie1第一行,往上1行为NULL,因此取默认值 1970-01-01 00:00:00  
    30.              cookie1第三行,往上1行值为第二行值,2015-04-10 10:00:02  
    31.              cookie1第六行,往上1行值为第五行值,2015-04-10 10:50:01  
    32. last_2_time: 指定了往上第2行的值,为指定默认值  
    33.          cookie1第一行,往上2行为NULL  
    34.          cookie1第二行,往上2行为NULL  
    35.          cookie1第四行,往上2行为第二行值,2015-04-10 10:00:02  
    36.          cookie1第七行,往上2行为第五行值,2015-04-10 10:50:01  

    LEAD

    与LAG相反
    LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值
    第一个参数为列名,第二个参数为往下第n行(可选,默认为1),第三个参数为默认值(当往下第n行为NULL时候,取默认值,如不指定,则为NULL)

    1. SELECT cookieid,  
    2. createtime,  
    3. url,  
    4. ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,  
    5. LEAD(createtime,1,'1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY createtime) AS next_1_time,  
    6. LEAD(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS next_2_time   
    7. FROM lxw1234;  
    8.    
    9.    
    10. cookieid createtime             url    rn       next_1_time             next_2_time   
    11. -------------------------------------------------------------------------------------------  
    12. cookie1 2015-04-10 10:00:00     url1    1       2015-04-10 10:00:02     2015-04-10 10:03:04  
    13. cookie1 2015-04-10 10:00:02     url2    2       2015-04-10 10:03:04     2015-04-10 10:10:00  
    14. cookie1 2015-04-10 10:03:04     1url3   3       2015-04-10 10:10:00     2015-04-10 10:50:01  
    15. cookie1 2015-04-10 10:10:00     url4    4       2015-04-10 10:50:01     2015-04-10 10:50:05  
    16. cookie1 2015-04-10 10:50:01     url5    5       2015-04-10 10:50:05     2015-04-10 11:00:00  
    17. cookie1 2015-04-10 10:50:05     url6    6       2015-04-10 11:00:00     NULL  
    18. cookie1 2015-04-10 11:00:00     url7    7       1970-01-01 00:00:00     NULL  
    19. cookie2 2015-04-10 10:00:00     url11   1       2015-04-10 10:00:02     2015-04-10 10:03:04  
    20. cookie2 2015-04-10 10:00:02     url22   2       2015-04-10 10:03:04     2015-04-10 10:10:00  
    21. cookie2 2015-04-10 10:03:04     1url33  3       2015-04-10 10:10:00     2015-04-10 10:50:01  
    22. cookie2 2015-04-10 10:10:00     url44   4       2015-04-10 10:50:01     2015-04-10 10:50:05  
    23. cookie2 2015-04-10 10:50:01     url55   5       2015-04-10 10:50:05     2015-04-10 11:00:00  
    24. cookie2 2015-04-10 10:50:05     url66   6       2015-04-10 11:00:00     NULL  
    25. cookie2 2015-04-10 11:00:00     url77   7       1970-01-01 00:00:00     NULL  
    26.    
    27. --逻辑与LAG一样,只不过LAG是往上,LEAD是往下。  

    FIRST_VALUE

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

    1. SELECT cookieid,  
    2. createtime,  
    3. url,  
    4. ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,  
    5. FIRST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS first1   
    6. FROM lxw1234;  
    7.    
    8. cookieid  createtime            url     rn      first1  
    9. ---------------------------------------------------------  
    10. cookie1 2015-04-10 10:00:00     url1    1       url1  
    11. cookie1 2015-04-10 10:00:02     url2    2       url1  
    12. cookie1 2015-04-10 10:03:04     1url3   3       url1  
    13. cookie1 2015-04-10 10:10:00     url4    4       url1  
    14. cookie1 2015-04-10 10:50:01     url5    5       url1  
    15. cookie1 2015-04-10 10:50:05     url6    6       url1  
    16. cookie1 2015-04-10 11:00:00     url7    7       url1  
    17. cookie2 2015-04-10 10:00:00     url11   1       url11  
    18. cookie2 2015-04-10 10:00:02     url22   2       url11  
    19. cookie2 2015-04-10 10:03:04     1url33  3       url11  
    20. cookie2 2015-04-10 10:10:00     url44   4       url11  
    21. cookie2 2015-04-10 10:50:01     url55   5       url11  
    22. cookie2 2015-04-10 10:50:05     url66   6       url11  
    23. cookie2 2015-04-10 11:00:00     url77   7       url11  

    LAST_VALUE

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

    1. SELECT cookieid,  
    2. createtime,  
    3. url,  
    4. ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,  
    5. LAST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS last1   
    6. FROM lxw1234;  
    7.    
    8.    
    9. cookieid  createtime            url    rn       last1    
    10. -----------------------------------------------------------------  
    11. cookie1 2015-04-10 10:00:00     url1    1       url1  
    12. cookie1 2015-04-10 10:00:02     url2    2       url2  
    13. cookie1 2015-04-10 10:03:04     1url3   3       1url3  
    14. cookie1 2015-04-10 10:10:00     url4    4       url4  
    15. cookie1 2015-04-10 10:50:01     url5    5       url5  
    16. cookie1 2015-04-10 10:50:05     url6    6       url6  
    17. cookie1 2015-04-10 11:00:00     url7    7       url7  
    18. cookie2 2015-04-10 10:00:00     url11   1       url11  
    19. cookie2 2015-04-10 10:00:02     url22   2       url22  
    20. cookie2 2015-04-10 10:03:04     1url33  3       1url33  
    21. cookie2 2015-04-10 10:10:00     url44   4       url44  
    22. cookie2 2015-04-10 10:50:01     url55   5       url55  
    23. cookie2 2015-04-10 10:50:05     url66   6       url66  
    24. cookie2 2015-04-10 11:00:00     url77   7       url77  

    如果不指定ORDER BY,则默认按照记录在文件中的偏移量进行排序,会出现错误的结果

    1. SELECT cookieid,  
    2. createtime,  
    3. url,  
    4. FIRST_VALUE(url) OVER(PARTITION BY cookieid) AS first2    
    5. FROM lxw1234;  
    6.    
    7. cookieid  createtime            url     first2  
    8. ----------------------------------------------  
    9. cookie1 2015-04-10 10:00:02     url2    url2  
    10. cookie1 2015-04-10 10:00:00     url1    url2  
    11. cookie1 2015-04-10 10:03:04     1url3   url2  
    12. cookie1 2015-04-10 10:50:05     url6    url2  
    13. cookie1 2015-04-10 11:00:00     url7    url2  
    14. cookie1 2015-04-10 10:10:00     url4    url2  
    15. cookie1 2015-04-10 10:50:01     url5    url2  
    16. cookie2 2015-04-10 10:00:02     url22   url22  
    17. cookie2 2015-04-10 10:00:00     url11   url22  
    18. cookie2 2015-04-10 10:03:04     1url33  url22  
    19. cookie2 2015-04-10 10:50:05     url66   url22  
    20. cookie2 2015-04-10 11:00:00     url77   url22  
    21. cookie2 2015-04-10 10:10:00     url44   url22  
    22. cookie2 2015-04-10 10:50:01     url55   url22  
    23.    
    24. SELECT cookieid,  
    25. createtime,  
    26. url,  
    27. LAST_VALUE(url) OVER(PARTITION BY cookieid) AS last2    
    28. FROM lxw1234;  
    29.    
    30. cookieid  createtime            url     last2  
    31. ----------------------------------------------  
    32. cookie1 2015-04-10 10:00:02     url2    url5  
    33. cookie1 2015-04-10 10:00:00     url1    url5  
    34. cookie1 2015-04-10 10:03:04     1url3   url5  
    35. cookie1 2015-04-10 10:50:05     url6    url5  
    36. cookie1 2015-04-10 11:00:00     url7    url5  
    37. cookie1 2015-04-10 10:10:00     url4    url5  
    38. cookie1 2015-04-10 10:50:01     url5    url5  
    39. cookie2 2015-04-10 10:00:02     url22   url55  
    40. cookie2 2015-04-10 10:00:00     url11   url55  
    41. cookie2 2015-04-10 10:03:04     1url33  url55  
    42. cookie2 2015-04-10 10:50:05     url66   url55  
    43. cookie2 2015-04-10 11:00:00     url77   url55  
    44. cookie2 2015-04-10 10:10:00     url44   url55  
    45. cookie2 2015-04-10 10:50:01     url55   url55  

    如果想要取分组内排序后最后一个值,则需要变通一下:

    1. SELECT cookieid,  
    2. createtime,  
    3. url,  
    4. ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,  
    5. LAST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS last1,  
    6. FIRST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime DESCAS last2   
    7. FROM lxw1234   
    8. ORDER BY cookieid,createtime;  
    9.    
    10. cookieid  createtime            url     rn     last1    last2  
    11. -------------------------------------------------------------  
    12. cookie1 2015-04-10 10:00:00     url1    1       url1    url7  
    13. cookie1 2015-04-10 10:00:02     url2    2       url2    url7  
    14. cookie1 2015-04-10 10:03:04     1url3   3       1url3   url7  
    15. cookie1 2015-04-10 10:10:00     url4    4       url4    url7  
    16. cookie1 2015-04-10 10:50:01     url5    5       url5    url7  
    17. cookie1 2015-04-10 10:50:05     url6    6       url6    url7  
    18. cookie1 2015-04-10 11:00:00     url7    7       url7    url7  
    19. cookie2 2015-04-10 10:00:00     url11   1       url11   url77  
    20. cookie2 2015-04-10 10:00:02     url22   2       url22   url77  
    21. cookie2 2015-04-10 10:03:04     1url33  3       1url33  url77  
    22. cookie2 2015-04-10 10:10:00     url44   4       url44   url77  
    23. cookie2 2015-04-10 10:50:01     url55   5       url55   url77  
    24. cookie2 2015-04-10 10:50:05     url66   6       url66   url77  
    25. cookie2 2015-04-10 11:00:00     url77   7       url77   url77  
    1. <span style="font-weight: bold; color: rgb(255, 0, 0); font-family: Arial, Helvetica, sans-serif; background-color: rgb(255, 255, 255);">提示:在使用分析函数的过程中,要特别注意ORDER BY子句,用的不恰当,统计出的结果就不是你所期望的。</span>  

    Hive分析窗口函数(五) GROUPING SETS,GROUPING__ID,CUBE,ROLLUP

    GROUPING SETS,GROUPING__ID,CUBE,ROLLUP

    这几个分析函数通常用于OLAP中,不能累加,而且需要根据不同维度上钻和下钻的指标统计,比如,分小时、天、月的UV数。

    数据准备:

    1. CREATE EXTERNAL TABLE lxw1234 (  
    2. month STRING,  
    3. day STRING,   
    4. cookieid STRING   
    5. ) ROW FORMAT DELIMITED   
    6. FIELDS TERMINATED BY ','   
    7. stored as textfile location '/tmp/lxw11/';  
    8.    
    9.    
    10. hive> select * from lxw1234;  
    11. OK  
    12. 2015-03 2015-03-10      cookie1  
    13. 2015-03 2015-03-10      cookie5  
    14. 2015-03 2015-03-12      cookie7  
    15. 2015-04 2015-04-12      cookie3  
    16. 2015-04 2015-04-13      cookie2  
    17. 2015-04 2015-04-13      cookie4  
    18. 2015-04 2015-04-16      cookie4  
    19. 2015-03 2015-03-10      cookie2  
    20. 2015-03 2015-03-10      cookie3  
    21. 2015-04 2015-04-12      cookie5  
    22. 2015-04 2015-04-13      cookie6  
    23. 2015-04 2015-04-15      cookie3  
    24. 2015-04 2015-04-15      cookie2  
    25. 2015-04 2015-04-16      cookie1  

    GROUPING SETS

    在一个GROUP BY查询中,根据不同的维度组合进行聚合,等价于将不同维度的GROUP BY结果集进行UNION ALL

    1. SELECT   
    2. month,  
    3. day,  
    4. COUNT(DISTINCT cookieid) AS uv,  
    5. GROUPING__ID   
    6. FROM lxw1234   
    7. GROUP BY month,day   
    8. GROUPING SETS (month,day)   
    9. ORDER BY GROUPING__ID;  
    10.    
    11. month      day            uv      GROUPING__ID  
    12. ------------------------------------------------  
    13. 2015-03    NULL            5       1  
    14. 2015-04    NULL            6       1  
    15. NULL       2015-03-10      4       2  
    16. NULL       2015-03-12      1       2  
    17. NULL       2015-04-12      2       2  
    18. NULL       2015-04-13      3       2  
    19. NULL       2015-04-15      2       2  
    20. NULL       2015-04-16      2       2  
    21.    
    22.    
    23. 等价于   
    24. SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM lxw1234 GROUP BY month   
    25. UNION ALL   
    26. SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM lxw1234 GROUP BY day  

    再如:

    1. SELECT   
    2. month,  
    3. day,  
    4. COUNT(DISTINCT cookieid) AS uv,  
    5. GROUPING__ID   
    6. FROM lxw1234   
    7. GROUP BY month,day   
    8. GROUPING SETS (month,day,(month,day))   
    9. ORDER BY GROUPING__ID;  
    10.    
    11. month         day             uv      GROUPING__ID  
    12. ------------------------------------------------  
    13. 2015-03       NULL            5       1  
    14. 2015-04       NULL            6       1  
    15. NULL          2015-03-10      4       2  
    16. NULL          2015-03-12      1       2  
    17. NULL          2015-04-12      2       2  
    18. NULL          2015-04-13      3       2  
    19. NULL          2015-04-15      2       2  
    20. NULL          2015-04-16      2       2  
    21. 2015-03       2015-03-10      4       3  
    22. 2015-03       2015-03-12      1       3  
    23. 2015-04       2015-04-12      2       3  
    24. 2015-04       2015-04-13      3       3  
    25. 2015-04       2015-04-15      2       3  
    26. 2015-04       2015-04-16      2       3  
    27.    
    28.    
    29. 等价于  
    30. SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM lxw1234 GROUP BY month   
    31. UNION ALL   
    32. SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM lxw1234 GROUP BY day  
    33. UNION ALL   
    34. SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM lxw1234 GROUP BY month,day  

    其中的 GROUPING__ID,表示结果属于哪一个分组集合。

    CUBE

    根据GROUP BY的维度的所有组合进行聚合。

    1. SELECT   
    2. month,  
    3. day,  
    4. COUNT(DISTINCT cookieid) AS uv,  
    5. GROUPING__ID   
    6. FROM lxw1234   
    7. GROUP BY month,day   
    8. WITH CUBE   
    9. ORDER BY GROUPING__ID;  
    10.    
    11.    
    12. month           day             uv     GROUPING__ID  
    13. --------------------------------------------  
    14. NULL            NULL            7       0  
    15. 2015-03         NULL            5       1  
    16. 2015-04         NULL            6       1  
    17. NULL            2015-04-12      2       2  
    18. NULL            2015-04-13      3       2  
    19. NULL            2015-04-15      2       2  
    20. NULL            2015-04-16      2       2  
    21. NULL            2015-03-10      4       2  
    22. NULL            2015-03-12      1       2  
    23. 2015-03         2015-03-10      4       3  
    24. 2015-03         2015-03-12      1       3  
    25. 2015-04         2015-04-16      2       3  
    26. 2015-04         2015-04-12      2       3  
    27. 2015-04         2015-04-13      3       3  
    28. 2015-04         2015-04-15      2       3  
    29.    
    30.    
    31.    
    32. 等价于  
    33. SELECT NULL,NULL,COUNT(DISTINCT cookieid) AS uv,0 AS GROUPING__ID FROM lxw1234  
    34. UNION ALL   
    35. SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM lxw1234 GROUP BY month   
    36. UNION ALL   
    37. SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM lxw1234 GROUP BY day  
    38. UNION ALL   
    39. SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM lxw1234 GROUP BY month,day  

    ROLLUP

    是CUBE的子集,以最左侧的维度为主,从该维度进行层级聚合。

    1. 比如,以month维度进行层级聚合:  
    2. SELECT   
    3. month,  
    4. day,  
    5. COUNT(DISTINCT cookieid) AS uv,  
    6. GROUPING__ID    
    7. FROM lxw1234   
    8. GROUP BY month,day  
    9. WITH ROLLUP   
    10. ORDER BY GROUPING__ID;  
    11.    
    12. month        day             uv     GROUPING__ID  
    13. ---------------------------------------------------  
    14. NULL             NULL            7       0  
    15. 2015-03          NULL            5       1  
    16. 2015-04          NULL            6       1  
    17. 2015-03          2015-03-10      4       3  
    18. 2015-03          2015-03-12      1       3  
    19. 2015-04          2015-04-12      2       3  
    20. 2015-04          2015-04-13      3       3  
    21. 2015-04          2015-04-15      2       3  
    22. 2015-04          2015-04-16      2       3  
    23.    
    24. 可以实现这样的上钻过程:  
    25. 月天的UV->月的UV->总UV  
    1. --把month和day调换顺序,则以day维度进行层级聚合:  
    2.    
    3. SELECT   
    4. day,  
    5. month,  
    6. COUNT(DISTINCT cookieid) AS uv,  
    7. GROUPING__ID    
    8. FROM lxw1234   
    9. GROUP BY day,month   
    10. WITH ROLLUP   
    11. ORDER BY GROUPING__ID;  
    12.    
    13.    
    14. day         month              uv     GROUPING__ID  
    15. -------------------------------------------------------  
    16. NULL            NULL               7       0  
    17. 2015-04-13      NULL               3       1  
    18. 2015-03-12      NULL               1       1  
    19. 2015-04-15      NULL               2       1  
    20. 2015-03-10      NULL               4       1  
    21. 2015-04-16      NULL               2       1  
    22. 2015-04-12      NULL               2       1  
    23. 2015-04-12      2015-04            2       3  
    24. 2015-03-10      2015-03            4       3  
    25. 2015-03-12      2015-03            1       3  
    26. 2015-04-13      2015-04            3       3  
    27. 2015-04-15      2015-04            2       3  
    28. 2015-04-16      2015-04            2       3  
    29.    
    30. 可以实现这样的上钻过程:  
    31. 天月的UV->天的UV->总UV  
    32. (这里,根据天和月进行聚合,和根据天聚合结果一样,因为有父子关系,如果是其他维度组合的话,就会不一样)  

    这种函数,需要结合实际场景和数据去使用和研究,只看说明的话,很难理解。


    SQL 窗口查询引入了三个新的概念:

    窗口分区、窗口帧、以及窗口函数。

    PARTITION 语句会按照一个或多个指定字段,将查询结果集拆分到不同的 窗口分区 中,并可按照一定规则排序。如果没有 PARTITION BY,则整个结果集将作为单个窗口分区;如果没有 ORDER BY,我们则无法定义窗口帧,进而整个分区将作为单个窗口帧进行处理。

    窗口帧 用于从分区中选择指定的多条记录,供窗口函数处理。Hive 提供了两种定义窗口帧的形式:ROWS 和 RANGE。两种类型都需要配置上界和下界。例如,ROWS BETWEENUNBOUNDED PRECEDING AND CURRENT ROW 表示选择分区起始记录到当前记录的所有行;SUM(close) RANGEBETWEEN 100 PRECEDING AND 200 FOLLOWING 则通过 字段差值 来进行选择。如当前行的 close 字段值是 200,那么这个窗口帧的定义就会选择分区中 close 字段值落在 100 至 400 区间的记录。以下是所有可能的窗口帧定义组合。如果没有定义窗口帧,则默认为 RANGE BETWEEN UNBOUNDEDPRECEDING AND CURRENT ROW

    (ROWS | RANGE) BETWEEN (UNBOUNDED | [num]) PRECEDING AND ([num] PRECEDING | CURRENTROW | (UNBOUNDED | [num]) FOLLOWING)

    (ROWS | RANGE) BETWEEN CURRENT ROW AND (CURRENTROW | (UNBOUNDED | [num]) FOLLOWING)

    (ROWS | RANGE) BETWEEN [num] FOLLOWING AND(UNBOUNDED | [num]) FOLLOWING



    官网的介绍: https://cwiki.apache.org/confluence/display/Hive/Enhanced+Aggregation%2C+Cube%2C+Grouping+and+Rollup

    展开全文
  • 窗口函数 今天跟大家分享一下窗口函数 举个例子:假设当前环境为impala, drop table if exists dw.stu; ----dw库中stu表------------------- create table dw.stu( fperiod string comment’年月’, name ...
  • 参考spark官网王家林DT大数据梦工厂场景将本地文件toNGroup.txt中内容:hadoop@master:~/resource$ cat toNGroup.txt hadoop 29 hadoop 87 hadoop 39 hadoop 27 hadoop 88 spark 29 spark 90 spark 27 spark 84 ...
  • 使用sas实现同比 环比 占比,其中环比和...proc report 可以实现proc print proc tabluate proc sort proc means 以及data步一些功能,所以有中想法,把proc report当做是进行复杂统计实现方法之一,比如sql...
  • 王家林DT大数据梦工厂场景将本地文件toNGroup.txt中内容:hadoop@master:~/resource$ cat toNGroup.txt hadoop 29 hadoop 87 hadoop 39 hadoop 27 hadoop 88 spark 29 spark 90 spark 27 spark 84 spark 92 ...
  • sql窗口函数

    万次阅读 多人点赞 2018-08-17 10:29:42
    窗口函数可以进行排序,...窗口函数就是为了实现OLAP而添加标准SQL功能。 窗口函数语法:其中[]中内容可以省略 &lt;窗口函数&gt; over ([partition by &lt;列清单&gt;] order by &lt...
  • SQL 窗口函数

    2020-08-28 23:33:02
    需要在每组内排名时,例如每个部门按照业绩来排名,可以使用SQL的窗口函数功能。和GROUP BY 不同,窗口函数不会减少数据的行数,窗口函数基本语法如下: <窗口函数> over (partition by <用于分组的别名>...
  • SQLServer 窗口函数

    2019-09-30 00:28:44
    SQLServer 窗口函数 一、窗口函数的作用  窗口函数是对一组值进行操作,不需要使用GROUP BY 子句对数据进行分组,还能够在同一行中同时返回基础行列和聚合列。窗口函数,基础列和聚合列查询都...
  • SQL——窗口函数

    2020-08-04 16:22:10
    窗口函数就是为了实现OLAP而添加标准SQL功能。 窗口函数语法:其中[]中内容可以省略 窗口函数大体可以分为以下两种:1.能够作为窗口函数的聚合函数(sum,avg,count,max,min) 2.rank,dense_rank。...
  • SQL | 窗口函数的补充

    2019-04-25 23:27:33
    窗口函数如何开多个窗口1 背景2 窗口函数如何开多个窗口2.1 具体需求和...最近实习时候,负责提数同事SQL贼6,偷瞄了一眼,又看到了窗口函数,而这个窗口函数,之前博客中笔者也有涉及到,具体见: SQ...
  • SQL窗口函数

    2020-04-22 17:55:41
    一、窗口函数 二、专用窗口函数的种类 三、作为窗口函数使用聚合函数 四、计算移动平均
  • 与聚集函数一样,窗口函数也针对定义行集(组)执行聚集,但它不像聚集函数那样每组之返回一个值,窗口函数可以为每组返回多个值。实际上,DB2中称这种函数为联机分析处理OLAP函数,而Oracle把它们称为解析函数,...
  • sql-窗口函数

    2020-08-18 23:09:35
    窗口函数用法: <窗口函数> OVER ( [PARTITION BY <列清单> ] ORDER BY <排序用列清单>...窗口函数的适用范围:只能在select子句中使用 用于排序专用窗口函数 1、RANK() 在计算排序时,若存
  • SQL ——窗口函数简介

    万次阅读 2018-11-28 22:23:20
    目录 1、窗口函数的描述 2、窗口函数元素 2.1、分区 2.2、排序 ...窗口是标准的SQL术语,用来描述SQL语句内OVER子句划定内容,这个内容就是窗口函数的作用域。而在OVER子句中,定义了窗口所...
  • sql_窗口函数总结

    2020-12-23 09:12:07
    sql 窗口函数总结: 注意:mysql -8.0 才支持 1.窗口函数语法: <窗口函数> over ( partition by <用于分组列名> order by <用于排序列名> ) demo: row_number() over (partition by ...
  • SQL_窗口函数

    千次阅读 2020-03-19 22:22:31
    1.窗口函数与分析函数 看到一篇写很好总结了,就把链接附上把,我们主要是结合一些题目看一下具体用法(https://blog.csdn.net/scgaliguodong123_/article/details/60135385) 1.1 窗口函数 FIRST_VALUE:取...
  • SQL的窗口函数 OVER窗口函数

    万次阅读 2013-11-17 00:51:31
    SQL的窗口函数 SQL/OR   与聚集函数一样,窗口函数也针对定义的行集(组)执行聚集,但它不像聚集函数那样每组之返回一个值,窗口函数可以为每组返回多个值。实际上,DB2中称这种函数为联机分析处理OLAP...
  • sql 窗口函数介绍(Introduction) In my last article, I explained what SQL window functions are and how they can be used. In this article, I’m going to share what I think are the 5 MOST useful window ...

空空如也

空空如也

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

sql的窗口函数