DB2 - Banco de dados - Examples of grouping sets, rollup, and cube queries



Desenvolvido por DORNELLES Carlos Alberto - Analista de Sistemas - Brasília DF. - cad_cobol@hotmail.com

DB2 12 - DB2 SQL - Examples of grouping sets, rollup, and cube queries
Db2 for z/OS 12.0.0

You can use GROUPING SETS, ROLLUP, and CUBE clauses of the GROUP BY clause in subselect queries.
Você pode usar as cláusulas GROUPING SETS, ROLLUP e CUBE da cláusula GROUP BY em consultas de subseleção.

The following examples illustrate the use of GROUPING SETS, ROLLUP, and CUBE clauses of the GROUP BY clause in subselect queries.
The queries in Examples 1 - 4 use a subset of the rows in the SALES tables based on the predicate 'WEEK(SALES_DATE) = 13'.
The other examples do not specify this predicate.

Os exemplos a seguir ilustram o uso das cláusulas GROUPING SETS, ROLLUP e CUBE da cláusula GROUP BY em consultas de subseleção.
As consultas nos Exemplos 1 a 4 usam um subconjunto das linhas nas tabelas SALES com base no predicado 'WEEK (SALES_DATE) = 13'.
Os outros exemplos não especificam esse predicado. 

   SELECT WEEK(SALES_DATE) AS WEEK
   ,      DAYOFWEEK(SALES_DATE) AS DAY_WEEK
   ,      SALES_PERSON, SALES AS UNITS_SOLD 
   FROM   SALES 
   WHERE  WEEK(SALES_DATE) = 13;

The previous query returns results similar to the following: A consulta anterior retorna resultados semelhantes aos seguintes: 

  WEEK        DAY_WEEK    SALES_PERSON    UNITS_SOLD 
  ----------- ----------- --------------- -----------
           13           6 LUCCHESSI                 3
           13           6 LUCCHESSI                 1
           13           6 LEE                       2
           13           6 LEE                       2
           13           6 LEE                       3
           13           6 LEE                       5
           13           6 GOUNOT                    3
           13           6 GOUNOT                    1
           13           6 GOUNOT                    7
           13           7 LUCCHESSI                 1
           13           7 LUCCHESSI                 2
           13           7 LUCCHESSI                 1
           13           7 LEE                       7
           13           7 LEE                       3
           13           7 LEE                       7
           13           7 LEE                       4
           13           7 GOUNOT                    2
           13           7 GOUNOT                   18
           13           7 GOUNOT                    1
Example 1:
A query with a basic GROUP BY clause over 3 columns:
Uma consulta com uma cláusula GROUP BY básica em 3 colunas: 
   SELECT WEEK(SALES_DATE) AS WEEK
   ,      DAYOFWEEK(SALES_DATE) AS DAY_WEEK
   ,      SALES_PERSON, SUM(SALES) AS UNITS_SOLD
   FROM   SALES
   WHERE  WEEK(SALES_DATE) = 13
   GROUP  BY WEEK(SALES_DATE)
   ,      DAYOFWEEK(SALES_DATE)
   ,      SALES_PERSON
   ORDER  BY WEEK
   ,      DAY_WEEK
   ,      SALES_PERSON;

The previous query returns results similar to the following:
A consulta anterior retorna resultados semelhantes aos seguintes: 

  WEEK        DAY_WEEK    SALES_PERSON    UNITS_SOLD 
  ----------- ----------- --------------- -----------
           13           6 GOUNOT                   11
           13           6 LEE                      12
           13           6 LUCCHESSI                 4
           13           7 GOUNOT                   21
           13           7 LEE                      21
           13           7 LUCCHESSI                 4

Example 2:
The following query produces the result based on two different grouping sets of rows from the SALES table.
A consulta a seguir produz o resultado com base em dois conjuntos de agrupamentos diferentes de linhas da tabela SALES. 
   SELECT WEEK(SALES_DATE) AS WEEK
   ,      DAYOFWEEK(SALES_DATE) AS DAY_WEEK
   ,      SALES_PERSON, SUM(SALES) AS UNITS_SOLD
   FROM   SALES 
   WHERE  WEEK(SALES_DATE) = 13
   GROUP  BY GROUPING SETS ((WEEK(SALES_DATE), SALES_PERSON)
   ,                       (DAYOFWEEK(SALES_DATE), SALES_PERSON))
   ORDER  BY WEEK
   ,      DAY_WEEK
   ,      SALES_PERSON;

