实现比较复杂的分组、小计与合计(总结)
求版主加 精华贴~
小菜鸟我来论坛,一个半星期了。今天技术积分终于突破了 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)。
小菜鸟我来论坛,一个半星期了。今天技术积分终于突破了 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)分组。括号内的表示捆绑,你懂的哦。
![](https://i-blog.csdnimg.cn/blog_migrate/ebeb01fbca05d2b45d5b007f95033ffc.png)
--学习是一步一步来的。
--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)。