文章目录

  • 窗口函数
  • 聚合函数+over()关键字
  • partition by子句
  • order by子句
  • window 子句
  • 序列函数ntile
  • 排序 row_number rank dense_rank
  • LAG和LEAD函数
  • first_value和last_value




记录为学习笔记

窗口函数

  在SQL中有一类函数叫做聚合函数,例如sum()、avg()、max()等等,这类函数可以将多行数据按照规则聚集为一行,一般来讲聚集后的行数是要少于聚集前的行数的.但是有时我们想要既显示聚集前的数据,又要显示聚集后的数据,这时我们便引入了窗口函数. （新老数据同屏显示，去除聚合，分组的弊端）

窗口函数在所有的后面 比order by前

聚合函数+over()关键字

查询在2015年4月份购买过的顾客及总人数,

select name ,count(*) over() from order where substring(orderdate,1,7)='2015-04';
结果:
name   count(*)购买次数
mart	5
mart	5
mart	5
mart	5
jack	5

去重
select distinct name ,count(*) over() from order where substring(orderdate,1,7)='2015-04';
结果:
mart	2
jack	2

2015-04日每人买的次数

select j.name,j.e from (select name,count(*) over() as e from order where substring(orderdate,1,7)='2015-04') j group by j.name,j.e;

结果：
jack	5
mart	5
每个用户截止到每月为止的最大订单数额和累计到该月的总访问次数

partition by子句

  可以称为查询分区子句，很类似于group by,将数据按照边界值分组，over之前的函数在每一个分组内进行，超出分组，函数重新计算
顾客的购买明细及月购买总额

select name,orderdate,cost,sum(cost) over(partition by month(orderdate))
from order
hive> select name,orderdate,cost,
    > sum(cost) over() as fullagg,
    > sum(cost) over(partition by name) as fullaggbyname,
    > sum(cost) over(partition by name order by orderdate) as fabno
    > from order;
结果：  
jack	2015-01-01	10	661	176	10
jack	2015-01-05	46	661	176	56
jack	2015-01-08	55	661	176	111
jack	2015-02-03	23	661	176	134
jack	2015-04-06	42	661	176	176
mart	2015-04-08	62	661	299	62
mart	2015-04-09	68	661	299	130
mart	2015-04-11	75	661	299	205
mart	2015-04-13	94	661	299	299
neil	2015-05-10	12	661	92	12
neil	2015-06-12	80	661	92	92
tony	2015-01-02	15	661	94	15
tony	2015-01-04	29	661	94	44
tony	2015-01-07	50	661	94	94

order by子句

order by会让输入对方数据 强制排序，因为窗口函数是最后执行的函数 order by默认情况下聚合从起始行到当前行的数据

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

window 子句

在partition by分组的情况下更细的划分

  • 只使用partition by，未指定order by 的聚合是 分组内的聚合
  • 使用order by 没有使用window 就是从默认起点到当前行
    多个窗口函数之间并不相互影响
    preceding:往前
    following: 往后
    current row:当前行
    unbounded:起点 unbounded preceding从前面的起点 ubbounded following 到后面的终点
select name,cost,sum(cost) over() as all  #所有行加在一起
sum(cost) over(partition by name) as fz  #按name分组，组内数据全相加
sum(cost) over(partition by name order by orderdate)  #按name分组，组内数据累加，有一行加一行
sum(cost) over(partition by name order by orderdate rows between unbounded preceding and current row) #由起点到当前行
sum(cost) over(partition by name order by orderdate rows between 1 preceding and current row)  #当前行和前面一行聚合
sum(cost) over(partition by name order by orderdate rows between 1 preceding and 1 following) #前一行 当前行 后一行 相加
sum(cost) over(partition by name order by orderdate rows between current row and unbounded following) # 当前行和后面所有行
from order;

序列函数ntile

ntile分片，用于讲数据舒徐切成n片，返回当前切片值

不支持window

应用场景获取顾客的金额前1/3的交易记录

hive> select name,orderdate,cost,ntile(3) over() from order;

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

第一片的数据

hive> select * from (
    > select name,orderdate,cost,ntile(3) over() as pt1 from order
    > ) t where t.pt1 =1;
    
mart	2015-04-13	94	1
neil	2015-06-12	80	1
mart	2015-04-11	75	1
neil	2015-05-10	12	1
mart	2015-04-09	68	1

排序 row_number rank dense_rank

三个排序都会返回排序值

row_number() 有相同的排名，次序不重复 1234

rank() 相同的排名，次序一样，会在次序中留下空位 1224

dense_rank() 相同的额、排名，次序一样，在次序中不会留下空位 1223

hive> select name,orderdate,cost,row_number() over(partition by name order by cost) as rn1,
    > rank() over(partition by name order by cost) as rn2,
    > dense_rank() over(partition by name order by cost) as rn3
    > from order;

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

LAG和LEAD函数

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

lag(field,n)n表示返回前n行的数据

hive> select name,orderdate,cost,
    > lag(orderdate,5) over(partition by name order by orderdate)
    > from order;

jack	2015-01-01	10	NULL
jack	2015-01-05	46	NULL
jack	2015-01-08	55	NULL
jack	2015-02-03	23	NULL
jack	2015-03-04	23	NULL
jack	2015-04-06	42	2015-01-01
mart	2015-04-08	62	NULL
mart	2015-04-09	68	NULL
mart	2015-04-11	75	NULL
mart	2015-04-13	94	NULL
neil	2015-05-10	12	NULL
neil	2015-06-12	80	NULL
tony	2015-01-02	15	NULL
tony	2015-01-04	29	NULL
tony	2015-01-07	50	NULL
至少消费过6次

first_value和last_value

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