索取营销诊断书
021-80394720QQ
    您的位置：上海毫米网络优化公司 > 网站优化分享 >
    相关推荐recommended
    【必看】最全开窗函数讲解和实战指南
    作者：mmseoamin日期：2024-01-25

    窗口函数（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张

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

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

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

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

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

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

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

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

    2）按成绩排名

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

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

     

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

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

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

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

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

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

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

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

    那什么是窗口函数呢？

    窗口函数出现在 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 表示将各个分区内的数据按 排序字段 进行排序

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

    window_expression 用于确定窗边界：

    名词含义
    preceding往前
    following往后
    current row当前行
    unbounded起点
    unbounded preceding从前面的起点
    unbounded following到后面的终点

    窗口边界使用详解

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

    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)

      窗口聚合函数有哪些？

      窗口函数返回类型函数功能说明
      AVG()参数类型为DECIMAL的返回类型为DECIMAL，其他为DOUBLEAVG 窗口函数返回输入表达式值的平均值，忽略 NULL 值。
      COUNT()BIGINTCOUNT 窗口函数计算输入行数。 COUNT(*) 计算目标表中的所有行，包括Null值；COUNT(expression) 计算特定列或表达式中具有非 NULL 值的行数。
      MAX()与传参类型一致MAX窗口函数返回表达式在所有输入值中的最大值，忽略 NULL 值。
      MIN()与传参类型一致MIN窗口函数返回表达式在所有输入值中的最小值，忽略 NULL 值。
      SUM()针对传参类型为DECIMAL的，返回类型一致；除此之外的浮点型为DOUBLE；传参类型为整数类型的，返回类型为BIGINTSUM窗口函数返回所有输入值的表达式总和，忽略 NULL 值。
      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;

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

      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;

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

       

      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;
       

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

       

      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;
       

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

      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;
       

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

      排名窗口函数
       

      窗口函数返回类型函数功能说明
      ROW_NUMBER()BIGINT根据具体的分组和排序，为每行数据生成一个起始值等于1的唯一序列数
      RANK()BIGINT对组中的数据进行排名，如果名次相同，则排名也相同，但是下一个名次的排名序号会出现不连续。
      DENSE_RANK() dense是稠密的意思，可以引申记忆BIGINTdense_rank函数的功能与rank函数类似，dense_rank函数在生成序号时是连续的，而rank函数生成的序号有可能不连续。当出现名次相同时，则排名序号也相同。而下一个排名的序号与上一个排名序号是连续的。
      PERCENT_RANK()DOUBLE计算给定行的百分比排名。可以用来计算超过了百分之多少的人;排名计算公式为：(当前行的rank值-1)/(分组内的总行数-1)
      CUME_DIST()DOUBLE计算某个窗口或分区中某个值的累积分布。假定升序排序，则使用以下公式确定累积分布：小于等于当前值x的行数 / 窗口或partition分区内的总行数。其中，x 等于 order by 子句中指定的列的当前行中的值
      NTILE()INT已排序的行划分为大小尽可能相等的指定数量的排名的组，并返回给定行所在的组的排名。如果切片不均匀，默认增加第一个切片的分布，不支持ROWS BETWEEN
       

      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) rk04
from q1_sales;
       

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

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

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

      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 ) rk09
from q1_sales;
       

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

      值窗口函数
       

      窗口函数返回类型函数功能说明
      LAG()与lead相反，用于统计窗口内往上第n行值。第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL.
      LEAD()用于统计窗口内往下第n行值。第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL.
      FIRST_VALUE取分组内排序后，截止到当前行，第一个值
      LAST_VALUE取分组内排序后，截止到当前行，最后一个值
       

      注意: 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)
       

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

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

      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)
       

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

      数仓增量数据合并
       

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

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

      环比
       

      数据准备
       

      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 rn
