SQL Window Function – part 1

Hi folks here in this article we are going to see what is window function in SQL, and how it is useful for data analyst for data preprocessing or generation accumulated data with lots of information, combination of calculation and lot of more.

Window function or analytic function is a function which uses values from one or multiple rows to return a value for each row. Window functions have an OVER clause; any function without an OVER clause is not a window function, but rather an aggregate or single-row function. (wikipedia answer).

Explanation  – Window functions are similar to the aggregation done in the GROUP BY clause. However, rows are not grouped into a single row, each row retains their separate identity. That is, a window function may return a single value for each row.

Why use window functions ?

Biggest Advantage with window function is. –  this work with aggregate and non-aggregates at same time. return all the rows along with aggregated values.

Second –  because it is easy to read and understand by users, reduce the complexity of the query, sub-queries and also it help in performance. And what is means by performance : Example – We can use window function instead of self join or do lots of sub query.

So let’s start some practical, where we can see this function in working manner.

Here is the main table where we are going to use window function –

In the above table we got company employees details, In which their name , Department and salary data are available.

So we will adding normal aggregate function and do the same thing with window function and know the difference between them.

select dept_name, max(salary) from employee
group by dept_name;

Output (Normal Aggregate function)

Window Function

select e.*,
max(salary) over(partition by dept_name) as max_salary
from employee e;

Output (Window Function)

Here you can see with window function we can get all other column details without using sub-queries.

what if we use aggregate with all column or the column which is not in aggregate list, will throw an error

select emp_ID, dept_name, max(salary) from employee
group by dept_name;

Here in the above query we used emp_ID as a column to show along with Maximum salary , Dept name.

Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'company.employee.emp_ID' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

List of window functions 

There are three main types of window functions available to use: aggregate, ranking, and value functions. In the image below, you can see some of the names of the functions that fall within each group.

Aggregate functions: we can use these functions to calculate various aggregations such as average, total # of rows, maximum or minimum values, or total sum within each window or partition.

Ranking functions: these functions are useful for ranking rows within its partition.

Value functions: these functions allow you to compare values from previous or following rows within the partition or the first or last value within the partition.

Will see more working example on each window function later on another article of this topic.

I hope this post helped you to know SQL Window Function – part 1. To get the latest news and updates follow us on Twitter & Facebook, subscribe to our YouTube channel.  And If you have any query then please let us know by using the comment form.