The previous query returns results similar to the following:
A consulta anterior retorna resultados semelhantes aos seguintes:

O exemplo a seguir ilustra o uso de várias funções de agregação ao usar uma cláusula CUBE.
O exemplo também usa funções de conversão e arredondamento para produzir um resultado decimal com precisão e escala razoáveis.

  WEEK        DAY_WEEK    SALES_PERSON    UNITS_SOLD 
  ----------- ----------- --------------- -----------
           13           - GOUNOT                   32
           13           - LEE                      33
           13           - LUCCHESSI                 8
            -           6 GOUNOT                   11
            -           6 LEE                      12
            -           6 LUCCHESSI                 4
            -           7 GOUNOT                   21
            -           7 LEE                      21
            -           7 LUCCHESSI                 4

The rows with WEEK 13 are from the first grouping set while the other rows are from the second grouping set.
As linhas com WEEK 13 são do primeiro conjunto de agrupamento, enquanto as outras linhas são do segundo conjunto de agrupamento.

Example 3:
If you use the three distinct columns involved in the grouping sets in Example 2 in a ROLLUP clause, you can see grouping sets for (WEEK,DAY_WEEK,SALES_PERSON), (WEEK, DAY_WEEK), (WEEK), and grand-total.
Se você usar as três colunas distintas envolvidas nos conjuntos de agrupamento no Exemplo 2 em uma cláusula ROLLUP, poderá ver os conjuntos de agrupamento para (WEEK, DAY_WEEK, SALES_PERSON), (WEEK, DAY_WEEK), (WEEK) e grand-total. 
   SELECT WEEK(SALES_DATE) AS WEEK
   ,      DAYOFWEEK(SALES_DATE) AS DAY_WEEK
   ,      SALES_PERSON, SUM(SALES) AS UNITS_SOLD
   FROM   SALES
   WHERE  WEEK(SALES_DATE) = 13
   GROUP  BY ROLLUP (WEEK(SALES_DATE)
   ,      DAYOFWEEK(SALES_DATE)
   ,      SALES_PERSON)
   ORDER  BY WEEK
   ,      DAY_WEEK
   ,      SALES_PERSON;

The previous query returns results similar to the following:
A consulta anterior retorna resultados semelhantes aos seguintes:

  WEEK        DAY_WEEK    SALES_PERSON    UNITS_SOLD 
  ----------- ----------- --------------- -----------
           13           6 GOUNOT                   11
           13           6 LEE                      12
           13           6 LUCCHESSI                 4
           13           6 -                        27
           13           7 GOUNOT                   21
           13           7 LEE                      21
           13           7 LUCCHESSI                 4
           13           7 -                        46
           13           - -                        73
            -           - -                        73

Example 4:
The same query as Example 3, using CUBE instead of ROLLUP results in additional grouping sets for (WEEK,SALES_PERSON), (DAY_WEEK,SALES_PERSON), (DAY_WEEK), and (SALES_PERSON) in the result.
A mesma consulta do Exemplo 3, usando CUBE em vez de ROLLUP resulta em conjuntos de agrupamentos adicionais para (WEEK, SALES_PERSON), (DAY_WEEK, SALES_PERSON), (DAY_WEEK) e (SALES_PERSON) no resultado. 
   SELECT WEEK(SALES_DATE) AS WEEK
   ,      DAYOFWEEK(SALES_DATE) AS DAY_WEEK
   ,      SALES_PERSON, SUM(SALES) AS UNITS_SOLD
   FROM   SALES
   WHERE  WEEK(SALES_DATE) = 13
   GROUP  BY CUBE ( WEEK(SALES_DATE)
   ,         DAYOFWEEK(SALES_DATE)
   ,         SALES_PERSON )
   ORDER  BY WEEK
   ,         DAY_WEEK
   ,         SALES_PERSON;

