[原]用SQL做单位换算

pumeifen朋友在首页提出了一个问题“SQL 问题 求解”，我对这个问题延伸一下描述为“用数据库来做单位换算”，以长度单位为例，常用的长度单位有：毫米、厘米、分米等等，而英制的长度单位有英寸、英尺、码等，而我国传统的长度单位也有寸、尺、丈等等。

小学时学习单位换算的基本算法都是将他们转换成同一个长度单位然后再换算的，我借鉴这个算法写下此文。

首先在Oracle中构建一个长度单位的转换表：

create table Length
(
  name varchar2(50),
  ratio number(10,5) not null,
  parent varchar2(50),
  constraints pk_length primary key (name)
)

插入测试数据：

insert into Length 
---- 国际标准的长度单位
select '毫米' ,1.0 ,null from dual union all
select '厘米' ,10.0 ,'毫米' from dual union all
select '分米' ,10.0 ,'厘米' from dual union all
select   '米' ,10.0 ,'分米' from dual union all
select '千米' ,1000.0 ,'米' from dual union all
---- 我国传统的长度单位
select '寸' ,3.33,'厘米' from dual union all
select '尺' ,10.0 ,'寸' from dual union all
select '丈' ,10.0 ,'尺' from dual union all
---- 少数欧美国家使用的英制长度单位
select '英寸' , 2.54 , '厘米'  from dual union all
select '英尺' , 12.0  ,  '英寸'  from dual union all
select '码' , 3.0  ,  '英尺'  from dual union all
select '浪' , 220.0  ,  '码'  from dual union all
select '英寻' , 2.0  ,  '码'  from dual union all
select '英里' , 1760.0  ,  '码'  from dual 

这个Length表实际上保存的是一棵树，简单表示如下：

如果，我想知道1英里=?千米，Oracle可以使用connect by .... start with 分别进行查询得从千米和英里到毫米之间的路径：

SELECT name,ratio,parent
FROM length
CONNECT BY nocycle PRIOR parent = name
START WITH name = '千米'
union all
select null,null,null from dual
union all
SELECT name,ratio,parent
FROM length
CONNECT BY nocycle PRIOR parent = name
START WITH name = '英里'

NAME            RATIO PARENT
---------- ---------- ----------
千米             1000 米
米                 10 分米
分米               10 厘米
厘米               10 毫米
毫米                1

英里             1760 码
码                  3 英尺
英尺               12 英寸
英寸             2.54 厘米
厘米               10 毫米
毫米                1

接下来只需要将千米和英里转换成毫米就可求比率了，SQL语句如下：

select 
(     --英里转化成毫米
  select exp(sum(ln(ratio)))
  FROM length
  CONNECT BY nocycle PRIOR  parent = name
  START WITH name = '英里'
)/(   --千米转化成毫米
  select exp(sum(ln(ratio)))
  FROM length
  CONNECT BY nocycle PRIOR  parent = name
  START WITH name = '千米'
) "英里:千米"
from dual;

 英里:千米
----------
  1.609344

这里有个小插曲，SQL中没有计算累积的聚合函数，需要变换一下才能算到累积的结果，详细可以看我写的一篇博文[原] 计算乘积的聚合函数跑哪去了呢？

在SQL Server 2005/2008中，可以使用CTE的语法，以下省略在SQL Server中创建测试表Length的过程。

WITH LengthTree
as 
(
  select name,ratio,parent,0 as level from Length 
  where name = '千米' 
  union all
  select l.name,l.ratio,l.parent,level+1 
    from LengthTree t 
    inner join Length l
  ON l.name=t.parent
)
select * from LengthTree

稍微变换一下，我将英里、千米到毫米之间的路径列出来：

;WITH LengthTree
as 
(
	select name,ratio,parent,0 as level,name as start from Length 
	where name in ( '千米' , '英里' )
	union all
	select l.name,l.ratio,l.parent,level+1,t.start
		from LengthTree t 
		inner join Length l
	ON l.name=t.parent
)
select * from LengthTree
order by start,level

最后，就是再变换一下求解英里和千米之间的比率啦，SQL 如下：

;WITH LengthTree
as 
(
	select name,ratio,parent,0 as level,name as start from Length 
	where name in ( '千米' , '英里' )
	union all
	select l.name,l.ratio,l.parent,level+1,t.start
		from LengthTree t 
		inner join Length l
	ON l.name=t.parent
)
select (
	select exp(sum(log(ratio))) from LengthTree where start='英里'
)/(
	select exp(sum(log(ratio))) from LengthTree where start='千米'
) as "英里:千米"

 

看到这里，可能有朋友会问，如果我求1英里=?浪，比较好的做法是都转换成码然后再做运算，但是按照这个算法英里和浪都会最终转换成毫米再进行运算，中间极有可能产生精度问题，而且运算量明显多很多。

 

是的，解决运算量的重点在于找到“千米路径”和“英里路径”的相交点，两条路径一旦相交，再往根节点“毫米”走下去的路径都是多余的（沿用上文，从“千米”到“毫米”的路径称为“千米路径”，从“英里”到“毫米”的路径称为“英里路径”）。

虽然，通过集合的并、交、差可剔除多余的路径，但SQL变得非常长篇累赘，而且需要读取的块/页数不见减少，于是作罢，如果大家有兴趣，可以自己写写，希望您有更好的解决方法。至于精度问题，我的意见是，不要偏太多就行了.......

希望本文对您有帮助。

 

长度单位主要参考百度百科的长度单位和wiki的英制单位。