sql-statistical-functions

SQL：聚合函数和窗口函数

现代 SQL 提供了强大的函数用于执行统计计算和数据分析。这些函数大致分为聚合函数和窗口函数。聚合函数对一组行进行操作，返回一个单一的汇总值；而窗口函数则对与当前行相关的行集进行计算。

注意：此处描述的函数是 SQL 标准的一部分，在大多数现代数据库系统（如 PostgreSQL、SQL Server、Oracle 和最新版本的 MySQL）中均可用。

1. 标准聚合函数

聚合函数通常与 GROUP BY 子句一起使用来汇总数据。让我们考虑一个示例 Sales（销售）表：

CREATE TABLE Sales (
    product_id INT,
    category VARCHAR(50),
    units_sold INT,
    sale_price DECIMAL(10, 2)
);

INSERT INTO Sales VALUES
(101, 'Electronics', 5, 1200.00),
(102, 'Electronics', 2, 800.00),
(201, 'Books', 10, 15.00),
(202, 'Books', 20, 12.50);

函数	描述	示例查询
COUNT()	计算行数。COUNT(*) 计算所有行，而 COUNT(column) 计算该列中的非 NULL 值。	SELECT category, COUNT(*) FROM Sales GROUP BY category;
SUM()	计算数值列的总和。	SELECT category, SUM(units_sold) FROM Sales GROUP BY category;
AVG()	计算数值列的平均值。	SELECT category, AVG(sale_price) FROM Sales GROUP BY category;
MIN()	查找列中的最小值。	SELECT MIN(sale_price) FROM Sales;
MAX()	查找列中的最大值。	SELECT MAX(sale_price) FROM Sales;

2. 窗口函数简介

窗口函数是 SQL 中用于数据分析的一项革命性功能。与会折叠行的聚合函数不同，窗口函数对特定行集（一个“窗口”或“分区”）执行计算，并为每一行返回一个值。

它们使用 OVER() 子句定义，该子句指定了如何对数据进行分区和排序以进行计算。

语法

FUNCTION_NAME() OVER (
    [PARTITION BY column1, column2, ...]
    [ORDER BY column3, column4, ...]
)

• PARTITION BY：将行分为不同的组（分区）。函数独立应用于每个分区。
• ORDER BY：对每个分区内的行进行排序。这对于排名和偏移函数是必需的。

常用窗口函数

以下是一些强大的示例：

函数	描述
ROW_NUMBER()	为分区内的每行分配一个唯一的连续整数。
RANK()	为每行分配一个排名。在出现并列时跳过排名（例如，1, 2, 2, 4）。
DENSE_RANK()	分配排名时没有间隔。在出现并列时不会跳过排名（例如，1, 2, 2, 3）。
LEAD()	访问同一结果集中下一行的数据，无需自连接。
LAG()	访问同一结果集中上一行的数据，无需自连接。

示例：按类别对产品销售额进行排名

让我们从 Sales 表中查找每个类别中销量最高的产品（按价格）。

SELECT
    product_id,
    category,
    sale_price,
    RANK() OVER (PARTITION BY category ORDER BY sale_price DESC) as price_rank
FROM
    Sales;

输出

RANK() 分别为“电子产品”和“书籍”计算。

产品 ID	类别	销售价格	价格排名
201	Books	15.00	1
202	Books	12.50	2
101	Electronics	1200.00	1
102	Electronics	800.00	2