[原]用SQL做单位换算

[原]⽤SQL做单位换算
pumeifen朋友在⾸页提出了⼀个问题“”，我对这个问题延伸⼀下描述为“⽤数据库来做单位换算”，以长度单位为例，常⽤的长度单位有：毫⽶、厘⽶、分⽶等等，⽽英制的长度单位有英⼨、英尺、码等，⽽我国传统的长度单位也有⼨、尺、丈等等。

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

⾸先在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.ratio,l.parent,level+1
from LengthTree t
inner join Length l
ON =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.ratio,l.parent,level+1,t.start
from LengthTree t
inner join Length l
ON =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.ratio,l.parent,level+1,t.start
from LengthTree t
inner join Length l
ON =t.parent
)
select (
select exp(sum(log(ratio))) from LengthTree where start='英⾥'
)/(
select exp(sum(log(ratio))) from LengthTree where start='千⽶'
) as "英⾥:千⽶"
看到这⾥，可能有朋友会问，如果我求1英⾥=?浪，⽐较好的做法是都转换成码然后再做运算，但是按照这个算法英⾥和浪都会最终转换成毫⽶再进⾏运算，中间极有可能产⽣精度问题，⽽且运算量明显多很多。

是的，解决运算量的重点在于找到“千⽶路径”和“英⾥路径”的相交点，两条路径⼀旦相交，再往根节点“毫⽶”⾛下去的路径都是多余的（沿⽤上⽂，从“千⽶”到“毫⽶”的路径称为“千⽶路径”，从“英⾥”到“毫⽶”的路径称为“英⾥路径”）。

虽然，通过集合的并、交、差可剔除多余的路径，但SQL变得⾮常长篇累赘，⽽且需要读取的块/页数不见减少，于是作罢，如果⼤家有兴趣，可以⾃⼰写写，希望您有更好的解决⽅法。

⾄于精度问题，我的意见是，不要偏太多就⾏了.......
希望本⽂对您有帮助。

长度单位主要参考和。