10 ms·

This looks great, but I couldn't get through the first question on aggregate functions. Are there any SQL books/tutorials that go over things like this? A lot

This looks great, but I couldn't get through the first question on aggregate functions. Are there any SQL books/tutorials that go over things like this? A lot of material I've seen has been like the classic image of "How to draw an owl. First draw two circles, then draw the rest of the owl", where they tell you the super basic stuff, then assume you know everything.

emmelaich 8y ago
Try Joe Celko's book "SQL for Smarties" or ... https://www.red-gate.com/simple-talk/sql/t-sql-programming/window-functions-in-sql/ https://www.red-gate.com/simple-talk/sql/t-sql-programming/w...
blattimwind 8y ago
Design queries iteratively. Having an understanding of relational algebra (not the symbols, but the concept; math is always about the concept) generally helps a lot as well; it's the reason why compsci database lectures often start with relational algebra. > We would like to find the total weight of cats grouped by age. But only return those groups with a total weight larger than 12. The total weight of cats grouped by age. SELECT sum(weight), age FROM cats GROUP BY age But only return those groups with a total weight larger than 12. SELECT sum(weight), age FROM cats GROUP BY age HAVING sum(weight) > 12 Ordered by age. SELECT sum(weight), age FROM cats GROUP BY age HAVING sum(weight) > 12 ORDER BY age The total weight column should be called total_weight. SELECT sum(weight) AS total_weight, age FROM cats GROUP BY age HAVING sum(weight) > 12 ORDER BY age
ktr 8y ago
My introduction to window functions (and the best write-up I’ve seen) was through the SQL Cookbook (http://shop.oreilly.com/product/9780596009762.do http://shop.oreilly.com/product/9780596009762.do). I highly recommend.
- AlisdairO 8y ago
  This is a fantastic book - the name doesn't do it justice.
AlisdairO 8y ago
(self plug) try pgexercises.com. It's in a very similar vein to windowfunctions - I understand it was one of the original inspirations - but it covers a much wider range of material, with a gradual growth from very basic stuff up to slightly advanced.
- mercer 8y ago
  I'll vouch for this plug! I've spent quite a bit of time learning SQL (and Postgres specific stuff), and this was one of my favorite resources. I can also recommend Learning SQL by Alan Beaulieu, which was recommended in some HN comment. After looking into various books, some of them Postgres specific, I found this one to be the best for learning from scratch.
twoquestions 8y ago
Thank you everyone for your help!
btilly 8y ago
The discussion at https://news.ycombinator.com/item?id=17231349 https://news.ycombinator.com/item?id=17231349 has many more links and descriptions of how to use window functions in practice. (Including my comment at the top about how in practice I find that you usually want to combine window functions with nested queries.)
westurner 8y ago
Ibis uses windowing functions for aggregations if the database supports them. IDK when support for the new SQLite support will be implemented? http://docs.ibis-project.org/sql.html#window-functions http://docs.ibis-project.org/sql.html#window-functions [EDIT] I created an issue for this here: https://github.com/ibis-project/ibis/issues/1597 https://github.com/ibis-project/ibis/issues/1597

emmelaich 8y ago

Try Joe Celko's book "SQL for Smarties" or ... https://www.red-gate.com/simple-talk/sql/t-sql-programming/window-functions-in-sql/ https://www.red-gate.com/simple-talk/sql/t-sql-programming/w...

blattimwind 8y ago

Design queries iteratively. Having an understanding of relational algebra (not the symbols, but the concept; math is always about the concept) generally helps a lot as well; it's the reason why compsci database lectures often start with relational algebra. > We would like to find the total weight of cats grouped by age. But only return those groups with a total weight larger than 12. The total weight of cats grouped by age. SELECT sum(weight), age FROM cats GROUP BY age But only return those groups with a total weight larger than 12. SELECT sum(weight), age FROM cats GROUP BY age HAVING sum(weight) > 12 Ordered by age. SELECT sum(weight), age FROM cats GROUP BY age HAVING sum(weight) > 12 ORDER BY age The total weight column should be called total_weight. SELECT sum(weight) AS total_weight, age FROM cats GROUP BY age HAVING sum(weight) > 12 ORDER BY age

ktr 8y ago

My introduction to window functions (and the best write-up I’ve seen) was through the SQL Cookbook (http://shop.oreilly.com/product/9780596009762.do http://shop.oreilly.com/product/9780596009762.do). I highly recommend.

AlisdairO 8y ago
This is a fantastic book - the name doesn't do it justice.

AlisdairO 8y ago

(self plug) try pgexercises.com. It's in a very similar vein to windowfunctions - I understand it was one of the original inspirations - but it covers a much wider range of material, with a gradual growth from very basic stuff up to slightly advanced.

mercer 8y ago
I'll vouch for this plug! I've spent quite a bit of time learning SQL (and Postgres specific stuff), and this was one of my favorite resources. I can also recommend Learning SQL by Alan Beaulieu, which was recommended in some HN comment. After looking into various books, some of them Postgres specific, I found this one to be the best for learning from scratch.

twoquestions 8y ago

Thank you everyone for your help!

btilly 8y ago

The discussion at https://news.ycombinator.com/item?id=17231349 https://news.ycombinator.com/item?id=17231349 has many more links and descriptions of how to use window functions in practice. (Including my comment at the top about how in practice I find that you usually want to combine window functions with nested queries.)

westurner 8y ago

Ibis uses windowing functions for aggregations if the database supports them. IDK when support for the new SQLite support will be implemented? http://docs.ibis-project.org/sql.html#window-functions http://docs.ibis-project.org/sql.html#window-functions [EDIT] I created an issue for this here: https://github.com/ibis-project/ibis/issues/1597 https://github.com/ibis-project/ibis/issues/1597