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

最新推荐文章于 2024-07-30 22:55:11 发布
最新推荐文章于 2024-07-30 22:55:11 发布
阅读量5.1k 收藏 125
点赞数 17
文章标签： 数据库 sql
本文为博主原创文章，未经博主允许不得转载。如果对你有帮助，请记得点赞支持，谢谢！
本文链接：https://blog.csdn.net/ZYC88888/article/details/129676229
版权
本文详细介绍了SQL中的窗口函数，包括窗口函数的作用、分类和用法。通过实例展示了如何使用窗口函数进行排名、去重、统计和分析数据，如RANK、DENSE_RANK、ROW_NUMBER、SUM、AVG等。此外，还讨论了窗口函数与GROUPBY的区别，并提供了优化思考和实际应用场景，如计算环比、同比增长率等。
摘要由CSDN通过智能技术生成

窗口函数（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 ranking
from 班级表

我们来解释下这个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 ranking
from 班级表

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

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

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

那什么是窗口函数呢？

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

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

[<window_expression>])</window_expression>

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 用于确定窗边界：

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

窗口边界使用详解

  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;


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;
 

 

排名窗口函数
 

窗口函数返回类型函数功能说明
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;
 

 


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;
 

 


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;
 

 

值窗口函数
 

窗口函数返回类型函数功能说明
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, <code>year</code>, last_value(sales) over (partition by emp_name order by <code>year</code>) as last_sale from emp_sales where <code>year</code> = 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 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 <code>saleorder</code> (

<code>order_id</code> int ,

<code>order_time</code> date ,

<code>order_num</code> int

)

-- 插入测试数据

INSERT INTO <code>saleorder</code> 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)
 

 

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

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

相关公式如下:
 同比增长率计算公式：(当年值-上年值)/上年值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)
 

 

其他案例
 

 

-- 建表

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;
 

扩展
 

一些优化思想
 

 

有时候，一个 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开窗函数讲解和实战指南（必看）

                

        

        

    

  


    

      

        

            

              
                
                  ZhaoYingChao88
                
              
            

            

                关注
              
            

        

        

          
    
            
  • 
              
                
                
                
                
                    17
                
              
              
    点赞
    
            
    • 
            
  • 
              
                
                
                
              
              
    踩
    
            
    • 
            
  • 
              
                
                
                
                
                    125
                
              
              
    
                
    
                  
                    收藏
                  
                
    
              
    
              
    
                
    
                  觉得还不错?
                  
                    一键收藏
                  
                 
                
    
              
    
            
    • 
                
  • 
                  
                    打赏
                    
                  
                  
    打赏
    
                
    • 
          
  • 
            
    
              
              
            
    
              
              
              
                  4
              
            
            
    评论
    
          
    • 
          
  • 
          
    • 
          
  • 
            
              
            
              
    
            
              
                
            
    
          
    • 
        

      

      










                



	

		

			

				
					
python实战应用讲解-【语法基础篇】初识Python（附示例代码）

				
			

			

				

					getusushu的博客
				

				

					02-18
					
					403
					
				

			

		

		

			
				
1989年圣诞节期间，在阿姆斯特丹，Guido为了打发圣诞节的无趣， 决心开发一个新的脚本解释程序，作为ABC 语言的一种继承,它就是Python。Python发展至今，已成为一门解释型、面向对象、动态数据类型的高级程序设计语言， 它拥有活跃的社区支持，丰富的工具包帮助我们完成编程。从云端、客户端，到物联网终端，再到现在的人工智能，python无处不在。

			
		

	



                

              
                



	

		

			

				
					
【MySQL】窗口函数详解（概念+练习+实战）

				
			

			

				

					CoderSharry的博客
				

				

					12-19
					
					1万+
					
				

			

		

		

			
				
1. Mysql 窗口函数相关概念：其中，语法结构是重点；
2. 常见窗口函数：聚合窗口函数、专用窗口函数（排序函数、分布函数等）
3. 相关习题与练习
4. 一个实际的练习供大佬们参考
5. 窗口函数“窗口”的体现，`ROWS BETWEEN`子句相关补充知识点

			
		

	



                


  
              

                


	

		

			

				
					
