I am curious, how many Haskell programmers do you all have? And more seriously, do you have any plans to help provide this functionality to non-Postgres SQLs, or at least help those trying to take inspiration from it?
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;
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;
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.
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.
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.
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;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.
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...
Agree - what would you need to see in PostgreSQL / TimescaleDB to enable you to switch from Big Query? We are all ears :-)
"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 :-)
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) device_id res
1 14
3 0
2 6