收集的经典的数据库的调优

很久不搞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.)

下面是答案
==========================================================================================================================================================
题目一:

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

题目二:

Java代码
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 

上面是复杂代码:
下面是简洁代码:
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 

题目三:

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 

简洁代码:
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 

题目四:

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