[转载]用SQL做单位换算

[转载][原]用SQL做单位换算 – killkill – 博客园.

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

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

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

1 create table Length
2 (
3 name varchar2(50),
4 ratio number(10,5) not null,
5 parent varchar2(50),
6 constraints pk_length primary key (name)
7 )

插入测试数据:

01 insert into Length
02 ---- 国际标准的长度单位
03 select '毫米' ,1.0 ,null from dual union all
04 select '厘米' ,10.0 ,'毫米' from dual union all
05 select '分米' ,10.0 ,'厘米' from dual union all
06 select '米' ,10.0 ,'分米' from dual union all
07 select '千米' ,1000.0 ,'米' from dual union all
08 ---- 我国传统的长度单位
09 select '寸' ,3.33,'厘米' from dual union all
10 select '尺' ,10.0 ,'寸' from dual union all
11 select '丈' ,10.0 ,'尺' from dual union all
12 ---- 少数欧美国家使用的英制长度单位
13 select '英寸' , 2.54 , '厘米' from dual union all
14 select '英尺' , 12.0  , '英寸' from dual union all
15 select '码' , 3.0  , '英尺' from dual union all
16 select '浪' , 220.0  , '码' from dual union all
17 select '英寻' , 2.0  , '码' from dual union all
18 select '英里' , 1760.0  , '码' from dual

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

image

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

01 SELECT name,ratio,parent
02 FROM length
03 CONNECT BY nocycle PRIOR parent = name
04 START WITH name = '千米'
05 union all
06 select null,null,null from dual
07 union all
08 SELECT name,ratio,parent
09 FROM length
10 CONNECT BY nocycle PRIOR parent = name
11 START WITH name = '英里'
01 NAME            RATIO PARENT
02 ---------- ---------- ----------
03 千米             1000 米
04 米                 10 分米
05 分米               10 厘米
06 厘米               10 毫米
07 毫米                1
08
09 英里             1760 码
10 码                  3 英尺
11 英尺               12 英寸
12 英寸             2.54 厘米
13 厘米               10 毫米
14 毫米                1

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

01 select
02 ( --英里转化成毫米
03 select exp(sum(ln(ratio)))
04 FROM length
05 CONNECT BY nocycle PRIOR parent = name
06 START WITH name = '英里'
07 )/( --千米转化成毫米
08 select exp(sum(ln(ratio)))
09 FROM length
10 CONNECT BY nocycle PRIOR parent = name
11 START WITH name = '千米'
12 ) "英里:千米"
13 from dual;
1 英里:千米
2 ----------
3 1.609344

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

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

01 WITH LengthTree
02 as
03 (
04 select name,ratio,parent,0 as level from Length
05 where name = '千米'
06 union all
07 select l.name,l.ratio,l.parent,level+1
08 from LengthTree t
09 inner join Length l
10 ON l.name=t.parent
11 )
12 select * from LengthTree

image

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

01 ;WITH LengthTree
02 as
03 (
04 select name,ratio,parent,0 as level,name as start from Length
05 where name in ( '千米' , '英里' )
06 union all
07 select l.name,l.ratio,l.parent,level+1,t.start
08 from LengthTree t
09 inner join Length l
10 ON l.name=t.parent
11 )
12 select * from LengthTree
13 order by start,level

image

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

01 ;WITH LengthTree
02 as
03 (
04 select name,ratio,parent,0 as level,name as start from Length
05 where name in ( '千米' , '英里' )
06 union all
07 select l.name,l.ratio,l.parent,level+1,t.start
08 from LengthTree t
09 inner join Length l
10 ON l.name=t.parent
11 )
12 select (
13 select exp(sum(log(ratio))) from LengthTree where start='英里'
14 )/(
15 select exp(sum(log(ratio))) from LengthTree where start='千米'
16 ) as "英里:千米"

image

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

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

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

希望本文对您有帮助。

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

赞(0) 打赏
分享到: 更多 (0)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