SQL's LAG and LEAD functions are used to access row data with specified offsets before and after the current row. 1. LAG(column, offset, default) Gets the value of the offset line before the current line. The default offset is 1. If it does not exist, it returns NULL or specify the default value; 2. LEAD(column, offset, default) Gets the value of the offset line after the current line. The usage is similar to LAG; 3. The basic syntax is to calculate adjacent records by sorting or grouping in combination with OVER clauses, such as viewing sales of the previous month and the next month by date; 4. Group calculations can be implemented through PARTITION BY, such as analyzing trends by region and product classification; 5. Trend changes can be judged in conjunction with CASE WHEN, such as sales growth, decline or flatness. It is also recommended to use CTE to avoid repeated calculations to optimize performance. Mastering these key points can be effectively used in scenarios such as time series analysis, month-on-month comparison and abnormal detection.

SQL's LAG and LEAD functions are window functions that access a row of data before or after the current row. They are particularly useful when analyzing time series, trend changes, or comparing adjacent records. Below are some common usage scenarios and examples to help you quickly understand the actual usage of these two functions.

1.What are LAG and LEAD?

• LAG(column, offset, default) : Get the value of offset line up (first) of the current line, the default is 1.
• LEAD(column, offset, default) : Get the value of offset line down (after) of the current line, the default is 1.

To give a simple example:
Suppose you have a sales record table, sorted by month. If you want to know the comparison between "this month's sales" and "last month", you can use LAG() ; if you want to see "next month", you can use LEAD() .

2. Basic syntax structure

 SELECT 
    date,
    Sales,
    LAG(sales, 1) OVER (ORDER BY date) AS prev_sales,
    LEAD(sales, 1) OVER (ORDER BY date) AS next_sales
FROM sales_data;

In the example above:

• After sorting by date , each row can see sales for the previous month and the next month.
• If there is no corresponding value (such as the first line does not have the previous one), the default return NULL.

You can specify default values as needed, for example:

 LAG(sales, 1, 0) OVER (ORDER BY date)

This way, when the previous line does not exist, it will return 0 instead of NULL.

3. Use LAG/LEAD in grouping

In actual business, different categories are often processed separately, such as viewing trends by region and product classification.

 SELECT 
    region,
    date,
    Sales,
    LAG(sales, 1, 0) OVER (PARTITION BY region ORDER BY date) AS prev_sales
FROM regional_sales;

The key point here is to add PARTITION BY region , indicating that the value of the previous row is calculated separately within each region.

Common application scenarios include:

• Daily sales comparison of different stores
• Time difference between users
• Analysis of price changes of multiple products

4. Trend analysis based on condition judgment

With the data from before and after lines, you can further make logical judgments, such as judging whether sales have increased.

 SELECT 
    date,
    Sales,
    LAG(sales) OVER (ORDER BY date) AS prev_sales,
    CASE 
        WHEN sales > LAG(sales) OVER (ORDER BY date) THEN 'Increase'
        WHEN sales < LAG(sales) OVER (ORDER BY date) THEN &#39;Decrease&#39;
        ELSE &#39;Same&#39;
    END AS trend
FROM sales_data;

This way you can directly see the trend changes every day. Note that since LAG is a window function, it is a bit troublesome to write repeatedly in CASE, but this is standard practice.

Some optimization suggestions:

• If the query is complicated, consider first using CTE or subquery to calculate the LAG value before judging
• Avoid calling the same LAG/LEAD multiple times in the same query, affecting performance

Basically that's it. LAG and LEAD seem simple, but are very practical in actual analysis, especially when you need to do tasks such as month-on-month, trend judgment, and abnormal detection. By mastering the sorting method and partitioning logic, you will be able to flexibly deal with various scenarios.

The above is the detailed content of SQL LAG and LEAD function examples. For more information, please follow other related articles on the PHP Chinese website!