Re: [sqlite] [EXTERNAL] Groups in C API
The feature in last paragraph is already the case with plain ORDER BY. All records in "group" order. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Stephan Buchert Gesendet: Dienstag, 30. Jänner 2018 12:17 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] [sqlite] Groups in C API Thanks for the replies. Allowing non-aggregate columns in aggregate queries is very useful, as shown with the min/max functions. Probably with this feature comes that SQLite even allows all non-aggregate columns in SELECTs with GROUP BY. Perhaps the documentation should warn more clearly, that in this case only one arbitrary row in each group is returned, not all the rows that the WHERE filter lets through. More useful would perhaps be, to return in this case (only non-aggregate columns but a GROUP BY) all rows, just grouped together is indicated by the GROUP BY. This would have a similar effect as an ORDER BY, but they are somewhat different if I look at the syntax diagrams. I have no idea how feasible it would be to get SQLite doing this. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Groups in C API
SQLite is special WRT to non-aggregate columns in aggregate queries. Most other engines will not allow columns that are neither group fields nor aggregated fields. SQLite OTOH does, and it promises that these fields are "related" to the group tuple. Consider: CREATE TABLE letters (type integer, letter text); INSERT INTO letters VALUES (1,'a'), (1,'e'), (1,'i'), (1,'o'), (1, 'u'); INSERT INTO letters VALUES (2,'b'), (2,'d'), (2,'f'), (2,'h'), (2, 'k'), (2,'l'),(2,'t'); INSERT INTO letters VALUES (3,'g'), (3,'j'), (3,'p), (3,'q'), (3,'y'); SELECT type,letter FROM letters GROUP BY type; This will return three rows, with one "random" (determined by the inner workings) letter from each type. SELECT type,MIN(letter) FROM lettters GROUP BY type; This will return the "smallest" letter for each group. Both are aggregate queries, even if the first one does not contain an aggregate expression. Adding more columns to both the table and the column list will have the additional values taken from the same row; in the case of exactly one MIN() or MAX() expression, from one "random" row where the column value matches the value returned for that group. Re user defined group function: SELECT a,b,c,MIN(d),my_func(a,b,c) FROM table GROUP BY 1,2,3; Even though this will give you access to the current values of the group fields, it does not allow you to examine the current row (unless you requery it from inside the xStep method, or you pass along a "magic" value (e.g. reference to in-memory image of the current row from a virtual table). -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Stephan Buchert Gesendet: Montag, 29. Jänner 2018 18:09 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] [sqlite] Groups in C API Ok, I should have tested this before asking. I had assumed that "If the SELECT statement is *a non-aggregate query*, then each expression in the result expression list is evaluated for each row in the dataset filtered by the WHERE clause" on https://www.sqlite.org/lang_select.html#resultset means, that, if it is a non-aggregate query, I would get every row regardless whether there is a GROUP BY or not. But actually the description after *Side note: Bare columns in an aggregate queries.* ... applies also to non-aggregate queries: I get exactly one arbitrary row in each group. But then I have a related question: to get my hands on each row in SELECTs with GROUP BY I could write an aggregate extension function. How do I see there, when a group ends and a new one starts? I.e. How do I implement the xStep and xFinal C functions as requested at https://sqlite.org/c3ref/create_function.html The built in SQL avg function returns the average in each group. How would I implement say variance, skewness, ... functions that do the same? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Groups in C API
What do you mean with groups ending and starting? The GROUP BY clause has the
effect of returning one record per group (i.e. distinct tuple of the group
expression), with the accumulated values belonging to that group. Each result
record is therefore in a separate group.
SQLite will (except for special cases, where the GROUP BY and ORDER BY clauses
can be satisfied by an index on the input rows) need to read ALL of the input
rows before it can output even the first result row.
-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von Stephan Buchert
Gesendet: Montag, 29. Jänner 2018 16:19
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Groups in C API
When processing SELECT ... statements having a GROUP BY clause, i.e.
SELECT ... GROUP BY ...;
in C, i.e. with a loop like
rc=sqlite3_step(stmt);
while rc==SQLITE_ROW {
...
rc=sqlite3_step(stmt);
}
is there a way to know when a group ends and the next starts? I have this of
course if the group by expression is also one of the result columns, but this
is not always necessarily the case.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
Gunter Hick | Software Engineer | Scientific Games International GmbH |
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43
1 80100 - 0
May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
