hive是面向oltp hive olap

窗口函数又叫OLAP函数/分析函数，窗口函数兼具分组和排序功能。

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

具体语法如下：XXX over (partition by xxx order by xxx)

over()里面的 partition by 和 order by 都不是必选的，over()里面可以只有partition by，也可以只有order by，也可以两个都没有，需根据需求灵活运用。

1.sum、avg、min、max

创建表

create table user_pv(
cookieid string,  -- 用户登录的cookie，即用户标识
createtime string, -- 日期
pv int -- 页面访问量
);

cookie1,2021-05-10,1
cookie1,2021-05-11,5
cookie1,2021-05-12,7
cookie1,2021-05-13,3
cookie1,2021-05-14,2
cookie1,2021-05-15,4
cookie1,2021-05-16,4

sum over (partition by xxx order by xxx)

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

 注意：

• over()里面加 order by 表示：分组内从起点到当前行的pv累积，如，11号的pv1=10号的pv+11号的pv, 12号=10号+11号+12号；
• over()里面不加 order by 表示：将分组内所有值累加。

2.row_number、rank、dense_rank、ntile

cookie1,2021-05-10,1
cookie1,2021-05-11,5
cookie1,2021-05-12,7
cookie1,2021-05-13,3
cookie1,2021-05-14,2
cookie1,2021-05-15,4
cookie1,2021-05-16,4
cookie2,2021-05-10,2
cookie2,2021-05-11,3
cookie2,2021-05-12,5
cookie2,2021-05-13,6
cookie2,2021-05-14,3
cookie2,2021-05-15,9
cookie2,2021-05-16,7

SELECT cookieid
       ,createtime
       ,pv
       ,RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn1
       ,DENSE_RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn2
       ,ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn3 
  FROM user_pv 
 WHERE cookieid = 'cookie1';

RANK()：分组后，从1开始排名，遇到相同值会在名次中留下空位

DENSE_RANK()：分组后，从1开始排名，遇到相同值不会留下空位

ROW_NUMBER():  分组后，从1开始排名，遇到相同值按照表中记录的顺序进行排列

NTILE的使用

需求：

取数据排序后的前三分之一的数据

ntile可以看成是：把有序的数据集合平均分配到指定的数量（num）个桶中, 将桶号分配给每一行。如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差1。

就是把组内数据分成几份

SELECT cookieid
       ,createtime
       ,pv
       ,NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn1
       ,NTILE(3) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn2
       ,NTILE(4) OVER(ORDER BY createtime) AS rn3
  FROM user_pv 
 ORDER BY cookieid,createtime;

 

3 lag、lead、first_value、last_value

创建用户访问页面表: user_url

CREATE TABLE user_url (
cookieid string,
createtime string,  --页面访问时间
url string       --被访问页面
);

cookie1,2021-06-10 10:00:02,url2
cookie1,2021-06-10 10:00:00,url1
cookie1,2021-06-10 10:03:04,1url3
cookie1,2021-06-10 10:50:05,url6
cookie1,2021-06-10 11:00:00,url7
cookie1,2021-06-10 10:10:00,url4
cookie1,2021-06-10 10:50:01,url5
cookie2,2021-06-10 10:00:02,url22
cookie2,2021-06-10 10:00:00,url11
cookie2,2021-06-10 10:03:04,1url33
cookie2,2021-06-10 10:50:05,url66
cookie2,2021-06-10 11:00:00,url77
cookie2,2021-06-10 10:10:00,url44
cookie2,2021-06-10 10:50:01,url55

lag

LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值。

第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

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

解析：
last_1_time: 指定了往上第1行的值，default为'1970-01-01 00:00:00'  
                 cookie1第一行，往上1行为NULL,因此取默认值 1970-01-01 00:00:00
                 cookie1第三行，往上1行值为第二行值，2021-06-10 10:00:02
                 cookie1第六行，往上1行值为第五行值，2021-06-10 10:50:01
last_2_time: 指定了往上第2行的值，为指定默认值
       cookie1第一行，往上2行为NULL
       cookie1第二行，往上2行为NULL
       cookie1第四行，往上2行为第二行值，2021-06-10 10:00:02
       cookie1第七行，往上2行为第五行值，2021-06-10 10:50:01

lead

与lag相反

LEAD(col,n,DEFAULT) 用于统计窗口内往下第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 user_url;

 

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 
  FROM user_url;

 

