迹忆客 专注技术分享

今天，我们将学习在 PostgreSQL 中将平均值舍入到小数点后 2 位。

在 PostgreSQL 中使用 AVG() 函数获取集合的平均值

AVG() 函数给出 PostgreSQL 中的平均值。这是一个聚合函数，允许我们计算平均值。

语法：

AVG(Column_Name)

假设我们要计算表 CAT 中具有 ID 和名称的列的平均值。然后，我们可以这样做，如下所示。

SELECT AVG(Name) from CAT;

这将返回表 CAT 列内的值的平均值。

让我们看看如何将获得的平均值四舍五入到不同的小数位。

使用 ROUND 函数将平均值舍入到 2 个小数位

ROUND 函数在 PostgreSQL 文档的 MATHEMATICAL FUNCTIONS AND OPERATORS 标题下给出。数学函数返回与其参数中提供的相同的数据类型。

ROUND 函数有两种不同的语法。

语法 1：

ROUND(dp or numeric)

语法 2：

ROUND(v numeric, s int)

第一个查询类型倾向于四舍五入到最接近的整数，这意味着你不能提供自定义小数位。因此，如果你在 42.4 上调用 ROUND 函数，它将返回 42。

如果你在函数中提供小数位参数，则参数中的 DP 代表双精度。数字类型可以是 2、4 和 8 字节浮点数或具有可选精度的浮点数。

第二个函数还考虑了你可能想要四舍五入的小数位，因此如果你调用一个函数将数字 42.4382 四舍五入为 3 位小数，你可以编写如下查询：

select ROUND(42.4382, 3)

输出：

你还可以看到运行以下内容：

select ROUND(42.5)

它将返回 43 作为整数，用 0.5 向上舍入趋向于舍入到最接近整数的上限部分。下限整数为 42，上限部分为 43。

现在，让我们制作一个表格，看看我们如何运行以下内容。让我们用两列 ID 和 CGPA 定义一个表 STUDENT。

CREATE TABLE student (
                       ID int PRIMARY KEY,
                       CGPA float8
)

现在让我们调用 INSERT 语句将一些值输入到这个新创建的表中。

insert into student values (1, 2.3), (2, 4), (3, 4) , (4, 3.76)

假设我们想要获得此表中列出的所有 CGPA 的 AVERAGE。使用 INTEGER 参数会导致我们得到不准确的答案。

所以我们可以运行一个查询：

select round(avg(CGPA), 2)
from student

为什么我们使用 2？因为它是我们所有 CGPA 中最多的小数位数。

我们可以使用这个数字四舍五入得到一个非常准确的答案。但是为什么会出现错误？

输出：

ERROR:  function round(double precision, integer) does not exist
LINE 10: select round(avg(CGPA), 2)

前一个 ROUND 语句允许 DP（双精度），但后一个查询语法不允许。

在该函数中，你只能使用小数点前最多 131072 位的数字。之后最多为 16383 位。

因此，float8 不起作用，因此你会遇到语法错误。

在 PostgreSQL 中使用 CAST 来消除四舍五入的语法错误

在之前的解决方案中，我们遇到了一个问题，即 ROUND 的后一种语法不支持浮点或双精度类型。因此，要运行查询，我们可以将 CAST 转换为数字作为可能的解决方案。

众所周知，numeric 的小数点后可以有大约 17000 位数字。我们可以继续有效地使用 CAST。

select round(avg(CGPA)::numeric, 2)
from student

这将返回 3.52，即对 CGPA 集进行四舍五入的确切结果。我们可以用于 CAST 的另一种语法是：

select round(CAST(avg(CGPA) as numeric), 2)
from student

在 PostgreSQL 中使用 TO CHAR 进行更好的十进制格式和舍入

在 PostgreSQL 中舍入任何数字的另一个方便的解决方法是使用 TO_CHAR 函数。它存在于 PostgreSQL 中的 DATA TYPE FORMATTING FUNCTIONS 分类下。

