Minor nitpick: Nested subqueries ARE awkward, which is why I would express

    SELECT device id, sum(abs_delta) as volatility
    FROM (
        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 )
    GROUP BY device_id;

this way:

    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  //edit - the remainder of this line is a typo: ) calc_delta
    )
    SELECT device id, sum(abs_delta) as volatility
    FROM temperature_delta_past_day
    GROUP BY device_id;

To me, it is a lot more natural to use SQL's CTE syntax to 'predefine' my projections before making use of them, instead of trying to define them inline - like the difference between when you'd define a lambda directly inline vs defining a separate function for it.

I don't know if trying to embed a DataFrame-esque api inside of SQL is a thing that would benefit me, but it is an interesting idea.

I agree. There is something magic with CTEs, just moving things around a little bit makes it so much easier to comprehend.

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.

CTEs are wonderful for readability, but until recent versions of PostgreSQL they were always "materialized" which can have performance implications vs. subqueries. The NOT MATERIALIZED option to CTEs was added in PostgreSQL 12. (Timescale Engineer)

Came here to say the same thing. Some years back, to pass my CS databases course, we were required to write really complex queries in the computer lab. We had very limited time to do so.

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.

Note that your second example has a misplaced pair of parens:

    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;

should probably be

    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;

You're correct - I was in a hurry to express the idea, and I failed to check the where clause.

I'm also using CTEs a lot precisely because subqueries are hard to reason about, and these pipelines are in turn so much easier too.

(NB: Post author here)

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.

They've been around quite a long time, but yeah it seems like only SQL89 gets taught.

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

(NB: Post author here)

We don't have many Haskell programmers, we mostly work in Rust, C and Go, but we're always open to new things...

This is pretty Postgres specific. From the beginning Postgres has focused on extensibility and allowed this sort of stuff with custom functions/operators/types. Many other SQL variants don't have that. It's one of the main things that sets Postgres apart from other databases, see Stonebreaker's great history of this, specifically the stuff on Object Relational databases [1].

We're pretty focused on building on top of Postgres because of that functionality. We do have some other stuff to make Postgres more scalable, and you're welcome to try us out, but if there's something specific that BigQuery offers that you need feel free to file a github issue around that too. But yeah, no plans to do things like this in other databases, they just don't have the infrastructure...

[1]:https://people.cs.umass.edu/~yanlei/courses/CS691LL-f06/pape...

(TimescaleDB co-founder)

Agree - what would you need to see in PostgreSQL / TimescaleDB to enable you to switch from Big Query? We are all ears :-)

Not the op, but infinite scalability without any infrastructure work required besides loading the data is what holds me on BigQuery

Thanks for sharing.

"infinite scalability" is something we are aiming for in our new vision for Timescale Cloud:

https://blog.timescale.com/blog/announcing-the-new-timescale...

We are not there today, but we are making progress.

Any other feedback - please let us know :-)

I wonder if the array functions in the presto engine (used for example in Amazon Athena) should be able to handle this too?

https://prestodb.io/docs/0.217/functions/array.html

Ok, a 15 minute hack. It may be possible to make it look nicer.

And it looked better in TFA, so good job!

    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)

GROUP BY device_id

    device_id res
    1        14
    3        0
    2        6