5 ms·
Minor nitpick: Nested subqueries ARE awkward, which is why I would express SELECT device id, sum(abs_delta) as volatility FROM ( SELECT device_
by jpitz 5y ago
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.
- fifilura 5y agoI 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.
- djk447 5y ago(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.
- jpitz 5y agoThey'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.
- EE84M3i 5y agoNote 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;
- jpitz 5y agoYou're correct - I was in a hurry to express the idea, and I failed to check the where clause.
- nextos 5y agoCame 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.
- pmontra 5y agoI'm also using CTEs a lot precisely because subqueries are hard to reason about, and these pipelines are in turn so much easier too.
- jpruittsql 5y agoCTEs 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)