SQLite Release 3.25.0 adds support for window functions

#1

Post by MarkusWinand » Wed, Aug 15, 2018, 7:46 AM UTC

SQLite Release 3.25.0 adds support for window functions
sqlite.org

Re: SQLite Release 3.25.0 adds support for window functions

#2

Post by Dinux » Wed, Aug 15, 2018, 8:36 AM UTC

Not sure if SQLite is still a 'lite' database

Re: SQLite Release 3.25.0 adds support for window functions

#3

Post by felixge » Wed, Aug 15, 2018, 8:43 AM UTC

Dinux wrote: post #2

Not sure if SQLite is still a 'lite' database

In the category of SQL databases, it certainly is. I mean it's not called LiteDB for a reason I suppose ;).

Re: SQLite Release 3.25.0 adds support for window functions

#4

Post by MarkusWinand » Wed, Aug 15, 2018, 8:46 AM UTC

Dinux wrote: post #2

Not sure if SQLite is still a 'lite' database

I've just built the snapshot. The binary is still less than 2MB.

And it works:

  sqlite> with t(x) as (values (1), (2)) select sum(x) over (order by x) from t;
  1
  3

Re: SQLite Release 3.25.0 adds support for window functions

#5

Post by nanimo » Wed, Aug 15, 2018, 8:47 AM UTC

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.

Re: SQLite Release 3.25.0 adds support for window functions

#6

Post by silvestrov » Wed, Aug 15, 2018, 8:52 AM UTC

I’d really really like if they improved “alter table” to include dropping/renaming columns/constraints, even if it required rewriting the whole table.

Re: SQLite Release 3.25.0 adds support for window functions

#7

Post by unixhero » Wed, Aug 15, 2018, 8:53 AM UTC

MarkusWinand wrote: post #4

Dinux wrote: post #2

Not sure if SQLite is still a 'lite' database

I've just built the snapshot. The binary is still less than 2MB. And it works: sqlite> with t(x) as (values (1), (2)) select sum(x) over (order by x) from t; 1 3

Insightful. Thanks!

Re: SQLite Release 3.25.0 adds support for window functions

#8

Post by usgroup » Wed, Aug 15, 2018, 8:57 AM UTC

SQLite guys, please add FDW support ala Postgres and easy foreign function support for Python and R, and you’ll corner most of analytics and data science.

Re: SQLite Release 3.25.0 adds support for window functions

#9

Post by gaius » Wed, Aug 15, 2018, 9:03 AM UTC

usgroup wrote: post #8

SQLite guys, please add FDW support ala Postgres and easy foreign function support for Python and R, and you’ll corner most of analytics and data science.

I’d argue what they would need for that is tighter integration with Pandas (Python) and data.table (R) but that would be nice

Clever support for multilevel indexes would be top of my wishlist

Re: SQLite Release 3.25.0 adds support for window functions

#10

Post by davidgould » Wed, Aug 15, 2018, 9:19 AM UTC

MarkusWinand wrote: post #4

Dinux wrote: post #2

Not sure if SQLite is still a 'lite' database

I've just built the snapshot. The binary is still less than 2MB. And it works: sqlite> with t(x) as (values (1), (2)) select sum(x) over (order by x) from t; 1 3

I just checked a stripped copy of postgresql-10 built with all options on and it is 7MB. That could be reduced a little by leaving out language support and ssl support etc.