Oppiskele Window Functions | Triggers, Window Functions and DCL

SQL Optimization and Query Features

Pyyhkäise näyttääksesi valikon

Window functions are powerful SQL tools that allow you to perform calculations across sets of table rows that are related to the current row. Unlike standard aggregate functions, which collapse query results into a single value per group, window functions keep each row of the query result visible while providing additional analytical insights.

A common use for window functions is calculating running totals. Imagine you have a sales table, which tracks each sale made by employees. If you want to see the cumulative sales amount for each employee as you move through their sales chronologically, a window function makes this straightforward.


              12345678910111213
            
SELECT
    employee_id,
    sale_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY employee_id
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM
    sales
ORDER BY
    employee_id, sale_date;

In this query, you calculate a running total of sales for each employee. The SUM(amount) function is used as a window function by combining it with the OVER() clause.

The PARTITION BY employee_id part tells SQL to restart the running total for each employee, so each employee's sales are tallied separately. The ORDER BY sale_date inside the OVER() clause ensures that the running total is calculated in chronological order of sales. The ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW specifies that the sum should include all sales from the start up to the current row.

The result is a list showing each sale, the employee who made it, and the cumulative sales amount for that employee up to that point in time. Window functions like this are essential for advanced reporting and analytics, as they let you compare each row to other rows in a flexible, dynamic way without losing detail in your results.

Oliko kaikki selvää?

Kiitos palautteestasi!

Osio 3. Luku 2

Kysy tekoälyä

Kysy mitä tahansa tai kokeile jotakin ehdotetuista kysymyksistä aloittaaksesi keskustelumme

Osio 3. Luku 2

Window Functions

1. What does the OVER() clause do in a window function?

2. How are window functions different from aggregate functions?

3. Can window functions be used with ORDER BY?