【转】rollup、cub、grouping sets、grouping、grouping_id在报表中的应用

摘自 http://blog.itpub.net/26977915/viewspace-734114/

在报表语句中经常要使用各种分组汇总,rollup和cube就是常用的分组汇总方式。

第一:group by rollup

1、如果使用诸如group by rollup(A,B,C)的方式分组,那么返回的分组结果是
(A,B,C) (A,B) (A) (NULL) 一共四种结果。即从右到左递减,最后来个合计。

例如:

SQL> select * from t;

YEARS     MONTHS PRODUCT_NA      SALES
---------- ---------- ---------- ----------
      2008          1 A                1000
      2008          1 B                1500
      2008          2 A                2000
      2008          2 B                3000
      2008          2 C                1000
      2008          3 A                3000

已选择6行。

SQL> select years,months,product_name,sum(sales) sum_sales from t
  2  group by rollup(years,months,product_name)
  3  /

YEARS     MONTHS PRODUCT_NA  SUM_SALES
---------- ---------- ---------- ----------
      2008          1 A                1000 ----------group by (years,months,product_name)
      2008          1 B                1500
      2008          1                  2500 ----------group by (years,months)
      2008          2 A                2000
      2008          2 B                3000
      2008          2 C                1000
      2008          2                  6000 ----------group by (years,months)
      2008          3 A                3000
      2008          3                  3000 ----------group by (years,months)
      2008                            11500 ----------group by (years)
                                      11500 ----------group by (NULL)

已选择11行。

2、如果使用诸如group by A,ROLLUP(B,C) 那么返回的分组方式是:
(A,B,C)  (A,B) (A,NULL)  及在这种情况下,先计算rollup里面的分组情况,再与A组合。

例如:
SQL> select years,months,product_name,sum(sales) sum_sales from t
  2  group by years,rollup(months,product_name)
  3  /

YEARS     MONTHS PRODUCT_NA  SUM_SALES
---------- ---------- ---------- ----------
      2008          1 A                1000 ----------group by (years,months,product_name)
      2008          1 B                1500
      2008          1                  2500 ----------group by (years,months)
      2008          2 A                2000
      2008          2 B                3000
      2008          2 C                1000
      2008          2                  6000
      2008          3 A                3000
      2008          3                  3000
      2008                            11500 ----------group by (years)

已选择10行。

第二:group by cube

1、如果使用诸如cube(A,B,C)的方式,那么返回的分组组合是
(A) (A,B) (A,C) (A,B,C) (B) (B,C) (C) (null) 共8种组合方式

例如:

SQL> select years,months,product_name,sum(sales) sum_sales from t
  2  group by cube(years,months,product_name)
  3  /

YEARS     MONTHS PRODUCT_NA  SUM_SALES
---------- ---------- ---------- ----------
                                      11500 ----------group by (null)
                      A                6000 ----------group by (product_name)
                      B                4500
                      C                1000
                    1                  2500
                    1 A                1000
                    1 B                1500
                    2                  6000
                    2 A                2000
                    2 B                3000
                    2 C                1000
                    3                  3000 ----------group by (months)
                    3 A                3000 ----------group by (months,product_name)
      2008                            11500 ----------group by (years)
      2008            A                6000
      2008            B                4500
      2008            C                1000 ----------group by (years,product_name)
      2008          1                  2500
      2008          1 A                1000
      2008          1 B                1500
      2008          2                  6000
      2008          2 A                2000
      2008          2 B                3000
      2008          2 C                1000
      2008          3                  3000 ----------group by (years,months)
      2008          3 A                3000 ----------group by (years,months,product_name)

已选择26行。

2、如果使用GROUP BY A,CUBE(B,C),那么返回的分组组合为:
(A,B) (A,B,C) (A,C) (A)

例如:
SQL> select years,months,product_name,sum(sales) sum_sales from t
  2  group by years,cube(months,product_name)
  3  /

YEARS     MONTHS PRODUCT_NA  SUM_SALES
---------- ---------- ---------- ----------
      2008                            11500 ----------group by (years)
      2008            A                6000 ----------group by (years,product_name)
      2008            B                4500
      2008            C                1000
      2008          1                  2500 ----------group by (years,months)
      2008          1 A                1000 ----------group by (years,months,product_name)
      2008          1 B                1500
      2008          2                  6000
      2008          2 A                2000
      2008          2 B                3000
      2008          2 C                1000
      2008          3                  3000
      2008          3 A                3000

已选择13行。

3、如果使用GROUP BY A,ROLLUP(B,C),CUBE(D,E),那么返回的分组组合为:

先分解cube:

a,rollup(b,c),d,e
a,rollup(b,c),d
a,rollup(b,c),e
a,rollup(b,c)

再分解ROLLUP而得到最终所有情况为:

a,b,c,d,e
a,b,d,e
a,d,e
a,b,c,d
a,b,d
a,d
a,b,c,e
a,b,e
a,e
a,b,c
a,b
a

