介绍下窗口函数的作用和原理
常见的窗口函数
窗口函数在实践中的10个案例

Sql中的窗口函数

一、介绍下窗口函数的作用和原理

在 SQL 的数据处理领域，窗口函数扮演着极为重要且独特的角色。它的主要作用体现在多个方面，能够极大地提升数据处理和分析的效率与深度。

从作用角度来看，窗口函数可以在不进行分组聚合操作的情况下，对数据进行分组计算。这意味着我们可以在保留原始数据每一行的详细信息的同时，对特定分组的数据进行诸如求和、求平均值、排名等计算。例如，在一个销售数据表中，我们可能希望了解每个销售人员的销售总额，同时又不想丢失每一笔销售记录的具体信息，这时窗口函数就能很好地满足需求。它还可以用于计算移动平均值，帮助我们分析数据的趋势变化。比如在股票交易数据中，通过计算一段时间内股票价格的移动平均值，能够更清晰地观察到股票价格的走势。

而其原理基于窗口的概念。所谓窗口，就是对查询结果集进行的一个逻辑分组。窗口函数会为每一行数据定义一个关联的窗口，这个窗口是根据用户指定的规则来确定的。规则通常包括分区（PARTITION BY）和排序（ORDER BY）两个关键部分。分区操作类似于 GROUP BY 子句，它将结果集按照指定的列进行分组，使得窗口函数在每个分组内独立计算。排序操作则决定了窗口内数据的顺序，对于一些依赖顺序的计算，如排名、累计求和等非常重要。例如，我们可以按照销售人员进行分区，然后按照销售日期进行排序，这样窗口函数就会在每个销售人员的销售记录组内，按照销售日期的顺序进行计算。

二、常见的窗口函数

在 SQL 中，有许多常见且实用的窗口函数，它们各自具有不同的功能和用途。

排名函数

ROW_NUMBER()：这个函数会为窗口内的每一行分配一个唯一的整数排名，排名是连续且不重复的。例如，在一个学生成绩表中，使用 ROW_NUMBER() 函数按照成绩从高到低排序，可以为每个学生分配一个排名，第一名的排名为 1，第二名的排名为 2，以此类推。
RANK()：它同样用于排名，但与 ROW_NUMBER() 不同的是，当存在相同值时，相同值的行将被赋予相同的排名，并且下一个排名会跳过相应的数量。比如，有两个学生的成绩相同且并列第一名，那么他们的排名都是 1，而第三名的排名将是 3。
DENSE_RANK()：该函数也是用于排名，当存在相同值时，相同值的行被赋予相同的排名，但下一个排名不会跳过。还是以学生成绩为例，如果有两个学生并列第一名，他们的排名都是 1，而第三名的排名将是 2。

聚合函数

SUM()：在窗口函数中使用 SUM() 可以计算窗口内指定列的总和。例如，在销售数据表中，我们可以使用 SUM() 窗口函数计算每个销售人员在一定时间段内的累计销售金额。
AVG()：用于计算窗口内指定列的平均值。比如，在员工工资表中，通过 AVG() 窗口函数可以计算每个部门员工的平均工资。
MIN() 和 MAX()：分别用于获取窗口内指定列的最小值和最大值。在商品价格表中，我们可以使用这两个函数找出每个类别商品的最低价格和最高价格。

偏移函数

LAG()：它允许我们访问窗口内当前行之前的某一行的数据。例如，在股票价格表中，使用 LAG() 函数可以获取前一天的股票价格，从而方便计算价格的变化。
LEAD()：与 LAG() 相反，LEAD() 函数用于访问窗口内当前行之后的某一行的数据。在销售数据中，我们可以用它来预测下一次销售的相关信息。

三、窗口函数在实践中的 10 个案例

案例 1：计算每个部门员工的工资排名

假设我们有一个员工表，包含员工 ID、部门 ID 和工资信息。我们可以使用 ROW_NUMBER() 窗口函数按照部门进行分区，然后在每个部门内按照工资从高到低进行排序，为每个员工分配一个排名。这样可以清晰地了解每个部门内员工工资的高低顺序。

案例 2：找出每个产品类别中价格最高的产品

在产品表中，包含产品 ID、产品类别和价格信息。使用 MAX() 窗口函数按照产品类别进行分区，找出每个类别中价格最高的产品。这有助于我们了解每个类别产品的价格上限。

案例 3：计算每个月的累计销售额

在销售表中，有销售日期和销售金额信息。通过 SUM() 窗口函数按照销售日期进行排序，计算每个月的累计销售额。这样可以直观地看到销售额的增长趋势。

案例 4：计算每个客户的订单数量排名

在订单表中，包含客户 ID 和订单信息。使用 RANK() 窗口函数按照客户进行分区，统计每个客户的订单数量并进行排名。这有助于我们识别出重要客户。

