Support window functions from SQLite 3.25.0

[pawelsalawa]

Details

https://sqlite.org/windowfunctions.html

Implementation notes

• Need to update:
  • syntax grammar file,
  • AST classes,
  • formatter classes,
  • add built-in window functions to list of known functions, so that the syntax assistant is aware of them,
• Result column of window function cannot be linked to a table/rowid, as it is a function (an aggregate function even), so it should be treated as an aggregated function result. Obviously it's not editable either.
• "Window functions may only appear in the result set and in the ORDER BY clause of a SELECT statement" - a quote from SQLite documentation.
• Defining custom window functions will be covered in User-Defined Aggregate Window Functions #3930 as it can be done independently & later (lower priority).

[thomasnield]

What's the status in getting this released? Anything I can do to help? Would love to use this for an O'Reilly Media video I'm shooting in June...

[pawelsalawa]

Unfortunately I don't have time to work on SQLiteStudio nowadays, due to personal reasons.
I'm not abandoning the project, I still develop it and I do small tasks from time to time. I will code more as soon as I have more time.

[pcava]

What a pity, I would be interested in using window functions in SQLiteStudio too...

[thomasnield]

@pawelsalawa totally understand and have done that with my own projects too....

[helix2001]

I've used LAG and LEAD function sucessfully with SQLiteStudio 3.2.1. Replaced the bundled sqlite3.dll with version 3.30 32bits and it works like a charm. The SQL syntax after the OVER clause will appear underlined, as if it were an error, but the results are correct.