窗口函数的详解

目录

        一、窗口函数的组成部

        二、窗口函数的类型

        三、窗口函数的排序和分区

        四、窗口函数的窗口框架

        示例

---

一、窗口函数的组成部分

1. 窗口函数本身：这是执行计算的函数，如 SUM(), AVG(), ROW_NUMBER() 等。

2. OVER子句：定义了窗口函数的计算范围。它由三部分组成：

   • PARTITION BY：将数据集分成多个独立的组，每个组内部进行计算。如果省略，整个数据集被视为一个单一的分区。
   • ORDER BY：在每个分区内对数据进行排序，这会影响某些窗口函数的计算结果，如 ROW_NUMBER(), RANK() 等。
   • ROWS或RANGE：定义了窗口的物理大小，即函数作用的行数范围。ROWS 是基于行号的，而 RANGE 是基于值的范围。

3. 窗口框架（Frame）：在某些窗口函数中，你可以通过窗口框架进一步细化窗口的大小。窗口框架可以是静态的（如 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING）或动态的（如 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW）。

二、窗口函数的类型

1. 聚合函数：如 SUM(), AVG(), MIN(), MAX() 等，它们计算分区内所有行的聚合值。

2. 排名函数：如 ROW_NUMBER(), RANK(), DENSE_RANK() 等，它们为分区内的每一行分配一个唯一的序号或排名。

3. 偏移函数：如 LEAD(), LAG() 等，它们允许你访问当前行的前面或后面的行的值。

三、窗口函数的排序和分区

• PARTITION BY：这个子句将结果集分成多个独立的分区，每个分区都是一个独立的数据集，窗口函数在每个分区上独立计算。例如，如果你按部门对员工数据进行分区，每个部门将有自己的窗口函数计算结果。

• ORDER BY：这个子句在每个分区内对行进行排序。对于排名函数，ORDER BY 子句决定了行的排名顺序。对于聚合函数，ORDER BY 子句决定了窗口的计算顺序。

四、窗口函数的窗口框架

• ROWS：基于行号的窗口框架，可以是固定的行数（如 ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING），也可以是相对于当前行的位置（如 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW）。

• RANGE：基于值的范围的窗口框架，它允许你基于列的值来定义窗口的大小。例如，RANGE BETWEEN 0 PRECEDING AND 0 FOLLOWING 会创建一个窗口，其中包含当前行和与当前行具有相同值的行。

示例

假设我们有一个销售数据表，包含日期和销售额：

SELECT
    date,
    sales,
    SUM(sales) OVER (PARTITION BY date ORDER BY time) AS running_total,
    ROW_NUMBER() OVER (PARTITION BY date ORDER BY time DESC) AS sales_rank
FROM
    sales_data;

这个查询会返回每天的销售额，以及每个日期的销售额的运行总计和销售额排名。

• SUM(sales) OVER (PARTITION BY date ORDER BY time) 会计算每个日期的销售额的累计总和。
• ROW_NUMBER() OVER (PARTITION BY date ORDER BY time DESC) 会为每个日期的销售额按时间降序排列，并为每行分配一个唯一的序号。

开窗函数的灵活性和强大功能使得它们在处理复杂的数据分析任务时非常有用。通过合理地使用分区、排序和窗口框架，你可以实现各种复杂的数据计算和分析。