在数据仓库建模时,应该使用哪种数据类型的度量值

在数据仓库建模中,很重要的模型就是星型模型,在星型模型中我们将表分为维度表和事实表,事实表中存放的可以进行计算(汇总,平均等)的列就是度量值。要进行计算的度量值,可以选择的数据类型也有好多种,那么我们应该选择哪一种呢?

首先定个大的方向,是整数还是小数?如果是整数,那么我们可以选择的数据类型就只有int和bigint了,16位或者8位的整数基本不用考虑,在数据仓库这种大数据量的环境下,很容易就overflow了。即使是int这种32位的整数,在数据量特别大的情况下,如果要做sum甚至是avg操作,很可能就会溢出,所以一般推荐使用bigint。

对于价格,金额这种类型的数据,一般会记录成小数,而且是两位小数,那么我们使用什么数据类型来进行存储呢?以SQL Server为例,我们可以选择的数据类型包括:

  • float
  • money
  • decimal/numeric

1.Float是一个非精确的数据类型,也就是说,存储的数据在读取出来时可能会有一定的误差。在财务这种一分钱都不能差的系统里面,是绝对不能采用的数据类型,在数据仓库中进行sum的话会使得sum的结果与实际结果不一致。但是Float并不是一无是处,笔者使用两千万行的数据对几种小数类型的数据进行性能测试,发现float在进行运算时具有一点优势,另外Float由于内部是采用科学计数法实现,所以可以存储非常非常大的数值。

print convert(money,'12345678901234567890');--Error
print convert(decimal,'12345678901234567890');--Error
print convert(float,'12345678901234567890');--Correct

2.Money是SQL Server特有的数据类型,在Oracle,MySQL中没有对应的类型。money的精度是可以到小数点后4位,所以对于我们平时记录两位小数的金额来说,是满足要求的。如果我们的度量值不是金额,而是其他含义的值,而且精度也不会超过4位小数(比如面积、长度、重量等),那么还是否可以使用money类型呢?如果只是进行sum、avg这样的运算,是完全可以使用money类型的。关于money和decimal的性能,有人专门做了个比较,我也使用两千万的数据进行了sum和avg的比较,发现money在计算上有一定的性能优势,但是这个优势也不是明显到速度能够提高好几倍的程度。老外做的比较的博客:http://sqlblog.com/blogs/aaron_bertrand/archive/2008/04/27/performance-storage-comparisons-money-vs-decimal.aspx

money类型在进行除法运算的时候,如果没有转换为decimal类型,那么就会造成精度丢失,因为money始终保留4位小数,所以最终结果可能会比decimal类型的有误差。所以最好不要把money类型的数据参与除法运算。

select sum(money1/money2) from testMoney;

如果一定要参与除法运算,那么我们可以将一个money类型和一个decimal类型进行除法运算,这样系统会自动转换成decimal类型,从而避免由于money只保留4位小数造成的精度丢失。

select sum(money1/decimal1) from testMoney;

3.Decimal类型和money类型一样都是精确数值类型,不同之处在于decimal类型可以指定占用的长度和小数后的精度。Decimal可以提供比Money更大的数据范围和更高的精度,当然也会占用更多的存储空间。

如果对于只保留2位小数的度量值,我们可以使用decimal(xx,2)来存储,前面的值根据数据量和数据值的大小来取,我一般写成decimal(18,2)。使用decimal类型进行除法运算时,不会出现money类型遇到的小数精度丢失的问题,即使我们只申明了decimal(xx,2),但是在进行除法运算的过程中,系统会保留很高的小数精度来进行计算。

Decimal的运算性能不如money,但是差距也不是那么的明显,在无法预期的对度量值的运算的情况下,使用decimal更保险。

总结:

如果是整数,就用bigint,避免数据量太大造成的int数据溢出。

如果是小数,而且不是那么关心精度,可以使用float,如果要计算的数值非法非常大就必须使用float,但是对于一分钱都不能差的情况下,就不要使用float类型。而应该使用money或者decimal。

如果不会有除法运算,而且数据的精度是在小数点后4位以内,那么使用money,其速度比decimal更快。

如果无法预期会不会有除法运算,或者要求的小数位数精度很高,那么就得使用decimal,速度比money慢一些,但是基本上还在同一个数量级。

posted @ 2013-11-15 10:23  深蓝  阅读(1824)  评论(0编辑  收藏

我要啦免费统计