Title: include sqlite-3.25+ (with window functions)
Type: behavior Stage:
Components: Versions:
Status: open Resolution:
Dependencies: Superseder:
Assigned To: Nosy List: Big Stone, ghaering, ronaldoussoren
Priority: normal Keywords:

Created on 2018-10-06 17:29 by Big Stone, last changed 2018-10-09 19:43 by ronaldoussoren.

Messages (4)
msg327253 - (view) Author: Big Stone (Big Stone) Date: 2018-10-06 17:29
sqlite-3.25 now includes window functions, something very important in modern SQL.

Could it be included in next Python maintenance release ?
msg327387 - (view) Author: Ronald Oussoren (ronaldoussoren) * (Python committer) Date: 2018-10-09 09:40
To completely do this requires two things:

1) Upgrade SQLite included in a number of binary distributions

2) Update the sqlite extension to allow creating custom window functions in Python

The latter is definitely something that cannot be done in a maintenance release. The former is up to the release managers.
msg327429 - (view) Author: Big Stone (Big Stone) Date: 2018-10-09 18:19
hum. On Windows at least, just drop-in replace the sqlite3.dll per the new sqlite-3.25 dll and the window functions just works.
==> I don't see the need for your latter point, at least on Windows.

checked with sqlite_bro on Python-3.6.7rc1 with example:
CREATE TABLE t2(a, b);
INSERT INTO t2 VALUES('a', 'one'), 
                     ('a', 'two'), 
                     ('a', 'three'), 
                     ('b', 'four'), 
                     ('c', 'five'), 
                     ('c', 'six');
SELECT a                        AS a,
       row_number() OVER win    AS row_number,
       rank() OVER win          AS rank,
       dense_rank() OVER win    AS dense_rank,
       percent_rank() OVER win  AS percent_rank,
       cume_dist() OVER win     AS cume_dist
msg327433 - (view) Author: Ronald Oussoren (ronaldoussoren) * (Python committer) Date: 2018-10-09 19:43
My latter points makes it possible to define custom windowing functions in Python, similar to how it is already possible to define other SQLite functions in Python.
Date User Action Args
2018-10-09 19:43:04ronaldoussorensetmessages: + msg327433
2018-10-09 18:19:14Big Stonesetmessages: + msg327429
2018-10-09 09:40:41ronaldoussorensetnosy: + ghaering, ronaldoussoren
type: behavior
messages: + msg327387
2018-10-06 17:29:26Big Stonecreate