The previous query returns results similar to the following:
A consulta anterior retorna resultados semelhantes aos seguintes:

  WEEK        DAY_WEEK    SALES_PERSON    UNITS_SOLD 
  ----------- ----------- --------------- -----------
           13           6 GOUNOT                   11
           13           6 LEE                      12
           13           6 LUCCHESSI                 4
           13           6 -                        27
           13           7 GOUNOT                   21
           13           7 LEE                      21
           13           7 LUCCHESSI                 4
           13           7 -                        46
           13           - GOUNOT                   32
           13           - LEE                      33
           13           - LUCCHESSI                 8
           13           - -                        73
            -           6 GOUNOT                   11
            -           6 LEE                      12
            -           6 LUCCHESSI                 4
            -           6 -                        27
            -           7 GOUNOT                   21
            -           7 LEE                      21
            -           7 LUCCHESSI                 4
            -           7 -                        46
            -           - GOUNOT                   32
            -           - LEE                      33
            -           - LUCCHESSI                 8
            -           - -                        73

Example 5:
The following query returns a result set that includes a grand-total of the selected rows from the SALES table together with a group of rows aggregated by SALES_PERSON and MONTH.
A consulta a seguir retorna um conjunto de resultados que inclui um total geral das linhas selecionadas da tabela SALES junto com um grupo de linhas agregadas por SALES_PERSON e MONTH. 
   SELECT SALES_PERSON
   ,      MONTH(SALES_DATE) AS MONTH
   ,      SUM(SALES) AS UNITS_SOLD
    FROM  SALES
    GROUP BY GROUPING SETS ((SALES_PERSON, MONTH(SALES_DATE)),
                            ()
                            )
    ORDER BY SALES_PERSON
    ,        MONTH;

The previous query returns results similar to the following:
A consulta anterior retorna resultados semelhantes aos seguintes:

  SALES_PERSON    MONTH       UNITS_SOLD 
  --------------- ----------- -----------
  GOUNOT                    3          35
  GOUNOT                    4          14
  GOUNOT                   12           1
  LEE                       3          60
  LEE                       4          25
  LEE                      12           6
  LUCCHESSI                 3           9
  LUCCHESSI                 4           4
  LUCCHESSI                12           1
  -                         -         155

Example 6:
This example shows two simple ROLLUP queries followed by a query which treats the two ROLLUP clauses as grouping sets in a single result set and specifies row ordering for each column involved in the grouping sets.
Este exemplo mostra duas consultas ROLLUP simples seguidas por uma consulta que trata as duas cláusulas ROLLUP como conjuntos de agrupamento em um único conjunto de resultados e especifica a ordem das linhas para cada coluna envolvida nos conjuntos de agrupamento.
Example 6-1:
   SELECT WEEK(SALES_DATE) AS WEEK
   ,      DAYOFWEEK(SALES_DATE) AS DAY_WEEK
   ,      SUM(SALES) AS UNITS_SOLD
   FROM   SALES
   GROUP  BY ROLLUP (WEEK(SALES_DATE)
   ,         DAYOFWEEK(SALES_DATE))
   ORDER  BY WEEK
   ,         DAY_WEEK;

The previous query returns results similar to the following:
A consulta anterior retorna resultados semelhantes aos seguintes:

  WEEK        DAY_WEEK    UNITS_SOLD 
  ----------- ----------- -----------
           13           6          27
           13           7          46
           13           -          73
           14           1          31
           14           2          43
           14           -          74
           53           1           8
           53           -           8
            -           -         155
Example 6-2:
   SELECT MONTH(SALES_DATE) AS MONTH
   ,      REGION
   ,      SUM(SALES) AS UNITS_SOLD
   FROM   SALES
   GROUP  BY ROLLUP (MONTH(SALES_DATE), REGION)
   ORDER  BY MONTH, REGION;

The previous query returns results similar to the following:
A consulta anterior retorna resultados semelhantes aos seguintes:

  MONTH       REGION          UNITS_SOLD 
  ----------- --------------- -----------
            3 Manitoba                 22
            3 Ontario-North             8
            3 Ontario-South            34
            3 Quebec                   40
            3 -                       104
            4 Manitoba                 17
            4 Ontario-North             1
            4 Ontario-South            14
            4 Quebec                   11
            4 -                        43
           12 Manitoba                  2
           12 Ontario-South             4
           12 Quebec                    2
           12 -                         8
            - -                       155
