SQL 小技巧

很久不搞sql了。整天就是java, java，也挺无聊的。

其实想想，sql还是挺好玩的，尤其是把别人吭哧吭哧几百行的stored procedure改写成一个简洁的query，或者一个本来要跑十分钟的stored proc改成几秒钟，感觉还是满爽的。

闲来无事，把一些小技巧回忆下来，省得以后考人的时候想不起来了。

1。一个基金performance表，记录着每个基金的每个月相比于前一个月的增长百分率，比如：
一月： 2.1（%）
二月： -0.5 (%)
三月：
...
十二月：15 (%)

基金用基金ticker来标识。

请列出每个基金从去年五月到今年五月的总共增长。

2。每个基金每个月都有打分，从一分到五分。请列出最近一年内每个基金被打五分的次数，四分的次数，三分的次数。如果一个基金被打至少三分的次数少于两次，就不要列出来了。

3。请针对上一年列出每个基金增长最好的那个月，包括基金ticker，当月的增长百分比，当月打分。如果两个月增长幅度相同，选择打分高的那个月；如果打分也相同，选择最近发生的那个月。

4。假设基金表不小心出现了重复数据，也就是ticker, 月份都相同，但是id是主键，唯一的。请删除重复记录。对重复记录，保留打分高的那条，如果打分也一样，保留其中任意一条。（其实，这个和三是一样的，不过算一个比较常见的dba要面临的问题）


（答案回头有时间写出来。提示：珍爱生命，远离存储过程！）

(I have discovered a truly marvelous proof of this, which this margin is too narrow to contain)

评论

19 楼 nihongye 2008-08-22  

ajoo 写道

nihongye 写道

引用

on x.ticker = nothing.ticker and 
where nothing.ticker is null 

矛盾！？

left join self nothing on ... where nothing.id is null

这个where nothing.id is null就是定义nothing的。所谓的nothing，就是说"id is null"或者说”不存在这样的记录“。
left join的时候，把所有右面不存在的记录填成null，而我们要找的就是这些不存在比它大的记录。

明白，谢谢了。习惯把条件写在where里，你的思路比较别致。

18 楼 ajoo 2008-08-21  

moses3017 写道

ajoo 写道

珍爱生命，远离存储过程！

请问楼主，此话怎讲？

存储过程背弃了sql的声明式编程的理念，重新倒退回了过程式代码。

它一般来说不好维护，很容易产生代码重复，不容易重构，不容易测试，数据库提供的语言功能也限制了表达能力（你能oo么？能抽象么？能fp么？）。我曾经维护过数千行的存储过程代码，到处copy-paste。要改点东西真是头疼呀。其实它做的事情如果sql写好一点，本来三四百行sql就可以搞定的。

它允许你做循环，支持游标，就客观上培养了很多懒人不去好好写sql，不去仔细研究query plan，而是用笨拙的循环写出乌龟爬一样的系统，然后埋怨数据库，或者埋怨存储过程。

而且很多情况下用宝贵的数据库服务器的处理器资源去做本来不该数据库做的事情，是可耻的浪费。

17 楼 moses3017 2008-08-21  

ajoo 写道

珍爱生命，远离存储过程！

请问楼主，此话怎讲？

16 楼 ajoo 2008-08-21  

nihongye 写道

引用

on x.ticker = nothing.ticker and 
where nothing.ticker is null 

矛盾！？

left join self nothing on ... where nothing.id is null

这个where nothing.id is null就是定义nothing的。所谓的nothing，就是说"id is null"或者说”不存在这样的记录“。
left join的时候，把所有右面不存在的记录填成null，而我们要找的就是这些不存在比它大的记录。

15 楼 bookong 2008-08-21  

原来是高中数学的“n^x*n^y=n^(x+y)”呀，无脸见高中数学老师。

14 楼 icefishc 2008-08-21  

lz是强人。向lz学习。
手头只有sybase. 而Sybase不支持在outter join的时候再做其他连接, 头痛.  在这种情况下lz有什么好建议么？

13 楼 nihongye 2008-08-21  

引用

