OLAP WINDOW FUNCTIONS GROUPING SETS.txt

WINDOW FUNCTIONS, GROUPING SETS, CUBES, ROLLUPS


GROUPING SETS



---- a group by statement
select
department,
joblevel,
sum(salary) as total_salary
from
employees
group by department, joblevel;

-------without group by sets we would do the following
select
department,
joblevel,
sum(salary) as total_salary
from
employees
group by department, joblevel
UNION ALL  --- stacking two tables
select
department,
null,
sum(salary) as total_salary
from
employees
group by department
UNION ALL
select
null,
joblevel,
sum(salary) as total_salary
from
employees
group by joblevel
UNION ALL
select
null,
null,
sum(salary) as total_salary
from
employees

IN order to produce the above results the employees table will be read four times which will impact performance.
It is possible to obtain the same results from one table read which will greatly improve performance.
These functions are built into SQL Server and are called GROUPING SETS, CUBES and ROLLUPS.

Using GROUPING SETS we would write the statement as:
select
department,
joblevel,
sum(salary) as total_salary
from
employees
Group BY
GROUPING SETS
(
   (department, joblevel), ----sum of salary by department and job level
   (department),  ----- sum of salary by department
   (joblevel), ----- sum of salary by job level
   () -----sum of salary over all
)
ORDER BY Grouping(department),Grouping(joblevel),joblevel


ROLLUP Function
ROLLUP is used to do aggregarion operations on multiple levels in a hierarchy. 
Rollup also provides a grand total.

select
department,
joblevel,
sum(salary) as total_salary
from
employees
GROUP BY ROLLUP(department) ----will provide a sum of salary by department and a grand total



select
department,
joblevel,
sum(salary) as total_salary
from
employees
GROUP BY department WITH ROLLUP --- same results as above

select
department,
joblevel,
sum(salary) as total_salary
from
employees
GROUP BY ROLLUP(department,joblevel) ---will provide summary of salary by department and job level, by department and a grand total.
Note that we will not get sum of salary by job level only, for this we need to use the CUBE function.

CUBE
The Cube() function generates all possible combinations of columns specified in the GROUP BY CUBE() clause.
select
department,
joblevel,
sum(salary) as total_salary
from
employees
Group By Cube(department,joblevel)
----
----Group by department,joblevel with Cube -- same cube function as above

CUBE vs. ROLLUP
Cube generates a result set that contains aggregates for all cominations of values in the selected columns whereas Rollup generates a result set that shows aggregates for a hierarchy of values in the selected columns.
The order of columns determines the results when using Rollup.

Cube
Continent, Country, City 
Continent, Country
Continent, City
Country, City
Continent
Country
City
Grand Total

Rollup
Continent, Country, City
Continent, Country
Continent
Grand Total


THE GROUPING FUNCTION IN SQL SERVER
The Grouping(Column) returns a boolean value of 0 or 1 and indicates whether a column in a GROUP BY list is aggregated or not. 
Grouping returns 1 if it is aggregated and 0 if it isn't aggregated in the result set.

 select
 continent,
 country,
 city,
 sum(sales) as totalsales,
 grouping(continent) as gp_continent,
 grouping(country) as gp_country,
 grouping(city) as gp_city
 from
 tbl_sales
 group by rollup(continent,country,city)

If data is aggregated across the grouping column then it will return a 1, if not a 0.
If we see gp_continent = 0, gp_country = 0 and gp_city = 1 then we are seeing the sum of sales aggregated by city.


GROUPING ID FUNCTION
What is the GROUPING_ID function?
Grouping can only be used on one column and will return only a 0 or 1
Grouping_ID returns the level of grouping on a group of columns
GROUPING_ID(colA,colB,colC) = Grouping(colA) + Grouping(colB) + Grouping(colC) and can return values 0 to 7 indicating the level of grouping.
Grouping_ID takes into account the hierarchy of grouping where 000 = 0, 001 = 1, 010 = 2, 011=3, 100=4,101=5, 110 = 6 and 111 = 7.
Grouping_ID concatenates all the Grouping() functions, performs the binary to decimal conversion and returns the equivalent integer.
In this way GROUPING_ID supplies the level of grouping and allows us to filter on these levels of grouping. 

000 no grouping = 0
001 grouping on city only = 1
010 grouping on country only = 2
011 grouping on country and city only = 3
100 grouping on continent only = 4
101 grouping on continent and city = 5
110 grouping on continent, country only = 6 
111 grouping on continent, country and city = 7
TO Filter on Grouping_ID level we would say 'HAVING GROUPING_ID(Continent,Country,City) = 3' for example.

 select
 continent,
 country,
 city,
 sum(sales) as totalsales,
 grouping(continent) as gp_continent,
 grouping(country) as gp_country,
 grouping(city) as gp_city,
 GROUPING_ID(continent,country,city)
 from
 tbl_sales
 group by rollup(continent,country,city)
 having  GROUPING_ID(continent,country,city) = 3

