Sequential Trend Analysis with Offset Functions

Snippet

Sequential Trend Analysis with Offset Functions

Uses the LAG function to access data from the previous row within the same result set without requiring a self-join. This is essential for calculating deltas or identifying trends in time-series data.

snippet.sql
sql
SELECT 
  reading_time, 
  temperature, 
  LAG(temperature) OVER (ORDER BY reading_time) AS prev_temp,
  temperature - LAG(temperature) OVER (ORDER BY reading_time) AS temp_delta
FROM sensor_readings
WHERE sensor_id = 'TS-101';

Breakdown

LAG(temperature) OVER (ORDER BY reading_time)

Retrieves the temperature value from the immediate preceding row based on the time sequence.

temperature - LAG(...)

Calculates the numeric difference between the current and previous reading.

Previous snippet Next snippet

From your library

Sequential Trend Analysis with Offset Functions

Related