Example 6-3:
   SELECT WEEK(SALES_DATE) AS WEEK
   ,      DAYOFWEEK(SALES_DATE) AS DAY_WEEK
   ,      MONTH(SALES_DATE) AS MONTH
   ,      REGION
   ,      SUM(SALES) AS UNITS_SOLD
   FROM   SALES
   GROUP  BY GROUPING SETS (ROLLUP(WEEK(SALES_DATE), 
                            DAYOFWEEK(SALES_DATE)),
                            ROLLUP(MONTH(SALES_DATE), REGION))
   ORDER  BY WEEK
   ,         DAY_WEEK
   ,         MONTH
   ,         REGION;

The previous query returns results similar to the following:
A consulta anterior retorna resultados semelhantes aos seguintes:

  WEEK        DAY_WEEK    MONTH       REGION          UNITS_SOLD 
  ----------- ----------- ----------- --------------- -----------
           13           6           - -                        27
           13           7           - -                        46
           13           -           - -                        73
           14           1           - -                        31
           14           2           - -                        43
           14           -           - -                        74
           53           1           - -                         8
           53           -           - -                         8
            -           -           3 Manitoba                 22
            -           -           3 Ontario-North             8
            -           -           3 Ontario-South            34
            -           -           3 Quebec                   40
            -           -           3 -                       104
            -           -           4 Manitoba                 17
            -           -           4 Ontario-North             1
            -           -           4 Ontario-South            14
            -           -           4 Quebec                   11
            -           -           4 -                        43
            -           -          12 Manitoba                  2
            -           -          12 Ontario-South             4
            -           -          12 Quebec                    2
            -           -          12 -                         8
            -           -           - -                       155
            -           -           - -                       155

Using the two ROLLUP clauses as grouping sets causes the result to include duplicate rows.
There are even two grand total rows.
Usar as duas cláusulas ROLLUP como conjuntos de agrupamento faz com que o resultado inclua linhas duplicadas.
Existem até duas linhas de total geral.

Observe how the use of ORDER BY has affected the results:
Observe como o uso de ORDER BY afetou os resultados:

  • In the first grouped set, week 53 has been repositioned to the end.
    No primeiro conjunto agrupado, a semana 53 foi reposicionada para o final.

  • In the second grouped set, month 12 has now been positioned to the end and the regions now display in alphabetic order.
    No segundo conjunto agrupado, o mês 12 agora foi posicionado no final e as regiões agora são exibidas em ordem alfabética.

  • Null values are sorted high.
    Os valores nulos são classificados como altos.

Example 7:
In queries that perform multiple ROLLUP operations in a single pass (such as Example 6-3) you might need to indicate which grouping set produced each row.
The following steps demonstrate how to provide a column (called GROUP) which indicates the origin of each row in the result set.
Origin means which of the two grouping sets produced the row in the result set.

Em consultas que realizam várias operações ROLLUP em uma única passagem (como o Exemplo 6-3), você pode precisar indicar qual conjunto de agrupamento produziu cada linha.
As etapas a seguir demonstram como fornecer uma coluna (chamada GRUPO) que indica a origem de cada linha no conjunto de resultados.
Origem significa qual dos dois conjuntos de agrupamento produziu a linha no conjunto de resultados.
Step 1:
Introduce a way of generating new data values, using a query which selects from the SYSDUMMY1 table.
The following query shows how a table (named X) can be derived with two columns, R1 and R2, and one row of data.
Introduzir uma maneira de gerar novos valores de dados, usando uma consulta que seleciona na tabela SYSDUMMY1.
A consulta a seguir mostra como uma tabela (denominada X) pode ser derivada com duas colunas, R1 e R2, e uma linha de dados. 
   SELECT R1 
   ,      R2 
   FROM  (SELECT 'GROUP 1' as R1, 'GROUP 2' as R2
          FROM SYSIBM.SYSDUMMY1) AS X(R1,R2);

The results are similar to the following:
Os resultados são semelhantes aos seguintes:

  R1      R2     
  ------- -------
  GROUP 1 GROUP 2
