【Teradata】grouping和rollup窗口函数

1.group by后带rollup子句

先按一定的规则产生多种分组，然后返回各个分组所产生的结果集的并集，且没有去掉重复数据（统计出的数据是求和还是最大值还是平均值等这就取决于SELECT后的聚合函数）。下面举例说明： 

例1：Group by rollup(A ,B) 

//返回如下3个分组的统计结果集的并集，且未去掉重复数据。
第一种：group by A,B 
第二种：group by A 
第三种：group by NULL （即没有分组，所有数据做一个统计）

例2：Group by rollup(A ,B,C) 

//返回如下4种分组统计结果集的并集，且未去掉重复数据
第一种：group by A,B,C 
第二种：group by A,B 
第三种：group by A 
第四种：group by NULL

例3：Group by C , rollup(A ,B)

//group by和rollup间还有其它列C，返回如下3种分组统计结果集的并集，且未去掉重复数据
第一种：group by C,A,B     
第二种：group by C,A      
第三种：group by C,NULL  等价于group by C 

例4：Group by rollup(A ,(B,C))

//多列被括号括在一起时，视为一个整体。返回如下3中分组统计结果的并集，且未去掉重复数据
第一种：group by A,B,C 
第二种：group by A   
第三种：group by NULL

2.group by后带cube子句

 group by后带rollup子句与group by后带cube子句的唯一区别就是： 

 带cube子句的group by会产生更多的分组统计数据。cube后的列有多少种组合（注意组合是与顺序无关的）就会有多少种分组。

 例：Group by cube(A ,B,C) 

//返回8种分组统计结果集的并集，且未去掉重复数据。
第一种：group by A,B,C 
第二种：group by A,B 
第三种：group by A,C 
第四种：group by B,C 
第五种：group by C 
第六种：group by B 
第七种：group by A 
第八种：group by NULL

 3.group by后带grouping sets子句 

 group by后带grouping sets子句效果，就是只返回按单个列分组后的统计数据，不返回多个列组合分组的统计数据。

 例1：Group by grouping sets(A ) 

//返回1种分组统计结果集
 第一种：group by A 

例2：Group by grouping sets(A ,B) 

//返回2种分组统计结果集的并集，且未去掉重复数据。 
第一种：group by A 
第二种：group by B 

例3：Group by grouping sets (A ,B,C) 

//返回3种分组统计结果集的并集，且未去掉重复数据。 
第一种：group by A 
第二种：group by B 
第三种：group by C 

 

CREATE MULTISET VOLATILE TABLE VTX,NO FALLBACK,NO LOG(
      FST_Lvl_Brch_Org_Id    VARCHAR(20)
     ,HQ_Org_Id    VARCHAR(20)
     ,Sec_Brch_Org_Id    VARCHAR(20)
     ,Inn_Org_Id    VARCHAR(20)
     ,COL_6    INT
 )NO PRIMARY INDEX
 ON COMMIT PRESERVE ROWS;
 
INSERT INTO VTX VALUES ('737001','','373000','737331',1);
INSERT INTO VTX VALUES ('737001','','373000','737332',2);
INSERT INTO VTX VALUES ('737001','','373000','737333',3);
INSERT INTO VTX VALUES ('737001','','373000','737334',4);
INSERT INTO VTX VALUES ('737001','','373000','737335',5);
INSERT INTO VTX VALUES ('737001','','373000','737336',6);
INSERT INTO VTX VALUES ('737001','','373000','737337',7);
INSERT INTO VTX VALUES ('737001','','373000','737338',8);
INSERT INTO VTX VALUES ('737001','','373000','737339',9);

SELECT COALESCE(COALESCE(DECODE(FST_Lvl_Brch_Org_Id,'',HQ_Org_Id,FST_Lvl_Brch_Org_Id),COALESCE(Sec_Brch_Org_Id,Inn_Org_Id)),'11111111')
    ,Sec_Brch_Org_Id
    ,DECODE(FST_Lvl_Brch_Org_Id,'',HQ_Org_Id,FST_Lvl_Brch_Org_Id)
    ,Inn_Org_Id
    ,SUM(COL_6)
FROM VTX
WHERE Sec_Brch_Org_Id='373000'
GROUP BY 1,
GROUPING SETS(Sec_Brch_Org_Id,DECODE(FST_Lvl_Brch_Org_Id,'',HQ_Org_Id,FST_Lvl_Brch_Org_Id),ROLLUP(Inn_Org_Id))
ORDER BY 1,2,3,4;

参考文档：

group by后加rollup子句的用法以及与cube和grouping sets子句的区别

SQL基础之GROUPING

GROUP函数-GROUP_ID,GROUPING,GROUPING_ID