Hacker News Reader

Comment by nanimo | Hacker News Reader

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.

View on HN

Ditto! 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

In 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

Oh that is a fun read! I hadn't seen that before.

That 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).

While 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.

Thanks 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).

Good thing too, since it's in just about everything: https://www.sqlite.org/mostdeployed.html

I find it fun to look around whatever room/bus/park that I'm in and try to count the instances of Sqlite.

Is it like curl in that regard?

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.

Try Joe Celko's book "SQL for Smarties" or ...

https://www.red-gate.com/simple-talk/sql/t-sql-programming/w...

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

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). I highly recommend.

This is a fantastic book - the name doesn't do it justice.

(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.

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.

Thank you everyone for your help!

The discussion at 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.)

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

[EDIT]

I created an issue for this here: https://github.com/ibis-project/ibis/issues/1597

> 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.

Reply on news.ycombinator.com