MySQL `LAG` Function Explanation

Overview

The LAG function in MySQL is a window function that allows you to access data from a previous row within the same result set. It is particularly useful for calculating differences between sequential rows, such as in time series analysis.

SQL Query Explanation

SELECT ROUND(AVG(daily_increase), 4) AS avg_daily_increase
FROM (
    SELECT dt1, (price - LAG(price) OVER (ORDER BY dt1)) AS daily_increase
    FROM binance.klines_1d
    WHERE dt1 >= CURDATE() - INTERVAL %d DAY
) AS price_changes
WHERE daily_increase IS NOT NULL;

1. Inner Query

Purpose: To calculate the daily price increase by comparing the current day's price (price) with the previous day's price.
LAG(price) OVER (ORDER BY dt1):
- The LAG function retrieves the value of the price column from the previous row.
- The ORDER BY dt1 clause ensures that the rows are processed in chronological order based on the dt1 (date) column.
price - LAG(price) OVER (ORDER BY dt1):
- This calculates the difference between the current day's price and the previous day's price, giving the daily_increase.

2. Filtering Dates

WHERE dt1 >= CURDATE() - INTERVAL %d DAY:
- This clause filters the rows to include only those within the last %d days from the current date.

3. Outer Query

Purpose: To calculate the average of the daily price increases.
ROUND(AVG(daily_increase), 4):
- This calculates the average (AVG) of the daily increases and rounds the result to four decimal places.
WHERE daily_increase IS NOT NULL:
- This excludes any rows where daily_increase is NULL to ensure that the average is calculated only on valid values.

How LAG Works

The full signature is LAG(expr [, offset [, default]]) OVER (window_spec), available in MySQL 8.0 and later. offset defaults to 1 (the immediately preceding row) and default is returned when no such row exists; without it the result is NULL. The window specification decides what "previous" means: ORDER BY sets the sequence, and an optional PARTITION BY restarts the sequence for each group, so the first row of every partition gets the default. Its mirror image, LEAD, reads following rows instead. Unlike a self-join on date - 1, LAG does not care about gaps in the dates: it simply returns whatever row precedes the current one in sort order.

Logical Query Order

Understanding evaluation order explains the subquery in the original example. The inner query computes daily_increase for each row; the outer query can then filter out the NULL from the first row and aggregate. Note that the WHERE dt1 >= ... filter runs before LAG, so the first day inside the date range has no predecessor even though older data exists in the table. If you want that first day's change too, compute LAG over a slightly wider range and filter afterwards.

More Examples

-- Per-symbol day-over-day change and percentage change
SELECT symbol,
       dt1,
       price,
       price - LAG(price) OVER w                     AS change_abs,
       ROUND(100 * (price / LAG(price) OVER w - 1), 2) AS change_pct
FROM   binance.klines_1d
WINDOW w AS (PARTITION BY symbol ORDER BY dt1);

-- Compare with the value 7 rows earlier, defaulting to the current price
SELECT dt1, price,
       price - LAG(price, 7, price) OVER (ORDER BY dt1) AS week_change
FROM   binance.klines_1d;

-- Detect gaps in a daily series
SELECT dt1,
       DATEDIFF(dt1, LAG(dt1) OVER (ORDER BY dt1)) AS days_since_prev
FROM   binance.klines_1d
HAVING days_since_prev > 1;

The named WINDOW clause avoids repeating the same specification. The gap-detection query uses MySQL's permission to reference a select alias in HAVING; in portable SQL wrap it in a CTE instead. The original example's %d is a placeholder filled in by application code (a Python format string); prefer bound parameters such as INTERVAL %s DAY with a driver argument to avoid SQL injection.

LAG is one of several value window functions. FIRST_VALUE and LAST_VALUE return the boundary rows of the window frame, NTH_VALUE returns an arbitrary position, and ranking functions such as ROW_NUMBER and RANK number the rows. Combining LAG with a running SUM() OVER is a common way to build sessionisation logic: flag a row as a new session when the gap since the previous event exceeds a threshold, then take the cumulative sum of the flags as a session id.

LAG vs Alternatives

Approach	Works on	Notes
LAG / LEAD	MySQL 8.0+, PostgreSQL, SQL Server, Snowflake	single pass over sorted rows; clearest
Self-join on previous key	all versions	needs a contiguous key; gaps break it
Correlated subquery (MAX(dt) < current)	all versions	often O(n²) without good indexes
User variables (@prev)	MySQL 5.x	evaluation order not guaranteed; deprecated pattern
pandas Series.shift(1)	Python	same idea once data is in memory

Pitfalls

Missing ORDER BY in OVER. Without it the row order is undefined and LAG returns arbitrary neighbours.
Ties. If two rows share the same dt1, their relative order is not deterministic; add a tiebreaker column.
Averaging differences. The mean of daily differences equals (last - first) / (n - 1), so it depends only on the endpoints; for growth rates average percentage changes or use a geometric mean.
Performance. An index on (symbol, dt1) lets MySQL read rows already sorted for the window instead of sorting them.

MySQL LAG Function Explanation