PostgreSQL 学习笔记 - 函数和操作符

简介

PostgreSQL 是一个开源的关系型数据库管理系统，它支持许多函数和操作符，这些函数和操作符可以帮助我们更高效地处理数据。

函数

函数是预定义的代码段，接收参数并返回结果。在 PostgreSQL 中，有许多内置函数可以用来处理数据，同时也可以创建自定义函数来满足特定需求。

数学函数

PostgreSQL 支持许多数学函数，例如 abs、round、ceiling 等等。下面是一些常用的数学函数示例：

-- 取绝对值
SELECT abs(-1); -- 1

-- 四舍五入
SELECT round(3.1415926, 2); -- 3.14

-- 向上取整
SELECT ceiling(3.14); -- 4

-- 向下取整
SELECT floor(3.14); -- 3

-- 幂运算
SELECT power(2, 3); -- 8

-- 平方根
SELECT sqrt(4); -- 2

字符串函数

PostgreSQL 支持许多字符串函数，例如 concat、substring、length 等等。下面是一些常用的字符串函数示例：

-- 拼接字符串
SELECT concat('hello', 'world'); -- helloworld

-- 截取子字符串
SELECT substring('hello world', 1, 5); -- hello

-- 获取字符串长度
SELECT length('hello world'); -- 11

-- 字符串转小写
SELECT lower('Hello World'); -- hello world

-- 字符串转大写
SELECT upper('Hello World'); -- HELLO WORLD

日期和时间函数

PostgreSQL 支持许多日期和时间函数，例如 now、extract、date_trunc 等等。下面是一些常用的日期和时间函数示例：

-- 获取当前日期和时间
SELECT now(); -- 2023-04-03 11:22:33.123456+08

-- 获取日期的年份
SELECT extract(year from now()); -- 2023

-- 获取日期的月份
SELECT extract(month from now()); -- 4

-- 获取日期的日份
SELECT extract(day from now()); -- 3

-- 截取日期到小时
SELECT date_trunc('hour', now()); -- 2023-04-03 11:00:00+08

聚合函数

PostgreSQL 支持许多聚合函数，例如 sum、avg、count、max、min 等等。下面是一些常用的聚合函数示例：

-- 求和
SELECT sum(1, 2, 3); -- 6

-- 求平均值
SELECT avg(1, 2, 3); -- 2

-- 计算行数
SELECT count(*) FROM table_name;

-- 求最大值
SELECT max(1, 2, 3); -- 3

-- 求最小值
SELECT min(1, 2, 3); -- 1

上述示例中，sum 函数计算给定值的总和，avg 函数计算给定值的平均值，count 函数计算表中行的数量，max 函数返回给定值的最大值，min 函数返回给定值的最小值。

除了上述内置聚合函数之外，还可以自定义聚合函数来满足特定需求。自定义聚合函数需要使用 CREATE AGGREGATE 命令来创建，下面是一个示例：

-- 创建一个计算标准差的聚合函数
CREATE AGGREGATE stddev_samp (numeric) (
    sfunc = numeric_inc,
    stype = numeric[],
    finalfunc = stddev_samp_final
);

-- 辅助函数，用于计算方差
CREATE FUNCTION variance_samp_final(numeric[]) RETURNS numeric AS $$
    SELECT variance_pop($1)
$$ LANGUAGE SQL IMMUTABLE;

-- 辅助函数，用于计算标准差
CREATE FUNCTION stddev_samp_final(numeric[]) RETURNS numeric AS $$
    SELECT sqrt(variance_samp_final($1))
$$ LANGUAGE SQL IMMUTABLE;

-- 辅助函数，用于计算平均值
CREATE FUNCTION numeric_inc(numeric[], numeric) RETURNS numeric[] AS $$
    SELECT array_append($1, $2)
$$ LANGUAGE SQL IMMUTABLE;

上述示例中，stddev_samp 是一个自定义聚合函数，用于计算给定数值的标准差。该函数使用了三个辅助函数：variance_samp_final 用于计算方差，stddev_samp_final 用于计算标准差，numeric_inc 用于计算平均值。

窗口函数

窗口函数是一种特殊的函数，它可以在不更改查询结果的情况下计算一些聚合函数。例如，我们可以使用窗口函数计算每个分组的平均值、总和、最大值、最小值等等。下面是一些常用的窗口函数示例：

-- 计算每个部门的平均工资
SELECT department, salary, avg(salary) OVER (PARTITION BY department) FROM employees;

-- 计算每个部门的总收入和平均收入
SELECT department, salary, sum(salary) OVER (PARTITION BY department) AS total_salary,
       avg(salary) OVER (PARTITION BY department) AS avg_salary
FROM employees;

上述示例中，OVER 子句用于定义窗口，并指定窗口函数要应用到的分区方式。