------------------------------------------------------------------------
OVER CLAUSE 

The OVER clause when used with PARTITION BY breaks up the data into partitions or windows. The specified function such as SUM operates on each window of data.
function() OVER (PARTITION BY Col1, Col2,...) where Col1 and Col2 are the columns that we want to perform the function on.
What functions are available? COUNT(), AVG(), SUM(), MIN(), MAX(), ROW_NUMBER(), RANK(), DENSE_RANK().

COUNT(DEPARTMENT_ID) OVER (PARTITION BY DEPARTMENT_ID) will partition the data by DEPARTMENT_ID and then take a COUNT() of records in each partition.


  /** simple count, sum, min, max and avg of salaries by department **/
  select
  count(*) as Employee_Count,
  sum(salary) as Sum_Salary,
  min(salary) as Min_Salary,
  max(salary) as Max_Salary,
  DeptID
  from
  [dbo].[Employees]
  group by DeptID;

What if you want this attached to each detail record? You want to include each record in the table plus these statistics in a report?

This will provide detail records of each employee along with the department statistics in each record.

 Select
  ID,
  Name,
  Title,
  Salary,
  DeptID,
  COUNT(DeptID) OVER (Partition by DeptID) as Dept_Count,
  SUM(Salary) OVER (Partition by DeptID) as Tot_Sal_By_Dept,
  MIN(Salary) OVER(Partition by DeptID) as Min_Sal_By_Dept,
  MAX(Salary) OVER(Partition by DeptID) as Max_Sal_By_Dept
  from 
  [dbo].[Employees]
----------------------------------------------------------------------
The ROW_NUMBER function
Returns the sequential number of a row starting at 1
ORDER BY clause is required
PARTITION BY clause is optional
If PARTITION BY is used then ROW_NUMBER is reset to 1 every time a partition changes

Syntax: ROW_NUMBER() OVER (ORDER BY Col1,Col2)
The ROW_NUMBER() function does not take any arguements

  Select
  Name,
  Title,
  DeptID,
  ROW_NUMBER() OVER(ORDER BY DeptID) as ROW_NUM
  from
  [dbo].[Employees]

Provides simple ROW_NUM output numbering 1 to #employees in DeptID order. There is no break-processing on DeptID.

To begin ROW_NUMBER at 1 for each category use the PARTITION_BY clause. First PARTITION the data and then ORDER the data.
  Select
  Name,
  Title,
  DeptID,
  ROW_NUMBER() OVER(PARTITION BY DeptID ORDER BY DeptID) as ROW_NUM
  from
  [dbo].[Employees]

ROW_NUMBER will begin at 1 everytime the DeptID changes.


RANK() AND DENSE_RANK() FUNCTIONS
Returns a rank starting at 1 based on the ordering of rows imposed by the ORDER BY clause
The ORDER BY clause is required
The PARTITION_BY clause is optional.
When the data is partitioned the rank is reset to 1 when the partition changes.
The RANK function will skip numbers if there is a tie but DENSE_RANK will not.
In the case where the first records in a table have equal rank, RANK() returns 1,1,3,4,5 but the function DENSE_RANK() will return 1,1,2,3,4.

The basic syntax is
RANK() OVER (ORDER BY Col1,Col2,...)
DENSE_RANK() OVER (ORDER BY Col1,Col2,...) --- DOES NOT LEAVE GAPS IN THE RANKINGS


Here I want a list of top ranking salaries. The statement may return more than one row:
  /* USING CTE */
  WITH [RESULT] AS
  (Select
     SALARY,
     RANK() OVER (ORDER BY SALARY DESC) AS SAL_RANK
     FROM
     [ProductDB].[dbo].[Employees]
	) SELECT 
   SALARY
   FROM
   [RESULT] 
   WHERE SAL_RANK = 1
If there are three records with the same ranking as 1 then the next highest RANK will be 4. 

Here I want salaries in top 3 ranking category so I will have to use DENSE_RANK
 WITH [RESULT] AS
  (Select
     SALARY,
     DENSE_RANK() OVER (ORDER BY SALARY DESC) AS SAL_RANK
     FROM
     [ProductDB].[dbo].[Employees]
	) SELECT 
   SALARY
   FROM
   [RESULT] 
   WHERE SAL_RANK <= 3
 
