Lära Types of Window Functions | Triggers, Window Functions and DCL

SQL Optimization and Query Features

Svep för att visa menyn

When working with SQL window functions, you can use them to perform calculations across sets of rows related to the current row, without collapsing results into a single value per group. Three commonly used window functions are ROW_NUMBER(), RANK(), and LAG(). These functions are especially useful when analyzing data such as the sales table, which tracks each sale by employee and date.

ROW_NUMBER() assigns a unique sequential number to each row within a partition, ordered by the criteria you specify. For instance, you can number each sale by an employee in the order they occurred;
RANK() is similar, but if there are ties (rows with equal values in the ordering column), it assigns the same rank to those rows and skips the next rank(s);
LAG() lets you access data from a previous row in the same result set, which is very helpful for comparing current and previous values, such as tracking how the amount of sales changes from one sale to the next for each employee.


              1234567891011121314151617
            
-- Number each sale for each employee by sale_date
SELECT
  sale_id,
  employee_id,
  amount,
  sale_date,
  ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY sale_date) AS sale_sequence
FROM sales;

-- Compare each sale amount to the previous sale's amount for that employee
SELECT
  sale_id,
  employee_id,
  amount,
  sale_date,
  LAG(amount, 1) OVER (PARTITION BY employee_id ORDER BY sale_date) AS previous_amount
FROM sales;

Ranking functions like ROW_NUMBER() are most useful when you want to assign a unique sequence to each row within a group, such as finding the first or latest transaction for each employee. RANK() is helpful when you need to handle ties gracefully, like ranking employees by total sales where some might have equal sales amounts. Value functions like LAG() are ideal for trend analysis, such as comparing a sale to the previous one to detect increases or decreases. Aggregate window functions (like SUM() OVER(), not shown above) are powerful for running totals or moving averages, giving you insights into cumulative performance over time. Choosing the right window function type depends on whether you need to rank, compare, or aggregate data within your result set.

Var allt tydligt?

Tack för dina kommentarer!

Avsnitt 3. Kapitel 3

Fråga AI

Fråga vad du vill eller prova någon av de föreslagna frågorna för att starta vårt samtal

Avsnitt 3. Kapitel 3

Types of Window Functions

1. What is the difference between ROW_NUMBER() and RANK()?

2. When would you use `LAG()` in a query?

3. Can you combine multiple window functions in one query?

Types of Window Functions

1. What is the difference between ROW_NUMBER() and RANK()?

2. When would you use LAG() in a query?

3. Can you combine multiple window functions in one query?

2. When would you use `LAG()` in a query?