【必看】最全开窗函数讲解和实战指南

窗口函数（Window Function）是 SQL2003 标准中定义的一项新特性，并在 SQL2011、SQL2016 中又加以完善，添加了若干拓展。

一.窗口函数有什么用？
在日常工作中，经常会遇到需要在每组内排名，比如下面的业务需求：

排名问题：每个部门按业绩来排名
topN问题：找出每个部门排名前N的员工进行奖励

面对这类需求，就需要使用sql的高级功能窗口函数了。

二.什么是窗口函数？

窗口函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据库数据进行实时分析处理。

窗口函数的基本语法如下：

 over (partition by order by )

那么语法中的都有哪些呢？

的位置，可以放以下两种函数：

1） 专用窗口函数，包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。

2） 聚合函数，如sum. avg, count, max, min等

因为窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中。

三.如何使用？

接下来，就结合实例，给大家介绍几种窗口函数的用法。

1.专用窗口函数rank

例如下图，是班级表中的内容

如果我们想在每个班级内按成绩排名，得到下面的结果。

以班级“1”为例，这个班级的成绩“95”排在第1位，这个班级的“83”排在第4位。上面这个结果确实按我们的要求在每个班级内，按成绩排名了。

得到上面结果的sql语句代码如下：

select *, rank() over (partition by 班级 order by 成绩 desc) as rankingfrom 班级表

我们来解释下这个sql语句里的select子句。rank是排序的函数。要求是“每个班级内按成绩排名”，这句话可以分为两部分：

1）每个班级内：按班级分组

partition by用来对表分组。在这个例子中，所以我们指定了按“班级”分组（partition by 班级）
2）按成绩排名

order by子句的功能是对分组后的结果进行排序，默认是按照升序（asc）排列。在本例中（order by 成绩 desc）是按成绩这一列排序，加了desc关键词表示降序排列。

通过下图，我们就可以理解partiition by（分组）和order by（在组内排序）的作用了。

窗口函数具备了我们之前学过的group by子句分组的功能和order by子句排序的功能。那么，为什么还要用窗口函数呢？

这是因为，group by分组汇总后改变了表的行数，一行只有一个类别。而partiition by和rank函数不会减少原表中的行数。例如下面统计每个班级的人数。

相信通过这个例子，你已经明白了这个窗口函数的使用：select *, rank() over (partition by 班级 order by 成绩 desc) as rankingfrom 班级表

窗口函数不同于我们熟悉的常规函数及聚合函数，它为每行数据进行一次计算，特点是输入多行（一个窗口）、返回一个值。

在报表等数据分析场景中，你会发现窗口函数真的很强大，灵活运用窗口函数可以解决很多复杂问题，比如去重、排名、同比及环比、连续登录等等。

既然窗口函数这么强大，更要了解和灵活运用它了，本文将对窗口函数进行一个全面的整理，讲一讲窗口函数是什么，有哪些分类，用法是什么，以及窗口函数的案例加深大家的理解。

那什么是窗口函数呢？

窗口函数出现在 SELECT 子句的表达式列表中，它最显著的特点就是 OVER 关键字。语法定义如下：

Function (arg1,..., argn) OVER ([PARTITION BY ] [ORDER BY ][])

Function (arg1,…, argn) 可以是下面的函数：

Aggregate Functions: 聚合函数,比如：sum(…)、 max(…)、min(…)、avg(…)等.Sort Functions: 数据排序函数, 比如 ：rank(…)、row_number(…)等.Analytics Functions: 统计和比较函数, 比如：lead(…)、lag(…)、 first_value(…)等.

