PostgreSQL - 常用函数

PostgreSQL - 有用的函数和操作符

PostgreSQL 提供了一套丰富且内置的函数和操作符，用于对数据执行操作。这些功能涵盖了从简单的聚合计算到复杂的字符串操作、数组处理，甚至查询 JSON 数据。

核心聚合函数

聚合函数对一组行执行计算并返回单个汇总行。它们通常与 GROUP BY 子句一起使用。

• PostgreSQL COUNT() − 计算符合指定条件的行数。COUNT(*) 计算所有行，而 COUNT(column_name) 计算指定列不为 NULL 的行。
• PostgreSQL MAX() − 返回列中一组值的最大值。
• PostgreSQL MIN() − 返回列中一组值的最小值。
• PostgreSQL AVG() − 计算一组数值的平均值。
• PostgreSQL SUM() − 计算一组数值的总和。

高级聚合和数组函数

现代应用程序通常需要更复杂的数据聚合。

• PostgreSQL STRING_AGG() − 将输入字符串连接成一个单一字符串，可选择使用分隔符。对于从分组数据创建逗号分隔列表非常有用。
• PostgreSQL ARRAY_AGG() − 将输入值（包括空值）聚合到 PostgreSQL 数组中。这对于将相关项目收集到单个字段中非常强大。
• PostgreSQL JSONB_AGG() / JSON_AGG() − 将值聚合为 JSON 数组。JSONB 是更推荐、更高效的二进制格式。

窗口函数

窗口函数对一组与当前行相关的表行执行计算。与聚合函数不同，它们不会折叠行；它们根据 OVER() 子句定义的相邻行“窗口”为每行返回一个值。

• 排名函数：ROW_NUMBER()、RANK()、DENSE_RANK() 用于根据特定排序为每行分配唯一排名。
• 聚合作为窗口函数：您可以使用 SUM()、AVG() 和 COUNT() 等标准聚合函数作为窗口函数来计算运行总计、移动平均值等。

示例：按薪水对员工排名

SELECT
    employee_name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as department_salary_rank
FROM
    employees;

常用字符串和数值函数

PostgreSQL 拥有一个用于数据操作的综合函数库。

• 字符串函数：LOWER()、UPPER()、LENGTH()、SUBSTRING()、TRIM()、CONCAT()。
• 数值函数：ROUND()、CEIL()、FLOOR()、ABS()、RANDOM()。
• 日期/时间函数：NOW()、CURRENT_DATE、EXTRACT()、AGE()。

进一步学习

这只是一个小部分示例。官方的 PostgreSQL 函数和操作符文档是探索所有可用功能的权威资源。