案例 5：找出每个地区销售业绩排名前三的销售人员

在销售表中，有销售人员 ID、地区和销售业绩信息。使用 DENSE_RANK() 窗口函数按照地区进行分区，对销售人员的销售业绩进行排名，然后筛选出排名前三的销售人员。这可以帮助我们奖励优秀的销售人员。

案例 6：计算每个员工的工资与部门平均工资的差值

在员工表中，有员工 ID、部门 ID 和工资信息。使用 AVG() 窗口函数按照部门进行分区，计算每个部门的平均工资，然后用每个员工的工资减去部门平均工资，得到差值。这有助于我们了解员工工资在部门内的相对水平。

案例 7：获取每个订单的上一个订单日期

在订单表中，有订单 ID 和订单日期信息。使用 LAG() 窗口函数按照订单日期进行排序，获取每个订单的上一个订单日期。这可以用于分析订单的时间间隔。

案例 8：预测每个客户的下一次购买金额

在销售表中，有客户 ID、销售日期和销售金额信息。使用 LEAD() 窗口函数按照客户进行分区，按照销售日期进行排序，预测每个客户的下一次购买金额。这有助于我们进行销售预测。

案例 9：计算每个产品的移动平均价格

在产品价格表中，有产品 ID、日期和价格信息。使用 AVG() 窗口函数按照产品进行分区，按照日期进行排序，计算每个产品的移动平均价格。这可以帮助我们观察产品价格的波动情况。

案例 10：找出每个季度销售额增长最快的产品

在销售表中，有产品 ID、销售日期和销售金额信息。我们可以先将销售日期转换为季度信息，然后使用窗口函数计算每个产品在每个季度的销售额增长率，最后找出每个季度销售额增长最快的产品。这有助于我们发现具有潜力的产品。

四、案例参考答案

案例 1：计算每个部门员工的工资排名

-- 假设存在名为 employees 的表，包含列 employee_id、department_id 和 salary
SELECT 
    employee_id,
    department_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) as salary_rank
FROM 
    employees;

解释：PARTITION BY department\_id 按部门对员工进行分组，ORDER BY salary DESC 在每个部门内按工资降序排列，ROW\_NUMBER() 为每个部门内的员工依次分配唯一的排名。

案例 2：找出每个产品类别中价格最高的产品

-- 假设存在名为 products 的表，包含列 product_id、category 和 price
SELECT 
    product_id,
    category,
    price
FROM (
    SELECT 
        product_id,
        category,
        price,
        MAX(price) OVER (PARTITION BY category) as max_price
    FROM 
        products
) subquery
WHERE 
    price = max_price;

解释：子查询中使用 MAX(price) OVER (PARTITION BY category) 计算每个产品类别中的最高价格，外层查询筛选出价格等于最高价格的产品。

案例 3：计算每个月的累计销售额

-- 假设存在名为 sales 的表，包含列 sale_date 和 sale_amount
SELECT 
    sale_date,
    sale_amount,
    SUM(sale_amount) OVER (ORDER BY sale_date) as cumulative_sales
FROM 
    sales;

解释：ORDER BY sale\_date 按销售日期排序，SUM(sale\_amount) OVER (ORDER BY sale\_date) 计算从开始到当前行的累计销售金额。

案例 4：计算每个客户的订单数量排名

-- 假设存在名为 orders 的表，包含列 customer_id 和 order_id
SELECT 
    customer_id,
    COUNT(order_id) as order_count,
    RANK() OVER (ORDER BY COUNT(order_id) DESC) as order_rank
FROM 
    orders
GROUP BY 
    customer_id;

解释：先使用 GROUP BY customer\_id 按客户分组统计订单数量，RANK() 对统计出的订单数量进行排名，ORDER BY COUNT(order\_id) DESC 按订单数量降序排列。

案例 5：找出每个地区销售业绩排名前三的销售人员

-- 假设存在名为 sales_records 的表，包含列 salesperson_id、region 和 sales_performance
SELECT 
    salesperson_id,
    region,
    sales_performance
FROM (
    SELECT 
        salesperson_id,
        region,
        sales_performance,
        DENSE_RANK() OVER (PARTITION BY region ORDER BY sales_performance DESC) as performance_rank
    FROM 
        sales_records
) subquery
WHERE 
    performance_rank <= 3;

解释：子查询中 PARTITION BY region 按地区分组，ORDER BY sales\_performance DESC 在每个地区内按销售业绩降序排列，DENSE\_RANK() 进行排名，外层查询筛选出排名前三的销售人员。

案例 6：计算每个员工的工资与部门平均工资的差值

-- 假设存在名为 employees 的表，包含列 employee_id、department_id 和 salary
SELECT 
    employee_id,
    department_id,
    salary,
    salary - AVG(salary) OVER (PARTITION BY department_id) as salary_difference
