[原]用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 "英里:千米"