[转]用partition by相对group by有什么优势吗？

用partition by相对group by有什么优势吗？

我们公司上了一套系统，我查看了一下原开发人员在做报表时写的SQL代码，如下所示：  
  select   distinct   t3.code,t3.name,t3.material_spec,t3.default_unit,  
  sum(t2.quantity)   over   (partition   by   t3.code)   sum_qty,  
  sum(t2.amount)   over   (partition   by   t3.code)   sum_amount,  
  sum(t2.tax_amount)   over   (partition   by   t3.code)   -   sum(t2.amount)   over   (partition   by   t3.code)   tax,  
  sum(t2.tax_amount)   over   (partition   by   t3.code)   total_amount  
  from   t1,t2,t3,t4  
  where   ...  
  order   by   t3.code  
  然后以下是我自己整理的代码：  
  select   t3.code,t3.name,t3.material_spec,t3.default_unit,  
  sum(t2.quantity)     sum_qty,  
  sum(t2.amount)   sum_amount,  
  sum(t2.tax_amount)     -   sum(t2.amount)   tax,  
  sum(t2.tax_amount)     total_amount  
  from   t1,t2,t3,t4  
  where   ...  
  group   by   t3.code,t3.name,t3.material_spec,t3.default_unit  
  order   by   t3.code  
  二者运行的结果一要，性能我的那个要比原开发人员写的来的快。而且相差的性能很明显。用group   by要快很多。我就想问一下这里的高人？用partition   by是不是有别的用意？会比group   by   来的更科学更好？（ps:自己查资料一直找不到答案，所以上这里来麻烦大家了）  
问题点数：100、回复次数：5Top

1 楼LoveLwn（小P）回复于 2005-03-08 16:28:34 得分 0

帮顶一下Top

2 楼slyfox123456（）回复于 2005-03-08 16:33:26 得分 0

order   by  
  没有分区表快  
  从算法上都可以看出来Top

3 楼keanu_wang（）回复于 2005-03-08 16:39:41 得分 100

partition   by关键字是oracle中分析性函数的一部分，它和聚合函数不同的地方在于它能返回一个分组中的多条记录，而聚合函数一般只有一条反映统计值的记录，partition   by用于给结果集分组，如果没有指定那么它把整个结果集作为一个分组，它有一部分函数既是聚合函数也是分析函数，比如avg、max，也有一部分是特有的，比如first、rank，除了order   by子句外，分析函数在一个查询中优先级最低。至于partition   by和group   by谁的性能更好，要看具体情况而定，从你所举的例子而言，partition   by的作用仅用于分组，那么性能可能比不上group   by。Top

4 楼lynx（lynx）回复于 2005-03-08 17:05:04 得分 0

sum()   over   (PARTITION   BY   ...)   是一个分析函数。   他执行的效果跟普通的sum   ...group   by   ...不一样，它计算组中表达式的累积和，而不是简单的和。  
  表a，内容如下：  
  B C D  
  02 02 1  
  02 03 2  
  02 04 3  
  02 05 4  
  02 01 5  
  02 06 6  
  02 07 7  
  02 03 5  
  02 02 12  
  02 01 2  
  02 01 23  
  select   b,c,sum(d)   e   from   a   group   by   b,c  
  得到：  
  B C E  
  02 01 30  
  02 02 13  
  02 03 7  
  02 04 3  
  02 05 4  
  02 06 6  
  02 07 7  
  而使用分析函数得到的结果是：  
  SELECT   b,   c,   d,   SUM(d)   OVER(PARTITION   BY   b,c   ORDER   BY   d)   e   FROM   a  
  B C E  
  02 01 2  
  02 01 7  
  02 01 30  
  02 02 1  
  02 02 13  
  02 03 2  
  02 03 7  
  02 04 3  
  02 05 4  
  02 06 6  
  02 07 7  
  结果不一样，这样看还不是很清楚，我们把d的内容也显示出来就更清楚了：  
  SELECT   b,   c,   d,SUM(d)   OVER(PARTITION   BY   b,c   ORDER   BY   d)   e   FROM   a  
  B C D E  
  02 01 2 2                     d=2,sum(d)=2  
  02 01 5 7                     d=5,sum(d)=7  
  02 01 23 30                   d=23,sum(d)=30  
  02 02 1 1                     c值不同，重新累计  
  02 02 12 13  
  02 03 2 2  
  02 03 5 7  
  02 04 3 3  
  02 05 4 4  
  02 06 6 6  
  02 07 7 7