on x.ticker = nothing.ticker and 
where nothing.ticker is null 

矛盾！？

12 楼 duanaiguo 2008-08-21  

非常不错，受教了！

11 楼 ajoo 2008-08-21  

嗯。稍微解释一下第三个问题的query。

其实它和第四个问题都可以归结为一类“根据某个自定义全序来寻找最优记录“。这个全序（java里的comparator），在第三题里面被定义为：

r1 > r2 iff r1.(percent, star, date) > r2.(percent, star, date)

如果sql支持矢量比较的话，query本来可以简单到：

select x.*
from perf x left join perf nothing
on x.ticker = nothing.ticker and x.(percent, star, date) < nothing.(percent, star, date)
where nothing.ticker is null

可以这么读：对每一个ticker, 找到(percent, star, date)这个矢量值最大（也就是说没有比它更大）的那个记录。

只不过因为缺乏内建矢量比较，被迫只好人肉用and/or来表达矢量比较。

那么第四个问题呢，用同样的（没有比它更好<=>最好）方式，可以写成：

select x.id
from perf x left join perf nothing
on x.(ticker, date) = nothing.(ticker, date) and x.(star, id) < nothing.(star, id)
where nothing.id is null

读出来就是：对每个ticker+date，找到（star, id）这个矢量值最大（没有比它更大）的那个记录的id。

10 楼 ajoo 2008-08-21  

第三个问题，先写出代码来吧。等有点时间再解释一下。第四个问题其实可以照猫画虎的：

select x.ticker, x.star, x.percent from perf x
left join perf nothing
on x.ticker = nothing.ticker and
(x.percent < nothing.percent or
  (x.percent=nothing.percent and x.star < nothing.star or
      x.star=nothing.star and x.date < nothing.date)
)
where nothing.ticker is null

9 楼 ajoo 2008-08-20  

第一个问题是我在维护一个金融分析软件的时候碰到的。原来的那位老兄正儿八经地用一个存储过程来搞这个东西：

create proc accumulate @startdate, @enddate
as
declare cursor c for select distinct ticker from perf

create temp table a

loop over c
  declare cursor c2 for select percent from perf where ticker = @ticker order by date
  loop over c2 and add up all percent for @ticker
    insert into a values(@ticker, @sum)

select * from a

这是伪代码，真正的代码要还要复杂一些。

存储过程的问题在哪？
1. 效率。用循环代替query，或者说用你自己的人肉nested-loop方案来代替优化器可能做到的range scan, hash join, merge join等等，写出来的东西爬得象蜗牛就没什么稀奇的了。
2. 灵活性。用query的话，你可以在where里用任意的表达式来过滤你想要的record，不见得永远都是一个startdate和enddate，比如你可以只关注s&p 500的ticker啦之类的。另外你还可以跟其它的表join，比如得到这个ticker的全名，基金经理老妈贵姓之类的。用存储过程？除非你用dynamic sql，否则对应每个需求你都要copy-paste然后修改一通。
3. 复杂，繁琐，难于维护。

我的解决方法嘛，只读提过了。为了完整，我再贴一遍我的用自然对数的方法：

select ticker, (exp(sum(log(1 + percent/100))) - 1) / 100 as accumulated_percent
from perf where date between startdate and enddate group by ticker

----------------------------------------------------------------------------------------
第二个问题，是一个简化版的问题。原始问题涉及了一些无关的条件，结果无非是要多join几个表，where clause和要select的column更多罢了。原始的代码大概是这样：

select ticker, count(*) as times into #five_star from perf
where star = 5 and date between startdate and enddate group by perf

select ticker, count(*) as times into #four_star from pef where star = 4 and date
between startdate and enddate group by perf

select ticker, count(*) as times into #three_star from perf where star = 3 and date
between startdate and enddate group by perf

select t.ticker, isnull(s5.times, 0) as star5, isnull(s4.times, 0) as star4, isnull(s3.times, 0) as star3
from ticker t
left join  #five_star s5 on t.ticker = s5.ticker
left join #four_star s4 on t.ticker = s4.ticker
left join #three_star s3 on t.ticker = s3.ticker
where isnull(s5.times, 0) + isnull(s4.times, 0) + isnull(s3.times, 0) >= 2

