Groups in C API

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Groups in C API

Stephan Buchert-2
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Groups in C API

Cezary H. Noweta
Hello,

On 2018-01-29 18:08, Stephan Buchert wrote:
> 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

SQLite manages an area of memory called ``aggregate context''. When you
call ``sqlite3_aggregate_context()'' from your ``xStep'' and/or
``xFinal'' then you will receive a pointer to that memory area, which is
kept as long as an invocation of ``xStep''/``xFinal'' considers the same
group of GROUP BY:

typedef struct myAggCtx {
   int notTheFirstTime;
   /* Other state variables follow */
} myAggCtx;

xStep(ctx, ...)
{
   myAggCtx *actx = sqlite3_aggregate_context(ctx, sizeof(myAggCtx));
   if ( 0 == actx->notTheFirstTime ) {
     /* Do your starting of group stuff */
     actx->notTheFirstTime = !0;
   }
   /* ... */
}

xFinal(ctx, ...)
   myAggCtx *actx = sqlite3_aggregate_context(ctx, sizeof(myAggCtx));
   if ( 0 == actx->notTheFirstTime ) {
     /* First time allocated context so there was no records in the group */
   }
   /* ... */
}

Beware of passing the same number of bytes each time, because
``sqlite3_aggregate_context()'' does not reallocates. If an aggregate
context has been allocated previously, then
``sqlite3_aggregate_context()'' will returns the same memory area
regardless of subsequent numbers of bytes passed. I.e.
sqlite3_aggregate_context(ctx, 1) will return 1byte size memory --
subsequent call (while in the same group of GROUP BY)
sqlite3_aggregate_context(ctx, 1000000) will return the same 1byte size
memory.

-- best regards

Cezary H. Noweta
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Groups in C API

Hick Gunter
In reply to this post by Stephan Buchert-2
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:[hidden email]] Im Auftrag von Stephan Buchert
Gesendet: Montag, 29. Jänner 2018 18:09
An: [hidden email]
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
[hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users