On Jul 21, 2013, at 10:15 PM, ss griffon <[email protected]> wrote:
> I'm writing an extension to SQLite that adds some aggregate functions.
> Some of them, require that the rows passed to the aggregate function
> be sorted. It seems as if lots of data bases (MySQL, PostgreSQL)
> support an ORDER BY clause in their aggregate functions. Does SQLite
> support anything like this?
Short answer: no.
Longer answer: there is a family of SQL:2003 functions referred as 'window
function' [1] and/or 'analytic function' [2] which work that way:
<function> <OVER_CLAUSE> :: =
OVER ( [ PARTITION BY <expr>, ... ]
[ ORDER BY <expression> ] )
Sadly none of this is supported by SQLite.
The latest issue of NoCOUG (Northern California Oracle Users Group) has a nice
little primer on analytics, around page 21:
"Wielding the Sword of Analytics"
http://www.nocoug.org/Journal/NoCOUG_Journal_201308.pdf
[1] http://en.wikipedia.org/wiki/Select_(SQL)#Window_function
[2] http://www.oracle-base.com/articles/misc/analytic-functions.php
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
