使用 PostgreSQL 处理货币数据

John Doe 十月 14, 2024

摘要:在本文中,我们将学习如何在 PostgreSQL 中处理货币数据。

目录

货币数据类型

货币

PostgreSQL 实际上确实有一个money数据类型。但不建议使用它,因为它不处理一分钱的小数部分,并且货币与数据库区域设置相关联。虽然 money 类型不是存储货币的最佳实践,但我确实认为,当希望将查询输出格式化为货币值时,money 对于转换数据非常方便。

浮点型

在任何使用带小数的正数和负数的系统中,浮点数通常都很流行(因为名称 float 意味着数字在数字小数位上浮动)。浮点型(real/float4)和双浮点数据类型(float8)可以用来存取货币数据,但它们通常并不理想,因为它们基本上不精确。

例如,这是正确的:

select 0.0001::float4;

这也是正确的:

select 0.0001::float4 + 0.0001::float4;

但是,如果我们尝试进行额外的分数运算,这并不会得到真正的预期结果:

select 0.0001::float4 + 0.0001::float4 + 0.0001::float4;

整型

很多人用integer来存储货币数据。整数不支持任何类型的小数,因此 100/3 可能等于 33.3333,但在整数的数学运算中,它只会得到 33。如果您知道您的最小单位是什么(甚至低至一分钱的分数部分),并且可以在数据库中使用乘数,这可以用于存储货币数据。因此,处理整数分钱的乘数为 100,如果您想表示 0.237928372 BTC 之类的金额,则乘数为 1000000000。这个单位是整体存储的,这解决了 float 无法表示的值的问题。这种技术仍然存在重大限制,因为integer最大只能存储 2147483647 的数字,bigint最大只能存储 9223372036854775807。

Integer 具有显著的性能和存储效率。它只是一个 4 字节大小的列,如果您使用的是bigint,则是 8 字节。另外,请记住,将货币存储为整数,要以传统格式输出,供前端或 sql 报告表示一元、一分或十进制数字,需要除法或转换为不同的数据类型。

数值型

numeric/decimal被广泛认为是在 PostgreSQL 中存储货币数据的理想数据类型。您可以输入很多小数位(10,000+ 位数字!),并且可以定义精度。数值数据类型有两个限定符,即精度和小数位,用于定义要使用的合理小数位数。创建类型时,它看起来会像NUMERIC(7,5)这样,其中精度为 7,小数部分为 5。

  • 精度是小数点前后的总位数。您需要将其设置为您可能需要存储的最高数量。所以这里 99.99999 是最大值,-99.99999 是最小值。
  • 小数部分是小数点后的数字数,因此这将是 5 位小数。

选择小数部位意味着 PostgreSQL 将在某个时候对数字进行舍入。如果要防止四舍五入,请确保您的 scale 数字确实非常高。

与整数相比,数值数据类型要占用很多空间,每列行 10 个字节。因此,如果您非常关心占用空间和性能,而数值型的精度不是那么重要,那么使用 integer 可能会更好。

存储货币数据

好的,我们有一个数据类型来存储实际的美分、美元、欧元等。现在我们如何存储货币?一般来说,如果您需要同时以多种货币存储资金,最好将货币与数字本身一起存储。如果您想要官方货币代码,请参阅 ISO 4217。您可以使用自定义检查约束来要求输入数据仅为某些货币类型,比如,如果您使用的是可能看起来像美元、英镑和欧元的货币值。

这是本教程中加载的内容。

CREATE TABLE products (
    sku SERIAL PRIMARY KEY,
    name VARCHAR(255),
    price NUMERIC(7,5),
    currency TEXT CHECK (currency IN ('USD', 'EUR', 'GBP'))
);

如果您正在处理多种格式的货币,则需要考虑很多因素。在许多情况下,在事务中会发生很多事情。假设数据库中以 USD 为单位设置的价格,要以 GBP 显示给用户。您会有一个像上面一样的设置,还有另外一个 GBP 汇率表。也许该表会通过 API 进行更新,因为货币价值在一天中会波动。您可能以一种货币设置价格,而以另一种货币支付价格,输入的是购买时支付的金额。

货币处理函数

  • 平均值

四舍五入到最接近的美分

SELECT ROUND(AVG(price), 2) AS truncated_average_price
FROM products;
  • 用 ceiling 四舍五入

求和并四舍五入到最接近的整数

SELECT CEIL(SUM(price)) AS rounded_total_price
FROM products;
  • 用 floor 向下舍入

求和并向下舍入到最接近的整数

SELECT FLOOR(SUM(price)) AS rounded_total_price
FROM products;
  • 中位数

计算中位数可能会稍微复杂一些,因为 PostgreSQL 没有内置的中位数函数,但您可以使用窗口函数来计算它

WITH sorted_prices AS (
    SELECT price,
           ROW_NUMBER() OVER (ORDER BY price) as r,
           COUNT(*) OVER () as total_count
    FROM products
)
SELECT FLOOR(AVG(price)) AS rounded_median_price
FROM sorted_prices
WHERE r IN (total_count / 2, (total_count + 1) / 2);
  • 转换到货币类型

如果您希望结果包含货币符号、逗号和点号。

SELECT CEIL(SUM(price))::money AS rounded_total_price_money
FROM products;

请注意,货币符号将根据您的区域设置进行显示,用show lc_monetary;会告诉您区域是什么设置,您可以将其更新为其他货币。

总结

  • 如果您只要处理整数的美分,并且您不需要美分的小数部分,可以使用intbigint。这样可以节省空间并提供更好的性能。将您的钱以美分存储,并在您的输出中转换为小数。如果所有货币都是同一类型,这也确实是首选的方法。如果您经常更换货币并处理小数美分,请尝试numeric
  • 要以分数美分为单位存储货币,可以使用numeric,甚至存储到许多许多小数点。如果您需要支持大量精度的货币值,这是最好的选择,但它会有一些存储和性能成本。
  • 将货币与实际货币值分开存储,以便您可以对货币换算进行计算。