呼，小样儿的，写这么长没累死我！
这个玩意儿挑眼太容易了：
1. 无故扫描整表三次！慢
2. 代码重复。我要把同样的where clause抄三遍。实际上，原始问题中，那个where和join之类的东西占了满屏，还要原封不动地照抄。维护的时候要改三个地方！
3. 比较容易出错。比如后面那个join，因为很有可能一个ticker没有被评过五星，如果不用left join和isnull()来绕，就可能漏掉记录。

解决方法嘛，当然是一遍扫描。其实说穿了一点不稀奇，就是想办法弄一个比count(*)聪明点的组合函数，让它能只对需要的记录计数。

select ticker,
sum(case star when 5 then 1 else 0 end) star5, 
sum(case star when 4 then 1 else 0 end) star4,
sum(case star when 3 then 1 else 0 end) star3
from perf
where date between startdate and enddate
group by ticker
having sum(case when star >=3 then 1 else 0 end) >=2

----------------------------------------------------------------------------------------
第三个问题，要复杂一些了。我也懒得写原始的解决方法了，太麻烦。其实如果你仔细想了的话，这个东西如果仅仅是要表现最好的那个百分比，是非常非常简单的。麻烦就在于我们需要那个百分比之外的其它column的信息。

哦，要下火车了，等有时间再写了只能。

8 楼 ajoo 2008-08-20  

效率没问题。实际上一般的query效率都在查询上，至于对查询结果的计算，代价基本都可以忽略了。全抓出来到程序里的话，大量数据就要从数据库跑到应用程序里，这个开销也不小的。你的程序还要自己做group by，麻烦。

精度也没问题。我是用它来代替一段傻不拉唧的cursor循环的，计算结果（精确到小数点后两位）分毫不差。毕竟，double计算本身就是有误差的。

不过，我用的是自然对数，没试过2或者10.

7 楼 Readonly 2008-08-20  

ajoo 写道

Readonly 写道

问题一，经过google得到一用sum,log,pow来得到累乘的答案：

SELECT pow(2, log(2, sum((100 + change) / 100))), ticker FROM performance group by ticker

嗯？你确定这个对么？

哦...发现sum和log放错位置了，应该是：

SELECT pow(2, sum(log(2,(100 + change) / 100))), ticker FROM performance group by ticker

不过，不知道这个效率如何，而且还有精确度的隐患，搞不好还不如存储过程或者全部抓出来用代码内存中计算。

等公布其他题目的答案...

6 楼 honda418 2008-08-20  

Readonly 写道

问题一，经过google得到一用sum,log,pow来得到累乘的答案：

SELECT pow(2, log(2, sum((100 + change) / 100))), ticker FROM performance group by ticker

5 楼 ajoo 2008-08-20  

Readonly 写道

ajoo竟然也用sql的...巨震
不过你这题目出的也太不专业了，起码得说明一下是什么数据库吧？
mysql? oracle?

居然都不用“虎躯”的，太不专业了！

4 楼 ajoo 2008-08-20  

Readonly 写道

问题一，经过google得到一用sum,log,pow来得到累乘的答案：

SELECT pow(2, log(2, sum((100 + change) / 100))), ticker FROM performance group by ticker

嗯？你确定这个对么？

3 楼 ajoo 2008-08-20  

Readonly 写道

问题一，经过google得到一用sum,log,pow来得到累乘的答案：

SELECT pow(2, log(2, sum((100 + change) / 100))), ticker FROM performance group by ticker

那你猜猜我当时经手的原始代码是怎么写的？

2 楼 Readonly 2008-08-20  

问题一，经过google得到一用sum,log,pow来得到累乘的答案：

SELECT pow(2, log(2, sum((100 + change) / 100))), ticker FROM performance group by ticker

1 楼 Readonly 2008-08-20  

ajoo竟然也用sql的...巨震
不过你这题目出的也太不专业了，起码得说明一下是什么数据库吧？
mysql? oracle?