Here I want the top ranking salaries by department - so I will partition the data set by department and then take a dense rank.
First partition the data set, then order within each partition and then take the dense rank

  WITH [RESULT] AS
  (Select
     DeptID,
     SALARY,
     DENSE_RANK() OVER (PARTITION BY DeptID ORDER BY SALARY DESC) AS SAL_RANK
     FROM
     [ProductDB].[dbo].[Employees]
	) SELECT 
   DeptID,
   Salary,
   Sal_Rank
   FROM
   [RESULT] 
   WHERE SAL_RANK <= 3

GROUPING DATA AND RANKING IT USING NTILE FUNCTION

Order by clause is required.
Partition By clause is optional.
NTILE distributes the rows into a specified number of groups
The groups may not have the same number of members depending on how they are divided up.
Larger groups come before smaller groups.
NTILE(2) of 10 rows will divide them equally into two groups with five records in each group.
NTILE(3) of 10 rows will divide them into three groups with the first group having four records and the rest having three records.

Syntax:
NTILE(Number_of_Groups) OVER(ORDER BY Col1,Col2,...)

   SELECT 
   ID,
   Name,
   Title,
   DeptID,
   Salary,
   NTILE(3) OVER (ORDER BY Salary) as NTILE
   FROM
   [ProductDB].[dbo].[Employees]

Will divide the Salaries into three groups with an NTILE value of 1, 2 or 3.
1 will be the lower third of salaries, 3 will be the top third of salaries.

I can further divide the data by Department and look at the NTILE of salaries within each department.
   SELECT 
   ID,
   Name,
   Title,
   DeptID,
   Salary,
   NTILE(3) OVER (PARTITION BY DeptID ORDER BY Salary) as [NTILE]
   FROM
   [ProductDB].[dbo].[Employees]

which will divide up the salaries into three groups within each department.
------------------------------------------------------------
LEAD AND LAG FUNCTIONS

The LEAD function is used to access subsequent rows along with the current row. 
The LAG function is used to access previous rows along with the current row.
ORDER BY clause is required
PARTITION BY clause is optional
LEAD(Column_Name,Offset,Default_value) OVER (ORDER BY Col1, Col2, ...)
LAG(Column_Name,Offset,Default_value) OVER (ORDER BY Col1,Col2,...)
The Default_Value will use used in place of NULLs. 
The Offset is 1 by default meaning that the functions will lead or lag by 1 row unless otherwise specified.

   SELECT
   ID,
   Name,
   Title,
   DeptID,
   Salary,
   LEAD(Salary,3,-1) OVER (ORDER BY SALARY) AS LEAD_BY_3,
   LAG(Salary,3,-1) OVER (ORDER BY SALARY) AS LAG_BY_3
   FROM
   [ProductDB].[dbo].[Employees]

This will give me a column for LEAD and LAG.
They are set to lead or lag by 3 records and will insert -1 if the field is NULL.
 
The LEAD and LAG function can also be used using the PARTITION BY option where the operations will only be performed on the partitioned segment of the data.

   SELECT
   ID,
   Name,
   Title,
   DeptID,
   Salary,
   LEAD(Salary,3,-1) OVER (PARTITION BY DEPTID ORDER BY SALARY) AS LEAD_BY_3,
   LAG(Salary,3,-1)  OVER (PARTITION BY DEPTID ORDER BY SALARY) AS LAG_BY_3
   FROM
   [ProductDB].[dbo].[Employees]

FIRST_VALUE FUNCTION
ORDER BY clause is required.
PARTITION BY clause is optional.
Will provide the first record in a set.
Syntax: FIRST_VALUE(Column_Name) OVER (ORDER BY Col1, Col2, ...)

   SELECT
   ID,
   Name,
   Title,
   DeptID,
   Salary,
   FIRST_VALUE(Name) OVER (ORDER BY Salary) AS First_Value
   FROM
   [ProductDB].[dbo].[Employees]

   SELECT
   ID,
   Name,
   Title,
   DeptID,
   Salary,
   FIRST_VALUE(Name) OVER (PARTITION BY DeptID ORDER BY Salary) AS First_Value
   FROM
   [ProductDB].[dbo].[Employees]

-------------------------------------------------------------
Window Functions
Widow functions come in three categories
Aggregate functions such as SUM, AVG, COUNT, MIN and MAX.
Ranking functions such as RANK, DENSE_RANK and ROW_NUMER
Analytic functions such as LEAD, LAG, FIRST_VALUE and LAST_VALUE

The OVER CLAUSE defines the ordering of rows and the PARTITION BY clause defines the window for the above function to operate on.
The OVER CLAUSE accepts three arguments to define a window: 
ORDER BY, which defines the logical order of the rows,PARTITION BY which defines the block of rows for the operation, ROWS or RANGE clause which limites the rows within the partition by specifying the start and end points within the partition.