它有助于将各种数据类型转换为字符串，反之亦然。我们可以使用以下语法转换为自定义小数位。

to_char ( numeric_type, text ) ? text

to_char(125, '999') ? 125

to_char(125.8::real, '999D9') ? 125.8

to_char(-125.8, '999D99S') ? 125.80-

你可以在上面看到的第二个参数有助于定义我们对数字进行四舍五入并打印其输出的模板。因此，如果我们想将一个数字四舍五入到特定的小数位，我们可以将模板定义为以下之一。

1. FM（前缀） - 填充模式（抑制前导零和填充空白），FMMonth
2. TH（后缀）- 大写序数后缀，DDTH，例如 12TH
3. the（后缀）- 小写序数后缀，DDth，例如 12th
4. FX（前缀）- 固定格式全局选项（见使用说明），FX Month DD Day
5. TM（前缀） - 翻译模式（使用基于 lc_time 的本地化日期和月份名称），TMMonth
6. SP（后缀） - 拼写模式（未实现），DDSP

使用 TO_CHAR 函数，让我们使用 TO_CHAR 函数对数字 352.45 进行四舍五入。

select to_char(352.45, 'FM999D9')

输出：

352.5

这意味着这可以正常工作。但是你可能想知道，我们附加到整数的 D 和 FM 是什么？

D 表示尾随小数位，并定义查询将其四舍五入的值的数量。

FM 代表填充模式。它删除所有空白和前导零。

如果你不把 FM 放在它前面，它会给出这样的输出。

输出：

"[........] 352.5"

因此，在我们的四舍五入结果之前放置 FM 会删除无效字符。

在 PostgreSQL 中为 ROUND 使用自定义重载函数

另一种对平均值进行四舍五入的方法可能是原始 ROUND 函数不支持的浮点类型，可以是创建一个可以重载它的函数。然后将提供的浮点类型参数 CAST 转换为数字，以便 ROUND 起作用。

此函数将再次调用 ROUND，但 CAST 是传递给 numeric 的第一个参数。

例子：

create function ROUND(num float, decim_places int) returns NUMERIC as $f$
 select ROUND(num::numeric, decim_places)
$f$ language SQL immutable

然后我们调用 ROUND 函数如下。

select ROUND(avg(cgpa), 2) from student

现在，这将在我们的查询中返回正确的结果，而无需任何 CASTING。

ROUND 重载函数有两个参数，一个是浮点数，另一个是整数。此函数返回由 SELECT ROUND() 操作正式返回的数值数据类型，该操作将 NUM 参数转换为数值。

为什么我们在函数中使用 IMMUTABLE 关键字？可以使用 IMMUTABLE、STABLE 或 VOLATILE 关键字定义函数。

IMMUTABLE 属性意味着如果一个函数不断地被赋予相同的参数，它不会调用该函数而是返回恒定的函数值。

如果你查看 PostgreSQL 文档的 FUNCTION VOLATILITY CATEGORIES 分类，你会注意到 IMMUTABLE 关键字的一个很好的示例。

如果你运行上面的查询，

SELECT Round(3.52, 1)

它返回一次 3.5；然后，如果你再次调用该函数，该函数将不会运行该查询。相反，它返回 3.5 作为其自身，因为参数未更改。

如果在创建函数时未定义此类属性，则这些函数中的任何一个的默认值为 VOLATILE。

对 PostgreSQL 中创建的 ROUND 函数的修改

可以修改我们上面创建的 ROUND 函数，以使用精度值为我们提供更好看的结果。

假设我们要将 21.56 四舍五入到 0.05 的精度；答案是怎么出来的？

如果我们没有定义精度，那么只有 21.56 的 ROUND 函数将返回 22。但是，在精度为 0.05 的情况下，我们可能需要解决 (+- 0.05) 的值错误。

