Sql Hacks 阅读感悟——数值处理

SQL基础：《SQL HACKS》第5章——数值处理，总共列举了17个tips，计算结果集的乘积、计算累加和、包含Join遗忘的行等，下述为我对这17个tips的评析。

#24 计算结果集的乘积

情景描述：计算多列的乘积

解决方案：通过数学手段，将乘积转换为对数加法运算，log(a*b*c) = log(a)+log(b)+log(c)

评析：sql中数值处理，很多都是基于数学手段，如刚才的指数函数、对数函数，后续tips中提到的中位数、gps定位距离计算等。

#25 计算累加和

情景描述：表中存储了每日的收支情况，要求计算每日的收支余额

解决方案：根据时间发生顺序做表自连，计算累加和

评析：熟练使用汇聚函数如sum，可以有效进行行汇总。通常在财务等处理上，我们记录的都是每日发生额，要计算每日的现金、银行存款余额就需要计算累加和。

#26 包含Join遗忘的行

情景描述：在连表时，我们经常会遗漏null条件的记录。如求每位员工的2月份出勤信息时，假设员工A没有2月份请病假，没有出勤记录，则通过连表员工信息表、考勤信息表时，就会略去员工A（给人感觉员工A离职了）。

解决方案：通过外联，null用特殊值（如0）来代替处理。

评析：合理利用外联，处理好null会有很多妙用。

#27 识别重叠范围

情景描述：求职人员有期望薪金区间required, r= [l,h]，提供的工作有薪金范围offered, o= [l,h]，寻找required和offered有交集的行记录。

解决方案：如何判断required和offered是否有交集，可以直接的用下述来表示，

1）r.h<=o.l

2) r.l>=o.l and r.h>=o.h

3) r.l<=o.l and r.h<=o.h

…

评析：区间比对的情况往往可以用边界值做反比可以解决问题，如果编辑值为null，则需要将null做最小、最大化处理。如上述的比较则完全可以用

r.h>=o.l and r.l<=o.h

#28避免零做除数

评析：转换null，sql中有很多null转换处理函数，如pgsql的coalesce

#29 计算行数的其它途径

评析：计算行数有 select count(*), select count(1), select count(字段)，需要注意的是如果用select count(字段)计算行数的话，则如果值为null，则会被略去。因此select count(*) 得到10的话，用 select count(字段)得到的可能是9。

#30 计算2个字段的最大值

情景描述：2个字段x、y，计算x、y的最大值

解决方案：运用数学手段实现，(x+y+abs(x-y))/2，实现x,y的最大值

评析：简单、灵活运用数学手段即可解决复杂问题，当然用case when x>y then x else y end也可实现x,y的最大值。

#31 反聚合count

评析：反计数问题是sql中一个比较难处理的问题，通常可以通过使用一个整数表来解决这个问题。如本案的新建integers表，create table integers(n integer);

#32 克服舍入误差

情景描述：计算某个字段的百分比值，如销售额amount,折扣率percent,求销售折扣。

解决方案：销售折扣 = 销售额 * 折扣率，貌似很简单，但是如涉及到汇总即小数位截位问题的话，则很有可能出现舍入误差。增加计算列 f=round(amount+percent,2)，然后对f汇总即可。

评析：对于小数截位问题，我们一定要注意舍入误差问题。对于本案要嘛先截后加，要嘛先加后截，切忌边截边加。

#33 同时得到值和小计

情景描述：给定记录集合S，要求用sql语句得到集合集合T，T=S U S的小计。

解决方案：得到S的小计 然后 union S 并按照特定列排序即可同时得到值和小计。

评析：该处实际上就是select union方式的运用，那么我们就要注意INTERSECT、EXCEPT的运用。

#34 计算中位数

情景描述：给定从小到大排序的集合S，找出S中间位置的元素，奇数集合时为第(n+1)/2个元素，偶数集合时为第n/2,n/2+1元素的平均值。

解决方案：创建临时表得到带有从小到大排序位置信息（简单理解为行号）的新集合N，然后利用N根据简单的中位数算法即可求解中位数。

Select avg(minutes) from taski, (select count(*) n from task) t

Where posn in (floor(n+1)/2, floor(n/2)+1)

评析：个人认为类似计算中位数，应该在具体应用去实现，或者定义成一个存储过程或者函数(pgsql 是函数)。但是，计算中位数中用到的一个技巧得到行号（posn）是一个很有用的tip。

#35 将结果展示为图表

评析：本tip核心就是字串函数的灵活运用，如填充字符串repeat, lpad等。

#36 计算GPS定位之间的距离

评析：本tips核心就是数学知识几何学的运用，因此不予展开。

#37 让货物清单和支付清单对应起来

评析：本例的一个典型应用就是会计行业的银行对账，会计人员总是会做银行对账单和银行、现金发生额间的勾稽关系。由于具体的应用有其特殊的技巧，因此本tip不予展开。

#38 找出换位错误

评析：本案类似于#37，没对上的那个肯定是录错了。但是本案有个技巧需要引起我们的注意，那就是快速定位换位错误。发生误差为9的倍数时，我们首先想到的是录入的时候发生了换位错误，入102录成了120等。

误差18，为9的2倍，则发生换位错误的可能组合为02, 13, 24, 35, 57, 68, 79。因此我们只要查找02,13,24,35,57,68,79结尾的值即可马上定位到发生换位错误的那个值。

#39 计算累进税

评析：作者对税级低限、税级高限边界值的灵活处理以及巧妙的crossjoin得到了纳税人员的累进税额。个人认为，由于税率表、人员信息表都是基本信息表记录数是有限的给定集合，因此可以编写一个函数getFunctionalTax()，传入纳税人员的收入，输出纳税人员的累进税额，更加可取。

#40 计算名次

评析：rank函数的应用。