FROM temp_test12;
结果：
month shop money rn
2019-01 a 1 1
2019-02 a 2 2
2019-03 a 3 3
2019-04 a 4 4
2019-05 a 5 5
2019-06 a 6 6
2019-01 b 2 1
2019-02 b 4 2
2019-03 b 6 3
2019-04 b 8 4
2019-05 b 10 5
2019-06 b 12 6
然后进行偏移自关联，将每个商铺的每个月的营业额和上个月的关联在一起：
WITH a
AS (
SELECT month
,shop
,MONEY
,ROW_NUMBER() OVER (
PARTITION BY shop ORDER BY month
) AS rn
FROM temp_test12
)
SELECT a1.month
,a1.shop
,a1.MONEY
,nvl(a2.month, '2018-12') before_month --为了便于理解，这里加入上月的月份。如果上月没有的月份取为2018-12
,nvl(a2.MONEY, 1) before_money --上月没有的营业额取为1
FROM a a1 --代表本月
LEFT JOIN a a2 --代表上月
ON a1.shop = a2.shop
AND 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_money
2019-01 a 1 2018-12 1
2019-02 a 2 2019-01 1
2019-03 a 3 2019-02 2
2019-04 a 4 2019-03 3
2019-05 a 5 2019-04 4
2019-06 a 6 2019-05 5
2019-01 b 2 2018-12 1
2019-02 b 4 2019-01 2
2019-03 b 6 2019-02 4
2019-04 b 8 2019-03 6
2019-05 b 10 2019-04 8
2019-06 b 12 2019-05 10
       

      lag 开窗函数实现环比
       

      SELECT month
,shop
,MONEY
,LAG(MONEY, 1, 1) OVER ( --取分组内上一行的营业额，如果没有上一行则取1
PARTITION BY shop ORDER BY month --按商铺分组，按月份排序
) AS before_money
FROM temp_test12;
-- 结果集如下
month shop money before_money
2019-01 a 1 1
2019-02 a 2 1
2019-03 a 3 2
2019-04 a 4 3
2019-05 a 5 4
2019-06 a 6 5
2019-01 b 2 1
2019-02 b 4 2
2019-03 b 6 4
2019-04 b 8 6
2019-05 b 10 8
2019-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_money
2019-01 a 1 1 NULL NULL 1
2019-02 a 2 1 1 1 1
2019-03 a 3 2 2 2 1
2019-04 a 4 3 3 3 2
2019-05 a 5 4 4 4 3
2019-06 a 6 5 5 5 4
2019-01 b 2 1 NULL NULL 1
2019-02 b 4 2 2 2 1
2019-03 b 6 4 4 4 2
2019-04 b 8 6 6 6 4
2019-05 b 10 8 8 8 6
2019-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_money
2019-01 a 1 2 2 3
2019-02 a 2 3 3 4
2019-03 a 3 4 4 5
2019-04 a 4 5 5 6
2019-05 a 5 6 6 7
2019-06 a 6 7 NULL 7
2019-01 b 2 4 4 6
2019-02 b 4 6 6 8
2019-03 b 6 8 8 10
2019-04 b 8 10 10 12
2019-05 b 10 12 12 7
2019-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_money
FROM temp_test12;
结果：
month shop money first_money
2019-01 a 1 1
2019-02 a 2 1
2019-03 a 3 1
2019-04 a 4 1
2019-05 a 5 1
2019-06 a 6 1
2019-01 b 2 2
2019-02 b 4 2
2019-03 b 6 2
2019-04 b 8 2
2019-05 b 10 2
2019-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_money
FROM temp_test12;
结果：
month shop money last_money
2019-01 a 1 1
2019-02 a 2 2
2019-03 a 3 3
2019-04 a 4 4
2019-05 a 5 5
2019-06 a 6 6
2019-01 b 2 2
2019-02 b 4 4
2019-03 b 6 6
2019-04 b 8 8
2019-05 b 10 10
2019-06 b 12 12
解释说明：
shop为a时，截止到每一行时，分组内的最后一行值都是该行本身。
shop为b时，截止到每一行时，分组内的最后一行值都是该行本身。
       

      连续登录
       

      数据准备
       

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

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

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

      select