除了常规的窗口函数之外，PostgreSQL 还支持一些特殊的窗口函数，例如 rank、dense_rank、row_number、lag、lead 等等。这些函数可以在不更改查询结果的情况下计算一些有用的统计信息。

下面是一些常用的特殊窗口函数示例：

-- 计算每个部门的排名
SELECT department, salary, rank() OVER (PARTITION BY department ORDER BY salary DESC) AS rank FROM employees;

-- 计算每个部门的密集排名
SELECT department, salary, dense_rank() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank FROM employees;

-- 给每个行添加一个行号
SELECT department, salary, row_number() OVER (ORDER BY salary DESC) AS row_number FROM employees;

-- 计算每个行的前一行和后一行的值
SELECT department, salary, lag(salary) OVER (ORDER BY salary DESC) AS lag, lead(salary) OVER (ORDER BY salary DESC) AS lead FROM employees;

上述示例中，rank 函数计算每个部门的排名，dense_rank 函数计算每个部门的密集排名，row_number 函数为每个行添加一个行号，lag 函数和 lead 函数用于计算每个行的前一行和后一行的值。

操作符

在 PostgreSQL 中，操作符是一种特殊的函数，它们使用一些特殊的符号来代替函数名。例如，加法操作符 + 相当于函数 add，减法操作符 - 相当于函数 subtract。

PostgreSQL 支持许多操作符，例如算术操作符、逻辑操作符、比较操作符等等。下面是一些常用的操作符示例：

-- 算术操作符
SELECT 1 + 2; -- 3
SELECT 3 - 2; -- 1
SELECT 2 * 3; -- 6
SELECT 10 / 2; -- 5

-- 逻辑操作符
SELECT true AND false; -- false
SELECT true OR false; -- true
SELECT NOT true; -- false

-- 比较操作符
SELECT 1 = 1; -- true
SELECT 1 <> 2; -- true
SELECT 1 < 2; -- true
SELECT 2 > 1; -- true
SELECT 1 <= 2; -- true
SELECT 2 >= 1; -- true

除了上述常用操作符之外，PostgreSQL 还支持一些其他有用的操作符，例如模糊匹配操作符、位操作符等等。下面是一些常用的操作符示例：

-- 模糊匹配操作符
SELECT 'Hello, World!' LIKE '%World%'; -- true
SELECT 'Hello, World!' ILIKE '%world%'; -- true

-- 位操作符
SELECT 8 & 10; -- 8
SELECT 8 | 10; -- 10
SELECT 8 # 10; -- 2
SELECT ~8; -- -9

上述示例中，LIKE 操作符用于进行模糊匹配，ILIKE 操作符用于进行不区分大小写的模糊匹配，&、|、# 和 ~ 操作符用于进行位运算。

此外，PostgreSQL 还支持自定义操作符，这可以通过 CREATE OPERATOR 命令来实现。自定义操作符可以使用现有的函数或自定义函数来实现，这使得 PostgreSQL 的操作符可以根据具体应用场景进行自定义。

内置函数和操作符

PostgreSQL 提供了许多内置函数和操作符，这些函数和操作符可以满足大多数常见的数据处理需求。下面是一些常用的内置函数和操作符：

| 类别 | 函数 / 操作符 | 描述 |
| ———— | ————————————————- | ——————————————– |
| 数学函数 | abs、floor、ceil、round | 绝对值、向下取整、向上取整、四舍五入 |
| 字符串函数 | lower、upper、trim | 小写、大写、去除空格 |
| 时间函数 | now、date_part、date_trunc | 当前时间、获取时间部分、截断时间部分 |
| 聚合函数 | count、sum、avg、max | 计数、求和、平均值、最大值 |
| 窗口函数 | rank、dense_rank、row_number、lag、lead | 排名、密集排名、行号、前一行和后一行的值 |
| 逻辑函数 | and、or、not | 逻辑与、逻辑或、逻辑非 |
| 比较函数 | =、<>、<、>、<=、>= | 相等、不等于、小于、大于、小于等于、大于等于 |
| 模糊匹配函数 | like、ilike | 模糊匹配、不区分大小写的模糊匹配 |
| 位函数 | &、|、#、~ |

以上仅是一些常用的函数和操作符示例，PostgreSQL 还提供了许多其他函数和操作符，这些函数和操作符可以根据不同的需求进行选择和使用。在使用 PostgreSQL 进行数据处理时，熟练掌握这些函数和操作符是非常重要的，可以帮助我们更加高效和准确地处理数据。

总结

PostgreSQL 是一种功能强大的关系型数据库管理系统，它提供了许多丰富的函数和操作符，可以满足各种数据处理需求。通过学习这些函数和操作符，我们可以更好地利用 PostgreSQL 进行数据处理，提高数据处理效率和准确性。