货币数据建模¶
On this page
概述¶
Applications that handle monetary data often require the ability to capture fractional units of currency and need to emulate decimal rounding with exact precision when performing arithmetic. The binary-based floating-point arithmetic used by many modern systems (i.e., float, double) is unable to represent exact decimal fractions and requires some degree of approximation making it unsuitable for monetary arithmetic. This constraint is an important consideration when modeling monetary data.
本文提及的数学计算指的是在服务器端由 mongod or mongos 执行的计算而非客户端的计算。
Numeric Model¶
The numeric model may be appropriate if you need to query the database for exact, mathematically valid matches or need to perform server-side arithmetic, e.g., $inc, $mul, and aggregation framework arithmetic.
monetary-value-exact-precision 把货币值乘以10的次方数。
monetary-value-arbitrary-precision 使用两个字段，一个字段以非数字形式保存真实地货币数值，一个字段则以浮点数的形式保存这个货币数值的大约值。
- Using a Scale Factor to convert the monetary value to a 64-bit integer (long BSON type) by multiplying by a power of 10 scale factor.
Non-Numeric Model¶
If there is no need to perform server-side arithmetic on monetary data or if server-side approximations are sufficient, modeling monetary data using the non-numeric model may be suitable.
如果你经常需要在服务器端做一些对货币数值的数学计算，那么严格精度可能会更合适一点。例如：
假如你需要对货币数值做完全一致的匹配，那么可以考虑使用 monetary-value-exact-precision。
注解
假如你需要做一些服务器端的计算，如 $inc, $mul, 和 aggregation framework arithmetic， 那么可以使用 monetary-value-exact-precision。
Numeric Model¶
Using the Decimal BSON Type¶
3.4 新版功能.
如果没有在服务器端对货币数值进行计算的需要，那么使用任意精度模式的方式可能更为适合一点。例如：
From the mongo shell decimal values are assigned and queried using the NumberDecimal() constructor. The following example adds a document containing gas prices to a gasprices collection:
db.gasprices.insert{ "_id" : 1, "date" : ISODate(), "price" : NumberDecimal("2.099"), "station" : "Quikstop", "grade" : "regular" }
The following query matches the document above:
db.gasprices.find( { price: NumberDecimal("2.099") } )
For more information on the decimal type, see NumberDecimal.
如果你需要处理任意的或者未知的精度，参见 monetary-value-arbitrary-precision。¶
如果服务器端的近似值计算可以满足要求，或者能够利用客户端作进一步处理，那么也可以使用 monetary-value-arbitrary-precision。
One-Time Collection Transformation¶
A collection can be transformed by iterating over all documents in the collection, converting the monetary value to the decimal type, and writing the document back to the collection.
注解
It is strongly advised to add the decimal value to the document as a new field and remove the old field later once the new field’s values have been verified.
警告
Be sure to test decimal conversions in an isolated test environment. Once datafiles are created or modified with MongoDB version 3.4 they will no longer be compatible with previous versions and there is no support for downgrading datafiles containing decimals.
Scale Factor Transformation:
Consider the following collection which used the Scale Factor approach and saved the monetary value as a 64-bit integer representing the number of cents:
{ "_id" : 1, "description" : "T-Shirt", "size" : "M", "price" : NumberLong("1999") },
{ "_id" : 2, "description" : "Jeans", "size" : "36", "price" : NumberLong("3999") },
{ "_id" : 3, "description" : "Shorts", "size" : "32", "price" : NumberLong("2999") },
{ "_id" : 4, "description" : "Cool T-Shirt", "size" : "L", "price" : NumberLong("2495") },
{ "_id" : 5, "description" : "Designer Jeans", "size" : "30", "price" : NumberLong("8000") }
The long value can be converted to an appropriately formatted decimal value by multiplying price and NumberDecimal("0.01") using the $multiply operator. The following aggregation pipeline assigns the converted value to the new priceDec field in the $addFields stage:
db.clothes.aggregate(
[
{ $match: { price: { $type: "long" }, priceDec: { $exists: 0 } } },
{
$addFields: {
priceDec: {
$multiply: [ "$price", NumberDecimal( "0.01" ) ]
}
}
}
]
).forEach( ( function( doc ) {
db.clothes.save( doc );
} ) )
The results of the aggregation pipeline can be verified using the db.clothes.find() query:
{ "_id" : 1, "description" : "T-Shirt", "size" : "M", "price" : NumberLong(1999), "priceDec" : NumberDecimal("19.99") }
{ "_id" : 2, "description" : "Jeans", "size" : "36", "price" : NumberLong(3999), "priceDec" : NumberDecimal("39.99") }
{ "_id" : 3, "description" : "Shorts", "size" : "32", "price" : NumberLong(2999), "priceDec" : NumberDecimal("29.99") }
{ "_id" : 4, "description" : "Cool T-Shirt", "size" : "L", "price" : NumberLong(2495), "priceDec" : NumberDecimal("24.95") }
{ "_id" : 5, "description" : "Designer Jeans", "size" : "30", "price" : NumberLong(8000), "priceDec" : NumberDecimal("80.00") }
If you do not want to add a new field with the decimal value, the original field can be overwritten. The following update() method first checks that price exists and that it is a long, then transforms the long value to decimal and stores it in the priceDec field:
db.clothes.update(
{ price: { $type: "long" } },
{ $mul: { price: NumberDecimal( "0.01" ) } },
{ multi: 1 }
)
The results can be verified using the db.clothes.find() query:
{ "_id" : 1, "description" : "T-Shirt", "size" : "M", "price" : NumberDecimal("19.99") }
{ "_id" : 2, "description" : "Jeans", "size" : "36", "price" : NumberDecimal("39.99") }
{ "_id" : 3, "description" : "Shorts", "size" : "32", "price" : NumberDecimal("29.99") }
{ "_id" : 4, "description" : "Cool T-Shirt", "size" : "L", "price" : NumberDecimal("24.95") }
{ "_id" : 5, "description" : "Designer Jeans", "size" : "30", "price" : NumberDecimal("80.00") }
Non-Numeric Transformation:
Consider the following collection which used the non-numeric model and saved the monetary value as a string with the exact representation of the value:
{ "_id" : 1, "description" : "T-Shirt", "size" : "M", "price" : "19.99" }
{ "_id" : 2, "description" : "Jeans", "size" : "36", "price" : "39.99" }
{ "_id" : 3, "description" : "Shorts", "size" : "32", "price" : "29.99" }
{ "_id" : 4, "description" : "Cool T-Shirt", "size" : "L", "price" : "24.95" }
{ "_id" : 5, "description" : "Designer Jeans", "size" : "30", "price" : "80.00" }
确定货币值需要的最高精度。比如说，你的应用程序可能使用十分之一美分作为最小单位。
db.clothes.find( { $and : [ { price: { $exists: true } }, { price: { $type: "string" } } ] } ).forEach( function( doc ) {
doc.priceDec = NumberDecimal( doc.price );
db.clothes.save( doc );
} );
The function does not output anything to the command line. The results can be verified using the db.clothes.find() query:
{ "_id" : 1, "description" : "T-Shirt", "size" : "M", "price" : "19.99", "priceDec" : NumberDecimal("19.99") }
{ "_id" : 2, "description" : "Jeans", "size" : "36", "price" : "39.99", "priceDec" : NumberDecimal("39.99") }
{ "_id" : 3, "description" : "Shorts", "size" : "32", "price" : "29.99", "priceDec" : NumberDecimal("29.99") }
{ "_id" : 4, "description" : "Cool T-Shirt", "size" : "L", "price" : "24.95", "priceDec" : NumberDecimal("24.95") }
{ "_id" : 5, "description" : "Designer Jeans", "size" : "30", "price" : "80.00", "priceDec" : NumberDecimal("80.00") }
Application Logic Transformation¶
It is possible to perform the transformation to the decimal type from within the application logic. In this scenario the application modified to perform the transformation as it accesses records.
The typical application logic is as follows:
- Test that the new field exists and that it is of decimal type
- If the new decimal field does not exist:
- Create it by properly converting old field values
- Remove the old field
- Persist the transformed record
Using a Scale Factor¶
注解
If you are using MongoDB version 3.4 or higher, using the decimal type for modeling monetary data is preferable to the Scale Factor method.
To model monetary data using the scale factor approach:
- Determine the maximum precision needed for the monetary value. For example, your application may require precision down to the tenth of one cent for monetary values in USD currency.
把带小数货币值乘以10的次方数以转换成一个整数。如果最高精度要求是十分之一美分，那么就可以把值乘以1000。
保存转换以后的整形数。
例如, 下面这个例子把 9.99 USD 乘以1000进行放大并得到9990。
{ price: 9990, currency: "USD" }
这个模型假设对于一个特定的货币值：
- The scale factor is consistent for a currency; i.e. same scaling factor for a given currency.
对货币值的放大因子（乘数）对于一种货币来说是一致的并且应用程序能够确定这个放大因子。
当使用这个模式的时候，应用程序必须始终一致的执行相应值得换算。
For use cases of this model, see Numeric Model.
Non-Numeric Model¶
使用任意精度方式建模时，我们需要用两个字段来保存货币值：
在一个字段里，把货币值原样不动的按照非数字字段进行存储，如 BinData 或者 string。
在另一个字段里，以双精度浮点数近似值的形式保存所需货币值。
下述例子使用任意精度模式来保存价格字段 9.99 USD 和费用字段 0.25 USD :
{
price: { display: "9.99", approx: 9.9900000000000002, currency: "USD" },
fee: { display: "0.25", approx: 0.2499999999999999, currency: "USD" }
}
With some care, applications can perform range and sort queries on the field with the numeric approximation. However, the use of the approximation field for the query and sort operations requires that applications perform client-side post-processing to decode the non-numeric representation of the exact value and then filter out the returned documents based on the exact monetary value.
For use cases of this model, see Non-Numeric Model.