count(distinct b.id) as c1
from
(
select id ,date,
lead(date,1,-1) over(partition by id order by date desc ) as date1
from tb_use a
group by id,date
) as b
where date_sub(cast(b.date as date),1)=cast(b.date1 as date);
结果：
c1
2
       

      特说说明：上文指出了连续登录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)
       

      使用窗口函数实现占比
       

      SELECT
order_month,
num, -- 月销量
total, -- 年销量
round( num / total, 2 ) AS ratio -- 月销量占年销量比
FROM
(
select
substr(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 rk
from saleorder
) temp
where 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)
       

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

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

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

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

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

      -- 环比增长率
select
now_month,
now_num,
last_num,
concat( nvl ( round( ( now_num - last_num ) / last_num * 100, 2 ), 0 ), "%" )
FROM
(
-- 2、查询上月销量
select
now_month,
now_num,
lag( t1.now_num, 1 ) over (order by t1.now_month ) as last_num
from
(
-- 1、按月统计销量
select
substr(order_time, 1, 7) as now_month,
sum(order_num) as now_num
from saleorder
group by
substr(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月份的数据。这种特殊情况应该如何处理？
       

      SELECT
t1.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 ratio
FROM
(
SELECT
DATE_FORMAT( order_time, 'yyyy-MM' ) AS now_month,
sum( order_num ) AS now_num
FROM
saleorder
GROUP BY
DATE_FORMAT( order_time, 'yyyy-MM' )
) t1
LEFT JOIN
(
SELECT
DATE_FORMAT( DATE_ADD( order_time, 365 ), 'yyyy-MM' ) AS now_month,
sum( order_num ) AS last_num
FROM
saleorder
GROUP BY
DATE_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)
       

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

      其他案例
       

       

      -- 建表
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 name
ORDER BY orderdate DESC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS precedingFollow, --当前window内，当前行到最后行的金额总和
sum(cast(cost AS INT)) over(PARTITION BY name
ORDER BY orderdate DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS currentFollow, --当前window内，按照时间进行排序
row_number() OVER(PARTITION BY name
ORDER BY orderdate DESC) AS rank,--用户上次购买的时间
lag(orderdate,1,'查无结果') over(PARTITION BY name
ORDER BY orderdate) AS lastTime,--用户下一次购买的时间
lead(orderdate,1,'查无结果') over(PARTITION BY name
ORDER BY orderdate)AS nextTime,--用户上次购物金额
lag(cost,1,'查无结果')over(PARTITION BY name
ORDER BY orderdate) AS lastCost,--用户下次购物金额
lead(cost,1,'查无结果') OVER (PARTITION BY name
ORDER BY orderdate) AS nextCost,--用户上一次+这次的购物金额
sum(cast(cost AS INT)) over(PARTITION BY name
ORDER 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 monthMinCost
FROM TEST.COSTITEM
       

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

      select
user_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 days
from t_hosp;
       

      扩展
       

      一些优化思想
       

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

      有时候，一个 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 语句的各个部分可以看作是按以下顺序“执行”的：
       

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

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

      参考：最全面的Hive开窗函数讲解和实战指南（必看）
       
     上一篇：IDEA下SpringBoot指定配置文件启动项目
     下一篇：【Python 爬虫脚本】Python爬取歌曲
    网站优化运营
     
    整站seo优化
     
    网络营销运营
     
    成功案例
     
    品牌创意网站
     
    营销型网站
     
    小程序
     
    微信小程序
     
    微信公众号
     
    百度熊掌号
     
    网站优化分享
     
    关于我们
     
    公司概况
     
    公司新闻
     
    公司文化
     
    结构体系
     
    企业资质
     
    加入我们
     
    关注我们
    扫描二维码
    获取更多信息
    精彩一手掌握
    Copyright © 2014-2019 seo,上海网站优化_网络制作_网站seo排名_网站建设推广_上海网络公司 版权所有
沪ICP备19022315号