SQL window functions can perform efficient calculations without reducing the number of rows. It performs operations such as ranking, summing up, grouping statistics on data through the window defined by OVER(). Common functions include: 1. ROW_NUMBER(), RANK(), and DENSE_RANK() for ranking, the difference is repeated value processing; 2. Aggregation functions such as SUM() and AVG() implement rolling statistics; 3. Use PARTITION BY to group by dimension, ORDER BY controls sorting, and frame range controls window size. Mastering window functions can effectively replace complex subqueries and improve query efficiency and readability.

SQL window functions are not as complicated as they sound, and using them correctly can save a lot of trouble. Many people like to nest subqueries or self-connections when writing queries, but in fact, window functions can solve many problems more elegantly, such as ranking, cumulative summation, grouping statistics, etc.

What is a window function?

Simply put, the window function calculates a set of related rows without changing the original row count . Unlike aggregate functions (such as SUM() or AVG() ) that combine multiple rows into one row, the window function retains each row and adds the result you want to calculate.

Common scenarios such as:

• Rank sales of each salesperson in their respective areas
• Calculate the average amount of each order in its customer history order
• Get monthly sales compared to last month

The basic syntax is like this:

 function_name (expression) OVER (
    [PARTITION BY partition_expression]
    [ORDER BY sort_expression]
    [frame_clause]
)

You can understand it as "what grouping first, then sorting by, and then doing calculations in this 'window'".

Common window functions and usage methods

Here are some of the most commonly used window functions and their typical uses:

ROW_NUMBER() , RANK() , DENSE_RANK()

These functions are used to generate rankings. The difference is how to deal with the same value:

• ROW_NUMBER() : Whether there are duplicate values ??or not, they are arranged with different numbers.
• RANK() : There are parallel rankings, and the number will be skipped later
• DENSE_RANK() : There are parallel rankings, but no number will be skipped

For example, suppose you are looking up sales data:

 SELECT 
    Salesperson,
    region,
    amount,
    ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS row_num,
    RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rank,
    DENSE_RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS density_rank
FROM sales;

You will find that the differences in the results of different ranking functions are mainly reflected in the juxtaposition.

Aggregated window functions such as SUM() , AVG() , MIN() , MAX() etc.

This type of function can do rolling statistics, such as the monthly cumulative sales:

 SELECT
    month,
    sales_amount,
    SUM(sales_amount) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sales
FROM monthly_sales;

Here we use ROWS BETWEEN ... to control the window range, which means "from the first line to the current line". You can also change to only look at the first 3 lines or the first and last few lines and adjust according to your needs.

A few tips for using window functions

1. Don't forget PARTITION BY If you don't add PARTITION BY , then the entire table will be treated as a big window. If you want to count separately by region, department, user, etc., remember to add it.

2. ORDER BY is very important Especially when ranking or accumulation is needed, ORDER BY determines the order of window insiders. If not specified, some functions may return unpredictable results.

3. Frame Clause allows flexible control of window size For example, if you want to calculate the average sales volume in the past 7 days, you can use something like:

    AVG(sales) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

4. Avoid misuse of GROUP BY and window functions Sometimes you will see someone using GROUP BY while using window functions. This is OK, but you should pay attention to understanding the role of each step. GROUP BY is an aggregation, while window functions are an enhanced version of "non-aggregation".

---

Basically that's it. The window function may be a bit tangled at first, but once you get it, you will find it much clearer than a bunch of subqueries. When you first start practicing, you can start with simple rankings and accumulations, and slowly try complex frame settings. After you become proficient, you will be much easier to write complex reports.

The above is the detailed content of SQL window functions tutorial. For more information, please follow other related articles on the PHP Chinese website!