Function pipelines: Building functional programming into PostgreSQL(blog.timescale.com)

    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;

    SELECT device_id, 
        reduce(
           zip_with(
               array_sort(zip(array_agg(ts), array_agg(val))), 
               slice(array_sort(zip(array_agg(ts), array_agg(val))),2, 5000), 
           (a,b) -> abs(a.field1 - b.field1))
        , 0, (s, x) -> s + COALESCE(x,0), s -> s) as res
    FROM (
        VALUES 
        (1, 2, 1),
        (1, 3, 0),
        (1, 9, 3),
        (1, 3, 4),
        (2, 2, 1),
        (2, 8, 0),
        (3, 4, 3)
    ) AS t(device_id, val, ts)

    device_id res
    1        14
    3        0
    2        6

  SELECT device_id, timevector(ts, val) -> sort() -> delta() -> abs() -> sum() as volatility