On Mon, 04 Mar 2013 16:51:35 -0500 Ryan Johnson <ryan.john...@cs.utoronto.ca> wrote:
> On 04/03/2013 2:20 PM, Petite Abeille wrote: > > On Mar 4, 2013, at 1:32 AM, James K. Lowden > > <jklow...@schemamania.org> wrote: > > > >> What do you have in mind? I've benn adding some user defined > >> functions and am thinking of creating a repository for them. > > All the so-called window functions from SQL:2003 (aka analytic > > functions): > > > > "Windowed Tables and Window Functions in SQL" > > http://wwwlgis.informatik.uni-kl.de/cms/fileadmin/courses/SS2008/NEDM/RDDM.Chapter.06.Windows_and_Query_Functions_in_SQL.pdf > > Postgresql has also had them for a while. Yes, and if I'm not mistaken Ingres's QUEL had them long before that. (I see Microsoft calls some of them e.g.. PERCENTILE_RANK "analytical". Why? "analysis", after all, means "take apart". They might be used for analysis, but window functions are every bit as synthetic as normal aggregate functions.) > I'd love to see sqlite3 gain them, but I don't think they can be > added efficiently as custom functions. Right you are, except it's not a question of efficient but of possible. A UDF works one of two ways: 1. Per row. The engine calls the function for each row. The function transforms the input into the output. Examples are sqrt() and pow(). 2. Aggregation. The engine passes the rows to be aggregated to the function. After the last row, it calls the function's "xFinal" function, which returns a value. Example: median(). In the per-row case, the function must produce its answer based on the (single) value provided. Ranking, for example, would be impossible because the "rows ahead" haven't been seen yet. In the case of aggregation, the calls to xFinal are determined by the GROUP BY clause. > The engine would need to become window-aware, which is > probably enough work to delay its introduction until a consortium > member decides they need it. I would say the same. It changes the grammar and almost certainly intrudes on the GROUP BY implementation. AFAIK it's not on the sqlite4 agenda. Adding nonstandard equivalent functionality by extending aggregate functions might be easier. But the inputs to the OVER clause -- partition, range, order -- would still be required. I'm surprised there's much interest in using SQLite for fancy grouping. My hobbyhorse is recursive queries, which IMO are much more of a nuisance to carry out. --jkl _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
