I am trying to understand an SQL written by a colleague and I am struggling to understand how does the GROUPING() function work in this case. Why is the value of date_level_year_quarter_month equal to 3 and 7 in the last two rows? Thank you in advance!
SELECT
t_year,
t_quarter,
t_month,
GROUPING(t_year, t_quarter, t_month) date_level_year_quarter_month
FROM
d_time
WHERE t_year IN (2016)
GROUP BY
ROLLUP(t_year, t_quarter, t_month)
ORDER BY 1, 2, 3;
+---------+------------+------------+-------------------------------+
| t_year | t_quarter | t_month | date_level_year_quarter_month |
+---------+------------+------------+-------------------------------+
| 2016 | 1 | 1 | 0 |
+---------+------------+------------+-------------------------------+
| 2016 | 1 | 2 | 0 |
+---------+------------+------------+-------------------------------+
| 2016 | 1 | 3 | 0 |
+---------+------------+------------+-------------------------------+
| 2016 | 1 | | 1 |
+---------+------------+------------+-------------------------------+
| 2016 | ... | ... | ... |
+---------+------------+------------+-------------------------------+
| 2016 | | | 3 |
+---------+------------+------------+-------------------------------+
| | | | 7 |
+---------+------------+------------+-------------------------------+
EDIT: It looks like for every dimension 'added', the value of GROUPING function is two times the value of 'previous grouping' + 1, however it is still unclear to me, how the function calculates this values in the first place.
SELECT
T_YEAR
, T_QUARTAL
, T_MONTH
, T_WEEK
, T_DATE
, GROUPING (T_YEAR ) AS LEVEL_YEAR
, GROUPING (T_YEAR, T_QUARTER) AS LEVEL_YEAR_QUARTAL
, GROUPING (T_YEAR, T_QUARTER, T_MONTH) AS LEVEL_YEAR_QUARTAL_MONTH
, GROUPING (T_YEAR, T_QUARTER, T_MONTH, T_WEEK) AS LEVEL_YEAR_QUARTAL_MONTH_WEEK
, GROUPING (T_YEAR, T_QUARTER, T_MONTH, T_WEEK, T_DAY) AS LEVEL_YEAR_QUARTAL_MONTH_WEEK_DAY
FROM D_TIME
WHERE T_YEAR=2016 AND T_WEEK = 1
GROUP BY
ROLLUP (T_YEAR, T_QUARTER, T_MONTH, T_WEEK, T_DAY)
ORDER BY 5,1,2,3,4;