我們經(jīng)常需要用數(shù)據(jù)庫(kù)來(lái)處理商品交易的貨幣值，在 PostgreSQL 中，有多種不同的方法來(lái)處理貨幣數(shù)據(jù)。

貨幣數(shù)據(jù)類型

貨幣

PostgreSQL 實(shí)際上確實(shí)有一個(gè)money數(shù)據(jù)類型。但不建議使用它，因?yàn)樗惶幚硪环皱X的小數(shù)部分，并且貨幣與數(shù)據(jù)庫(kù)區(qū)域設(shè)置相關(guān)聯(lián)。雖然 money 類型不是存儲(chǔ)貨幣的最佳實(shí)踐，但我確實(shí)認(rèn)為，當(dāng)希望將查詢輸出格式化為貨幣值時(shí)，money 對(duì)于轉(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）可以用來(lái)存取貨幣數(shù)據(jù)，但它們通常并不理想，因?yàn)樗鼈兓旧喜痪_。

例如，這是正確的：

select 0.0001::float4;

這也是正確的：

select 0.0001::float4 + 0.0001::float4;

但是，如果我們嘗試進(jìn)行額外的分?jǐn)?shù)運(yùn)算，這并不會(huì)得到真正的預(yù)期結(jié)果：

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

整型

很多人用integer來(lái)存儲(chǔ)貨幣數(shù)據(jù)。整數(shù)不支持任何類型的小數(shù)，因此 100/3 可能等于 33.3333，但在整數(shù)的數(shù)學(xué)運(yùn)算中，它只會(huì)得到 33。如果您知道您的最小單位是什么（甚至低至一分錢的分?jǐn)?shù)部分），并且可以在數(shù)據(jù)庫(kù)中使用乘數(shù)，這可以用于存儲(chǔ)貨幣數(shù)據(jù)。因此，處理整數(shù)分錢的乘數(shù)為 100，如果您想表示 0.237928372 BTC 之類的金額，則乘數(shù)為 1000000000。這個(gè)單位是整體存儲(chǔ)的，這解決了 float 無(wú)法表示的值的問(wèn)題。這種技術(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最大只能存儲(chǔ) 2147483647 的數(shù)字，bigint最大只能存儲(chǔ) 9223372036854775807。

Integer 具有顯著的性能和存儲(chǔ)效率。它只是一個(gè) 4 字節(jié)大小的列，如果您使用的是bigint，則是 8 字節(jié)。另外，請(qǐng)記住，將貨幣存儲(chǔ)為整數(shù)，要以傳統(tǒng)格式輸出，供前端或 sql 報(bào)告表示一元、一分或十進(jìn)制數(shù)字，需要除法或轉(zhuǎn)換為不同的數(shù)據(jù)類型。

數(shù)值型

numeric/decimal被廣泛認(rèn)為是在 PostgreSQL 中存儲(chǔ)貨幣數(shù)據(jù)的理想數(shù)據(jù)類型。您可以輸入很多小數(shù)位（10,000+ 位數(shù)字！），并且可以定義精度。數(shù)值數(shù)據(jù)類型有兩個(gè)限定符，即精度和小數(shù)位，用于定義要使用的合理小數(shù)位數(shù)。創(chuàng)建類型時(shí)，它看起來(lái)會(huì)像NUMERIC(7,5)這樣，其中精度為 7，小數(shù)部分為 5。

• ? 精度是小數(shù)點(diǎn)前后的總位數(shù)。您需要將其設(shè)置為您可能需要存儲(chǔ)的最高數(shù)量。所以這里 99.99999 是最大值，-99.99999 是最小值。

• ? 小數(shù)部分是小數(shù)點(diǎn)后的數(shù)字?jǐn)?shù)，因此這將是 5 位小數(shù)。

選擇小數(shù)部位意味著 PostgreSQL 將在某個(gè)時(shí)候?qū)?shù)字進(jìn)行舍入。如果要防止四舍五入，請(qǐng)確保您的 scale 數(shù)字確實(shí)非常高。

