6 ms·
https://www.windowfunctions.com https://www.windowfunctions.com is a good introduction to window functions. Besides that, the comprehensive testing and evaluat
by nanimo 8y ago
https://www.windowfunctions.com https://www.windowfunctions.com is a good introduction to window functions.
Besides that, the comprehensive testing and evaluation of SQLite never ceases to amaze me. I'm usually hesitant to call software development "engineering", but SQLite is definitely well-engineered.
- tomnipotent 8y ago> but SQLite is definitely well-engineered. Considering it was written for the U.S. Navy to run software on guided missile destroyers, that makes sense.
- cosmie 8y agoDitto! For anyone who isn't familiar with SQLite's testing procedures, read this[1] fascinating page. The SQLite project has a mind boggling 711 times more test code than SQLite itself has. Put another way, only 0.1% of the project's code is SQLite itself. The other 99.9% consists of tests for that 0.1%. [1] https://www.sqlite.org/testing.html https://www.sqlite.org/testing.html
- OskarS 8y agoIn a similar vein, I rarely (if ever) seen a library that handles dynamic memory allocation more robustly than SQLite. This page is a glory to behold: https://www.sqlite.org/malloc.html https://www.sqlite.org/malloc.html
- cosmie 8y agoOh that is a fun read! I hadn't seen that before.
- clappski 8y agoThat page is inspiring, for a commodity, open source and old software project everything is very clearly defined and the language is very definitive which I hope is indicative of the actual library quality (I’ve never used SQLite).
- mabbo 8y agoWhile in complete agreement with you on how amazing SQLite's engineering practices are, your math is off by an order of magnitude. 1/711 = 0.00140646976 0.00140646976 ~= 0.14%, not 0.01%. I'll go put on my "pedant" hat now.
- cosmie 8y agoThanks for catching that! Although it was more a typo than a math error (as the 99.9% figure was correct). I'll chalk it up to posting when I should be sleeping (4:30am local time).
- sehugg 8y agoGood thing too, since it's in just about everything: https://www.sqlite.org/mostdeployed.html https://www.sqlite.org/mostdeployed.html
- twoquestions 8y agoThis 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 agoTry 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 agoDesign 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 agoMy 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 agoThis is a fantastic book - the name doesn't do it justice.