Step 2:
Form the cross product of the table X with the SALES table. The following query adds columns R1 and R2 to every row.
Forme o produto vetorial da tabela X com a tabela SALES. A consulta a seguir adiciona as colunas R1 e R2 a cada linha. 
   SELECT R1
   ,      R2
   ,      WEEK(SALES_DATE) AS WEEK
   ,      DAYOFWEEK(SALES_DATE) AS DAY_WEEK
   ,      MONTH(SALES_DATE) AS MONTH
   ,      REGION
   ,      SALES AS UNITS_SOLD
   FROM   SALES
   ,     (SELECT 'GROUP 1' as R1
          ,      'GROUP 2' as R2
          FROM SYSIBM.SYSDUMMY1) AS X(R1,R2);
Step 3:
Now the R1 and R2 columns can be combined with the grouping sets to include R1 and R2 in the rollup analysis.
Agora, as colunas R1 e R2 podem ser combinadas com os conjuntos de agrupamento para incluir R1 e R2 na análise de rollup. 
   SELECT R1
   ,      R2
   ,      WEEK(SALES_DATE) AS WEEK
   ,      DAYOFWEEK(SALES_DATE) AS DAY_WEEK
   ,      MONTH(SALES_DATE) AS MONTH
   ,      REGION, SUM(SALES) AS UNITS_SOLD
   FROM   SALES
   ,     (SELECT 'GROUP 1' as R1
          ,      'GROUP 2' as R2
          FROM SYSIBM.SYSDUMMY1) AS X(R1,R2)
   GROUP  BY GROUPING SETS ((R1, ROLLUP(WEEK(SALES_DATE),
                             DAYOFWEEK(SALES_DATE))),
                            (R2,ROLLUP(MONTH(SALES_DATE), REGION)))
   ORDER BY WEEK
   ,        DAY_WEEK
   ,        MONTH
   ,        REGION

The previous query returns results similar to the following: A consulta anterior retorna resultados semelhantes aos seguintes:

  R1        R2      WEEK     DAY_WEEK  MONTH     REGION        UNITS_SOLD 
  -------   ------- -------- --------- --------- ----------    -----------
  GROUP 1   -             13         6         - -                      27
  GROUP 1   -             13         7         - -                      46
  GROUP 1   -             13         -         - -                      73
  GROUP 1   -             14         1         - -                      31
  GROUP 1   -             14         2         - -                      43
  GROUP 1   -             14         -         - -                      74
  GROUP 1   -             53         1         - -                       8
  GROUP 1   -             53         -         - -                       8
  -         GROUP 2        -         -         3 Manitoba               22
  -         GROUP 2        -         -         3 Ontario-North           8
  -         GROUP 2        -         -         3 Ontario-South          34
  -         GROUP 2        -         -         3 Quebec                 40
  -         GROUP 2        -         -         3 -                     104
  -         GROUP 2        -         -         4 Manitoba               17
  -         GROUP 2        -         -         4 Ontario-North           1
  -         GROUP 2        -         -         4 Ontario-South          14
  -         GROUP 2        -         -         4 Quebec                 11
  -         GROUP 2        -         -         4 -                      43
  -         GROUP 2        -         -        12 Manitoba                2
  -         GROUP 2        -         -        12 Ontario-South           4
  -         GROUP 2        -         -        12 Quebec                  2
  -         GROUP 2        -         -        12 -                       8
  -         GROUP 2        -         -         - -                     155
  GROUP 1   -              -         -         - -                     155
Step 4:
Because R1 and R2 are used in different grouping sets, whenever R1 is non-null in the result, R2 is null and whenever R2 is non-null in the result, R1 is null.
You can consolidate these columns into a single column (GROUP) using the COALESCE function.
You can also use the GROUP column in the ORDER BY clause to keep the results of the two grouping sets together.

