窗口函数

窗口函数跨查询结果的行执行计算。它们在HAVING子句之后但在ORDER BY子句之前运行。调用窗口函数需要使用OVER子句来指定窗口的特殊语法。窗口具有三个组成部分：

分区规范，它将输入行分为不同的分区。这类似于GROUP BY子句如何将行分为聚合函数的不同组。
排序规范，它确定窗口函数将处理输入行的顺序。

窗口框架，指定给定行该功能要处理的行的滑动窗口。如果未指定帧，则默认为“RANGE UNBOUNDED PRECEDING”，与“UNBOUNDEEN PREBODING AND CURRENT ROWGE”相同。该帧包含从分区的开始到当前行的最后一个对等方的所有行。在没有ORDER BY的情况下，所有行都被视为对等行，因此未绑定的前导和当前行之间的范围等于未绑定的前导和未绑定的后续之间的范围。

例如：下面的查询将salary表中的信息按照每个部门员工工资的大小进行排序。

 --创建数据表并插入数据
create table salary (dept varchar, userid varchar, sal double);
 insert into salary values ('d1','user1',1000),('d1','user2',2000),('d1','user3',3000),('d2','user4',4000),('d2','user5',5000); 

--数据查询
select dept,userid,sal,rank() over (partition by dept order by sal desc) as rnk from salary order by dept,rnk;
dept | userid |  sal   | rnk 
------|--------|--------|-----
 d1   | user3  | 3000.0 |   1 
 d1   | user2  | 2000.0 |   2 
 d1   | user1  | 1000.0 |   3 
 d2   | user5  | 5000.0 |   1 
 d2   | user4  | 4000.0 |   2

Aggregate Functions

所有的聚合函数都能通过添加over子句来当做窗口函数使用。聚合函数将在当前窗口框架下的每行记录进行运算。

下面的查询生成每个职员按天计算的订单价格的滚动总和。

select dept,userid,sal,sum(sal) over (partition by dept order by sal desc) as rolling_sum from salary order by dept,userid,sal;
dept | userid |  sal   | rolling_sum 
------|--------|--------|-------------
 d1   | user1  | 1000.0 |      6000.0 
 d1   | user2  | 2000.0 |      5000.0 
 d1   | user3  | 3000.0 |      3000.0 
 d2   | user4  | 4000.0 |      9000.0 
 d2   | user5  | 5000.0 |      5000.0 
(5 rows)

Ranking Functions

cume_dist()→ bigint

描述：小于等于当前值的行数/分组内总行数–比如，统计小于等于当前薪水的人数，所占总人数的比例。

 --查询示例
SELECT dept, userid, sal, CUME_DIST() OVER(ORDER BY sal) AS rn1, CUME_DIST() OVER(PARTITION BY dept ORDER BY sal) AS rn2 FROM salary;
dept | userid |  sal   | rn1 |        rn2         
------|--------|--------|-----|--------------------
 d2   | user4  | 4000.0 | 0.8 |                0.5 
 d2   | user5  | 5000.0 | 1.0 |                1.0 
 d1   | user1  | 1000.0 | 0.2 | 0.3333333333333333 
 d1   | user2  | 2000.0 | 0.4 | 0.6666666666666666 
 d1   | user3  | 3000.0 | 0.6 |                1.0 
(5 rows)

dense_rank()→ bigint
描述：返回值在一组值中的排名。这与rank（）相似，不同的是tie值不会在序列中产生间隙。

ntile(n)→ bigint

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

--创建表并插入数据
create table cookies_log (cookieid varchar,createtime  date,pv int);
insert into cookies_log values 
	('cookie1',date '2020-07-10',1),
	('cookie1',date '2020-07-11',5), 
	('cookie1',date '2020-07-12',7), 
	('cookie1',date '2020-07-13',3),
	('cookie1',date '2020-07-14',2), 
	('cookie1',date '2020-07-15',4), 
	('cookie1',date '2020-07-16',4), 
	('cookie2',date '2020-07-10',2),
	('cookie2',date '2020-07-11',3), 
	('cookie2',date '2020-07-12',5),
        ('cookie2',date '2020-07-13',6), 
        ('cookie2',date '2020-07-14',3), 
        ('cookie2',date '2020-07-15',9),
	('cookie2',date '2020-07-16',7);
