当前位置：首页 > 行业动态 > 正文

如何高效运用SQL开窗函数进行数据分析？

admin
行业动态
2024-12-20
2017

开窗函数是SQL中的一种高级功能，用于在结果集的一组相关行上执行计算。常见的开窗函数包括 ROW_NUMBER(), RANK(), DENSE_RANK(), 和 SUM() OVER(), 等等。

开窗函数是SQL中一种强大的工具，它允许在不使用聚合函数的GROUP BY子句的情况下对一组值进行操作，开窗函数可以在每一行上同时返回基础行的列和聚合列，这为数据分析提供了更大的灵活性，以下是关于开窗函数的详细介绍：

如何高效运用SQL开窗函数进行数据分析？第1张

一、基本概念

开窗函数，也称为窗口函数或分析函数，是SQL中的一种特殊函数，它允许用户在结果集的“窗口”上执行计算，而无需将数据分组，开窗函数与聚合函数的主要区别在于，开窗函数可以在同一行中同时返回基础行的列和聚合列，而聚合函数则通常需要使用GROUP BY子句来对数据进行分组。

二、语法形式

开窗函数的语法形式通常为：函数 + OVER (PARTITION BY <分组用列> ORDER BY <排序用列>)。PARTITION BY用于指定分组的列，ORDER BY用于指定排序的列，这两个子句都是可选的，但至少需要出现一个。

三、分类

开窗函数主要分为两大类：排序开窗函数和聚合开窗函数。

1、排序开窗函数：这类函数主要用于生成行号或排名，常见的排序开窗函数包括：

ROW_NUMBER()：为结果集中的每一行分配一个唯一的序号，相同值的排名连续。

RANK()：为结果集中的每一行分配一个唯一的排名，相同值的排名相同，后续排名不连续。

DENSE_RANK()：为结果集中的每一行分配一个唯一的排名，相同值的排名相同，后续排名连续。

NTILE(n)：将结果集均匀分布到指定的桶中，返回每个值所属的桶号。

2、聚合开窗函数：这类函数用于计算聚合值，如求和、计数、最小值、最大值等，常见的聚合开窗函数包括：

SUM()：计算指定列的总和。

COUNT()：计算指定列的非空值数量。

MIN()：返回指定列的最小值。

MAX()：返回指定列的最大值。

AVG()：计算指定列的平均值。

四、示例代码

以下是一个使用开窗函数的示例代码：

SELECT grade, subject, score,
       ROW_NUMBER() OVER (PARTITION BY subject ORDER BY score DESC) AS row_numbers,
       RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS ranks,
       DENSE_RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS dense_ranks,
       NTILE(2) OVER (PARTITION BY subject ORDER BY score DESC) AS ntiles,
       SUM(score) OVER (PARTITION BY subject) AS sum_no_order_by,
       SUM(score) OVER (PARTITION BY subject ORDER BY score DESC) AS sum_order_by,
       COUNT(score) OVER (PARTITION BY subject) AS count_no_order_by,
       COUNT(score) OVER (PARTITION BY subject ORDER BY score DESC) AS count_order_by,
       MIN(score) OVER (PARTITION BY subject) AS min_no_order_by,
       MIN(score) OVER (PARTITION BY subject ORDER BY score DESC) AS min_order_by,
       MAX(score) OVER (PARTITION BY subject) AS max_no_order_by,
       MAX(score) OVER (PARTITION BY subject ORDER BY score DESC) AS max_order_by,
       AVG(score) OVER (PARTITION BY subject) AS avg_no_order_by,
       AVG(score) OVER (PARTITION BY subject ORDER BY score DESC) AS avg_order_by
FROM score;

在这个示例中，我们首先创建了一个名为score的表，并插入了一些测试数据，我们使用开窗函数对表中的数据进行了多种计算，包括生成行号、排名、聚合值等。