Hive  开窗函数详解

				
			

			

				

					程序员学习圈
				

				

					08-28
					
					1681
					
				

			

		

		

			
				
1.Hive 数据存储

  Hive支持的存储数的格式主要有：TEXTFILE 、SEQUENCEFILE、ORC、PARQUET。TEXTFILE和SEQUENCEFILE的存储格式都是基于行存储的；ORC和PARQUET是基于列式存储的。

1.1 列式存储和行式存储基本介绍

 • 行存储的特点：查询满足条件的一整行数据的时候，列存储则需要去每个聚集的字段找到对应的每个列的值，行...

			
		

	





	

		

			

				
					
窗口函数-详细讲解分析

					
最新发布

				
			

			

				

					qq_62757927的博客
				

				

					07-30
					
					3139
					
				

			

		

		

			
				
窗口函数是一种SQL函数，非常适合于数据分析，其最大的特点就是：输入值是从SELECT语句的结果集中的一行或者多行的"窗口"中获取的，也可以理解为窗口有大有小（行数有多有少）。通过OVER子句，窗口函数与其他的SQL函数有所区别，如果函数具有OVER子句，则它是窗口函数。如果它缺少了OVER子句，则他就是个普通的聚合函数。窗口函数可以简单地解释为类似于聚合函数的计算函数，但是通过GROUP BY子句组合的常规聚合会隐去正在聚合的各个行，最终输出称为一行。

			
		

	



		

			
		



	

		

			

				
					
mysql开窗函数有哪些_mysql开窗函数

				
			

			

				

					weixin_29955689的博客
				

				

					01-20
					
					2438
					
				

			

		

		

			
				
开窗函数：它可以理解为记录集合，开窗函数也就是在满足某种条件的记录集合上执行的特殊函数。对于每条记录都要在此窗口内执行函数，有的函数随着记录不同，窗口大小都是固定的，这种属于静态窗口；有的函数则相反，不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口。开窗函数的本质还是聚合运算，只不过它更具灵活性，它对数据的每一行，都使用与该行相关的行进行计算并返回计算结果。开窗函数和普通聚合函数的区别聚合...

			
		

	





	

		

			

				
					
开窗函数

				
			

			

				

					dhv98472的博客
				

				

					06-02
					
					107
					
				

			

		

		

			
				
·格式
函数名(列) OVER(选项)
·作用
一般的聚集函数返回值是单值，而开窗函数返回值是多值，可能是一列的形式
·示例

select S#,AVG(score) 平均成绩,(RANK() over (order by AVG(score))) rank
    from sc
    group by s#

结果：


转载于:https://www.c...

			
		

	





	

		

			

				
					
开窗函数学习整理

				
			

			

				

					wh502574188的博客
				

				

					09-02
					
					2874
					
				

			

		

		

			
				
一、开窗函数是什么
1、定义：
普通的聚合函数聚合的行集是组,开窗函数聚合的行集是窗口。因此,普通的聚合函数每组(Group by)只返回一个值，而开窗函数则可为窗口中的每行都返回一个值。简单理解，就是对查询的结果多出一列，这一列可以是聚合值，也可以是排序值。在ISO SQL规定了这样的函数为开窗函数，在 Oracle中则被称为分析函数，而在DB2中则被称为OLAP函数。
（聚合函数是对一组值执行计算并返回单一的值的函数。）
2、特征：
区别于聚合函数，开窗函数在聚合函数后增加了OVER 关键字。SQL允许

			
		

	





	

		

			

				
					
各种开窗函数

				
			

			

				

					weixin_49539546的博客
				

				

					03-20
					
					651
					
				

			

		

		

			
				
1 sum(sal)over()

求和  partition by order by 可以省略



2 avg(sal)over()

求平均 partition by order by 可以省略



3 count()over()

计数 partition by order by 可以省略



4 row_number()over( order by sal)

