Azure AI Services

SQL Window Functions 

 SQL window functions operate over a set of rows related to the current row within the result set.

- Aggregate functions, return a single value for a group of rows,

- Window functions return a single value for each row from the underlying query, based on the related set of rows.

Lets say is a employees salaries table.

- Simple SQL can return listing of employees ordered by salaries 

- Using Aggregate like Sum can show total money paid as salary to all employess.

- Adding group by to it can show salary total by department.

- Using window functions can show number next to each employees showing his rank.

SQL window functions are also called OLAP (Online Analytical Processing) functions.

Window functions are a subset of SQL functions that operate over a set (or "window") of rows related to the current row within the result set. Unlike aggregate functions, which return a single value for a group of rows, window functions return a single value for each row from the underlying query, based on the related set of rows. For example in a query result set Lag function can access previous row can be used to say see difference is salaries of employees.

Window Functions provide deeper insights into data, allowing for calculations across sets of rows related to the current row. Additionally, they often lead to more efficient queries as compared to self-joins or subqueries. Moreover, they can provide running totals, moving averages, and other cumulative metrics without altering the granularity of the query result.

Lets see an example

Aggregate function SUM , will return one number of total like below

SELECT

    SUM(salary) sum_salary

FROM

    employees

 

Use of simple window function like below  can show it next to each row without complex SQL Code

SELECT

    first_name,

    last_name,

    salary,

    SUM(salary) OVER() sum_salary

FROM

    employees;

Here is the partial output:

Going deep we can also see between subsets of data. for example rank within inside of each dept

SELECT              

                               RANK() OVER (PARTITION BY department ORDER BY salary DESC)

                                              AS dept_ranking,

                               department,

                               employee_id,

                               full_name,

                               salary

FROM employee;

results below:

What if we want to have the same repor

 

The following picture illustrates the main difference between aggregate functions and window functions:

Types of Window Functions

The window functions are divided into three types value window functions, aggregation window functions, and ranking window functions:

Value window functions

·         FIRST_VALUE()

·         LAG()

·         LAST_VALUE()

·         LEAD()

Ranking window functions

·         CUME_DIST()

·         DENSE_RANK()

·         NTILE()

·         PERCENT_RANK()

·         RANK()

·         ROW_NUMBER()

Aggregate window functions

·         AVG()

·         COUNT()

·         MAX()

·         MIN()

·         SUM()

Let's take a closer look at RANK() and DENSE_RANK()

RANK()

The RANK() function assigns a unique rank to each distinct row within a result set based on the values in one or more columns. If two (or more) rows have the same values in the specified columns, they get the same rank. However, when identical ranks are assigned, it causes the next rank(s) in the sequence to be skipped, depending on the number of duplicates.

 

https://www.sqltutorial.org/sql-window-functions/