group by

group by是迄今为止我觉得做报表最强大的语句。求聚合值非常方便，主要有以下几个应用：

1.  group by 简单应用

可以按照时间维度，或者地域，销售组织维度做出聚合值。简单好用，比如我要求出销售需求对应每个物料的合计数，如下是abap代码：

SELECT MATNR SUM( VMENG )
FROM vbbe
into TABLE lt_test
GROUP BY matnr.

不用loop之后再collect，非常方便。

2. rollup

rollup的行为非常直接，它根据grouping list的rollup条款创建合计：

首先，它计算grouping条款的标准聚合。

然后，它按照grouping list列从右到左进行更高层的聚合。

最后，创建n＋1层的总计。

例如： group by rollup(A,B,C),则oracle最后得到的聚合结果为(A,B,C), (A,B), (A),()

Rollup对group by 的扩展比较简单，但非常高效，对一个查询增加的开销非常少。

我觉得BO中的hierarchy搞不好就是用了rollup去做的层级关系。

举个例子：

select spart,bzirk,vkorg,sum(zzfkimg)
from s901
where sptag = '20041010'
and bzirk = '0001' or bzirk = '0002'
group by rollup (spart,bzirk,vkorg);

运行结果：

SP BZIRK  VKOR SUM(ZZFKIMG)
-- ------ ---- ------------
10 0001   5101          427
10 0001   8010           18
10 0001   8100           61
10 0001                    506
10 0002   5101         9735
10 0002   6101         6244
10 0002   8010        10225
10 0002   8030        92089
10 0002   8040        58528
10 0002   8050       425771
10 0002   8060         1791

SP BZIRK  VKOR SUM(ZZFKIMG)
-- ------ ---- ------------
10 0002   8070          168
10 0002   8100       436348
10 0002   8110        21624
10 0002                 1062523
10                         1063029
11 0002   2501       956300
11 0002                 956300
11                          956300
20 0001   5101        79
20 0001                  79
20 0002   5101         6917

SP BZIRK  VKOR SUM(ZZFKIMG)
-- ------ ---- ------------
20 0002   8030         1933
20 0002   8040         1254
20 0002   8050         9670
20 0002   8060         1469
20 0002   8100         3510
20 0002                   24753
20                           24832
30 0001   2101        400000
30 0001                  400000
30 0002   2101        4488416
30 0002   2301           14

SP BZIRK  VKOR SUM(ZZFKIMG)
-- ------ ---- ------------
30 0002   2401          720
30 0002   5101         2900
30 0002   8030           96
30 0002   8040         1611
30 0002   8050        18952
30 0002   8060            2
30 0002   8070          147

30 0002   8100        20054
30 0002                  4532912
30                          4932912
40 0002   2201          666

SP BZIRK  VKOR SUM(ZZFKIMG)
-- ------ ---- ------------
40 0002   2401           11
40 0002   8030           80
40 0002   8040            9
40 0002   8050           47
40 0002   8100           76
40 0002                    889
40                             889
50 0001   2301           13
50 0001                   13
50 0002   2301         4603
50 0002   7101         4279

SP BZIRK  VKOR SUM(ZZFKIMG)
-- ------ ---- ------------
50 0002   8030            8
50 0002   8040          203
50 0002   8050           26
50 0002   8100           37
50 0002                   9156
50                            9169
60 0002   2401        31826
60 0002   8030            4
60 0002                  31830
60                          31830
70 0002   5201            2

SP BZIRK  VKOR SUM(ZZFKIMG)
-- ------ ---- ------------
70 0002                     2
70                             2
                                 7018963

可以看到是如何对数据进行聚合的。

3. cube

Cube进行grouping列规定的grouping，创建所有可能的聚合，例如规定了cube（customer_name,market_name）那么返回的结果将是2的2次方个。分别为（customer_name,market_name）(market_name)(customer_name)()

Cube扩展在计算交叉报表时非常有用，当然我们可以使用select union all替代rollup或cube，但这需要很多的select union all语句。这样做不仅不够高效，而且加长了sql语句，不方便维护和扩充。

举个cube的例子：

SQL> select bzirk,spart,sum(zzfkimg)
  2  from s901
  3  where sptag = '20041010'
  4  And bzirk = '0001' or bzirk = '0002'
  5  group by cube(bzirk, spart);

BZIRK  SP SUM(ZZFKIMG)
------ -- ------------
0001   10          506
0001   20           79
0001   30       400000
0001   50           13
0001            400598
0002   10      1062523
0002   11       956300
0002   20        24753
0002   30      4532912
0002   40          889
0002   50         9156

BZIRK  SP SUM(ZZFKIMG)
------ -- ------------
0002   60        31830
0002   70            2
0002           6618365
       10      1063029
       11       956300
       20        24832
       30      4932912
       40          889
       50         9169
       60        31830
       70            2

BZIRK  SP SUM(ZZFKIMG)
------ -- ------------
               7018963

4. grouping sets

grouping sets提供了指定汇总集合条件的功能。我们可以通过为select group by语句制定。例子如下：

SELECT
prod_category, prod_subcategory, country_id, cust_city, count(*)
FROM products, sales, customers
WHERE sales.prod_id = products.prod_id
AND sales.cust_id=customers.cust_id
AND sales.time_id = '01-oct-00'
AND customers.cust_year_of_birth BETWEEN 1960 and 1970
GROUP BY GROUPING SETS
(
(prod_category, prod_subcategory, country_id, cust_city),
(prod_category, prod_subcategory, country_id),
(prod_category, prod_subcategory),
country_id
)

 

rollup 和cube带来的一个问题是，在返会的结果中如何能准确区分出那些是小计，哪些是汇总数据呢。这点可以使用grouping和grouping_id函数解决。

另外，我们还可以通过having过虑掉我们不希望在结果中出现的数据。