一个很好的方法是将数字除以定义的准确度值，即 0.05，四舍五入我们得到的答案，然后再次乘以 0.05（准确度值）。

因此，如果我们想用更好的估计来四舍五入 21.56，我们可以说：

21.56/0.05 = 431.2

使用值 431.2，我们可以将其四舍五入为 431，然后将其乘以 0.05 得到答案 21.55，完美地四舍五入而没有错误 (+- 0.05)。

如果我们希望答案在小数点后一位以内，我们可以假设误差为 (+- 0.5)，然后执行以下操作：

21.56/0.5 = 43.12

四舍五入将得到 43，乘以 0.5 得到舍入值 21.5。这很好，因为存在估计错误，并且 (21.56 ~~ 21.6) 以这种方式不正确。

现在你了解了准确性的工作原理，让我们再次使用 ROUND 定义一个函数以返回正确的值。我们将传递两个参数，一个是我们要四舍五入的数字，另一个是准确度值。

该函数的其余部分将遵循与上面提供的相同的语法。

create function ROUND(num float, acc float) returns FLOAT as $f$
	select ROUND(num/acc)*acc
$f$ language SQL immutable

因此，当你以 0.05 的准确度值运行查询时，

select ROUND(avg(cgpa), 0.02::float8) from student

输出：

3.52 (double precision)

为了安全起见，我们使用了 FLOAT8 铸件；如果我们有另一个具有相同数量参数的函数，我们可能会混淆我们的 PostgreSQL 数据库。

PostgreSQL 中 ROUND 重载的性能声明

已添加此部分以了解 ROUND 可用的最佳选项。

我们将为此添加一个摘要，以便他们可以浏览并进行操作。但是，需要高效和更快解决方案的开发人员可以继续阅读详细说明。

• Function Overload 比 ROUND 的 CAST 编码更快。
• 当结合 JIT 优化时，SQL 可能会在性能上超过 PLPGSQL。

有两种方法可以对浮点数或双精度进行舍入。一个是标准 CAST，另一个是 FUNCTION OVERLOAD。

使用 CAST 运算符会在性能上产生相当大的差异，如果你关闭 SEQ SCANS，成本会高出 7 倍。将此与 FUNCTION OVERLOAD 进行比较，你会发现后者更好。

具有诸如 STABLE 或 IMMUTABLE 等属性的 FUNCTION OVERLOAD 往往会减少运行函数带来的开销，从而显着提高性能并随着时间的推移减少问题。

如果每次都调用常量参数，则查询不必重复运行。相反，函数值按原样返回相同的值。

在我们这里的函数中，我们使用了语言 SQL 而不是 PLPGSQL，PLPGSQL 也可以使用。如果我们要提高性能，为什么我们使用 SQL 而不是 PLPGSQL？

PostgreSQL 文档指出 PLPGSQL 是一种更好的程序计算方式。为什么？

SQL 会以增量方式发送查询，处理它们，等待，然后在发送另一个查询之前进行计算，PLPGSQL 会将这些计算分组并减少 SQL 倾向于执行的多次解析。

在这种情况下，PLPGSQL 似乎是减少开销的完美选择。然而，SQL 往往更适合 JIT OPTIMIZATION。

JIT 代表 JUST-IN-TIME，这意味着它在运行时评估 CPU 最早可以执行的查询以节省时间。JIT 加速操作使用内联来减少函数调用的开销。

JIT 会将这些函数的主体内联到可以执行的不同表达式中。当然，这会降低性能开销。

JIT OPTIMIZATION 在我们的 PostgreSQL 数据库中使用 LLVM 基础架构。在 LLVM 文档的 TRANSFORM PROCESS 部分，你可以看到优化如何工作并有效地产生结果。

PostgreSQL 中具有不同数字表示的 ROUND

浮点数是二进制表示。如果你还记得，你会意识到浮点数带有尾数，一个数字表达式，前面有指数增加/减少或前面的符号。

浮点数以三种不同的模式四舍五入：

