How to use SQL FIRST_VALUE and LAST_VALUE Functions
From Theory to Practice, fully explained.
Window Functions have been introduced in MySQL 8, giving many analysis possibilities that would have previously required hacks or the use of a programming language.
In this article, you will learn about a concrete example of Window Function usage and the general operation typical to all those available in MySQL 8.
Window Functions: What is it 🤨?
A windowed function is a function able to apply an operation to a row using other rows of the table that are linked to it.
For example, if we want each customer of an e-commerce site to evaluate its performance by comparing it to the most profitable and least profitable of its Segment:
- We calculate the best customer by Segment;
- We calculate the worst customer by Segment;
- We list each customer;
Problem: If you calculate the best and the worst customer, you will have to perform aggregation, and therefore, you will not be able in the same time to obtain the profit of each customer, and even less to imagine calculating “the difference to the best” for example 😟.