例如:
SQL> select years,months,product_name,sum(sales) sum_sales from t
  2  group by years,rollup(months),cube(product_name)
  3  /

YEARS     MONTHS PRODUCT_NA  SUM_SALES
---------- ---------- ---------- ----------
      2008          1 A                1000 ----------group by (years,months,product_name)
      2008          2 A                2000
      2008          3 A                3000
      2008          1 B                1500
      2008          2 B                3000
      2008          2 C                1000
      2008            A                6000 ----------group by (years,product_name)
      2008            B                4500
      2008            C                1000
      2008          1                  2500 ----------group by (years,product_name)
      2008          2                  6000
      2008          3                  3000
      2008                            11500 ----------group by (years)

已选择13行。

第三:grouping sets
如果使用group by A,grouping sets(B,C) 那么相当于group by A,B UNION ALL group by A,C

例如:
SQL> select years,months,product_name,sum(sales) sum_sales from t
  2  group by years,grouping sets(months,product_name)
  3  /

YEARS     MONTHS PRODUCT_NA  SUM_SALES
---------- ---------- ---------- ----------
      2008          2                  6000 ----------group by (years,months)
      2008          1                  2500
      2008          3                  3000
      2008            B                4500 ----------group by (years,product_name)
      2008            C                1000
      2008            A                6000

已选择6行。

-----------------------------------------------------------------------------------------------------------------华丽的分割线!

现实中可能希望出现小计、合计等字样的报表,那么可以使用grouping函数来达到美化的效果!

第三:grouping(exp),当没有对exp分组汇总时,便返回1;

例如:
SQL> select months,product_name,sum(sales) sum_sales,grouping(product_name) from t
  2  group by rollup(months,product_name)
  3  /

MONTHS PRODUCT_NA  SUM_SALES GROUPING(PRODUCT_NAME)
---------- ---------- ---------- ----------------------
         1 A                1000                      0 ----------group by (months,product_name)
         1 B                1500                      0
         1                  2500                      1 ----------group by (months)
         2 A                2000                      0
         2 B                3000                      0
         2 C                1000                      0
         2                  6000                      1 ----------group by (months)
         3 A                3000                      0
         3                  3000                      1 ----------group by (months)
                           11500                      1 ----------group by (null)

已选择10行。

第四:GROUPING_ID(exp1,exp2,…,expN)={GROUPING(exp1)||GROUPING(exp2)||…||GROUPING(expN)}变成十进制数,如:
如果GROUPING(A)=1,GROUPING(B)=0,GROUPING(C)=1,那么
GROUPING_ID(A,B,C) = [101]二进制 = 5,
GROUPING_ID(B,A,C) = [011]二进制 = 3.

例如:
SQL> select years,months,product_name,sum(sales) sum_sales,grouping_id(years,months,product_name) g_id from t
  2  group by rollup(years,months,product_name)
  3  /

YEARS     MONTHS PRODUCT_NA  SUM_SALES       G_ID
---------- ---------- ---------- ---------- ----------
      2008          1 A                1000          0
      2008          1 B                1500          0
      2008          1                  2500          1 ----------group by (years,months) 001=1
      2008          2 A                2000          0
      2008          2 B                3000          0
      2008          2 C                1000          0
      2008          2                  6000          1
      2008          3 A                3000          0
      2008          3                  3000          1
      2008                            11500          3 ----------group by (years)   011=3
                                      11500          7 ----------group by (null)    111=7

已选择11行。

了解了grouping和grouping_id函数后,便可以结合decode函数来生成小计合计的效果了;

SQL> select decode(grouping(months)+grouping(product_name),1,'月份小计',2,'合计:',months) months,
  2  product_name,sum(sales) sum_sales from t
  3  group by rollup(months,product_name)
  4  /

MONTHS                                   PRODUCT_NA  SUM_SALES
---------------------------------------- ---------- ----------
1                                        A                1000
1                                        B                1500
月份小计                                                  2500
2                                        A                2000
2                                        B                3000
2                                        C                1000
月份小计                                                  6000
3                                        A                3000
月份小计                                                  3000
合计:                                                   11500

已选择10行。

SQL> select decode(grouping_id(months,product_name),1,'月份小计:',2,'产品小计:',3,'合计:',months) months,
  2  product_name,sum(sales) sum_sales from t
  3  group by cube(months,product_name)
  4  order by 2
  5  /

MONTHS                                   PRODUCT_NA  SUM_SALES
---------------------------------------- ---------- ----------
1                                        A                1000
2                                        A                2000
3                                        A                3000
产品小计:                               A                6000
1                                        B                1500
2                                        B                3000
产品小计:                               B                4500
2                                        C                1000
产品小计:                               C                1000
月份小计:                                                2500
月份小计:                                                6000
月份小计:                                                3000
合计:                                                   11500

已选择13行。

点评:group by rollup、group by cube、grouping sets、grouping函数、grouping_id函数这些属于报表常用函数,要灵活运用!

上一篇:Java程序运行在Docker等容器环境有哪些新问题


下一篇:Spring的JDBC异常抽象