-- 查询结果
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 cookies_log
ORDER BY cookieid,createtime;
cookieid  | createtime | pv | rn1 | rn2 | rn3 
----------|------------|----|-----|-----|-----
 cookie1  | 2020-07-10 |  1 |   1 |   1 |   1 
 cookie1  | 2020-07-11 |  5 |   1 |   1 |   1 
 cookie1  | 2020-07-12 |  7 |   1 |   1 |   2 
 cookie1  | 2020-07-13 |  3 |   1 |   2 |   2 
 cookie1  | 2020-07-14 |  2 |   2 |   2 |   3 
 cookie1  | 2020-07-15 |  4 |   2 |   3 |   4 
 cookie1  | 2020-07-16 |  4 |   2 |   3 |   4 
 cookie2  | 2020-07-10 |  2 |   1 |   1 |   1 
 cookie2  | 2020-07-11 |  3 |   1 |   1 |   1 
 cookie2  | 2020-07-12 |  5 |   1 |   1 |   2 
 cookie2  | 2020-07-13 |  6 |   1 |   2 |   2 
 cookie2  | 2020-07-14 |  3 |   2 |   2 |   3 
 cookie2  | 2020-07-15 |  9 |   2 |   3 |   3 
 cookie2  | 2020-07-16 |  7 |   2 |   3 |   4 
(14 rows)

percent_rank()→ double

描述：返回值在一组值中的百分比排名。结果为（r-1）/（n-1），其中r是该行的rank（），n是窗口分区中的总行数。

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 salary;
 dept | userid |  sal   | rn1  | rn11 | rn12 | rn2 
------|--------|--------|------|------|------|-----
 d2   | user4  | 4000.0 | 0.75 |    4 |    5 | 0.0 
 d2   | user5  | 5000.0 |  1.0 |    5 |    5 | 1.0 
 d1   | user1  | 1000.0 |  0.0 |    1 |    5 | 0.0 
 d1   | user2  | 2000.0 | 0.25 |    2 |    5 | 0.5 
 d1   | user3  | 3000.0 |  0.5 |    3 |    5 | 1.0 
(5 rows)

rank()→ bigint

描述：返回值在一组值中的排名。等级为1加上该行之前与该行不对等的行数。因此，排序中的平局值将在序列中产生缺口。对每个窗口分区执行排名。

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 cookies_log
WHERE cookieid = 'cookie1';
 cookieid | createtime | pv | rn1 | rn2 | rn3 
----------|------------|----|-----|-----|-----
 cookie1  | 2020-07-12 |  7 |   1 |   1 |   1 
 cookie1  | 2020-07-11 |  5 |   2 |   2 |   2 
 cookie1  | 2020-07-15 |  4 |   3 |   3 |   3 
 cookie1  | 2020-07-16 |  4 |   3 |   3 |   4 
 cookie1  | 2020-07-13 |  3 |   5 |   4 |   5 
 cookie1  | 2020-07-14 |  2 |   6 |   5 |   6 
 cookie1  | 2020-07-10 |  1 |   7 |   6 |   7 
(7 rows)

row_number()→ bigint

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

SELECT cookieid, createtime, pv, ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn from cookies_log;
  cookieid | createtime | pv | rn 
 ----------|------------- |----|----
 cookie2  | 2020-07-15 |  9 |  1 
 cookie2  | 2020-07-16 |  7 |  2 
 cookie2  | 2020-07-13 |  6 |  3 
 cookie2  | 2020-07-12 |  5 |  4 
 cookie2  | 2020-07-14 |  3 |  5 
 cookie2  | 2020-07-11 |  3 |  6 
 cookie2  | 2020-07-10 |  2 |  7 
 cookie1  | 2020-07-12 |  7 |  1 
 cookie1  | 2020-07-11 |  5 |  2 
 cookie1  | 2020-07-15 |  4 |  3 
 cookie1  | 2020-07-16 |  4 |  4 
 cookie1  | 2020-07-13 |  3 |  5 
 cookie1  | 2020-07-14 |  2 |  6 
 cookie1  | 2020-07-10 |  1 |  7 
