19.1.3. Window Functions

• Perform calculations across rows related to the current row.

Window functions are a special type of function in SQL that allow you to perform calculations across a set of rows that are related to the current row. This means that instead of having to aggregate all your data into one row, you can retain the details of each row while still being able to perform calculations that consider additional data in relation to that row. It's similar to having a running total or a ranking based on certain criteria within a specified group of rows.

Imagine you're a teacher grading students in different classes. While you can calculate the average score for the entire school (which would represent an aggregate), you can also calculate the average score just for each class while still seeing the individual scores of the students. This is analogous to using a window function — you're viewing individual performance while analyzing group performance at the same time.

• Common examples: ROW_NUMBER(), RANK(), LAG(), LEAD().

Some of the most commonly used window functions include: 1. ROW_NUMBER(): Assigns a unique sequential integer to rows within a partition of a result set, starting at 1. 2. RANK(): Similar to ROW_NUMBER(), but if there are ties, it assigns the same rank to tied rows and the next rank in the sequence is skipped. 3. LAG(): Provides access to a row at a given physical offset that comes before the current row in the result set. 4. LEAD(): Similar to LAG, but it allows you to access rows that come after the current row. Each of these functions has a particular use case for scenarios where you need to analyze data related to the rows themselves.

Consider a sports tournament where you're tracking player performances. Using ROW_NUMBER(), you can assign each player a sequential number based on their overall score. With RANK(), if two players score the same, they both get the same rank, but the ranking for the next player would skip a number. If you want to find out how a player's performance compares to previous games, LAG() can tell you their score from the last game, while LEAD() can give you the score for the next game they're scheduled to play. This dynamic view helps coaches make better decisions.

sqlCopyEditSELECT name, department, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank FROM employees;

This SQL query uses the RANK() window function to assign a rank to each employee based on their salary, but it does so within separate departments. The PARTITION BY clause divides the dataset into partitions (in this case, each department). The ORDER BY clause within the window function specifies that we want to rank the employees within each department based on their salary in descending order. This way, you get a rank for each employee relative to their peers in the same department, which is incredibly useful for understanding their standing within that specific group rather than the entire organization.

Imagine a school where students are ranked not just overall but within their classes. Just like teachers might want to see how each student fares in comparison to their classmates (and not compared to the whole school), this SQL query helps businesses manage employees by allowing departments to see how their workers stack up against each other based on salary. If a manager knows which employees are the top earners in their department, they can better strategize promotions and salary adjustments.

--