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