• LAST_VALUE的使用：

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

SELECT cookieid
       ,createtime
       ,url
       ,ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn
       ,LAST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS last1 
  FROM user_url;

 

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

使用的是 FIRST_VALUE 的倒序取出分组内排序最后一个值！

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

4 cume_dist

创建员工薪水表 staff_salary

CREATE EXTERNAL TABLE staff_salary (
dept string,
userid string,
sal int
);

d1,user1,1000
d1,user2,2000
d1,user3,3000
d2,user4,4000
d2,user5,5000

CUME_DIST：小于等于当前值的行数/分组内总行数。order默认顺序 ：正序

SELECT dept
       ,userid
       ,sal
       ,CUME_DIST() OVER(ORDER BY sal) AS rn1
       ,CUME_DIST() OVER(PARTITION BY dept ORDER BY sal) AS rn2 
  FROM staff_salary;

 

rn1: 没有partition,所有数据均为1组，总行数为5，
     第一行：小于等于1000的行数为1，因此，1/5=0.2
     第三行：小于等于3000的行数为3，因此，3/5=0.6
rn2: 按照部门分组，dpet=d1的行数为3,
     第二行：小于等于2000的行数为2，因此，2/3=0.6666666666666666

 

5 grouping sets、grouping__id、cube、rollup

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

创建用户访问表 user_date

CREATE TABLE user_date (
month STRING,
day STRING, 
cookieid STRING 
);

2021-03,2021-03-10,cookie1
2021-03,2021-03-10,cookie5
2021-03,2021-03-12,cookie7
2021-04,2021-04-12,cookie3
2021-04,2021-04-13,cookie2
2021-04,2021-04-13,cookie4
2021-04,2021-04-16,cookie4
2021-03,2021-03-10,cookie2
2021-03,2021-03-10,cookie3
2021-04,2021-04-12,cookie5
2021-04,2021-04-13,cookie6
2021-04,2021-04-15,cookie3
2021-04,2021-04-15,cookie2
2021-04,2021-04-16,cookie1

GROUPING SETS的使用：

grouping sets是一种将多个group by 逻辑写在一个sql语句中的便利写法。

等价于将不同维度的GROUP BY结果集进行UNION ALL。

SELECT month
       ,day
       ,COUNT(DISTINCT cookieid) AS uv
       ,GROUPING__ID 
  FROM user_date 
 GROUP BY month,day 
GROUPING SETS (month,day) 
 ORDER BY GROUPING__ID;

注：上述SQL中的GROUPING__ID，是个关键字，表示结果属于哪一个分组集合，根据grouping sets中的分组条件month，day，1是代表month，2是代表day。

 

SELECT month
      ,NULL as day
      ,COUNT(DISTINCT cookieid) AS uv
      ,1 AS GROUPING__ID 
  FROM user_date 
 GROUP BY month 

UNION ALL 

SELECT NULL as month
       ,day
       ,COUNT(DISTINCT cookieid) AS uv
       ,2 AS GROUPING__ID 
  FROM user_date 
 GROUP BY day;

• CUBE的使用：

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

SELECT month
       ,day
       ,COUNT(DISTINCT cookieid) AS uv
       ,GROUPING__ID 
  FROM user_date 
 GROUP BY month,day 
  WITH CUBE 
 ORDER BY GROUPING__ID;

 

等价于

SELECT NULL,NULL,COUNT(DISTINCT cookieid) AS uv,0 AS GROUPING__ID FROM user_date

UNION ALL 

SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING__ID FROM user_date GROUP BY month 

UNION ALL 

SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING__ID FROM user_date GROUP BY day

UNION ALL 

SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING__ID FROM user_date GROUP BY month,day;

• ROLLUP的使用：

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

比如，以month维度进行层级聚合：

SELECT month
       ,day
       ,COUNT(DISTINCT cookieid) AS uv
       ,GROUPING__ID  
  FROM user_date 
 GROUP BY month,day
  WITH ROLLUP 
 ORDER BY GROUPING__ID;

 

把month和day调换顺序，则以day维度进行层级聚合：

SELECT  day
       ,month
       ,COUNT(DISTINCT cookieid) AS uv
       ,GROUPING__ID  
  FROM user_date 
 GROUP BY day,month
  WITH ROLLUP 
 ORDER BY GROUPING__ID;