Chapter 5: Grouping and Windowing

Question 1

What is a grouping set?

Answer 1

Group the data by a set of attributes (columns).

Question 2

When a query becomes a grouped query?

Answer 2

A query becomes a grouped query when you use a group function, a GROUP BY clause, or both.

Question 3

What does return a grouped query?

Answer 3

Grouped queries return one result row per group.

Question 4

What is the difference between COUNT(column) and COUNT(*)?

Answer 4

The former ignores the NULL and the latter doesn’t ignore NULL.

Question 5

Can the NULL be grouped in a GROUP BY?

Answer 5

Yes, it can.

Question 6

If I want define more than a grouping set, which clause can I use?

Answer 6

GROUPING SETS, ROLLUP or CUBE.

Question 7

What does do the GROUPING SETS clause?

Answer 7

List all grouping sets that the user want define in the query.

Question 8

What’s the function of empty set represented by a empty parentheses () in a GROUPING SETS clause?

Answer 8

It calculates the grant total.

Question 9

What does do the CUBE clause?

Answer 9

Accept a list of expressions and define all possible grouping sets that can be generated from the inputs.

Question 10

What does do the ROLLUP clause?

Answer 10

Given a set of columns, it’ll detailed the most right column. For example, GROUP BY ROLLUP(col1, col2, col3) will produce 4 groups: (1) grouped by col1, col2, col3. (2) grouped by col1, col2. (3) grouped by col1. (4) (), this is the grand total.

Question 11

What does do the function GROUPING?

Answer 11

Return 1 when the column value is added by the clause ROLLUP or CUBE, or return 0 when the column value is from the grouping set.

Question 12

What is pivoting data?

Answer 12

Is a techinque that groups and aggregates data.

Question 13

What are the elements which need to be identified in a pivot query?

Answer 13

1. What I want to see on rows? This is known as grouping element.
2. What I want to see on columns? This is known as spreading element.
3. What I want to see in the intersection of a column and a row? This is known as data or aggregation element.

Question 14

What is unpivoting data?

Answer 14

Unpivoting data can be considered the inverse of pivoting. The starting point is some pivoted
data. When unpivoting data, you rotate the input data from a state of columns to a state of
rows.

Question 15

What is the difference between a Group Aggregate Function and a Window Aggregate Function?

Answer 15

A group aggregate function is applied to per group from a result set, whereas a window aggregate function is applied to a windows of rows.

Question 16

What is a window function?

Answer 16

It’s a function that’s applied to a set of rows, not like a commun function, which it’s applied to a value, or even a set of values. The word “window” refers to the set of rows that’s applied the function.

Question 17

How I can apply a window function?

Answer 17

Using the OVER clause.

Question 18

What does do the “ OVER (PARTITION BY col1, …, coln)”?

Answer 18

It applies a window function according to a partition done in the set (it does subset in a set). A partition it’s like a group by. When we do “group by col1”, it’ll partition the result set by the each distinct value from the col1. That’s how also works PARTITION BY.

Question 19

What does do the “ OVER (ORDER BY col1, …, coln)”?

Answer 19

It says in which order is going to be evaluated the row in a set or subset. It can also be used with the PARTITION BY sub clause.

Question 20

RANGE/ROWS terminology in the OVER clause

Answer 20

ROWS or RANGE- specifying rows or range.
PRECEDING – get rows before the current one.
FOLLOWING – get rows after the current one.
UNBOUNDED – when used with PRECEDING or FOLLOWING, it returns all before or after.
CURRENT ROW

Question 21

Mention some of windows functions, his purpose and what does.

Answer 21

Ranking functions:

1. ROW_NUMBER: enumerate each row in a group.
2. RANK: as his name says, ranks a row according to the order by. If there’s a tie, then it’ll create a gap in the sequence: 1 2 3 5. There’s not the number 4, so that means there’s a gap.
3. DENSE_RANK: as RANK, but there’s not gap if there’s a tie.

Analytic functions:
1. LEAD: The LEAD function is used to read a value from the next row, or the row below the actual row. If
the next row doesn’t exist, then NULL is returned.
2. LAG: The LAG() function is similar to the LEAD() function, but it returns the row before the actual row.

Aggregation functions:

1. SUM.
2. AVG.
3. MIN.
4. MAX.
5. COUNT.

Question 22

What’s the difference between GROUPING SETS, ROLLUP and CUBE?

Answer 22

All of them allows you to group by more than group set. GROUPING SETS creates sets static, static means the user has to say what are the grouping sets which he/she wants. ROLLUP creates grouping sets dynamically, where the most right column it’s the column which it’s more detailed. For example: GROUP BY ROLLUP(col1, col2, col3) will create the following grouping sets: (col1,col2,col3), (col1,col2), (col1), (). The last one it’s the grand total. And the CUBE it’s similar to the ROLLUP, but it makes all possible combination between columns. For example: GROUP BY CUBE(col1, col2,col3) makes the following grouping sets: (col1,col2,col3), (col1,col2), (col1,col3), (col2,col3), (col1), (col2), (col3), (). Another difference it’s with GROUPING SETS the grand total is not calculated, it needs to be specified with () notation.

Question 23

What’s the syntax for Pivoting in SQL Server?

Answer 23

SELECT ,
FROM
PIVOT ( ( FOR IN ())) AS ;

Notes:

: it can be one, or more than one (1, 2, …, N).
: the source table just only needs the non pivot column, the pivot column and the column which will be aggregated (aggregate column).

Question 24

What’s the syntax for Unpivoting in SQL Server?

Answer 24

SELECT , ,
FROM
UNPIVOT( FOR IN ()) AS ;

Notes:

: it’s the name of the column which will hold the aggregate values from the pivot table.
: it can be one or more than one value (1, 2, …, N).

Question 25

What’s GROUPING SETS Algebra?

Answer 25

It’s possible has more than one GROUPING SETS sub clause in the GROUP BY clause, each sub clause separated by comma. When this happens, both GROUPING SETS multiply each other. For example: GROUP BY GROUPING SETS ( (A), (B), (C) ), GROUPING SETS ( (D), (E)), will be logically equivalent to: GROUP BY GROUPING SETS ((A,D), (A,E), (B,D), (B,E), (C,D), (C,E)).

Question 26

What does do the GROUPING_ID function?

Answer 26

The formula which it’s used to calculate the GROUPING_ID is:
GROUPING_ID(col1, col2, …, coln) = GROUPING(col1)2^(n-1) + GROUPING(col2)2^(n-2) + … + GROUPING(coln)*2^(n-n)
The function GROUPING_ID says the level of the grouping. For example: let’s say in our GROUP BY clause, we have
the sub clause ROLLUP(col1, col2, col3). In case it’s grouping by col1, col2, the GROUPING_ID function (GROUPING_ID(col1,col2,col3))
will return 1, saying the current row it’s grouped by col1, col2. In case it’s grouping by col1, the GROUPING_ID function
will return 3, saying the current row it’s grouped by col1 only.In case it’s grouped as a whole set (no grouping sets), the GROUPING_ID
function will return 7.

Question 27

What’s the difference between GROUPING and GROUPING_ID function?

Answer 27

GROUPING it’s for calulate if the NULL it’s for placeholder or not. GROUPING_ID, with the help of GROUPING function, calculates the level of grouping.