4 ms·

TFA says he's using this to > implement a realtime analytics dashboard for an application with over a billion event rows. I'm interested in seeing how well t

TFA says he's using this to > implement a realtime analytics dashboard for an application with over a billion event rows. I'm interested in seeing how well this could be attained in SQLite with window functions[1] since SQLite doesn't seem to support a CREATE AGGREGATE. There would also need to be some python[2] to provide the PL/SQL logic shown. [1] https://sqlite.org/windowfunctions.html https://sqlite.org/windowfunctions.html [2] https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.create_function https://docs.python.org/3/library/sqlite3.html#sqlite3.Conne...

cryptonector 1y ago
> I'm interested in seeing how well this could be attained in SQLite with window functions[1] since SQLite doesn't seem to support a CREATE AGGREGATE. SQLite3 very much does support user-defined aggregate functions, just not with a CREATE AGGREGATE statement. You have to use one of the `sqlite3_create_function*()` C functions[0][1][2] to create a user-defined aggregate function: The sixth, seventh and eighth parameters passed to the three "sqlite3_create_function*" functions, xFunc, xStep and xFinal, are pointers to C-language functions that implement the SQL function or aggregate. A scalar SQL function requires an implementation of the xFunc callback only; NULL pointers must be passed as the xStep and xFinal parameters. An aggregate SQL function requires an implementation of xStep and xFinal and NULL pointer must be passed for xFunc. To delete an existing SQL function or aggregate, pass NULL pointers for all three function callbacks. The sixth, seventh, eighth and ninth parameters (xStep, xFinal, xValue and xInverse) passed to sqlite3_create_window_function are pointers to C-language callbacks that implement the new function. xStep and xFinal must both be non-NULL. xValue and xInverse may either both be NULL, in which case a regular aggregate function is created, or must both be non-NULL, in which case the new function may be used as either an aggregate or aggregate window function. More details regarding the implementation of aggregate window functions are available here. > There would also need to be some python[2] to provide the PL/SQL logic shown. Ah, so you do know about the `sqlite3_create_function*()` functions. You don't have to use Python, though you can of course. [0] https://sqlite.org/c3ref/create_function.html [1] https://sqlite.org/appfunc.html#the_aggregate_function_callbacks [2] https://sqlite.org/windowfunctions.html#udfwinfunc*
- smitty1e 1y ago
  Hmmm...so a python generator might be just the thing for that aggregate...

cryptonector 1y ago

> I'm interested in seeing how well this could be attained in SQLite with window functions[1] since SQLite doesn't seem to support a CREATE AGGREGATE. SQLite3 very much does support user-defined aggregate functions, just not with a CREATE AGGREGATE statement. You have to use one of the `sqlite3_create_function*()` C functions[0][1][2] to create a user-defined aggregate function: The sixth, seventh and eighth parameters passed to the three "sqlite3_create_function*" functions, xFunc, xStep and xFinal, are pointers to C-language functions that implement the SQL function or aggregate. A scalar SQL function requires an implementation of the xFunc callback only; NULL pointers must be passed as the xStep and xFinal parameters. An aggregate SQL function requires an implementation of xStep and xFinal and NULL pointer must be passed for xFunc. To delete an existing SQL function or aggregate, pass NULL pointers for all three function callbacks. The sixth, seventh, eighth and ninth parameters (xStep, xFinal, xValue and xInverse) passed to sqlite3_create_window_function are pointers to C-language callbacks that implement the new function. xStep and xFinal must both be non-NULL. xValue and xInverse may either both be NULL, in which case a regular aggregate function is created, or must both be non-NULL, in which case the new function may be used as either an aggregate or aggregate window function. More details regarding the implementation of aggregate window functions are available here. > There would also need to be some python[2] to provide the PL/SQL logic shown. Ah, so you do know about the `sqlite3_create_function*()` functions. You don't have to use Python, though you can of course. [0] https://sqlite.org/c3ref/create_function.html [1] https://sqlite.org/appfunc.html#the_aggregate_function_callbacks [2] https://sqlite.org/windowfunctions.html#udfwinfunc*

smitty1e 1y ago
Hmmm...so a python generator might be just the thing for that aggregate...