FROM 
    employees;

解释：PARTITION BY department\_id 按部门分组，AVG(salary) OVER (PARTITION BY department\_id) 计算每个部门的平均工资，用员工工资减去部门平均工资得到差值。

案例 7：获取每个订单的上一个订单日期

-- 假设存在名为 orders 的表，包含列 order_id 和 order_date
SELECT 
    order_id,
    order_date,
    LAG(order_date) OVER (ORDER BY order_date) as previous_order_date
FROM 
    orders;

解释：ORDER BY order\_date 按订单日期排序，LAG(order\_date) 获取当前行的上一行的订单日期。

案例 8：预测每个客户的下一次购买金额

-- 假设存在名为 sales 的表，包含列 customer_id、sale_date 和 sale_amount
SELECT 
    customer_id,
    sale_date,
    sale_amount,
    LEAD(sale_amount) OVER (PARTITION BY customer_id ORDER BY sale_date) as next_purchase_amount
FROM 
    sales;

解释：PARTITION BY customer\_id 按客户分组，ORDER BY sale\_date 在每个客户内按销售日期排序，LEAD(sale\_amount) 获取当前行的下一行的销售金额。

案例 9：计算每个产品的移动平均价格

-- 假设存在名为 product_prices 的表，包含列 product_id、price_date 和 price
SELECT 
    product_id,
    price_date,
    price,
    AVG(price) OVER (PARTITION BY product_id ORDER BY price_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_average
FROM 
    product_prices;

解释：PARTITION BY product\_id 按产品分组，ORDER BY price\_date 按价格日期排序，ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 定义窗口范围为当前行和前两行，AVG(price) 计算该窗口内的平均价格。

案例 10：找出每个季度销售额增长最快的产品

-- 假设存在名为 sales 的表，包含列 product_id、sale_date 和 sale_amount
WITH quarterly_sales AS (
    SELECT 
        product_id,
        DATE_TRUNC('quarter', sale_date) as quarter,
        SUM(sale_amount) as quarterly_sales_amount
    FROM 
        sales
    GROUP BY 
        product_id, DATE_TRUNC('quarter', sale_date)
),
sales_growth AS (
    SELECT 
        product_id,
        quarter,
        quarterly_sales_amount,
        ((quarterly_sales_amount - LAG(quarterly_sales_amount) OVER (PARTITION BY product_id ORDER BY quarter)) / LAG(quarterly_sales_amount) OVER (PARTITION BY product_id ORDER BY quarter)) as growth_rate
    FROM 
        quarterly_sales
)
SELECT 
    product_id,
    quarter,
    quarterly_sales_amount,
    growth_rate
FROM (
    SELECT 
        product_id,
        quarter,
        quarterly_sales_amount,
        growth_rate,
        RANK() OVER (PARTITION BY quarter ORDER BY growth_rate DESC) as growth_rank
    FROM 
        sales_growth
) subquery
WHERE 
    growth_rank = 1;

解释：首先使用 WITH 子句创建 quarterly\_sales 公共表表达式，按产品和季度分组统计季度销售额。接着创建 sales\_growth 公共表表达式，使用 LAG() 函数计算每个产品相邻季度的销售额增长率。最后在子查询中对每个季度的产品按增长率降序排名，外层查询筛选出排名为 1 的产品，即每个季度销售额增长最快的产品。

Sql中的窗口函数

Sql中的窗口函数

一、介绍下窗口函数的作用和原理

二、常见的窗口函数

排名函数

聚合函数

偏移函数

三、窗口函数在实践中的 10 个案例

案例 1：计算每个部门员工的工资排名

案例 2：找出每个产品类别中价格最高的产品

案例 3：计算每个月的累计销售额

案例 4：计算每个客户的订单数量排名

案例 5：找出每个地区销售业绩排名前三的销售人员

案例 6：计算每个员工的工资与部门平均工资的差值

案例 7：获取每个订单的上一个订单日期

案例 8：预测每个客户的下一次购买金额

案例 9：计算每个产品的移动平均价格

案例 10：找出每个季度销售额增长最快的产品

四、案例参考答案

案例 1：计算每个部门员工的工资排名

案例 2：找出每个产品类别中价格最高的产品

案例 3：计算每个月的累计销售额

案例 4：计算每个客户的订单数量排名

案例 5：找出每个地区销售业绩排名前三的销售人员

案例 6：计算每个员工的工资与部门平均工资的差值

案例 7：获取每个订单的上一个订单日期

案例 8：预测每个客户的下一次购买金额

案例 9：计算每个产品的移动平均价格

案例 10：找出每个季度销售额增长最快的产品

常见的Sql语句模板

这里我将开始给大家讲一下Hadoop

延伸阅读: