hive sql第五天

窗口函数与分析函数

---

在sql中有一类函数叫做聚合函数，例如sum(),avg(),max()，这类函数可以将多行数据按照规则聚集为一行，一般来说聚集后的行数是要少于聚集前的行数的。但是有时想要既显示聚集前的数据，又要显示聚集后的数据，这时我们引入了窗口函数。窗口函数又叫OLAP函数/分析函数，窗口函数兼具分组和排序功能

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

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

sum、avg、min、max

准备数据

建表语句

create table test_1(cookieid string,createtime string,pv int) row format delimited fields terminated by ',';
加载数据
load data local inpath '/root/hivedata/test_1.dat' into table test_1;
cookie1,2020-04-10,1
cookie1,2020-04-11,5
cookie1,2020-04-12,7
cookie1,2020-04-13,3
cookie1,2020-04-14,2
cookie1,2020-04-15,4
cookie1,2020-04-16,4

开启智能本地模式

set hive.exec.mode.local.auto = true;

SUM函数和窗口函数的配合使用：结果和order by 相关，默认为升序

pv1：分组内从起点到当前行的pv累加

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

pv2：同pv1

select cookieid,createtime,pv,sum(pv) over (partition by cookieid order by createtime rows between unbounded preceding and current row) as pv2 from test_1;

pv3：分组内cookieid1所有的pv累加

select cookieid,createtime,pv,sum(pv) over(partittion by cookieid) as pv3 from test_1;

pv4：分组内当前行+往前3行

select cookieid,createtime,pv,sum(pv) over (partition by cookieid order by createtime rows between 3 preceding and current row) as pv4 from test_1;

pv5：分组内当前行+往前3行+往后1行

select cookieid,createtime,pv,sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and 1 following) as pv5 from test_1;

pv6：分组内当前行+往后所有行

select cookieid,createtime,pv,sum(pv) over (partition by cookieid order by createtime rows between current row and unbounded following) as pv6 from test_1;

如果不指定rows between,默认为从起点到当前行

如果不指定order by 则将分组内所有值累加

关键是理解rows between含义，也叫作window子句

preceding：往前

following：往后

current row：当前

unbounded：起点

unbounded preceding：表示从前面的起点

unbounded following：表示到后面的终点

AVG,MIN,MAX,和SUM的用法一样

row_number、rank、dense_rank、ntile

准备数据

cookie1,2020-04-10,1
cookie1,2020-04-11,5
cookie1,2020-04-12,7
cookie1,2020-04-13,3
cookie1,2020-04-14,2
cookie1,2020-04-15,4
cookie1,2020-04-16,4
cookie2,2020-04-10,2
cookie2,2020-04-11,3
cookie2,2020-04-12,5
cookie2,2020-04-13,6
cookie2,2020-04-14,3
cookie2,2020-04-15,9
cookie2,2020-04-16,7
create table test_2(cookieid string,createtime string,pv int) row format delimited fields terminated by ',' stored as testfile;

加载数据
load data local inpath '/root/hivedata/test_2.dat' into table test_2;

• ROW_NUMBER()使用

row_number()从1开始，按照顺序，生成分组内记录的序列

select cookieid,createtime,pv,row_number() over(partition by cookieid order by pv desc) as rn from test_2;

• RANK和DENSE_RANK使用

RANK()生成数据项在分组中的排名，排名相等会在名次中留下空位 1224

DENSE_RANK()生成数据项在分组中的排名，排名相等不会留下空位12234

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 cookidid order by pv desc) as rn3 from test_2 where cookieid = 'cookie1';

• NTILE

有时会有这样的需求：如果数据排序后分为三部分，业务人员只关心其中一部分，如何将这中间的三分之一数据拿出来呢？NTILE可以满足

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

然后可以根据桶号，选取前或后n分之几的数据。数据就回完整展示出来，知识给相应的数据打标签，具体要取几分之几的数据，需要再嵌套一层根据标签取出

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 test_2 order by cookieid ,createtime;

其他窗口函数

---

lag、lead、first_value、last_value

• LAG

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

第一个参数为列名，第二个参数为往上第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 test_4;

last_1_time: 指定了往上第1行的值，default为'1970-01-01 00:00:00'

cookie1第一行，往上1行为NULL,因此取默认值 1970-01-01 00:00:00

cookie1第三行，往上1行值为第二行值，2015-04-10 10:00:02

cookie1第六行，往上1行值为第五行值，2015-04-10 10:50:01

last_2_time: 指定了往上第2行的值，为指定默认值

cookie1第一行，往上2行为NULL

cookie1第二行，往上2行为NULL

cookie1第四行，往上2行为第二行值，2015-04-10 10:00:02

cookie1第七行，往上2行为第五行值，2015-04-10 10:50:01

• LEAD

与LAG相反LEAD(col,n,DEFAULT)用于统计窗口内往下第n行值

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

• FIRST_VALUE

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

• LAST_VALUE

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

cume_dist，percent_rank

这两个序列分析函数不是很常用，

注意：序列函数不支持WINDOW子句

数据准备

d1,user1,1000
d1,user2,2000
d1,user3,3000
d2,user4,4000
d2,user5,5000
create external table test_t3 (dept STRING,userid string,sal int) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' stored as textfile;
加载数据：load data local inpath '/root/hivedata/test_t3.dat' into table test_t3;

• cume_dist

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

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

• PERCENT_RANK

percent_rank：分组内当前行的RANK值-1/分组内总行数-1

grouping sets,grouping_id,cube,rollup

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

数据准备

2020-03,2020-03-10,cookie1
2020-03,2020-03-10,cookie5
2020-03,2020-03-12,cookie7
2020-04,2020-04-12,cookie3
2020-04,2020-04-13,cookie2
2020-04,2020-04-13,cookie4
2020-04,2020-04-16,cookie4
2020-03,2020-03-10,cookie2
2020-03,2020-03-10,cookie3
2020-04,2020-04-12,cookie5
2020-04,2020-04-13,cookie6
2020-04,2020-04-15,cookie3
2020-04,2020-04-15,cookie2
2020-04,2020-04-16,cookie1
CREATE TABLE test_t5 (month STRING,day STRING, cookieid STRING ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' stored as textfile;
加载数据：load data local inpath '/root/hivedata/test_t5.dat' into table test_t5;

• GROUPING SETS

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

等价于将不同维度的GROUP BY结果进行union all

GROUPING_ID 表示结果属于哪一个分组集合

SELECT month,day,COUNT(DISTINCT cookieid) AS uv,GROUPING_ID FROM test_t5 GROUP BY month,day GROUPING SETS (month,day) ORDER BY GROUPINGID;

grouping_id表示这一组结果属于哪个分组集合，根据grouping sets中的分组条件month，day，1是代表month，2是代表day

等价于

SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING_ID FROM test_t5 GROUP BY month UNION ALL SELECT NULL as month,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING_ID FROM test_t5 GROUP BY day;

• CUBE

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

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

等价于

SELECT NULL,NULL,COUNT(DISTINCT cookieid) AS uv,0 AS GROUPING_ID FROM test_t5UNION ALL SELECT month,NULL,COUNT(DISTINCT cookieid) AS uv,1 AS GROUPING_ID FROM test_t5 GROUP BY month UNION ALL SELECT NULL,day,COUNT(DISTINCT cookieid) AS uv,2 AS GROUPING_ID FROM test_t5 GROUP BY dayUNION ALL SELECT month,day,COUNT(DISTINCT cookieid) AS uv,3 AS GROUPING_ID FROM test_t5 GROUP BY month,day;

• ROLLUP

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

比如，以month维度进行层级聚合：SELECT month,day,COUNT(DISTINCT cookieid) AS uv,GROUPING_IDFROM test_t5 GROUP BY month,dayWITH ROLLUP ORDER BY GROUPING_ID;

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

SELECT day,month,COUNT(DISTINCT cookieid) AS uv,GROUPING_IDFROM test_t5 GROUP BY day,month WITH ROLLUP ORDER BY GROUPING_ID;（这里，根据天和月进行聚合，和根据天聚合结果一样，因为有父子关系，如果是其他维度组合的话，就会不一样）