ROUND TOWARDS ZERO: 1.5 to 1 (truncate extra digits)
ROUND HALF-WAY FROM ZERO: 1.5 to 2 (if fraction equal to half of the base, go forward to the nearest integer)
ROUND HALF TO EVEN (Banker's ROUND): 1.5 to 2 but 2.5 to 2 (we always round off to even, resulting in case of half base to reduce errors)

让我们从对十进制浮点数进行四舍五入开始。这种四舍五入很简单，并遵循四舍五入到底数一半处最接近整数的基本语法。

在开始之前，你必须清楚地了解用于四舍五入不同数字表示的公式； DECIMAL、BINARY 和 HEXA-DECIMAL。我们将使用具有不同 RADIX（数字基数）值的标准化 IBM 提供的公式。

公式：

sign(x)* ( b(e-n) ) * floor( abs(x) * ( b(n-e) ) + 1/2

在这个公式中，b 代表基数。e 代表我们想要四舍五入的值的指数。

ABS 代表绝对值，一个正数表示。FLOOR 代表前面最接近的整数。

2.5 的 FLOOR 将是 2，而 CEILING 将是 3。

因此，使用不同的 RADIX 值将为每种表示形式提供不同的答案。如果我们用十进制表示法对 3.567 进行四舍五入，我们将得到：

 3.6 for decimal digit = 1

使用 BINARY，我们会得到：

3.6 for decimal digit = 3/4

并使用十六进制，

3.6 for decimal digit = 1/2

因此，我们现在可以为这些不同的数字表示创建一个函数。

对于我们的十进制情况，我们将调用 ROUND 函数。但是，我们将更改二进制和十六进制的语句。

对于两者，我们首先将我们的数字分成两个不同的部分，一个在小数部分之前，另一个在小数部分之后。

如果要四舍五入的数字小于最高有效位，我们会将我们的值截断为 BIN_BITS 和我们四舍五入的数字之间的差值。否则，我们将截断这些数字。

CREATE FUNCTION trunc_bin(x bigint, t int) RETURNS bigint AS $f$
    SELECT ((x::bit(64) >> t) << t)::bigint;
$f$ language SQL IMMUTABLE;

CREATE FUNCTION ROUND(
    x float,
    xtype text,  -- 'bin', 'dec' or 'hex'
    xdigits int DEFAULT 0
)
RETURNS FLOAT AS $f$
    SELECT CASE
        WHEN xtype NOT IN ('dec','bin','hex') THEN 'NaN'::float
        WHEN xdigits=0 THEN ROUND(x)
        WHEN xtype='dec' THEN ROUND(x::numeric,xdigits)
        ELSE (s1 ||'.'|| s2)::float
        END
    FROM (
        SELECT s1,
            lpad(
                trunc_bin( s2::bigint, CASE WHEN xd<bin_bits THEN bin_bits - xd ELSE 0 END )::text,
                l2,
                '0'
            ) AS s2
        FROM (
             SELECT *,
                (floor( log(2,s2::numeric) ) +1)::int AS bin_bits, -- most significant bit position
                CASE WHEN xtype='hex' THEN xdigits*4 ELSE xdigits END AS xd
            FROM (
                SELECT s[1] AS s1, s[2] AS s2, length(s[2]) AS l2
                FROM (SELECT regexp_split_to_array(x::text,'\.')) t1a(s)
             ) t1b
        ) t1c
    ) t2
$f$ language SQL IMMUTABLE;

因此，我们可以使用它在任何可能的数字表示上调用 ROUND。

在 PostgreSQL 中使用 TRUNC 进行舍入显示

你可以使用的另一个函数是 TRUNC 方法，它会在特定标记处截断值。

所以在位置 2 的 42.346 上调用 TRUNC 将使它：

42.34

如果需要四舍五入到最接近的 FLOOR 整数，可以使用 TRUNC，如下所示：

SELECT TRUNC(42.346, 2)