PostgreSQL技术问答26 - Math
本文是《PostgreSQL技术问答》系列文章中的一篇。关于这个系列的由来，可以参阅开篇文章：
文章的编号只是一个标识，在系列中没有明确的逻辑顺序和意义。读者进行阅读时，不用太关注这个方面。
本文主要讨论的内容是在Postgres中，如何进行相关的数学计算，就是Math相关特性和功能。
Postgres如何实现数学计算
Postgres可以直接在SQL语句中，进行相关字段和数据的数学计算。它们的实现一般有两种方式，操作符和函数。操作符的使用大体和通用的编程语言相同，可以完成最常见和基本的数学计算操作，并且容易理解和使用。对于更复杂和多样的数学计算，Postgres提供了很多内置相关的函数，也可以直接在SQL语句中使用。
在实际使用过程中，可能需要稍微注意一下，操作符和函数一般都有操作数的数据类型的限制，使用时需要进行确认或者转换。当然，在很多情况下Postgres也会帮助使用者自动进行转换，简化操作和使用，具体的问题，需要在具体的场景中进行检查和测试。
从数学类型方面来看，Postgres中提供的方法，大体包括了整数(包括二进制形式)和数字类型(浮点或者双精度)。
有那些可用的数学计算符号
下表列出了在Postgres中，可以直接使用的数学计算操作符：
| 操作符 | 功能和描述 | 示例 | 结果 |
|---|---|---|---|
| + | 加法操作 | 2 + 3 | 5 |
| - | 减法操作 | 5 - 3 | 2 |
| * | 乘法操作 | 2 * 3 | 6 |
| / | 除法操作 | 7.0 / 2 | 3.5 |
| / | 整数除法操作 | 7 / 2 | 3 |
| % | 求余操作 | 5 % 4 | 1 |
| 求幂操作 | 2 ^ 3 | 8 | |
| |/ | 平方根 | |/ 9 | 3 |
| ||/ | 立方根 | ||/27 | 3 |
| ! | 阶乘 | 5 ！ | 120 |
| ！！ | 阶乘前缀模式 | !! 5 | 120 |
| @ | 求绝对值 | @ -5.0 | 5 |
| & | 比特与 | 91 & 15 | 11 |
| | | 比特或 | 32 | 3 | 5 |
| # | 比特异或 | 17 # 5 | 20 |
| ~ | 比特非 | ~ 1 | -2 |
| << | 比特左移位 | 1 << 4 | 16 |
| >> | 比特右移位 | 8 >> 2 | 2 |
合理的使用PG数学计算操作符，可以大大简化SQL语句的编写和功能实现。但这些操作符的使用，有一些学习和理解的门槛，开发者需要平衡使用。实际上，PG提供了可以自定义的操作符和操作计算方式，用户可以自己定义操作符(和已有的不冲突)，来完成更复杂的数据计算或者组合。
有那些可用的数学计算函数
PG提供的数学计算相关的函数，主要有三种类型，包括了常规数学计算，三角函数和随机函数等等。下表列出了Postgres支持的数学计算相关的函数：
| 函数 | 结果数据类型 | 功能和描述 | 示例 |
|---|---|---|---|
| abs(x) | 同输入 | 求绝对值 | abs(-17.4) -> 17.45 |
| cbrt(dp) | dp | 求立方根 | cbrt(27.0) -> 3 |
| ceil(dp/n) | 同输入 | 比当前数更大或相等的整数(天花板) | ceil(-42.8) -> -42 |
| ceiling(dp/n) | 同输入 | 和ceil相同 | ceiling( 95.3) -> 96 |
| degrees(dp) | dp | 弧度转角度 | degrees(0.5) -> 28.64788975654126 |
| div(n,n) | 同输入 | 除法操作，支持整数除法 | div(9,4) -> 2 |
| exp(dp/n) | 同输入 | 求自然对数的指数 | exp(1.0) -> 2.718281828459056 |
| floor(dp/n) | 同输入 | 比当前更小或相等的整数(地板) | floor(-42.8) -> -43 |
| ln(dp/n) | 同输入 | 求自然对数 | ln(2.0) -> 0.6931471805599456 |
| log(dp/n) | 同输入 | 以10为底的对数 | log(100.0) -> 2 |
| log(n,n) | dp | 求给定底数的对数 | log(2.0, 64.0) -> 6.0000000000 |
| mod(y, x) | 同输入 | 除法求余 | mod(9,4) -> 1 |
| pi() | dp | "π" 常数值 | 3.14159265358979 |
| power(dp,dp) | 同输入 | 求幂 | power(9.0, 3.0）-> |
| radians(dp) | dp | 角度转弧度 | radians(45.0) -> 0.785398163397448 |
| round(dp/n) | 同输入 | 近似取整 | round(42.4) -> 42 |
| round(dp/n, i) | n | 近似保留位数 | round(42.4382, 2) -> 42.44 |
| sign(dp/n) | 同输入 | 取符号(-1, 0, +1) | sign(-8.4)-> -1 |
| sqrt(dp/n ) | 同输入 | 平方根 | sqrt(2.0) -> 1.4142135623731 |
| trunc(dp/n) | 同输入 | 截断取整 | trunc(42.8) -> 42 |
| trunc(n, i) | n | 截断保留位数 | trunc(42.4382, 2) -> 42.43 |
| width_bucket(n,n,n,i) | i | 求某值(参数1)，落入在将从b1(参数2)到b2(参数3)分隔成为i个桶中的桶的位置 | width_bucket(5.35, 0.024, 10.06, 5) -> 3 |
| width_bucket(dp,dp,dp,i) | i | 同width_bucket(参数类型差别) | |
| random() | n | 取0和1之间的随机数 | random() -> 0.132324 |
| setseed(dp) | n | 设置随机数种子 | |
| sin(x) | dp | 三角函数sin | |
| cos(x) | dp | 三角函数cos | |
| tan(x) | dp | 三角函数tan | |
| cot(x) | dp | 三角函数cotangent | |
| asin(x) | dp | sin倒数 | |
| acos(x) | dp | cos倒数 | |
| atan(x) | dp | tangent倒数 | |
| atan2(y,x) | dp | tangent y/x 的倒数 |
举几个SQL语句的例子
了解了上述操作符和函数定义之后，可以在SQL语句中作为表达式进行使用，示例如下：
select 1+2, 3-4, 5*6, 7/8;
?column? | ?column? | ?column? | ?column?
----------+----------+----------+----------
3 | -1 | 30 | 0
(1 row)
with D(d1,d2) as (values (1.1, -2.1), (-1.8, 3.9) ) select d1, ceil(d1), floor(d1), round(d1) from D;
d1 | ceil | floor | round
------+------+-------+-------
1.1 | 2 | 1 | 1
-1.8 | -1 | -2 | -2
(2 rows)
Time: 184.648 ms
可以看到，这些数学操作符和函数，和正常的SQL表达式使用是没有什么差异的，非常简单易用。
有什么需要注意的地方吗
笔者认为， 作为一个编程和应用平台，Postgres支持相关数学数值的计算和操作，也是应有之义。但我们应该能够感觉到，如果在SQL中加入了一些比较高级的数学计算(例如求根、三角函数等)，当数据量比较大的时候，还是会对性能造成一些影响的。所以，数据库系统的长处在于数据的存储和查询，而非计算。有相关的处理需求，需要仔细的评估和验证，确保这些操作不会对高负荷的数据库应用造成影响。
如果在这方面出现问题，可以考虑两种缓解方案。一是使用外部程序，如在前端进行处理和计算；二是如果数据变更不频繁，可以考虑计算后进行存储，而非查询时计算。
另一个需要注意的地方就是，很多操作符和操作函数的参数，是有数据类型的限制的，在实际使用时，需要注意相关参数的匹配和数据类型的正确转换。
小结
本文讨论了Postgres中，如何支持数学计算的实现方式，包括可用的计算方式，相关的操作符和函数等等。这部分的内容不是很多，概念也比较清晰简单。开发者只需要在合适的场景中，知晓这些功能特性，并且能够按照业务需求灵活应用即可。
转载自:https://juejin.cn/post/7395867506056151094