You could even move the val and lag(val) to two different columns and do the abs() in the summary.

That way you can query temperature_delta_past_day and see for yourself what it does.

Many people failed because their queries became complex monoliths, hard to debug or optimize when things went wrong.

That's because they limited themselves to SQL-92. We were using Oracle, so there was no reason not to use SQL:1999. I made heavy use of WITH, and it was quite effortless.

    WITH temperature_delta_past_day AS
    (
        SELECT device_id, abs(val - lag(val) OVER (PARTITION BY device_id ORDER BY ts)) as abs_delta 
        FROM measurements
        WHERE ts >= now() - '1 day'::interval) calc_delta
    )
    SELECT device id, sum(abs_delta) as volatility
    FROM temperature_delta_past_day
    GROUP BY device_id;

    WITH temperature_delta_past_day AS
    (
        SELECT device_id, abs(val - lag(val) OVER (PARTITION BY device_id ORDER BY ts)) as abs_delta 
        FROM measurements
        WHERE ts >= now() - '1 day'::interval
    )
    SELECT device id, sum(abs_delta) as volatility
    FROM temperature_delta_past_day
    GROUP BY device_id;

Yeah. CTEs definitely make it a bit easier to read, though some people get more confused by them, especially because they don't exist in all SQL variants.

And totally agree with that last bit! We want to see if it's useful for folks, it's released experimentally now and we'll see what folks can do with it. One thing that's fun and that we may do a post explaining a bit more is that these pipelines are actually values as well, so the transforms that you run can be stored in a column in the database as well.

And that starts offering some really mind-bending stuff. The example I used was building on the one in the post except now you have thermocouples with different calibration curves. You can actually store a polynomial or other calibration curve in a column and apply the correct calibration to each individual thermocouple with a JOIN...which is kinda crazy, but pretty awesome. So we want to figure out how to use these and what people can do with them and see where it takes us.

Window Functions and CTEs are both major force multipliers in the language, so I always encourage folks to go learn them.