(14 rows)

Value Functions

通常情况下，要重视null值。如果指定了IGNORE NULLS，那么计算中所有包含x为null值的行都会被排除掉，如果所有行的x字段值都是null值，将会返回默认值，否则返回null值。

-- 数据准备
create table cookie_views( cookieid varchar,createtime timestamp,url varchar);
insert into cookie_views values
('cookie1',timestamp '2020-07-10 10:00:02','url20'),
('cookie1',timestamp '2020-07-10 10:00:00','url10'),
('cookie1',timestamp '2020-07-10 10:03:04','urll3'),
('cookie1',timestamp '2020-07-10 10:50:05','url60'),
('cookie1',timestamp '2020-07-10 11:00:00','url70'),
('cookie1',timestamp '2020-07-10 10:10:00','url40'),
('cookie1',timestamp '2020-07-10 10:50:01','url50'),
('cookie2',timestamp '2020-07-10 10:00:02','url23'),
('cookie2',timestamp '2020-07-10 10:00:00','url11'),
('cookie2',timestamp '2020-07-10 10:03:04','url33'),
('cookie2',timestamp '2020-07-10 10:50:05','url66'),
('cookie2',timestamp '2020-07-10 11:00:00','url77'),
('cookie2',timestamp '2020-07-10 10:10:00','url47'),
('cookie2',timestamp '2020-07-10 10:50:01','url55');

first_value(x)→ [same as input]

描述：返回窗口的第一个值。

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 cookie_views;
cookieid |       createtime        |  url  | rn | first1 
----------|-------------------------|-------|----|--------
 cookie1  | 2020-07-10 10:00:00.000 | url10 |  1 | url10  
 cookie1  | 2020-07-10 10:00:02.000 | url20 |  2 | url10  
 cookie1  | 2020-07-10 10:03:04.000 | urll3 |  3 | url10  
 cookie1  | 2020-07-10 10:10:00.000 | url40 |  4 | url10  
 cookie1  | 2020-07-10 10:50:01.000 | url50 |  5 | url10  
 cookie1  | 2020-07-10 10:50:05.000 | url60 |  6 | url10  
 cookie1  | 2020-07-10 11:00:00.000 | url70 |  7 | url10  
 cookie2  | 2020-07-10 10:00:00.000 | url11 |  1 | url11  
 cookie2  | 2020-07-10 10:00:02.000 | url23 |  2 | url11  
 cookie2  | 2020-07-10 10:03:04.000 | url33 |  3 | url11  
 cookie2  | 2020-07-10 10:10:00.000 | url47 |  4 | url11  
 cookie2  | 2020-07-10 10:50:01.000 | url55 |  5 | url11  
 cookie2  | 2020-07-10 10:50:05.000 | url66 |  6 | url11  
 cookie2  | 2020-07-10 11:00:00.000 | url77 |  7 | url11  
(14 rows)

last_value(x)→ [same as input]

描述：返回窗口的最后一个值。

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 cookie_views;
 cookieid |       createtime        |  url  | rn | last1 
----------|-------------------------|-------|----|-------
 cookie2  | 2020-07-10 10:00:00.000 | url11 |  1 | url11 
 cookie2  | 2020-07-10 10:00:02.000 | url23 |  2 | url23 
 cookie2  | 2020-07-10 10:03:04.000 | url33 |  3 | url33 
 cookie2  | 2020-07-10 10:10:00.000 | url47 |  4 | url47 
 cookie2  | 2020-07-10 10:50:01.000 | url55 |  5 | url55 
 cookie2  | 2020-07-10 10:50:05.000 | url66 |  6 | url66 
 cookie2  | 2020-07-10 11:00:00.000 | url77 |  7 | url77 
 cookie1  | 2020-07-10 10:00:00.000 | url10 |  1 | url10 
 cookie1  | 2020-07-10 10:00:02.000 | url20 |  2 | url20 
 cookie1  | 2020-07-10 10:03:04.000 | urll3 |  3 | urll3 
 cookie1  | 2020-07-10 10:10:00.000 | url40 |  4 | url40 
 cookie1  | 2020-07-10 10:50:01.000 | url50 |  5 | url50 
 cookie1  | 2020-07-10 10:50:05.000 | url60 |  6 | url60 
 cookie1  | 2020-07-10 11:00:00.000 | url70 |  7 | url70 