Como R1 e R2 são usados em conjuntos de agrupamento diferentes, sempre que R1 não for nulo no resultado, R2 será nulo e sempre que R2 for não nulo no resultado, R1 será nulo.
Você pode consolidar essas colunas em uma única coluna (GRUPO) usando a função COALESCE.
Você também pode usar a coluna GROUP na cláusula ORDER BY para manter os resultados dos dois conjuntos de agrupamento juntos. 
   SELECT COALESCE(R1,R2) AS GROUP
   ,      WEEK(SALES_DATE) AS WEEK
   ,      DAYOFWEEK(SALES_DATE) AS DAY_WEEK
   ,      MONTH(SALES_DATE) AS MONTH
   ,      REGION, SUM(SALES) AS UNITS_SOLD
   FROM SALES
   ,    (SELECT 'GROUP 1' as R1
         ,      'GROUP 2' as R2
         FROM   SYSIBM.SYSDUMMY1) AS X(R1,R2)
   GROUP BY GROUPING SETS ((R1, ROLLUP(WEEK(SALES_DATE),
                            DAYOFWEEK(SALES_DATE))),
                           (R2, ROLLUP(MONTH(SALES_DATE), 
                            REGION)))
   ORDER BY GROUP
   ,        WEEK
   ,        DAY_WEEK
   ,        MONTH, REGION;

The previous query returns results similar to the following:
A consulta anterior retorna resultados semelhantes aos seguintes:


  GROUP   WEEK        DAY_WEEK    MONTH       REGION     UNITS_SOLD 
  ------- ----------- ----------- ----------- ---------- -----------
  GROUP 1          13           6           - -                   27
  GROUP 1          13           7           - -                   46
  GROUP 1          13           -           - -                   73
  GROUP 1          14           1           - -                   31
  GROUP 1          14           2           - -                   43
  GROUP 1          14           -           - -                   74
  GROUP 1          53           1           - -                    8
  GROUP 1          53           -           - -                    8
  GROUP 1           -           -           - -                  155
  GROUP 2           -           -           3 Manitoba            22
  GROUP 2           -           -           3 Ontario-North        8
  GROUP 2           -           -           3 Ontario-South       34
  GROUP 2           -           -           3 Quebec              40
  GROUP 2           -           -           3 -                  104
  GROUP 2           -           -           4 Manitoba            17
  GROUP 2           -           -           4 Ontario-North        1
  GROUP 2           -           -           4 Ontario-South       14
  GROUP 2           -           -           4 Quebec              11
  GROUP 2           -           -           4 -                   43
  GROUP 2           -           -          12 Manitoba             2
  GROUP 2           -           -          12 Ontario-South        4
  GROUP 2           -           -          12 Quebec               2
  GROUP 2           -           -          12 -                    8
  GROUP 2           -           -           - -                  155

Example 8:
The following example illustrates the use of various aggregate functions when using a CUBE clause.
The example also makes use of cast functions and rounding to produce a decimal result with reasonable precision and scale.

O exemplo a seguir ilustra o uso de várias funções de agregação ao usar uma cláusula CUBE.
O exemplo também usa funções de conversão e arredondamento para produzir um resultado decimal com precisão e escala razoáveis. 
   SELECT MONTH(SALES_DATE) AS MONTH
   ,      REGION
   ,      SUM(SALES) AS UNITS_SOLD
   ,      MAX(SALES) AS BEST_SALE
   ,      CAST(ROUND(AVG(DECIMAL(SALES)),2) AS DECIMAL(5,2)) 
          AS AVG_UNITS_SOLD 
   FROM   SALES 
   GROUP  BY CUBE(MONTH(SALES_DATE),REGION)
   ORDER  BY MONTH, REGION;

The previous query returns results similar to the following:
A consulta anterior retorna resultados semelhantes aos seguintes:

MONTH       REGION          UNITS_SOLD  BEST_SALE   AVG_UNITS_SOLD
----------- --------------- ----------- ----------- --------------
          3 Manitoba                 22           7           3.14
          3 Ontario-North             8           3           2.67
          3 Ontario-South            34          14           4.25
          3 Quebec                   40          18           5.00
          3 -                       104          18           4.00
          4 Manitoba                 17           9           5.67
          4 Ontario-North             1           1           1.00
          4 Ontario-South            14           8           4.67
          4 Quebec                   11           8           5.50
          4 -                        43           9           4.78
         12 Manitoba                  2           2           2.00
         12 Ontario-South             4           3           2.00
         12 Quebec                    2           1           1.00
         12 -                         8           3           1.60
          - Manitoba                 41           9           3.73
          - Ontario-North             9           3           2.25
          - Ontario-South            52          14           4.00
          - Quebec                   53          18           4.42
          - -                       155          18           3.87

Veja também:


© Copyright IBM Corp.