实现比较复杂的分组、小计与合计(总结)

实现比较复杂的分组、小计与合计(总结)

求版主加 精华贴~
小菜鸟我来论坛，一个半星期了。今天技术积分终于突破了 200分。
感谢 dingjun123 以及 各位大侠对我的无私帮助。我也要帮助下新人，特别总结下聚合函数over，与分组函数group by rollup
内容可能对高手来说是个笑话，但orlace的知识不是笑话
我援引的帖子是----> 如何实现比较复杂的分组、小计与合计
http://www.itpub.net/viewthread. ... p;extra=&page=1
这是2008年的一个老帖。在dingjun123和各位大侠的帮助下。我终于明白了很多。
下面分享下我的学习成果（ 基础代码在二楼贴出）

首先纠正下我原来对over的一个误解。我总觉得，rank(),row_number(),dense_rank() 这些都是跟over一起用的。
其实这导致了我后来走入了一个很大的误区。其实 sum(),avg()还有其他函数，都是可以写在over前面的；如
SELECT STOCK_ID,QTY,
SUM(QTY) OVER(PARTITION BY STOCK_ID),
AVG(QTY) OVER(PARTITION BY STOCK_ID)  FROM T_DIST;
这个函数的真正作用是根据STOCK_ID进行分组求和（sum），或者求平均数(avg)。独立作为一列，加在最后。
注意，这里没有group 函数 哦~

GROUP BY ROLLUP 函数大家都懂。
SELECT STOCK_ID,TYPE_CD,DISCOUNT,SUM(QTY) FROM T_DIST GROUP BY ROLLUP(STOCK_ID,(TYPE_CD,DISCOUNT));
根据STOCK_ID，（TYPE_CD,DISCOUNT）分组。括号内的表示捆绑，你懂的哦。  


--学习是一步一步来的。

--1，
select stock_Id,TYPE_CD,DISCOUNT,QTY from t_dist;

--2，聚合函数，over,‘行’分组求和
SELECT STOCK_ID,QTY,SUM(QTY) OVER(PARTITION BY STOCK_ID)  FROM T_DIST;
--(最后多一列,上面提及了）

--3，group,‘列’分组求和
SELECT STOCK_ID,TYPE_CD,DISCOUNT,SUM(QTY) FROM T_DIST GROUP BY ROLLUP(STOCK_ID,(TYPE_CD,DISCOUNT));
--(由于是分组统计多了4行合计)

--现在在3的基础上+ b1,b2,b3（看步骤2）

SELECT STOCK_ID,
TYPE_CD B1, DISCOUNT B2, SUM(QTY) B3, 
SUM( TYPE_CD) OVER(PARTITION BY STOCK_ID)  A1,
SUM( DISCOUNT) OVER(PARTITION BY STOCK_ID) A2,
SUM( SUM(QTY)) OVER(PARTITION BY STOCK_ID) A3
  FROM T_DIST GROUP BY ROLLUP(STOCK_ID,(TYPE_CD,DISCOUNT));

--我们可以发现 b1和b2( 蓝色),b3 ( 紫色)本来就是同级别的。 a1,a2,a3只不过对b1,b2,b3求了和而已；

--继续增加2列吧

SELECT STOCK_ID,
TYPE_CD B1,DISCOUNT B2,SUM(QTY) B3, 
SUM(TYPE_CD) OVER(PARTITION BY STOCK_ID)  A1,
SUM(DISCOUNT) OVER(PARTITION BY STOCK_ID) A2,
SUM(SUM(QTY)) OVER(PARTITION BY STOCK_ID) A3,
grouping(STOCK_ID) k1,  
grouping(TYPE_CD) k2,grouping(DISCOUNT) k3, 

FROM T_DIST GROUP BY ROLLUP(STOCK_ID,(TYPE_CD,DISCOUNT));

/*这里增加的k1是最后的分组（STOCK_ID在前），
k2,k3是同级，因为在gollup中，绑定了 TYPE_CD和DISCOUNT*/


好了，最后的大戏来了。我到底发现了什么呢？？？
就是rollup与over步骤先后顺序，先走的 rollup,然后是 over。
证据是什么呢？因为rollup 之后才会有grouping（xx）,而且在over函数中还可以使用；

看终结部分，再增加2列（经原来a3改造过成p3）

SELECT STOCK_ID,
TYPE_CD B1,DISCOUNT B2,SUM(QTY) B3, 
SUM(TYPE_CD) OVER(PARTITION BY STOCK_ID)  A1,
SUM(DISCOUNT) OVER(PARTITION BY STOCK_ID) A2,
SUM(SUM(QTY)) OVER(PARTITION BY STOCK_ID) A3,
SUM(SUM(QTY)) OVER(PARTITION BY STOCK_ID,(GROUPING(TYPE_CD))) P3,
GROUPING(STOCK_ID) K1,  --
GROUPING(TYPE_CD) K2,GROUPING(DISCOUNT) K3 --
FROM T_DIST GROUP BY ROLLUP(STOCK_ID,(TYPE_CD,DISCOUNT));

注意到A3和p3的区别没。
这partition分组用的grouping(type_cd)，其实是GROUP BY ROLLUP之后产生的。
因此这里的over分组，其实还用了之前的产生的grouping(type_cd)。