(14 rows)

nth_value(x, offset)→ [same as input]

描述：返回距窗口开头指定偏移量的值。偏移量从1开始。偏移量可以是任何标量表达式。如果偏移量为null或大于窗口中的值数，则返回null。偏移量不允许为0或者负数。

SELECT cookieid,createtime,url,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
NTH_VALUE(url,3) OVER(PARTITION BY cookieid ORDER BY createtime) AS last1 
FROM cookie_views;
cookieid |       createtime        |  url  | rn | last1 
----------|-------------------------|-------|----|-------
 cookie1  | 2020-07-10 10:00:00.000 | url10 |  1 | NULL  
 cookie1  | 2020-07-10 10:00:02.000 | url20 |  2 | NULL  
 cookie1  | 2020-07-10 10:03:04.000 | urll3 |  3 | urll3 
 cookie1  | 2020-07-10 10:10:00.000 | url40 |  4 | urll3 
 cookie1  | 2020-07-10 10:50:01.000 | url50 |  5 | urll3 
 cookie1  | 2020-07-10 10:50:05.000 | url60 |  6 | urll3 
 cookie1  | 2020-07-10 11:00:00.000 | url70 |  7 | urll3 
 cookie2  | 2020-07-10 10:00:00.000 | url11 |  1 | NULL  
 cookie2  | 2020-07-10 10:00:02.000 | url23 |  2 | NULL  
 cookie2  | 2020-07-10 10:03:04.000 | url33 |  3 | url33 
 cookie2  | 2020-07-10 10:10:00.000 | url47 |  4 | url33 
 cookie2  | 2020-07-10 10:50:01.000 | url55 |  5 | url33 
 cookie2  | 2020-07-10 10:50:05.000 | url66 |  6 | url33 
 cookie2  | 2020-07-10 11:00:00.000 | url77 |  7 | url33 
(14 rows)

lead(x[, offset[, default_value]])→ [same as input]

描述：返回窗口分区中当前行之后的偏移行处的值。偏移量从0开始，即当前行。偏移量可以是任何标量表达式。默认偏移量为1。如果偏移量为null，则返回null。如果偏移量指向不在分区内的行，则返回default_value，或者如果未指定，则返回null。lead（）函数要求指定窗口顺序。不得指定窗框。

