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

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

貨幣

PostgreSQL 實際上確實有一個money數(shù)據(jù)類型。但不建議使用它，因為它不處理一分錢的小數(shù)部分，并且貨幣與數(shù)據(jù)庫區(qū)域設置相關聯(lián)。雖然 money 類型不是存儲貨幣的最佳實踐，但我確實認為，當希望將查詢輸出格式化為貨幣值時，money 對于轉換數(shù)據(jù)非常方便。

浮點型

在任何使用帶小數(shù)的正數(shù)和負數(shù)的系統(tǒng)中，浮點數(shù)通常都很流行（因為名稱 float 意味著數(shù)字在數(shù)字小數(shù)位上浮動）。浮點型（real/float4）和雙浮點數(shù)據(jù)類型（float8）可以用來存取貨幣數(shù)據(jù)，但它們通常并不理想，因為它們基本上不精確。

例如，這是正確的：

select 0.0001::float4;

這也是正確的：

select 0.0001::float4 + 0.0001::float4;

但是，如果我們嘗試進行額外的分數(shù)運算，這并不會得到真正的預期結果：

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

整型

很多人用integer來存儲貨幣數(shù)據(jù)。整數(shù)不支持任何類型的小數(shù)，因此 100/3 可能等于 33.3333，但在整數(shù)的數(shù)學運算中，它只會得到 33。如果您知道您的最小單位是什么（甚至低至一分錢的分數(shù)部分），并且可以在數(shù)據(jù)庫中使用乘數(shù)，這可以用于存儲貨幣數(shù)據(jù)。因此，處理整數(shù)分錢的乘數(shù)為 100，如果您想表示 0.237928372 BTC 之類的金額，則乘數(shù)為 1000000000。這個單位是整體存儲的，這解決了 float 無法表示的值的問題。這種技術仍然存在重大限制，因為integer最大只能存儲 2147483647 的數(shù)字，bigint最大只能存儲 9223372036854775807。

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

數(shù)值型

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

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

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

選擇小數(shù)部位意味著 PostgreSQL 將在某個時候對數(shù)字進行舍入。如果要防止四舍五入，請確保您的 scale 數(shù)字確實非常高。

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

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

好的，我們有一個數(shù)據(jù)類型來存儲實際的美分、美元、歐元等?，F(xiàn)在我們?nèi)绾未鎯ω泿?？一般來說，如果您需要同時以多種貨幣存儲資金，最好將貨幣與數(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'))
);

如果您正在處理多種格式的貨幣，則需要考慮很多因素。在許多情況下，在事務中會發(fā)生很多事情。假設數(shù)據(jù)庫中以 USD 為單位設置的價格，要以 GBP 顯示給用戶。您會有一個像上面一樣的設置，還有另外一個 GBP 匯率表。也許該表會通過 API 進行更新，因為貨幣價值在一天中會波動。您可能以一種貨幣設置價格，而以另一種貨幣支付價格，輸入的是購買時支付的金額。

貨幣處理函數(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ù)

計算中位數(shù)可能會稍微復雜一些，因為 PostgreSQL 沒有內(nèi)置的中位數(shù)函數(shù)，但您可以使用窗口函數(shù)來計算它

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;

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

總結

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

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

• ? 將貨幣與實際貨幣值分開存儲，以便您可以對貨幣換算進行計算。