sql / expert
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
1
2
3
4
5
6
7
SELECTreading_time,temperature,LAG(temperature) OVER (ORDER BY reading_time) AS prev_temp,temperature - LAG(temperature) OVER (ORDER BY reading_time) AS temp_deltaFROM sensor_readingsWHERE sensor_id = 'TS-101';
Breakdown
1
LAG(temperature) OVER (ORDER BY reading_time)
Retrieves the temperature value from the immediate preceding row based on the time sequence.
2
temperature - LAG(...)
Calculates the numeric difference between the current and previous reading.