SELECT cookieid,createtime,url,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
LEAD(createtime,1,timestamp '2020-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 cookie_views;
 cookieid |       createtime        |  url  | rn |       next_1_time       |       next_2_time       
----------|-------------------------|-------|----|-------------------------|-------------------------
 cookie2  | 2020-07-10 10:00:00.000 | url11 |  1 | 2020-07-10 10:00:02.000 | 2020-07-10 10:03:04.000 
 cookie2  | 2020-07-10 10:00:02.000 | url23 |  2 | 2020-07-10 10:03:04.000 | 2020-07-10 10:10:00.000 
 cookie2  | 2020-07-10 10:03:04.000 | url33 |  3 | 2020-07-10 10:10:00.000 | 2020-07-10 10:50:01.000 
 cookie2  | 2020-07-10 10:10:00.000 | url47 |  4 | 2020-07-10 10:50:01.000 | 2020-07-10 10:50:05.000 
 cookie2  | 2020-07-10 10:50:01.000 | url55 |  5 | 2020-07-10 10:50:05.000 | 2020-07-10 11:00:00.000 
 cookie2  | 2020-07-10 10:50:05.000 | url66 |  6 | 2020-07-10 11:00:00.000 | NULL                    
 cookie2  | 2020-07-10 11:00:00.000 | url77 |  7 | 2020-01-01 00:00:00.000 | NULL                    
 cookie1  | 2020-07-10 10:00:00.000 | url10 |  1 | 2020-07-10 10:00:02.000 | 2020-07-10 10:03:04.000 
 cookie1  | 2020-07-10 10:00:02.000 | url20 |  2 | 2020-07-10 10:03:04.000 | 2020-07-10 10:10:00.000 
 cookie1  | 2020-07-10 10:03:04.000 | urll3 |  3 | 2020-07-10 10:10:00.000 | 2020-07-10 10:50:01.000 
 cookie1  | 2020-07-10 10:10:00.000 | url40 |  4 | 2020-07-10 10:50:01.000 | 2020-07-10 10:50:05.000 
 cookie1  | 2020-07-10 10:50:01.000 | url50 |  5 | 2020-07-10 10:50:05.000 | 2020-07-10 11:00:00.000 
 cookie1  | 2020-07-10 10:50:05.000 | url60 |  6 | 2020-07-10 11:00:00.000 | NULL                    
 cookie1  | 2020-07-10 11:00:00.000 | url70 |  7 | 2020-01-01 00:00:00.000 | NULL                    
(14 rows)

lag(x[, offset[, default_value]])→ [same as input]

描述：返回窗口分区中当前行之前的偏移行的值，偏移量从0开始，即当前行，偏移量可以是任何标量表达式，默认偏移量为1。如果偏移量为null，则返回null。如果偏移量指向不在分区内的行，则返回default_value。如果未指定，则返回null。lag（）函数要求指定窗口顺序，不得指定窗框。

 SELECT cookieid, createtime, url, ROW_NUMBER() OVER(PARTITION BY cookieid  ORDER BY createtime) AS rn, 
   LAG(createtime,1, timestamp '2020-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 cookie_views;

cookieid |       createtime         |  url  | rn |       last_1_time       |       last_2_time       
----------|-------------------------|-------|----|-------------------------|-----------------------
 cookie2  | 2020-07-10 10:00:00.000 | url11 |  1 | 2020-01-01 00:00:00.000 | NULL                    
 cookie2  | 2020-07-10 10:00:02.000 | url23 |  2 | 2020-07-10 10:00:00.000 | NULL                    
 cookie2  | 2020-07-10 10:03:04.000 | url33 |  3 | 2020-07-10 10:00:02.000 | 2020-07-10 10:00:00.000 
 cookie2  | 2020-07-10 10:10:00.000 | url47 |  4 | 2020-07-10 10:03:04.000 | 2020-07-10 10:00:02.000 
 cookie2  | 2020-07-10 10:50:01.000 | url55 |  5 | 2020-07-10 10:10:00.000 | 2020-07-10 10:03:04.000 
 cookie2  | 2020-07-10 10:50:05.000 | url66 |  6 | 2020-07-10 10:50:01.000 | 2020-07-10 10:10:00.000 
 cookie2  | 2020-07-10 11:00:00.000 | url77 |  7 | 2020-07-10 10:50:05.000 | 2020-07-10 10:50:01.000 
 cookie1  | 2020-07-10 10:00:00.000 | url10 |  1 | 2020-01-01 00:00:00.000 | NULL                    
 cookie1  | 2020-07-10 10:00:02.000 | url20 |  2 | 2020-07-10 10:00:00.000 | NULL                    
 cookie1  | 2020-07-10 10:03:04.000 | urll3 |  3 | 2020-07-10 10:00:02.000 | 2020-07-10 10:00:00.000 
 cookie1  | 2020-07-10 10:10:00.000 | url40 |  4 | 2020-07-10 10:03:04.000 | 2020-07-10 10:00:02.000 
 cookie1  | 2020-07-10 10:50:01.000 | url50 |  5 | 2020-07-10 10:10:00.000 | 2020-07-10 10:03:04.000 
 cookie1  | 2020-07-10 10:50:05.000 | url60 |  6 | 2020-07-10 10:50:01.000 | 2020-07-10 10:10:00.000 
 cookie1  | 2020-07-10 11:00:00.000 | url70 |  7 | 2020-07-10 10:50:05.000 | 2020-07-10 10:50:01.000 
(14 rows)

父主题： HetuEngine SQL函数和操作符说明

上一篇：聚合函数

下一篇：数组函数和运算符