OVER ([PARTITION BY ] [ORDER BY ] 其中包括以下可选项：

PARTITION BY表示将数据先按字段进行分区
ORDER BY表示将各个分区内的数据按排序字段进行排序

window_expression 用于确定窗边界：

窗口边界使用详解

1. 如果不指定 PARTITION BY，则不对数据进行分区，换句话说，所有数据看作同一个分区；
2. 如果不指定 ORDER BY，则不对各分区做排序，通常用于那些顺序无关的窗口函数，例如 SUM()
3. 如果不指定窗口子句，则默认采用以下的窗口定义：
   a、若不指定 ORDER BY，默认使用分区内所有行ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
   b、若指定了 ORDER BY，默认使用分区内第一行到当前值ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

窗口函数的计算过程（语法中每个部分都是可选的）

• 按窗口定义，将所有输入数据分区、再排序（如果需要的话）
• 对每一行数据，计算它的窗口范围
• 将窗口内的行集合输入窗口函数，计算结果填入当前行

数据准备

-- 创建表CREATE TABLE IF NOT EXISTS q1_sales (emp_name string,emp_mgr string,dealer_id int,sales int,stat_date string)ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'STORED as TEXTFILE;-- 插入测试数据insert into table q1_sales (emp_name,emp_mgr,dealer_id,sales,stat_date)values('Beverly Lang','Mike Palomino',2,16233,'2020-01-01'),('Kameko French','Mike Palomino',2,16233,'2020-01-03'),('Ursa George','Rich Hernandez',3,15427,'2020-01-04'),('Ferris Brown','Dan Brodi',1,19745,'2020-01-02'),('Noel Meyer','Kari Phelps',1,19745,'2020-01-05'),('Abel Kim','Rich Hernandez',1,12369,'2020-01-03'),('Raphael Hull','Kari Phelps',1,8227,'2020-01-02'),('Jack Salazar','Kari Phelps',1,9710,'2020-01-01'),('May Stout','Rich Hernandez',3,9308,'2020-01-05'),('Haviva Montoya','Mike Palomino',2,9308,'2020-01-03');-- 查看测试数据信息select * from q1_sales;+--------------------+-------------------+---------------------+-----------------+---------------------+| q1_sales.emp_name | q1_sales.emp_mgr | q1_sales.dealer_id | q1_sales.sales | q1_sales.stat_date |+--------------------+-------------------+---------------------+-----------------+---------------------+| Beverly Lang | Mike Palomino | 2 | 16233 | 2020-01-01 || Kameko French | Mike Palomino | 2 | 16233 | 2020-01-03 || Ursa George | Rich Hernandez | 3 | 15427 | 2020-01-04 || Ferris Brown | Dan Brodi | 1 | 19745 | 2020-01-02 || Noel Meyer | Kari Phelps | 1 | 19745 | 2020-01-05 || Abel Kim | Rich Hernandez | 1 | 12369 | 2020-01-03 || Raphael Hull | Kari Phelps | 1 | 8227 | 2020-01-02 || Jack Salazar | Kari Phelps | 1 | 9710 | 2020-01-01 || May Stout | Rich Hernandez | 3 | 9308 | 2020-01-05 || Haviva Montoya | Mike Palomino | 2 | 9308 | 2020-01-03 |+--------------------+-------------------+---------------------+-----------------+---------------------+10 rows selected (0.223 seconds)

窗口聚合函数有哪些？

select emp_name,emp_mgr,dealer_id,sales,sum(sales) over () as sample1, -- 所有sales和sum(sales) over (partition by dealer_id) as sample2, -- 按dealer_id分组，组内数据累加sum(sales) over (partition by dealer_id ORDER BY stat_date) as sample3, -- 按dealer_id分组，时间排序，组内数据逐个相加sum(sales)OVER (PARTITION BY dealer_id ORDER BY stat_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as sample4, -- 按dealer_id分组，时间排序，组内由起点到当前行的聚合sum(sales)OVER (PARTITION BY dealer_id ORDER BY stat_date ROWS BETWEEN 1 PRECEDING and CURRENT ROW) as sample5, -- 按dealer_id分组，时间排序，组内当前行和前面一行做聚合sum(sales)over (PARTITION BY dealer_id ORDER BY stat_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as sample6, -- 按dealer_id分组，时间排序，组内当前行和前一行和后一行聚合sum(sales)over (PARTITION BY dealer_id ORDER BY stat_date ROWS BETWEEN CURRENT ROW and UNBOUNDED FOLLOWING) as sample7 -- 按dealer_id分组，时间排序，组内当前行和后面所有行from q1_sales;

select emp_name,emp_mgr,dealer_id,sales,count(sales) over () as sample1, -- 所有条数count(sales) over (partition by dealer_id) as sample2, -- 按dealer_id分组，组内数据数量count(sales) over (partition by dealer_id ORDER BY stat_date) as sample3, -- 按dealer_id分组，时间排序，组内数据条数逐个相加count(sales)OVER (PARTITION BY dealer_id ORDER BY stat_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as sample4, -- 按dealer_id分组，时间排序，组内由起点到当前行的聚合count(sales)OVER (PARTITION BY dealer_id ORDER BY stat_date ROWS BETWEEN 1 PRECEDING and CURRENT ROW) as sample5, -- 按dealer_id分组，时间排序，组内当前行和前面一行做聚合count(sales)over (PARTITION BY dealer_id ORDER BY stat_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as sample6, -- 按dealer_id分组，时间排序，组内当前行和前一行和后一行聚合count(sales)over (PARTITION BY dealer_id ORDER BY stat_date ROWS BETWEEN CURRENT ROW and UNBOUNDED FOLLOWING) as sample7 -- 按dealer_id分组，时间排序，组内当前行和后面所有行from q1_sales;

select emp_name,emp_mgr,dealer_id,sales,avg(sales) over () as sample1, -- 所有sales聚合avg(sales) over (partition by dealer_id) as sample2, -- 按dealer_id分组，组内数据累加avg(sales) over (partition by dealer_id ORDER BY stat_date) as sample3, -- 按dealer_id分组，时间排序，组内数据逐个相加avg(sales)OVER (PARTITION BY dealer_id ORDER BY stat_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as sample4, -- 按dealer_id分组，时间排序，组内由起点到当前行的聚合avg(sales)OVER (PARTITION BY dealer_id ORDER BY stat_date ROWS BETWEEN 1 PRECEDING and CURRENT ROW) as sample5, -- 按dealer_id分组，时间排序，组内当前行和前面一行做聚合avg(sales)over (PARTITION BY dealer_id ORDER BY stat_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as sample6, -- 按dealer_id分组，时间排序，组内当前行和前一行和后一行聚合avg(sales)over (PARTITION BY dealer_id ORDER BY stat_date ROWS BETWEEN CURRENT ROW and UNBOUNDED FOLLOWING) as sample7 -- 按dealer_id分组，时间排序，组内当前行和后面所有行from q1_sales;

select emp_name,emp_mgr,dealer_id,sales,max(sales) over () as sample1, -- 所有sales聚合max(sales) over (partition by dealer_id) as sample2, -- 按dealer_id分组，组内数据累加max(sales) over (partition by dealer_id ORDER BY stat_date) as sample3, -- 按dealer_id分组，时间排序，组内数据逐个相加max(sales)OVER (PARTITION BY dealer_id ORDER BY stat_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as sample4, -- 按dealer_id分组，时间排序，组内由起点到当前行的聚合max(sales)OVER (PARTITION BY dealer_id ORDER BY stat_date ROWS BETWEEN 1 PRECEDING and CURRENT ROW) as sample5, -- 按dealer_id分组，时间排序，组内当前行和前面一行做聚合max(sales)over (PARTITION BY dealer_id ORDER BY stat_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as sample6, -- 按dealer_id分组，时间排序，组内当前行和前一行和后一行聚合max(sales)over (PARTITION BY dealer_id ORDER BY stat_date ROWS BETWEEN CURRENT ROW and UNBOUNDED FOLLOWING) as sample7 -- 按dealer_id分组，时间排序，组内当前行和后面所有行from q1_sales;

select emp_name,emp_mgr,dealer_id,sales,min(sales) over () as sample1, -- 所有sales聚合min(sales) over (partition by dealer_id) as sample2, -- 按dealer_id分组，组内数据累加min(sales) over (partition by dealer_id ORDER BY stat_date) as sample3, -- 按dealer_id分组，时间排序，组内数据逐个相加min(sales)OVER (PARTITION BY dealer_id ORDER BY stat_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as sample4, -- 按dealer_id分组，时间排序，组内由起点到当前行的聚合min(sales)OVER (PARTITION BY dealer_id ORDER BY stat_date ROWS BETWEEN 1 PRECEDING and CURRENT ROW) as sample5, -- 按dealer_id分组，时间排序，组内当前行和前面一行做聚合min(sales)over (PARTITION BY dealer_id ORDER BY stat_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as sample6, -- 按dealer_id分组，时间排序，组内当前行和前一行和后一行聚合min(sales)over (PARTITION BY dealer_id ORDER BY stat_date ROWS BETWEEN CURRENT ROW and UNBOUNDED FOLLOWING) as sample7 -- 按dealer_id分组，时间排序，组内当前行和后面所有行from q1_sales;

排名窗口函数

select *,ROW_NUMBER() over(partition by dealer_id order by sales desc) rk01,RANK() over(partition by dealer_id order by sales desc) rk02,DENSE_RANK() over(partition by dealer_id order by sales desc) rk03,PERCENT_RANK() over(partition by dealer_id order by sales desc) rk04from q1_sales;

select *,CUME_DIST() over(partition by dealer_id order by sales ) rk05,CUME_DIST() over(partition by dealer_id order by sales desc) rk06from q1_sales;

select *,NTILE(2) over(partition by dealer_id order by sales ) rk07,NTILE(3) over(partition by dealer_id order by sales ) rk08,NTILE(4) over(partition by dealer_id order by sales ) rk09from q1_sales;

值窗口函数

注意: last_value默认的窗口是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，表示当前行永远是最后一个值，需改成RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。

select emp_name, dealer_id, sales, first_value(sales) over (partition by dealer_id order by sales) as dealer_low from q1_sales;|-----------------|------------|--------|-------------|| emp_name | dealer_id | sales | dealer_low ||-----------------|------------|--------|-------------|| Raphael Hull | 1 | 8227 | 8227 || Jack Salazar | 1 | 9710 | 8227 || Ferris Brown | 1 | 19745 | 8227 || Noel Meyer | 1 | 19745 | 8227 || Haviva Montoya | 2 | 9308 | 9308 || Beverly Lang | 2 | 16233 | 9308 || Kameko French | 2 | 16233 | 9308 || May Stout | 3 | 9308 | 9308 || Abel Kim | 3 | 12369 | 9308 || Ursa George | 3 | 15427 | 9308 ||-----------------|------------|--------|-------------|10 rows selected (0.299 seconds)select emp_name, dealer_id, sales, year, last_value(sales) over (partition by emp_name order by year) as last_sale from emp_sales where year = 2013;|-----------------|------------|--------|-------|------------|| emp_name | dealer_id | sales | year | last_sale ||-----------------|------------|--------|-------|------------|| Beverly Lang | 2 | 5324 | 2013 | 5324 || Ferris Brown | 1 | 22003 | 2013 | 22003 || Haviva Montoya | 2 | 6345 | 2013 | 13100 || Haviva Montoya | 2 | 13100 | 2013 | 13100 || Kameko French | 2 | 7540 | 2013 | 7540 || May Stout | 2 | 4924 | 2013 | 15000 || May Stout | 2 | 8000 | 2013 | 15000 || May Stout | 2 | 15000 | 2013 | 15000 || Noel Meyer | 1 | 13314 | 2013 | 13314 || Raphael Hull | 1 | -4000 | 2013 | 14000 || Raphael Hull | 1 | 14000 | 2013 | 14000 || Ursa George | 1 | 10865 | 2013 | 10865 ||-----------------|------------|--------|-------|------------|12 rows selected (0.284 seconds)

开窗案例举例

如何使用开窗函数去重

select * from (select *,row_number() over(partition by emp_mgr order by stat_date desc) rk from q1_sales) tmp where rk = 1;+-----------------+-----------------+----------------+------------+----------------+---------+| tmp.emp_name | tmp.emp_mgr | tmp.dealer_id | tmp.sales | tmp.stat_date | tmp.rk |+-----------------+-----------------+----------------+------------+----------------+---------+| Ferris Brown | Dan Brodi | 1 | 19745 | 2020-01-02 | 1 || Noel Meyer | Kari Phelps | 1 | 19745 | 2020-01-05 | 1 || Haviva Montoya | Mike Palomino | 2 | 9308 | 2020-01-03 | 1 || May Stout | Rich Hernandez | 3 | 9308 | 2020-01-05 | 1 |+-----------------+-----------------+----------------+------------+----------------+---------+4 rows selected (25.707 seconds)

如何使用开窗函数进行排名

select *,row_number() over(partition by dealer_id order by sales desc) rk from q1_sales;+--------------------+-------------------+---------------------+-----------------+---------------------+-----+| q1_sales.emp_name | q1_sales.emp_mgr | q1_sales.dealer_id | q1_sales.sales | q1_sales.stat_date | rk |+--------------------+-------------------+---------------------+-----------------+---------------------+-----+| Noel Meyer | Kari Phelps | 1 | 19745 | 2020-01-05 | 1 || Ferris Brown | Dan Brodi | 1 | 19745 | 2020-01-02 | 2 || Abel Kim | Rich Hernandez | 1 | 12369 | 2020-01-03 | 3 || Jack Salazar | Kari Phelps | 1 | 9710 | 2020-01-01 | 4 || Raphael Hull | Kari Phelps | 1 | 8227 | 2020-01-02 | 5 || Kameko French | Mike Palomino | 2 | 16233 | 2020-01-03 | 1 || Beverly Lang | Mike Palomino | 2 | 16233 | 2020-01-01 | 2 || Haviva Montoya | Mike Palomino | 2 | 9308 | 2020-01-03 | 3 || Ursa George | Rich Hernandez | 3 | 15427 | 2020-01-04 | 1 || May Stout | Rich Hernandez | 3 | 9308 | 2020-01-05 | 2 |+--------------------+-------------------+---------------------+-----------------+---------------------+-----+10 rows selected (23.38 seconds)

数仓增量数据合并

基于上述的排名和区中方法结合，可以实现数仓增量抽取的数据和历史数据合并去重。

你需要了解的全量表,增量表及拉链表

环比

数据准备

select * from temp_test12;create table if not exists temp_test12 (month string comment '月份'，shop string comment '店铺'，money string comment '营业额');insert into table temp_test12 (month,shop,money)values('2019-01','a',1),('2019-04','a',4),('2019-02','a',2),('2019-03','a',3),('2019-06','a',6),('2019-05','a',5),('2019-01','b',2),('2019-02','b',4),('2019-03','b',6),('2019-04','b',8),('2019-05','b',10),('2019-06','b',12);select * from temp_test12;+--------------------+-------------------+---------------------+| temp_test12.month | temp_test12.shop | temp_test12.money |+--------------------+-------------------+---------------------+| 2019-01 | a | 1 || 2019-04 | a | 4 || 2019-02 | a | 3 || 2019-03 | a | 4 || 2019-06 | a | 6 || 2019-05 | a | 5 || 2019-01 | b | 2 || 2019-02 | b | 4 || 2019-03 | b | 6 || 2019-04 | b | 8 || 2019-05 | b | 10 || 2019-06 | b | 12 |+--------------------+-------------------+--------------------+10 rows selected (23.38 seconds)

需求描述

查询店铺上个月的营业额，结果字段如下：
| 月份 | 商铺 | 本月营业额 | 上月营业额|

不使用开窗函数实现方案

实现这个需求我们需要先使用row_number()over按商铺分组，按月份排序得出这样一个结果：SELECT month,shop,money,ROW_NUMBER() OVER (PARTITION BY shop ORDER BY month) AS rnFROM temp_test12;结果：month shop money rn2019-01 a 1 12019-02 a 2 22019-03 a 3 32019-04 a 4 42019-05 a 5 52019-06 a 6 62019-01 b 2 12019-02 b 4 22019-03 b 6 32019-04 b 8 42019-05 b 10 52019-06 b 12 6然后进行偏移自关联，将每个商铺的每个月的营业额和上个月的关联在一起：WITH aAS (SELECT month,shop,MONEY,ROW_NUMBER() OVER (PARTITION BY shop ORDER BY month) AS rnFROM temp_test12)SELECT a1.month,a1.shop,a1.MONEY,nvl(a2.month, '2018-12') before_month --为了便于理解，这里加入上月的月份。如果上月没有的月份取为2018-12,nvl(a2.MONEY, 1) before_money --上月没有的营业额取为1FROM a a1 --代表本月LEFT JOIN a a2 --代表上月ON a1.shop = a2.shopAND a1.month = substr(add_months(CONCAT (a2.month,'-01'), 1), 1, 7) --增加月份的函数add_months中至少要传入年月日GROUP BY a1.month,a1.shop,a1.MONEY,nvl(a2.month, '2018-12'),nvl(a2.MONEY, 1);结果：a1.month a1.shop a1.money before_month before_money2019-01 a 1 2018-12 12019-02 a 2 2019-01 12019-03 a 3 2019-02 22019-04 a 4 2019-03 32019-05 a 5 2019-04 42019-06 a 6 2019-05 52019-01 b 2 2018-12 12019-02 b 4 2019-01 22019-03 b 6 2019-02 42019-04 b 8 2019-03 62019-05 b 10 2019-04 82019-06 b 12 2019-05 10

lag 开窗函数实现环比

SELECT month,shop,MONEY,LAG(MONEY, 1, 1) OVER ( --取分组内上一行的营业额，如果没有上一行则取1PARTITION BY shop ORDER BY month --按商铺分组，按月份排序) AS before_moneyFROM temp_test12;-- 结果集如下month shop money before_money2019-01 a 1 12019-02 a 2 12019-03 a 3 22019-04 a 4 32019-05 a 5 42019-06 a 6 52019-01 b 2 12019-02 b 4 22019-03 b 6 42019-04 b 8 62019-05 b 10 82019-06 b 12 10

lag 其他用法演示

SELECT month,shop,MONEY,LAG(MONEY, 1, 1) OVER (PARTITION BY shop ORDER BY month) AS before_money,LAG(MONEY, 1) OVER (PARTITION BY shop ORDER BY month) AS before_money --第三个参数不写的话，如果没有上一行值，默认取null,LAG(MONEY) OVER (PARTITION BY shop ORDER BY month) AS before_money --第二个参数不写默认为1，第三个参数不写的话，如果没有上一行值，默认取null，结果与上一列相同,LAG(MONEY, 2, 1) OVER (PARTITION BY shop ORDER BY month) AS before_2month_money --取两个月前的营业额FROM temp_test12;-- 结果集month shop money before_money before_money before_money before_2month_money2019-01 a 1 1 NULL NULL 12019-02 a 2 1 1 1 12019-03 a 3 2 2 2 12019-04 a 4 3 3 3 22019-05 a 5 4 4 4 32019-06 a 6 5 5 5 42019-01 b 2 1 NULL NULL 12019-02 b 4 2 2 2 12019-03 b 6 4 4 4 22019-04 b 8 6 6 6 42019-05 b 10 8 8 8 62019-06 b 12 10 10 10 8-- 解释说明：-- shop为a时，before_money指定了往上第1行的值，如果没有上一行值，默认取null，这里指定为1。-- a的第1行，往上1行值为NULL,指定第三个参数取1，不指定取null 。-- a的第2行，往上1行值为第1行营业额值，1。-- a的第6行，往上1行值为为第5行营业额值，5

lead 求下月营业额

lead(col,n,default)与lag相反，统计分组内往下第n行值。第一个参数为列名，第二个参数为往下第n行（可选，不填默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）。

新添一列每个商铺下个月的营业额，结果字段如下： 月份 商铺 本月营业额 下月营业额SELECT month,shop,MONEY,LEAD(MONEY, 1, 7) OVER (PARTITION BY shop ORDER BY month) AS after_money,LEAD(MONEY, 1) OVER (PARTITION BY shop ORDER BY month) AS after_money --第三个参数不写的话，如果没有下一行值，默认取null,LEAD(MONEY, 2, 7) OVER (PARTITION BY shop ORDER BY month) AS after_2month_money --取两个月后的营业额FROM temp_test12;结果：month shop money after_money after_money after_2month_money2019-01 a 1 2 2 32019-02 a 2 3 3 42019-03 a 3 4 4 52019-04 a 4 5 5 62019-05 a 5 6 6 72019-06 a 6 7 NULL 72019-01 b 2 4 4 62019-02 b 4 6 6 82019-03 b 6 8 8 102019-04 b 8 10 10 122019-05 b 10 12 12 72019-06 b 12 7 NULL 7解释说明：shop为a时，after_money指定了往下第1行的值，如果没有下一行值，默认取null，这里指定为1。a的第1行，往下1行值为第2行营业额值，2。a的第2行，往下1行值为第3行营业额值，4。a的第6行，往下1行值为NULL,指定第三个参数取7，不指定取null。

first_value(col)

用于取分组内排序后，截止到当前行，第一个col的值。

ELECT month,shop,MONEY,first_value(MONEY) OVER (PARTITION BY shop ORDER BY month) AS first_moneyFROM temp_test12;结果：month shop money first_money2019-01 a 1 12019-02 a 2 12019-03 a 3 12019-04 a 4 12019-05 a 5 12019-06 a 6 12019-01 b 2 22019-02 b 4 22019-03 b 6 22019-04 b 8 22019-05 b 10 22019-06 b 12 2解释说明：shop为a时，截止到每一行时，分组内的第一行值都是1。shop为b时，截止到每一行时，分组内的第一行值都是2。

last_value(col)

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

SELECT month,shop,MONEY,last_value(MONEY) OVER (PARTITION BY shop ORDER BY month) AS last_moneyFROM temp_test12;结果：month shop money last_money2019-01 a 1 12019-02 a 2 22019-03 a 3 32019-04 a 4 42019-05 a 5 52019-06 a 6 62019-01 b 2 22019-02 b 4 42019-03 b 6 62019-04 b 8 82019-05 b 10 102019-06 b 12 12解释说明：shop为a时，截止到每一行时，分组内的最后一行值都是该行本身。shop为b时，截止到每一行时，分组内的最后一行值都是该行本身。

连续登录

数据准备

源数据，文件中是以,号隔开的id,dateA,2018-09-04B,2018-09-04C,2018-09-04A,2018-09-05A,2018-09-05C,2018-09-05A,2018-09-06B,2018-09-06C,2018-09-06A,2018-09-04B,2018-09-04C,2018-09-04A,2018-09-05A,2018-09-05C,2018-09-05A,2018-09-06B,2018-09-06C,2018-09-06

展现连续登陆两天的用户信息

select*from(selectid ,date,lead(date,1,-1) over(partition by id order by date desc ) as date1 -- 按照用户分组，登录时间降序排序，获取上一次登录日期from tb_use agroup by id,date -- 去重当日重复登录，) as bwhere date_sub(cast(b.date as date),1)=cast(b.date1 as date); -- 判定当前登录日期的上一天是否与上一次登录日期一致，一致则判定为连续登录结果：b.id b.date b.date1A 2018-09-06 2018-09-05A 2018-09-05 2018-09-04C 2018-09-06 2018-09-05C 2018-09-05 2018-09-04

统计连续登陆两天的用户个数

(n天就只需要把lead(date,2,-1)中的2改成n-1并且把date_sub(cast(b.date as date),2)中的2改成n-1)

selectcount(distinct b.id) as c1from(select id ,date,lead(date,1,-1) over(partition by id order by date desc ) as date1from tb_use agroup by id,date) as bwhere date_sub(cast(b.date as date),1)=cast(b.date1 as date);结果：c12

特说说明：上文指出了连续登录2天的场景，针对其他连续登录场景，假设连续登录n天，可将lead(date,1,-1)中的1改成n-1，date_sub(cast(b.date as date),1)中的1改成n-1。

占比、同比、环比计算（lag函数，lead函数）

数据准备

-- 创建表并插入数据CREATE TABLE saleorder (order_id int ,order_time date ,order_num int)-- 插入测试数据INSERT INTO saleorder VALUES(1, '2020-04-20', 420),(2, '2020-04-04', 800),(3, '2020-03-28', 500),(4, '2020-03-13', 100),(5, '2020-02-27', 300),(6, '2020-01-07', 450),(7, '2019-04-07', 800),(8, '2019-03-15', 1200),(9, '2019-02-17', 200),(10, '2019-02-07', 600),(11, '2019-01-13', 300);select * from saleorder;+---------------------+-----------------------+----------------------+| saleorder.order_id | saleorder.order_time | saleorder.order_num |+---------------------+-----------------------+----------------------+| 1 | 2020-04-20 | 420 || 2 | 2020-04-04 | 800 || 3 | 2020-03-28 | 500 || 4 | 2020-03-13 | 100 || 5 | 2020-02-27 | 300 || 6 | 2020-01-07 | 450 || 7 | 2019-04-07 | 800 || 8 | 2019-03-15 | 1200 || 9 | 2019-02-17 | 200 || 10 | 2019-02-07 | 600 || 11 | 2019-01-13 | 300 |+---------------------+-----------------------+----------------------+11 rows selected (0.331 seconds)

使用窗口函数实现占比

SELECTorder_month,num, -- 月销量total, -- 年销量round( num / total, 2 ) AS ratio -- 月销量占年销量比FROM(selectsubstr(order_time, 1, 7) as order_month, --查询月份sum(order_num) over (partition by substr(order_time, 1, 7)) as num, --根据月份分组，统计月销量sum( order_num ) over ( PARTITION BY substr( order_time, 1, 4 ) ) total, --根据年分组，统计年销量row_number() over (partition by substr(order_time, 1, 7)) as rkfrom saleorder) tempwhere rk = 1;+--------------+-------+--------+--------+| order_month | num | total | ratio |+--------------+-------+--------+--------+| 2019-04 | 800 | 3100 | 0.26 || 2019-03 | 1200 | 3100 | 0.39 || 2019-02 | 800 | 3100 | 0.26 || 2019-01 | 300 | 3100 | 0.1 || 2020-04 | 1220 | 2570 | 0.47 || 2020-03 | 600 | 2570 | 0.23 || 2020-02 | 300 | 2570 | 0.12 || 2020-01 | 450 | 2570 | 0.18 |+--------------+-------+--------+--------+8 rows selected (49.433 seconds)

使用窗口函数实现环比计算

什么是环比、什么是同比？
与上年度数据对比称”同比”，与上月数据对比称”环比”。

相关公式如下:
同比增长率计算公式：(当年值-上年值)/上年值x100%

环比增长率计算公式：(当月值-上月值)/上月值x100%

-- 环比增长率selectnow_month,now_num,last_num,concat( nvl ( round( ( now_num - last_num ) / last_num * 100, 2 ), 0 ), "%" )FROM(-- 2、查询上月销量selectnow_month,now_num,lag( t1.now_num, 1 ) over (order by t1.now_month ) as last_numfrom(-- 1、按月统计销量selectsubstr(order_time, 1, 7) as now_month,sum(order_num) as now_numfrom saleordergroup bysubstr(order_time, 1, 7)) t1) t2;+------------+----------+-----------+----------+| now_month | now_num | last_num | _c3 |+------------+----------+-----------+----------+| 2019-01 | 300 | NULL | 0.0% || 2019-02 | 800 | 300 | 166.67% || 2019-03 | 1200 | 800 | 50.0% || 2019-04 | 800 | 1200 | -33.33% || 2020-01 | 450 | 800 | -43.75% || 2020-02 | 300 | 450 | -33.33% || 2020-03 | 600 | 300 | 100.0% || 2020-04 | 1220 | 600 | 103.33% |+------------+----------+-----------+----------+8 rows selected (50.521 seconds)

— 同比增长率计算公式

同比的话，如果每个月都齐全，都有数据lag(num,12)就ok.。我们的例子中只有19年和20年1-4月份的数据。这种特殊情况应该如何处理？

SELECTt1.now_month,nvl ( now_num, 0 ) AS now_num,nvl ( last_num, 0 ) AS last_num,nvl ( round( ( now_num - last_num ) / last_num, 2 ), 0 ) AS ratioFROM(SELECTDATE_FORMAT( order_time, 'yyyy-MM' ) AS now_month,sum( order_num ) AS now_numFROMsaleorderGROUP BYDATE_FORMAT( order_time, 'yyyy-MM' )) t1LEFT JOIN(SELECTDATE_FORMAT( DATE_ADD( order_time, 365 ), 'yyyy-MM' ) AS now_month,sum( order_num ) AS last_numFROMsaleorderGROUP BYDATE_FORMAT( DATE_ADD( order_time, 365 ), 'yyyy-MM' )) AS t2 ON t1.now_month = t2.now_month;+---------------+----------+-----------+--------+| t1.now_month | now_num | last_num | ratio |+---------------+----------+-----------+--------+| 2019-01 | 300 | 0 | 0.0 || 2019-02 | 800 | 0 | 0.0 || 2019-03 | 1200 | 0 | 0.0 || 2019-04 | 800 | 0 | 0.0 || 2020-01 | 450 | 300 | 0.5 || 2020-02 | 300 | 800 | -0.63 || 2020-03 | 600 | 1200 | -0.5 || 2020-04 | 1220 | 800 | 0.53 |+---------------+----------+-----------+--------+8 rows selected (76.929 seconds)

其他案例

-- 建表CREATE TABLE order_info(name string,orderdate string,cost string);-- 数据加载INSERT INTO table order_info (name,orderdate,cost) VALUE ('jack','2020-01-01','10'),('tony','2020-01-02','15'),('jack','2020-02-03','23'),('tony','2020-01-04','29'),('jack','2020-01-05','46'),('jack','2020-04-06','42'),('tony','2020-01-07','50'),('jack','2020-01-08','55'),('mart','2020-04-08','62'),('mart','2020-04-09','68'),('neil','2020-05-10','12'),('mart','2020-04-11','75'),('neil','2020-06-12','80'),('mart','2020-04-13','94');SELECT name,orderdate,cost, --当前window内，当前行的前一行到后一行 金额总和sum(cast(cost AS INT)) over(PARTITION BY nameORDER BY orderdate DESC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS precedingFollow, --当前window内，当前行到最后行的金额总和sum(cast(cost AS INT)) over(PARTITION BY nameORDER BY orderdate DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS currentFollow, --当前window内，按照时间进行排序row_number() OVER(PARTITION BY nameORDER BY orderdate DESC) AS rank,--用户上次购买的时间lag(orderdate,1,'查无结果') over(PARTITION BY nameORDER BY orderdate) AS lastTime,--用户下一次购买的时间lead(orderdate,1,'查无结果') over(PARTITION BY nameORDER BY orderdate)AS nextTime,--用户上次购物金额lag(cost,1,'查无结果')over(PARTITION BY nameORDER BY orderdate) AS lastCost,--用户下次购物金额lead(cost,1,'查无结果') OVER (PARTITION BY nameORDER BY orderdate) AS nextCost,--用户上一次+这次的购物金额sum(cast(cost AS INT)) over(PARTITION BY nameORDER BY orderdate ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS lastCurrentCost,--用户每月购物金额sum(cast(cost AS INT)) over(PARTITION BY name,month(orderdate)ORDER BY month(orderdate)) AS monthCost,--用户当月单词消费最大值max(cast(cost AS INT)) over(PARTITION BY name,month(orderdate)ORDER BY orderdate) AS monthMaxCost,--用户当月单词消费最小值min(cast(cost AS INT)) over(PARTITION BY name,month(orderdate)ORDER BY orderdate) as monthMinCostFROM TEST.COSTITEM

间隔，最近两次间隔，登录间隔，出院间隔等等

selectuser_name,age,in_hosp,out_hosp,datediff(in_hosp,LAG(out_hosp,1,in_hosp) OVER(PARTITION BY user_name ORDER BY out_hosp asc)) as daysfrom t_hosp;

扩展

一些优化思想

有时候，一个 SELECT 语句中包含多个窗口函数，它们的窗口定义（OVER子句）可能相同、也可能不同。显然，对于相同的窗口，完全没必要再做一次分区和排序，我们可以将它们合并成一个 Window 算子。

那如何利用一次排序计算多个窗口函数呢？某些情况下，这是可能的。下面的例子如下：

ROW_NUMBER() OVER (PARTITION BY dealer_id ORDER BY sales) AS rank,

AVG(sales) OVER (PARTITION BY dealer_id) AS avgsales …

虽然这 2 个窗口并非完全一致，但是AVG(sales)不关心分区内的顺序，完全可以复用ROW_NUMBER()的窗口，这里提供了一种方式，尽一切可能利用能够复用的机会。

窗口函数 VS. 聚合函数

从聚合这个意义上出发，似乎窗口函数和 Group By 聚合函数都能做到同样的事情。但是，它们之间的相似点也仅限于此了！这其中的关键区别在于：
窗口函数仅仅只会将结果附加到当前的结果上，它不会对已有的行或列做任何修改。而 Group By 的做法完全不同：对于各个 Group 它仅仅会保留一行聚合结果。

有的读者可能会问，加了窗口函数之后返回结果的顺序明显发生了变化，这不算一种修改吗？因为 SQL 及关系代数都是以 multi-set 为基础定义的，结果集本身并没有顺序可言，ORDER BY 仅仅是最终呈现结果的顺序。

另一方面，从逻辑语义上说，SELECT 语句的各个部分可以看作是按以下顺序“执行”的：

注意到窗口函数的求值仅仅位于 ORDER BY 之前，而位于 SQL 的绝大部分之后。这也和窗口函数只附加、不修改的语义是呼应的，结果集在此时已经确定好了，再依次计算窗口函数。

参考：最全面的Hive开窗函数讲解和实战指南（必看）