由 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;
会告诉您区域是什么设置,您可以将其更新为其他货币。
总结
- 如果您只要处理整数的美分,并且您不需要美分的小数部分,可以使用
int
或bigint
。这样可以节省空间并提供更好的性能。将您的钱以美分存储,并在您的输出中转换为小数。如果所有货币都是同一类型,这也确实是首选的方法。如果您经常更换货币并处理小数美分,请尝试numeric
。 - 要以分数美分为单位存储货币,可以使用
numeric
,甚至存储到许多许多小数点。如果您需要支持大量精度的货币值,这是最好的选择,但它会有一些存储和性能成本。 - 将货币与实际货币值分开存储,以便您可以对货币换算进行计算。