PostgreSQL 数据库

常用聚合函数与窗口函数

在 PostgreSQL 中，聚合函数（Aggregate Functions）和窗口函数（Window Functions）是非常强大的工具，用于进行复杂的数据分析和处理。这些函数使得数据查询更加灵活和高效。

聚合函数 Aggregate Functions

• COUNT: 计算行数。

SELECT COUNT(*) FROM table_name;

• SUM: 计算某列的总和。

SELECT SUM(column_name) FROM table_name;

• AVG: 计算某列的平均值。

SELECT AVG(column_name) FROM table_name;

• MAX: 找出某列的最大值。

SELECT MAX(column_name) FROM table_name;

• MIN: 找出某列的最小值。

SELECT MIN(column_name) FROM table_name;

• ARRAY_AGG: 将一组值合并为一个数组。

SELECT ARRAY_AGG(column_name) FROM table_name;

• STRING_AGG: 将一组字符串值连接成一个单一的字符串。

SELECT STRING_AGG(column_name, ', ') FROM table_name;

• STDDEV: 计算标准偏差。

SELECT STDDEV(column_name) FROM table_name;

窗口函数 Window Functions

窗口函数用于数据的分区，允许进行每个分区的聚合计算，而不是整个结果集。

• ROW_NUMBER: 对每个分区内的行进行编号。

SELECT ROW_NUMBER() OVER (PARTITION BY column_name ORDER BY another_column) FROM table_name;

• RANK: 对结果进行排名，相同值会有相同的排名，但会跳过随后的排名。

SELECT RANK() OVER (PARTITION BY column_name ORDER BY another_column) FROM table_name;

• DENSE_RANK: 与 RANK 类似，但不会跳过任何排名。

SELECT DENSE_RANK() OVER (PARTITION BY column_name ORDER BY another_column) FROM table_name;

• NTILE: 将分区的数据分成指定数量的近似大小的组。

SELECT NTILE(number_of_tiles) OVER (PARTITION BY column_name ORDER BY another_column) FROM table_name;

• LEAD: 获取当前行的下一行的值。

SELECT LEAD(column_name) OVER (ORDER BY another_column) FROM table_name;

• LAG: 获取当前行的上一行的值。

SELECT LAG(column_name) OVER (ORDER BY another_column) FROM table_name;

• FIRST_VALUE: 获取分区内的第一个值。

SELECT FIRST_VALUE(column_name) OVER (PARTITION BY another_column) FROM table_name;

• LAST_VALUE: 获取分区内的最后一个值。

SELECT LAST_VALUE(column_name) OVER (PARTITION BY another_column) FROM table_name;

这些函数使得在数据库级别上执行复杂的数据处理和分析成为可能，提高了效率并减少了将大量数据加载到应用层的需要。特别是在处理大型数据集时，这些函数非常有用。

开启插件

SELECT * FROM pg_extension;

CREATE EXTENSION vector;