syntax for ROWS or RANGE
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
'Unbounded Preceding' means that the widow starts at the first row of the result set.
'Unbounded Following' means that the window ends at the last row in the result set

   SELECT
   ID,
   Name,
   Title,
   DeptID,
   Salary,
   AVG(Salary) OVER (ORDER BY Salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS Average_Salary,
   COUNT(Salary) OVER (ORDER BY Salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS Count_Salary,
   SUM(Salary) OVER (ORDER BY Salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS Sum_Salary
   FROM
   [ProductDB].[dbo].[Employees]

The above statement will provide a single average, single count and single sum for all rows.
This will be added to each individual employee record.
However when we add a partition by we will get a single value describing each group in the partition.

 SELECT
   ID,
   Name,
   Title,
   DeptID,
   Salary,
   AVG(Salary) OVER (PARTITION BY DEPTID ORDER BY Salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS Average_Salary,
   COUNT(Salary) OVER (PARTITION BY DEPTID ORDER BY Salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS Count_Salary,
   SUM(Salary) OVER (PARTITION BY DEPTID ORDER BY Salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS Sum_Salary
   FROM
   [ProductDB].[dbo].[Employees]

The above statement will provide a single average, count and sum of salary for each group in the partition.
These values will be added to each employee record within the partition.

Moving window

By specifying a preceeding and following value a moving window is created.
  SELECT
   ID,
   Name,
   Title,
   DeptID,
   Salary,
   AVG(Salary) OVER (PARTITION BY DEPTID ORDER BY Salary ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS Average_Salary,
   COUNT(Salary) OVER (PARTITION BY DEPTID ORDER BY Salary ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS Count_Salary,
   SUM(Salary) OVER (PARTITION BY DEPTID ORDER BY Salary ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS Sum_Salary
   FROM
   [ProductDB].[dbo].[Employees]

Here the average, count and sum of the current row, previous row and suceeding row is calculated for each employee record.
This is a moving window which provides a unique value for each record.
If we use PARTITION BY MONTH we can get a sliding average over last 3 months etc. This is very useful for trending.


ROWS vs. RANGE Clause
Default value for window functions is RANGE and UNBOUNDED PRECEEDING and CURRENT ROW.

What is the difference between using ROWS and RANGE in these two statements?

  SELECT
   ID,
   Name,
   Title,
   DeptID,
   Salary,
   AVG(Salary) OVER (ORDER BY Salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Average_Salary,
   COUNT(Salary) OVER (ORDER BY Salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Count_Salary,
   SUM(Salary) OVER (ORDER BY Salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Sum_Salary
   FROM
   [ProductDB].[dbo].[Employees]

ROWS treats each row individually. Even if there are duplicate values each row is taken into account and used in the calculation.


  SELECT
   ID,
   Name,
   Title,
   DeptID,
   Salary,
   AVG(Salary) OVER (ORDER BY Salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Average_Salary,
   COUNT(Salary) OVER (ORDER BY Salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Count_Salary,
   SUM(Salary) OVER (ORDER BY Salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Sum_Salary
   FROM
   [ProductDB].[dbo].[Employees]

RANGE does not process duplicate values as compared to ROWS which does. 
RANGE treats duplicates as a single entity.

---------------------------------------------------------
THE PIVOT AND UNPIVOT Functions

Pivot transforms rows into columns and UNPIVOT transforms columns into rows.
PIVOT
Pivot takes unique values in a column and transforms this into multiple columns, in other words, it rotates the table and puts the data into cross tabular format.
Example:
For the table:
create table tbl_SalesByCountry
(
  SalesAgent varchar(30),
  Country varchar(20),
  SalesAmount money
)

We can get the tablular output using US,UK,China and Japan as sample countries:

select
SalesAgent,
US,
UK,
China,
Japan
from
tbl_SalesByCountry
PIVOT (
   SUM(SalesAmount)
   FOR Country
   IN ([US],[UK],[China],[Japan])

) AS Pivot_Table


UNPIVOT
Sample table
create table tbl_SalesAgentByCountry
(
 SalesAgent varchar(30),
 US money,
 UK money,
 Mexico money,
 Japan money,
 China money,
 India money
)

The following sql code will produce a data set having only three columns,
SalesAgent, Country and Sales_Amount. US,UK,Mexico,Japan,China and India values appear in the column Countries with their respective sales amounts.
Each record in the original table will produce six records in the new table.

Note: in the case where there are extraneous fields that will not be included in the final pivot table use the following code to avoid artifacts

Select
SalesAgent,
US,
UK,
China,
Japan
from
   (Select
    SalesAgent,
    Country,
    SalesAmount
    from
    tbl_SalesByCountry)  Source_Table
  PIVOT
  (
     SUM(SalesAmount) 
     FOR Country 
     IN ([US],[UK],[China],[Japan])
  )  Pivot_Table