算法是计算机科学中个重要研究方向是解决复杂问题关键在计算机世界中算法无处不在数据库是存储数据和执行大批量计算场所在数据库中使用些简单SQL命令进行存储、查询、统计、以解决现实世界中问题已经是屡见不鲜随着数据量大幅度增加和业务规则日益复杂越来越需要种专门思路方法来满足效率和准确性方面要求如何把解决问题复杂算法转换为数据库能够执行命令也是数据库应用技术研究个方面本文以MSSQL中命令来阐述例子
数据库中可以存储实体数据集合在进行运算时数据库使用批量计算思路方法来处理数据批量从存储设备上读取数据处理的后又批量写回存储设备有数据库提供了游标游标可以读取出表中行数据中每个字段对这些字段进行复杂业务规则计算然后再写回数据库中和使用批量思路方法比较批量计算思路方法消耗资源相对比较少而使用游标则占用太多资源速度比较慢效率较低并且还有加锁条件等许多限制
比如对于数据库中存储了学生成绩student_Score(sno,cno,score,level)成绩从0分到100分不等如果需要在分数后面存储个字段字level来介绍说明成绩优劣90分以上A80-90分为B60-80分为C60分以下为D,以下有几种算法都可以达到同样目标:
1.定义个游标选择student_Score表中所有成绩记录定义个存储成绩变量@cur_score存储当前纪录分数定义个存储当前分数所在成绩级别变量@cur_level,用以存储成绩好坏标记算法如下:如果游标中纪录不为空从游标中取出当前纪录成绩判断成绩所在分数段把结果存储在变量@cur_level中,以@cur_level中值更新当前纪录中level字段整个过程需要至少读取数据库两次次为获得纪录次需要写入数据库每条记录都需要经过这个过程效率相对低
2.依次批量更新数据库把所有level字段值设置为D,再次更新数据库把成绩大于等于60纪录Level字段更新为C,依次更新B,A这样做个缺点是有些纪录Level字段被更新多次比如个记录最后Level字段值是A,则它首先被更新为D,依次被更新为C,B,A这些重复更新是可以被消除把算法改进下就可以省去重复更新花费更新后算法是这样把成绩介于0和60分纪录Level字段更新为D依次更新各个分数段成绩实现这种算法SQL语句并不难写出使用Between…and…表达式即可以表达例如介于80到90的间纪录选择条件
3.鉴于第 2种思路方法最后分析使用between…and…表达式同时参照个表来更新纪录则可以方便表达分数段和相应level信息把这些信息存储到个表level_about中在更新student_score表过程中可以参照这个表计算过程中需要把level_about表内容读出来然后进行计算对于整个计算过程来说牺牲空间和部分效率来换来操作方便由于现在计算机速度相当快level_about表占用空间又很小这方面损失可以忽略不记Level_about表中信息至少包含3个字段:start_score,记录起始分数end_score记录终止分数,level记录介于起始分数和终止分数的间分数应该得到成绩表中数据应该类似于这样:
Start_scoreEnd_scorelevel
059D
6079C
8089B
90100A

更新student_Score表中纪录需要依据Start_score和End_score来判断当前记录中成绩所在Level,在MSSQL中实现SQL语句:Updatestudent_scorestudent_score.level=level_about.levelfromlevel_aboutwherestudent.scorebetweenlevel_about.start_scoreandlevel_about.end_score比较以上3种思路方法实现同个目采用区别算法实现效果是区别 [Page]
些简单算法不需要经过修改就可以直接应用到数据库中比如业务需要每天晚上都需要结算天情况周两次自动结算奖金结算奖金时间在每周再周和周 4晚上0点为了实现系统自动结算需要使用系统任务给系统制订个作业指定每天晚上0点结算就可以实现系统自动结算(由于结算时间间隔可能是会变化不能使用作业中定时功能)为了可以在周和周 4结算在数据库中设置个表misc其中字段相当于全局变量表中只有条纪录使用其中个字段(days)来记录当前结算次数也就是以系统开始运行为标准经过天数系统执行任务同时更新misc表中days使其增长updatemiscdays=days+1业务需求是每周和周 4结算奖金不难发现奇数次结算依次相差7天偶数次结算依次相差7天相邻奇数次和偶数此结算相差3天可以使用求余方式来统这个问题如果当前天数(days)和7求余结果为0或者当前天数(days)减去3的后求余结果为0则当前天数是结算日期具体实现算法是:1、提取当前天数到个变量中declare@days@days=(selectdaysfrommisc),2.判断是否满足结算条件@days%7=0or(@days-3)%7=0begin…..end类似于这样简单算法可以直接应用到数据库中而不会发生问题
复杂业务规则需要复杂算法复杂规则对于个有具体数字变量来说实现起来已经比较复杂如果应用到数据库中存储杂乱无章大批数字并且实现批量计算则需要对算法进行大幅度调整


