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)

评论

39 楼 xushengcheng1985 2008-10-04  

我想请教一下打分的标准是什么？

38 楼 gwinthis 2008-09-26  

楼主举得例子有些极端，简单的SQL可以实现当然不用存储过程了。SQL语句和存储过程应该是没有矛盾的，我想大家遇到问题都是首先考虑使用SQL语句，如果SQL实现太复杂，会再考虑存储过程。特别是一些报表，字段来自很多表，并且需要复杂的计算。比如SQL语句包含6、7个表，左套一层右套一层，这样的语句让数据库来解析和执行很难想象会是什么样的结果，性能可想而知，编写难度也比较大，可读性当然也不会太好。在这种情况我觉得可以使用存储过程来代替一个复杂的SQL语句，把问题分散解决，对数据库，开发人员，维护人员都是个不错的选择。

37 楼 xiogxiog 2008-09-25  

脱离SQL就是脱离本质，   搞那么多虚的东西是没用的
碰上专业的数据仓库比如TERRADATA，你就是得老老实实用他的专用查询语句
搞技术的也不要太执着，见人说人话，见鬼说鬼话即可！   

36 楼 willmore 2008-09-23  

不存在符合条件的记录,也就是唯一边界的记录,对于ON 只能用等值连接,所以WHERE里补空件.

35 楼 armorking 2008-09-19  

lintomny 写道

armorking 写道

对于oracle
第二个问题，

select 
      基金表.PK
    , count(decode(基金表.分数, 5, 1, 0)) as count_5
    , count(decode(基金表.分数, 4, 1, 0)) as count_4
    , count(decode(基金表.分数, 3, 1, 0)) as count_3
    , count(decode(基金表.分数, 5, 1, 4, 1, 3, 1, 0)) as count_543
from 基金表
group by 基金表.PK
having count_543 > 2

这里的having count_543 > 2可以这么写吗？
没听说查询出来的别名可以作having过滤项。至少Oracle9i下这是不行的。

的确，这是个笔误

34 楼 singhua 2008-09-18  

太强悍了，对我触动很大，讨厌存储过程，可是我离不开存储过程！

33 楼 caoyangx 2008-09-16  

你不会是做财帮子、晨星、数米等相关网站的吧？
如果是，我们是同行。呵呵。

32 楼 ccxw1983 2008-09-13  

出现一些很笨的sql写法往往是因为写sql的人对sql所知甚少，或者是在别人的基础上修改，图快修改而来。
多知道些sql知识，在条件允许的条件下多点责任心应该可以避免这类事情。
当然如果有dba，有些事情在自己只知道一些笨的解决方法的时候应该先咨询下。

31 楼 lintomny 2008-09-09  

<p>第三个问题，可以使用数据库提供的分析函数呀</p>
<pre name='code' class='sql'>select ticker, percent, star, ymonth 
from ( 
  select ticker, percent, star, ymonth, 
    row_number () over (partition by ticker order by percent desc, star desc, ymonth desc) as rrow 
   from performance 
  ) 
where rrow=1;  </pre>
<p> </p>
<p>我想ajoo说的是不使用分析函数的情况下解决问题的办法吧。</p>

30 楼 lintomny 2008-09-09  

armorking 写道

对于oracle
第二个问题，

select 
      基金表.PK
    , count(decode(基金表.分数, 5, 1, 0)) as count_5
    , count(decode(基金表.分数, 4, 1, 0)) as count_4
    , count(decode(基金表.分数, 3, 1, 0)) as count_3
    , count(decode(基金表.分数, 5, 1, 4, 1, 3, 1, 0)) as count_543
from 基金表
group by 基金表.PK
having count_543 > 2

这里的having count_543 > 2可以这么写吗？
没听说查询出来的别名可以作having过滤项。至少Oracle9i下这是不行的。

29 楼 icefishc 2008-09-08  

shoulders 写道

icefishc 写道

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

我也用sybase,这种写法是SQL的标准,不会有问题的。

http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/12062;hf=0
  真的不行的

28 楼 InnocentBoy 2008-09-05  

很支持，我喜欢，我也喜欢搞这种事情。

27 楼 programmedbloke 2008-09-04  

恩，不错!

26 楼 Tony_zc 2008-09-02  

值得学习~

25 楼 eclipse2008 2008-08-26  

sql调优还是蛮好玩的

24 楼 armorking 2008-08-22  

对于oracle

第一个问题
可以用自定义集约函数搞定

可以参看
http://www.iteye.com/post/519866
那里有一个关于字符串连接(concat)用的自定义集约函数

第二个问题，

select 
      基金表.PK
    , count(decode(基金表.分数, 5, 1, 0)) as count_5
    , count(decode(基金表.分数, 4, 1, 0)) as count_4
    , count(decode(基金表.分数, 3, 1, 0)) as count_3
    , count(decode(基金表.分数, 5, 1, 4, 1, 3, 1, 0)) as count_543
from 基金表
group by 基金表.PK
having count_543 > 2

23 楼 ajoo 2008-08-22  

shoulders 写道

ajoo 写道

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

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

在使用外链接的时候，where clause 跟left join on clause，数据库优化器会先使用哪个clause过滤数据啊？

这是根据索引来的，不是简单的谁先谁后。

咱们先假设ticker上有索引。

那么比如你的where clause如果有"and ticker in ('a','b','c')"，这个肯定就先index seek了。在seek出来的基础上，在怎么搞都无所谓了。

但是where nothing.ticker is null肯定不会是先被过滤的，因为做不到，只有join之后你才知道哪个ticker是null。

所以据我的经验，如果没有上面那种额外的过滤条件的话，根据数据量的大小，这里可能是一个merge join或者hash join配上table scan/index scan。

22 楼 shoulders 2008-08-22  

icefishc 写道

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

我也用sybase,这种写法是SQL的标准,不会有问题的。

21 楼 shoulders 2008-08-22  

ajoo 写道

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

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

在使用外链接的时候，where clause 跟left join on clause，数据库优化器会先使用哪个clause过滤数据啊？

20 楼 licogi 2008-08-22  

ajoo 写道

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

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

...

很不错的SQL 收藏了 

不过第一题有个小问题 计算增长率的部分应该是：
(exp(sum(log(1 + percent/100))) - 1) * 100

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