Dbs101 Flipclass4

Ranking in SQL

The RANK ( ) function is one of the window functions in SQL.

What is a Window function?

A window function is a special function that looks at the data and computes the results for those parts, and the RANK() function is also a window function that assigns a rank to each row based on the provided column.

How does the RANK function work?

It looks through the specified column and computes the ranks for all those rows based on the order specified after the ORDER BY keyword. Then, it sorts the ranks either in ascending or descending order. Lastly it adds a new column that contents all the rank.

If any row gets the same value (e.g., second or 2nd) in the provided column, they are ranked the same, and the function leaves a gap in such cases, therefore assigning the next row with rank 4th, skipping the position 3rd.

The RANK function ranks all the developers according to their experience, and the person with the most experience gets the highest rank. Notice that there are two 3rd ranks, and the rank function, after assigning the rank for those two rows, skips the rank 4th.

Why?

It is done because this incrementation behavior of the rank function is designed to maintain consistency in ranking across the dataset.

Windowing

A Window function performs an aggregate-like operation on a set of query rows; however, it does not group the query rows into a single row as output like the aggregation operation. Instead, it produces a result for every row.

The row for which the function evaluation takes place is called the current row.

Pivoting

In SQL, PIVOT is a relational operation that is used to transform one table into another in order to achieve a simpler view of the table. Alternatively, we can say that the pivot operation converts the rows of a table into columns in the table.

It involves rotating the table value expression by turning the unique values from one column into multiple columns in the output.

Rollup

What is a GROUP BY operation?

It is an operation that groups all the rows of a selected column into single row to draw a conclusion like aggregation, or it’s also called the grand total.

What is a RollUp operation?

A rollup operation is the upgraded version of a GROUP BY operation that not only provides the grand total but also provides the subtotals.

Example :

Rollup in SQL is commonly used to calculate the aggregation of hierarchical data.

Using a GROUP BY query along with the ROLLUP operation helps us in grouping salary by country and gender. ROLLUP in SQL also computes the subtotal at the country level and gender total.

Cube

The CUBE operation is a type of GROUP BY clause that generates a result set that represents aggregations for all the combinations of values in the selected columns.

ALTER VIEW view-name AS SELECT column1, column2, …, columnN FROM table-name WHERE condition

In the above operation, the cube will generate the result set for the following combinations:

• Column 1 , Column 2
• Column 1
• Column 2
• All rows