比如业务规则需要在员工每4000元奖金中扣除400元作为重复消费并且在扣除最后400元重复消费次奖励件产品需要在数据库中使用个表(award_repeat)记录产生重复消费如果次扣除奖金不足400元在下次结算时候接着扣除直到扣除奖金够400元然后奖励件产品进入下次循环比如现在奖金总数达到了3600元则不会扣除如果达到了3700元则要扣除100元如果达到了7700元则要扣除410元并且产生个重复消费
为了实现这个规则在员工表(member)中记录每个员工奖金总数([total_award])同时记录重复消费次数([repeat_num])在另外过渡表(award_day)中记录每次奖金和每次扣除重复消费奖金最后在奖金表(award)中综合当次奖金和当次结算需要扣除重复消费就得到了当次结算实际发放奖金采用批量计算思路方法实现算法是:在计算奖金的后扣除重复消费的前把当前奖金累加到员工([total_award])字段([total_award])记录没有扣除重复消费所有奖金总和实现重复消费计算算法是设定条件(F1)为在member表中存在奖金总数大于等于重复消费次数加1后乘以4000如果有满足条件F1记录则选择满足条件纪录中主键和当前日期(days)插入到重复消费表(award_repeat)中,然后更新member表中满足条件F1repeat_num使其增加1重复检查条件F1直到member表中没有满足条件F1纪录 [Page]
扣除重复消费金额算法相对比较复杂综合考虑员工总共奖金情况他们处在区别范围如图所示
绿色部分是不用扣除黄色部分是需要扣除掉把问题统在起所有奖金总数总体分为两个部分部分在绿色部分的中部分在黄色部分的中分别讨论这两种情况1如果奖金总数落在绿色范围的中则需要扣除奖金总数(A1)为重复消费次数(repeat_num)乘于400而本次结算需要扣除奖金金额为需要扣除奖金总数(A1)减去以前扣除奖金总数2如果奖金总数落在黄色范围的中则需要扣除奖金总数不仅包括重复消费次数(repeat_num)乘于400而且还包括部分黄色部分而这些黄色部分可以这样计算得到:奖金总数(total_award)减去重复消费次数(repeat_num)乘以4000再减去3600即得到部分黄色区域金额把需要扣除金额加起来(A2)减去已经扣除金额总和就是本次结算需要扣除奖金金额
基于上述分析算法分为两个部分个部分用于计算重复消费相应Sql语句为
whileexists(select1frommemberwheretotal_award>=(repeat_num+1)*4000)
begin
insertoaward_repeat(turns,days,m_ID,award_type)
select@turns,@days,m_ID,0
frommember
wheretotal_award>=(repeat_num+1)*4000
--记录重复消费以便于公司送产品
updatemember
repeat_num=repeat_num+1
wheretotal_award>=(repeat_num+1)*4000
--更新计算结果,并进入循环 [Page]
end
由于采用逐步增加Repeat_num思路方法每次计算重复消费的后更新重复消费次数经过有限次计算的后循环结束不会陷入死循环
算法第 2部分实现分为两种情况分别实现如下:(AwardRepeat为视图数据来自award_day表视图内容为员工编号m_id和相应编号扣除奖金总和,由于些员工没有扣除过奖金需要使用外连接来确保完整性)
第种情况:奖金总额落在绿色部分
exists(select1frommemberwheretotal_awardbetween4000*repeat_numand4000*repeat_num+3600)

begin
insertoaward_day(turns,days,m_ID,award_num)
--批量次性插入需要扣除奖金纪录
select@turns,@days,member.m_ID,(-1)*(400*repeat_num/*(A1)*/
+(whenaward_numis


nullthen0award_numend))
frommemberleftjoinawardRepeaton(member.m_ID=awardRepeat.m_ID)
wheretotal_awardbetween4000*repeat_num+1and4000*repeat_num+3600
and(-1)*(400*repeat_num+(whenaward_numisnullthen0award_numend))<0 [Page]
--确保扣除金额为0纪录不被插入
end
第 2种情况:奖金总额落在黄色部分
exists(select1frommemberwheretotal_awardbetween4000*repeat_num+3600+1and4000*(repeat_num+1))
begin
insertoaward_day(turns,days,m_ID,award_num)
select@turns,@days,member.m_ID,(-1)*(400*repeat_num+total_award-4000*repeat_num-3600/*A2*/
+(whenaward_numisnullthen0award_numend))
frommemberleftjoinawardRepeaton(member.m_ID=awardRepeat.m_ID)
wheretotal_awardbetween4000*repeat_num+3600+1and4000*(repeat_num+1)
and(-1)*(400*repeat_num+total_award-4000*repeat_num-3600+(whenaward_numisnullthen0award_numend))<0 [Page]
--确保扣除金额为0纪录不被插入
end
实验结果:(award_day)表部分
award_day_idturnsdaysm_IDaward_num
181513m000000000-20
199616m000000000-380
252925m000000000-330
3051028m000000000-70
3321131m000000000-400
4151545m000000000-800

结论:在数据库中研究和实现算法有着相当大困难同时也是种挑战随着现实世界中业务规则日益复杂相应数据库应用软件Software实现业务规则需要算法也日益复杂把复杂算法应用在数据库中需要找到个统方式在熟悉业务规则前提下根据数据库特点和相应执行命令能力找到种适合数据库批量计算步骤是解决问题关键