與整數(shù)相比，數(shù)值數(shù)據(jù)類型要占用很多空間，每列行 10 個(gè)字節(jié)。因此，如果您非常關(guān)心占用空間和性能，而數(shù)值型的精度不是那么重要，那么使用 integer 可能會(huì)更好。

存儲(chǔ)貨幣數(shù)據(jù)

好的，我們有一個(gè)數(shù)據(jù)類型來(lái)存儲(chǔ)實(shí)際的美分、美元、歐元等。現(xiàn)在我們?nèi)绾未鎯?chǔ)貨幣？一般來(lái)說(shuō)，如果您需要同時(shí)以多種貨幣存儲(chǔ)資金，最好將貨幣與數(shù)字本身一起存儲(chǔ)。如果您想要官方貨幣代碼，請(qǐng)參閱 ISO 4217。您可以使用自定義檢查約束來(lái)要求輸入數(shù)據(jù)僅為某些貨幣類型，比如，如果您使用的是可能看起來(lái)像美元、英鎊和歐元的貨幣值。

這是本教程中加載的內(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ù)中會(huì)發(fā)生很多事情。假設(shè)數(shù)據(jù)庫(kù)中以 USD 為單位設(shè)置的價(jià)格，要以 GBP 顯示給用戶。您會(huì)有一個(gè)像上面一樣的設(shè)置，還有另外一個(gè) GBP 匯率表。也許該表會(huì)通過(guò) API 進(jìn)行更新，因?yàn)樨泿艃r(jià)值在一天中會(huì)波動(dòng)。您可能以一種貨幣設(shè)置價(jià)格，而以另一種貨幣支付價(jià)格，輸入的是購(gòu)買時(shí)支付的金額。

貨幣處理函數(shù)

• ? 平均值

四舍五入到最接近的美分

SELECT ROUND(AVG(price), 2) AS truncated_average_price
FROM products;

• ? 用 ceiling 四舍五入

求和并四舍五入到最接近的整數(shù)

SELECT CEIL(SUM(price)) AS rounded_total_price
FROM products;

• ? 用 floor 向下舍入

求和并向下舍入到最接近的整數(shù)

SELECT FLOOR(SUM(price)) AS rounded_total_price
FROM products;

• ? 中位數(shù)

計(jì)算中位數(shù)可能會(huì)稍微復(fù)雜一些，因?yàn)?PostgreSQL 沒(méi)有內(nèi)置的中位數(shù)函數(shù)，但您可以使用窗口函數(shù)來(lái)計(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);

• ? 轉(zhuǎn)換到貨幣類型

如果您希望結(jié)果包含貨幣符號(hào)、逗號(hào)和點(diǎn)號(hào)。

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

請(qǐng)注意，貨幣符號(hào)將根據(jù)您的區(qū)域設(shè)置進(jìn)行顯示，用show lc_monetary;會(huì)告訴您區(qū)域是什么設(shè)置，您可以將其更新為其他貨幣。

總結(jié)

• ? 如果您只要處理整數(shù)的美分，并且您不需要美分的小數(shù)部分，可以使用int或bigint。這樣可以節(jié)省空間并提供更好的性能。將您的錢以美分存儲(chǔ)，并在您的輸出中轉(zhuǎn)換為小數(shù)。如果所有貨幣都是同一類型，這也確實(shí)是首選的方法。如果您經(jīng)常更換貨幣并處理小數(shù)美分，請(qǐng)嘗試numeric。

• ? 要以分?jǐn)?shù)美分為單位存儲(chǔ)貨幣，可以使用numeric，甚至存儲(chǔ)到許多許多小數(shù)點(diǎn)。如果您需要支持大量精度的貨幣值，這是最好的選擇，但它會(huì)有一些存儲(chǔ)和性能成本。

• ? 將貨幣與實(shí)際貨幣值分開(kāi)存儲(chǔ)，以便您可以對(duì)貨幣換算進(jìn)行計(jì)算。