排序 partition by 可以省略 order by 不可以省略 



5 rank( )over(or...

			
		

	





	

		

			

				
					
web安全攻防渗透测试实战指南

				
			

			

				

					jhf223344的博客
				

				

					04-05
					
					9002
					
				

			

		

		

			
				
1. Nmap的基本

Nmap + ip 6+ ip

Nmap -A 开启操作系统识别和版本识别功能

– T（0-6档） 设置扫描的速度 一般设置T4 过快容易被发现

-v 显示信息的级别，-vv显示更详细的信息

192.168.1.1/24 扫描C段 192.168.11 -254 =上

nmap -A -T4 -v -iL ~/targets.txt (iL表示要扫描的目标位于一个文档中)

   --------------- 192.168.1.1/24 --exclude 19...

			
		

	





	

		

			

				
					
Web安全攻防渗透测试实战指南笔记 三

				
			

			

				

					Ren59421的博客
				

				

					04-05
					
					7981
					
				

			

		

		

			
				
第二章

2.1 在Linux系统中安装LANMP

LANMP是Linux下Apache、Nginx、MySQL和PHP的应用环境，本节演示的是WDLinux的一款集成的安装包。

首先，下载需要的安装包，命令如下所示。

wegt http://dl.wdlinux.cn/files/lanmp_v3.tar.gz

下载完成后进行解压，解压文件的命令为tar zxvf lanmp_v3.tar.gz，运行环境如下图所示。

     

输入sh lanmp.sh命令运行LANMP，这时程...

			
		

	





	

		

			

				
					
Excel VAB编程实战宝典

				
			

			

				

					03-16
				

			

		

		

			
				
4. **函数与过程**：介绍Sub过程和Function函数，学习参数传递，理解ByVal和ByRef的区别，以及如何自定义函数和过程。  5. **错误处理**：学习On Error语句，了解如何进行错误捕获和处理，提高程序的健壮性。  **...

			
		

	





	

		

			

				
					
SQL SERVER 开窗函数简介

					
热门推荐

				
			

			

				

					liu_1983的专栏
				

				

					02-17
					
					1万+
					
				

			

		

		

			
				
在SQL SERVER 2005/2008支持两种排名开窗函数和聚集开窗函数。
以SQL SERVER中分面页为例，按时间顺序列出定单号。
WITH OrderInfo AS 
(
 SELECT ROW_NUMBER() OVER(ORDER BY OrderDate) AS Number,
 OrderID,CustomerID, EmployeeID,OrderDate FROM

			
		

	





	

		

			

				
					
开窗函数——

				
			

			

				

					lzpgala的博客
				

				

					03-08
					
					401
					
				

			

		

		

			
				
--开窗函数格式： 函数名(列) OVER(选项)--partition by--order by     --ORDER BY 字段名 RANGE|ROWS BETWEEN 边界规则1 AND 边界规则2--详细转自：--http://www.mamicode.com/info-detail-1774214.html...

			
		

	





	

		

			

				
					
窗口函数

				
			

			

				

					root1994的博客
				

				

					06-23
					
					200
					
				

			

		

		

			
				
窗口函数
not in
dwd_user_members
dm_member_info
新增会员
select
pl,
browser,
co
count(distinct mid)
from dwd_user_members a
left join dm_member_info b on b.mid=a.mid
where b.mid is null
;
Not in 这个是性能不好的,影响执行...

			
		

	





	

		

			

				
					
开窗函数及其作用、范式

				
			

			

				

					czh8720130的博客
				

				

					01-09
					
					6892
					
				

			

		

		

			
				
1.  三种排序都可以全部排序也都可以组内排序, 主要看需求2. 三种排序的区别不重复排名123rank()重复且跳过113重复不跳过112。

			
		

	





	

		

			

				
					
SQL-开窗函数

				
			

			

				

					cxu123321的博客
				

				

					07-05
					
					1108
					
				

			

		

		

			
				
SQL开窗函数

开窗函数：在开窗函数出现之前存在着很多用 SQL 语句很难解决的问题，很多都要通过复杂的相关子查询或者存储过程来完成。
为了解决这些问题，在 2003 年 ISO SQL 标准加入了开窗函数，开窗函数的使用使得这些经典的难题可以被轻松的解决。目前在 MSSQLServer、Oracle、DB2 等主流数据库中都提供了对开窗函数的支持，不过非常遗憾的是 MYSQL 暂时还未对开窗函...

			
		

	





	

		

			

				
					
开窗函数的详细的介绍

				
			

			

				

					Mr_xiaolang的博客
				

				

					04-10
					
					1855
					
				

			

		

		

			
				
开窗函数的保姆级介绍，以及详情的范围控制函数的应用

			
		

	





	

		

			

				
					
mysql开窗函数用法

				
			

			

				

					09-06
				

			

		

		

			
				
MySQL开窗函数是一种用于在查询结果集中执行聚合计算的函数。它可以对分组数据进行排序、排名、累计求和等操作。以下是MySQL开窗函数的用法：

1. ROW_NUMBER()：给每一行分配一个唯一的整数值，表示其在分组或排序中的位置。

示例：
SELECT col1, col2, ROW_NUMBER() OVER (ORDER BY col1) AS row_num
FROM table;

2. RANK()：为每一行分配一个排名值，相同数值的行具有相同的排名，下一个排名是跳过相同排名数量后的下一个整数。

示例：
SELECT col1, col2, RANK() OVER (ORDER BY col1) AS rank_num
FROM table;

3. DENSE_RANK()：为每一行分配一个密集排名值，相同数值的行具有相同的密集排名，下一个密集排名不会跳过相同排名数量。

示例：
SELECT col1, col2, DENSE_RANK() OVER (ORDER BY col1) AS dense_rank_num
FROM table;

4. NTILE(x)：将结果集分成x个桶，并为每个桶分配一个编号（从1到x）。

示例：
SELECT col1, col2, NTILE(4) OVER (ORDER BY col1) AS bucket_num
FROM table;

5. LAG()和LEAD()：分别用于获取当前行前一行和后一行的值。

示例：
SELECT col1, col2, LAG(col1) OVER (ORDER BY col1) AS previous_value
FROM table;

这些是MySQL开窗函数的一些常见用法，它们可以在SELECT语句中与OVER子句结合使用来实现更复杂的查询和分析操作。请注意，开窗函数只能在MySQL 8.0版本及以上使用。

			
		

	



              




          




        



    





    



      

      

        
      

      





	

		
评论 4

	

	
	




  

    

      添加红包
      
    

    

      

        
        

          
          
        

        
请填写红包祝福语或标题

      

      

        
        

          
          个
        

        
红包个数最小为10个

      

      

        
        

          
          元
        

        
红包金额最低5元

      

      

        
        

          当前余额3.43元
          前往充值 >
        

      

      

        

          需支付：10.00元
        

        
        
      

    

  





  

    
    
  

  

    
    
  








  

    

      

        

          

            
            
成就一亿技术人!

          

          

        

        

          

          

            领取后你会自动成为博主和红包主的粉丝
            规则
          

        

      

      

        

          

            
              
            
            

              
hope_wisdom
 发出的红包
            

          

        

        

          

          

          

        

      

    

    

  





	
打赏作者

	

		

		
			
		
		

		

			
ZhaoYingChao88

			
整理不易，还请多多支持，谢谢

		

	

	

			

	

	

    ¥1
    ¥2
    ¥4
    ¥6
    ¥10
    ¥20
	

	

		

			

				

					扫码支付：¥1
				

				

					

					
					获取中
					

				

				

					
					
					扫码支付
				

			

		

		

			
您的余额不足，请更换扫码支付或充值

			
打赏作者

		

	



      
      

      
实付元

      
使用余额支付

      

        

          

            
            点击重新获取
          

        

        
扫码支付

      

      

        
          
            
          
          
        
      

      

        
        钱包余额
          0
          

            
            

              

                
抵扣说明：

                
 1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
 2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

              

            

          

      

      余额充值