我們經(jīng)常需要用數(shù)據(jù)庫來處理商品交易的貨幣值，在 PostgreSQL 中，有多種不同的方法來處理貨幣數(shù)據(jù)。
貨幣數(shù)據(jù)類型
貨幣
PostgreSQL 實(shí)際上確實(shí)有一個(gè)money數(shù)據(jù)類型。但不建議使用它，因?yàn)樗惶幚硪环皱X的小數(shù)部分，并且貨幣與數(shù)據(jù)庫區(qū)域設(shè)置相關(guān)聯(lián)。雖然 money 類型不是存儲貨幣的最佳實(shí)踐，但我確實(shí)認(rèn)為，當(dāng)希望將查詢輸出格式化為貨幣值時(shí)，money 對于轉(zhuǎn)換數(shù)據(jù)非常方便。
浮點(diǎn)型
在任何使用帶小數(shù)的正數(shù)和負(fù)數(shù)的系統(tǒng)中，浮點(diǎn)數(shù)通常都很流行（因?yàn)槊Q float 意味著數(shù)字在數(shù)字小數(shù)位上浮動(dòng)）。浮點(diǎn)型（real/float4）和雙浮點(diǎn)數(shù)據(jù)類型（float8）可以用來存取貨幣數(shù)據(jù)，但它們通常并不理想，因?yàn)樗鼈兓旧喜痪_。
例如，這是正確的：
select 0.0001::float4;
這也是正確的：
select 0.0001::float4 + 0.0001::float4;
但是，如果我們嘗試進(jìn)行額外的分?jǐn)?shù)運(yùn)算，這并不會得到真正的預(yù)期結(jié)果：
select 0.0001::float4 + 0.0001::float4 + 0.0001::float4;
整型
很多人用integer來存儲貨幣數(shù)據(jù)。整數(shù)不支持任何類型的小數(shù)，因此 100/3 可能等于 33.3333，但在整數(shù)的數(shù)學(xué)運(yùn)算中，它只會得到 33。如果您知道您的最小單位是什么（甚至低至一分錢的分?jǐn)?shù)部分），并且可以在數(shù)據(jù)庫中使用乘數(shù)，這可以用于存儲貨幣數(shù)據(jù)。因此，處理整數(shù)分錢的乘數(shù)為 100，如果您想表示 0.237928372 BTC 之類的金額，則乘數(shù)為 1000000000。這個(gè)單位是整體存儲的，這解決了 float 無法表示的值的問題。這種技術(shù)仍然存在重大限制，因?yàn)?code style="-webkit-tap-highlight-color: transparent; margin: 0px; padding: 3px 5px; outline: 0px; max-width: 100%; box-sizing: border-box !important; overflow-wrap: break-word !important; text-align: left; line-height: 1.75; font-size: 14.4px; color: rgb(221, 17, 68); background: rgba(27, 31, 35, 0.05); border-radius: 4px;">integer最大只能存儲 2147483647 的數(shù)字，bigint最大只能存儲 9223372036854775807。
Integer 具有顯著的性能和存儲效率。它只是一個(gè) 4 字節(jié)大小的列，如果您使用的是bigint，則是 8 字節(jié)。另外，請記住，將貨幣存儲為整數(shù)，要以傳統(tǒng)格式輸出，供前端或 sql 報(bào)告表示一元、一分或十進(jìn)制數(shù)字，需要除法或轉(zhuǎn)換為不同的數(shù)據(jù)類型。
數(shù)值型
numeric/decimal被廣泛認(rèn)為是在 PostgreSQL 中存儲貨幣數(shù)據(jù)的理想數(shù)據(jù)類型。您可以輸入很多小數(shù)位（10,000+ 位數(shù)字?。?，并且可以定義精度。數(shù)值數(shù)據(jù)類型有兩個(gè)限定符，即精度和小數(shù)位，用于定義要使用的合理小數(shù)位數(shù)。創(chuàng)建類型時(shí)，它看起來會像NUMERIC(7,5)這樣，其中精度為 7，小數(shù)部分為 5。
選擇小數(shù)部位意味著 PostgreSQL 將在某個(gè)時(shí)候?qū)?shù)字進(jìn)行舍入。如果要防止四舍五入，請確保您的 scale 數(shù)字確實(shí)非常高。
與整數(shù)相比，數(shù)值數(shù)據(jù)類型要占用很多空間，每列行 10 個(gè)字節(jié)。因此，如果您非常關(guān)心占用空間和性能，而數(shù)值型的精度不是那么重要，那么使用 integer 可能會更好。
存儲貨幣數(shù)據(jù)
好的，我們有一個(gè)數(shù)據(jù)類型來存儲實(shí)際的美分、美元、歐元等?，F(xiàn)在我們?nèi)绾未鎯ω泿?？一般來說，如果您需要同時(shí)以多種貨幣存儲資金，最好將貨幣與數(shù)字本身一起存儲。如果您想要官方貨幣代碼，請參閱 ISO 4217。您可以使用自定義檢查約束來要求輸入數(shù)據(jù)僅為某些貨幣類型，比如，如果您使用的是可能看起來像美元、英鎊和歐元的貨幣值。
這是本教程中加載的內(nèi)容。
CREATE TABLE products (
sku SERIAL PRIMARY KEY,
name VARCHAR(255),
price NUMERIC(7,5),
currency TEXT CHECK (currency IN ('USD', 'EUR', 'GBP'))
);
如果您正在處理多種格式的貨幣，則需要考慮很多因素。在許多情況下，在事務(wù)中會發(fā)生很多事情。假設(shè)數(shù)據(jù)庫中以 USD 為單位設(shè)置的價(jià)格，要以 GBP 顯示給用戶。您會有一個(gè)像上面一樣的設(shè)置，還有另外一個(gè) GBP 匯率表。也許該表會通過 API 進(jìn)行更新，因?yàn)樨泿艃r(jià)值在一天中會波動(dòng)。您可能以一種貨幣設(shè)置價(jià)格，而以另一種貨幣支付價(jià)格，輸入的是購買時(shí)支付的金額。
貨幣處理函數(shù)
四舍五入到最接近的美分
SELECT ROUND(AVG(price), 2) AS truncated_average_price
FROM products;
求和并四舍五入到最接近的整數(shù)
SELECT CEIL(SUM(price)) AS rounded_total_price
FROM products;
求和并向下舍入到最接近的整數(shù)
SELECT FLOOR(SUM(price)) AS rounded_total_price
FROM products;
計(jì)算中位數(shù)可能會稍微復(fù)雜一些，因?yàn)?PostgreSQL 沒有內(nèi)置的中位數(shù)函數(shù)，但您可以使用窗口函數(shù)來計(jì)算它
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);
如果您希望結(jié)果包含貨幣符號、逗號和點(diǎn)號。
SELECT CEIL(SUM(price))::money AS rounded_total_price_money
FROM products;
請注意，貨幣符號將根據(jù)您的區(qū)域設(shè)置進(jìn)行顯示，用show lc_monetary;會告訴您區(qū)域是什么設(shè)置，您可以將其更新為其他貨幣。
總結(jié)
? 如果您只要處理整數(shù)的美分，并且您不需要美分的小數(shù)部分，可以使用int或bigint。這樣可以節(jié)省空間并提供更好的性能。將您的錢以美分存儲，并在您的輸出中轉(zhuǎn)換為小數(shù)。如果所有貨幣都是同一類型，這也確實(shí)是首選的方法。如果您經(jīng)常更換貨幣并處理小數(shù)美分，請嘗試numeric。
? 要以分?jǐn)?shù)美分為單位存儲貨幣，可以使用numeric，甚至存儲到許多許多小數(shù)點(diǎn)。如果您需要支持大量精度的貨幣值，這是最好的選擇，但它會有一些存儲和性能成本。
? 將貨幣與實(shí)際貨幣值分開存儲，以便您可以對貨幣換算進(jìn)行計(jì)算。
該文章在 2024/10/14 10:07:45 編輯過
400 186 1886
