Re: [sqlite] ordinary CTE containing sum()

[Doug Currie]

Thank you, Richard. It works for me now.

e


On Mon, Feb 9, 2015 at 1:30 PM, Richard Hipp  wrote:

> On 2/7/15, Doug Currie  wrote:
> > In response to this SO question:
> >
> >
> http://stackoverflow.com/questions/28377210/how-to-retrieve-rank-based-on-total-mark-in-sqlite-table
> >
> > I tried to formulate a query without temp tables using an ordinary CTE,
> but
> > received an error "misuse of aggregate: sum()".
> >
>
> Possibly fixed on trunk now.  Please test and confirm.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ordinary CTE containing sum()

[Richard Hipp]

On 2/7/15, Doug Currie  wrote:
> In response to this SO question:
>
> http://stackoverflow.com/questions/28377210/how-to-retrieve-rank-based-on-total-mark-in-sqlite-table
>
> I tried to formulate a query without temp tables using an ordinary CTE, but
> received an error "misuse of aggregate: sum()".
>

Possibly fixed on trunk now.  Please test and confirm.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ordinary CTE containing sum()

[Doug Currie]

For those interested in the initial "misuse of aggregate" issue of this
thread, there is now a ticket:

http://www.sqlite.org/src/tktview?name=2f7170d73b

e


On Mon, Feb 9, 2015 at 9:19 AM, Keith Medcalf <kmedc...@dessus.com> wrote:

>
> Not exactly since aggregates are implemented as functions.
>
> In the case of sum(a + b + c) you have the overhead of one aggregate setup
> call, one call per row (after the three additions are done) and one
> finalizer call to retrieve the aggregate and release the context.
>
> In the case of sum(a) + sum(b) + sum(c) you have three initializer calls
> being made to set up three different aggregate contexts.  Then on each row
> you call the increment function three times for three different contexts,
> then after the aggregate is complete you make three calls to finalize the
> three aggregates and release their contexts, then add up the sum.
>
> The number of additions is the same, but the latter (multiplicity of
> aggregate contexts) adds significantly to the size of the code path.
>
> This may be on the order of only a couple thousand instructions per row,
> but it is a couple *more* thousands of instructions per row than the former
> sum(a + b + c) case.
>
> This will not be significant where you are dealing with 10 rows, but when
> you have thousands or millions of rows it is quite significant.  It will
> also use more energy and concomitantly increase the temperature of the CPU,
> thus contributing to global warming.
>
> ---
> Theory is when you know everything but nothing works.  Practice is when
> everything works but no one knows why.  Sometimes theory and practice are
> combined:  nothing works and no one knows why.
>
> >-Original Message-
> >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> >boun...@sqlite.org] On Behalf Of R.Smith
> >Sent: Monday, 9 February, 2015 04:51
> >To: sqlite-users@sqlite.org
> >Subject: Re: [sqlite] ordinary CTE containing sum()
> >
> >
> >On 2/8/2015 10:23 PM, James K. Lowden wrote:
> >>
> >> I have a couple of efficiency questions for those who know:
> >>
> >> 1.  Is the left-join on a CTE apt to be more effecient than the version
> >> that uses a correlated subquery in the SELECT clause?
> >
> >I'm guessing it matters in some DBs but from testing it seems much the
> >same in SQLite.
> >
> >>
> >> 2.  Is there any performance difference between
> >>
> >>  sum(ca1 +ca2 + exam)
> >> and
> >>  sum(ca1) + sum(ca2) + sum(exam)
> >>
> >> I would expect the left join is faster than a correlated subquery, and
> >> that fewer aggregates is better than more.
> >
> >Now this is easy to check but the answer is simple too - I know it looks
> >in SQL terms like something more complicated is taking place, but in
> >reality it's all the same, consider that it is just like asking which of
> >these are faster:
> >
> >(1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9)
> >--  OR --
> >(1 + 2 + 3) + (4 + 5 + 6) + (7 + 8 + 9)
> >
> >Count the plusses, they are the same. The difference to the
> >processor/engine (ultimately) of your two queries are merely order of
> >adding, but no difference to addition operations or amount of function
> >calls. (Unless "adding" by itself is a significantly different/slower
> >operation when done inside the aggregate function than outside it, but
> >that would fit somewhere between devious and insane).
> >
> >IF you could somehow get rid of the loop or change the compound
> >iteration count it might have a viable effect, but that is not the case
> >here.
> >
> >
> >___
> >sqlite-users mailing list
> >sqlite-users@sqlite.org
> >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ordinary CTE containing sum()

[Keith Medcalf]

Not exactly since aggregates are implemented as functions.  

In the case of sum(a + b + c) you have the overhead of one aggregate setup 
call, one call per row (after the three additions are done) and one finalizer 
call to retrieve the aggregate and release the context.

In the case of sum(a) + sum(b) + sum(c) you have three initializer calls being 
made to set up three different aggregate contexts.  Then on each row you call 
the increment function three times for three different contexts, then after the 
aggregate is complete you make three calls to finalize the three aggregates and 
release their contexts, then add up the sum.

The number of additions is the same, but the latter (multiplicity of aggregate 
contexts) adds significantly to the size of the code path.

This may be on the order of only a couple thousand instructions per row, but it 
is a couple *more* thousands of instructions per row than the former sum(a + b 
+ c) case.

This will not be significant where you are dealing with 10 rows, but when you 
have thousands or millions of rows it is quite significant.  It will also use 
more energy and concomitantly increase the temperature of the CPU, thus 
contributing to global warming.

---
Theory is when you know everything but nothing works.  Practice is when 
everything works but no one knows why.  Sometimes theory and practice are 
combined:  nothing works and no one knows why.

>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of R.Smith
>Sent: Monday, 9 February, 2015 04:51
>To: sqlite-users@sqlite.org
>Subject: Re: [sqlite] ordinary CTE containing sum()
>
>
>On 2/8/2015 10:23 PM, James K. Lowden wrote:
>>
>> I have a couple of efficiency questions for those who know:
>>
>> 1.  Is the left-join on a CTE apt to be more effecient than the version
>> that uses a correlated subquery in the SELECT clause?
>
>I'm guessing it matters in some DBs but from testing it seems much the
>same in SQLite.
>
>>
>> 2.  Is there any performance difference between
>>
>>  sum(ca1 +ca2 + exam)
>> and
>>  sum(ca1) + sum(ca2) + sum(exam)
>>
>> I would expect the left join is faster than a correlated subquery, and
>> that fewer aggregates is better than more.
>
>Now this is easy to check but the answer is simple too - I know it looks
>in SQL terms like something more complicated is taking place, but in
>reality it's all the same, consider that it is just like asking which of
>these are faster:
>
>(1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9)
>--  OR --
>(1 + 2 + 3) + (4 + 5 + 6) + (7 + 8 + 9)
>
>Count the plusses, they are the same. The difference to the
>processor/engine (ultimately) of your two queries are merely order of
>adding, but no difference to addition operations or amount of function
>calls. (Unless "adding" by itself is a significantly different/slower
>operation when done inside the aggregate function than outside it, but
>that would fit somewhere between devious and insane).
>
>IF you could somehow get rid of the loop or change the compound
>iteration count it might have a viable effect, but that is not the case
>here.
>
>
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ordinary CTE containing sum()

[R.Smith]

On 2/8/2015 10:23 PM, James K. Lowden wrote:


I have a couple of efficiency questions for those who know:

1.  Is the left-join on a CTE apt to be more effecient than the version
that uses a correlated subquery in the SELECT clause?


I'm guessing it matters in some DBs but from testing it seems much the 
same in SQLite.




2.  Is there any performance difference between

sum(ca1 +ca2 + exam)
and
sum(ca1) + sum(ca2) + sum(exam)

I would expect the left join is faster than a correlated subquery, and
that fewer aggregates is better than more.


Now this is easy to check but the answer is simple too - I know it looks 
in SQL terms like something more complicated is taking place, but in 
reality it's all the same, consider that it is just like asking which of 
these are faster:


(1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9)
--  OR --
(1 + 2 + 3) + (4 + 5 + 6) + (7 + 8 + 9)

Count the plusses, they are the same. The difference to the 
processor/engine (ultimately) of your two queries are merely order of 
adding, but no difference to addition operations or amount of function 
calls. (Unless "adding" by itself is a significantly different/slower 
operation when done inside the aggregate function than outside it, but 
that would fit somewhere between devious and insane).


IF you could somehow get rid of the loop or change the compound 
iteration count it might have a viable effect, but that is not the case 
here.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ordinary CTE containing sum()

[Clemens Ladisch]

James K. Lowden wrote:
> 1.  Last I checked, SELECT in a column position in the SELECT clause as
> in
>
>   select foo (select ...)
>
> is not permitted by the SQL standard.

This example indeed is not valid SQL syntax.

However, SELECT in a column position is allowed:

select (select 42);

This is called a scalar subquery, and it's allowed in place of any
expression.  (The SQL standard requires it to return exactly one row.)

> 1.  Is the left-join on a CTE apt to be more effecient than the version
> that uses a correlated subquery in the SELECT clause?

Some databases' query optimizers can do a better job with joins than
with subqueries.  In SQLite, left joins and correlated subqueries
are executed in exactly the same way.

> 2.  Is there any performance difference between
>
>   sum(ca1 +ca2 + exam)
> and
>   sum(ca1) + sum(ca2) + sum(exam)

The second one will do three aggregations in parallel, but I'd expect
the difference to be too small to be noticeable.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ordinary CTE containing sum()

[James K. Lowden]

On Sun, 8 Feb 2015 09:57:54 -0500
Doug Currie  wrote:

> tonypdmtr  on SO
> posted a CTE solution; it is something like this, which works for me:
> 
> with tt (S_id, total) as
>(select S_id, sum(ca1) + sum(ca2) + sum(exam) as total
>from t group by S_id
>union values (NULL, 0))
> select s.S_id, s.total,
>(select count(*)+1 from tt as r where r.total > s.total) as
> rank from tt as s where S_id is not NULL;
> 
> But my question remains, why is the UNION necessary in the  CTE?
> 
> why doesn't this work? ...
> 
> with tt (S_id, total) as
>(select S_id, sum(ca1) + sum(ca2) + sum(exam) as total
>from t group by S_id)
> select s.S_id, s.total,
>(select count(*)+1 from tt as r where r.total > s.total) as
> rank from tt as s;

I don't know the answer to your questions, except to say that IMO the
SO answer is incorrect for at least two reasons:

1.  Last I checked, SELECT in a column position in the SELECT clause as
in 

select foo (select ...)

is not permitted by the SQL standard.  

2.  In principle, adding data in a query to "make it work" is the wrong
approach.  Here "and S_id is not NULL" is needed to compensate for that
UNION.  

Why it's "needed", in the sense that you get "misuse of aggregate", I
don't know.  It looks like valid SQLite syntax to me.  But I like my
corrected syntax better:

with tt (S_id, total) as
   (select S_id, sum(ca1 +ca2 + exam) as total
   from t group by S_id)
select S.S_id, S.total, 1+count(lesser.total) as RANK
from tt as S
left join tt as lesser
on   S.total < lesser.total
group by S.S_id, S.total
order by S.total desc;
S_idtotal   RANK  
--  --  --
2   198 1 
4   198 1 
5   183 3 
3   165 4 
1   143 5 

because it produces the desired result using standard syntax.  I would
argue that's a clearer expression of the problem, too.  

I have a couple of efficiency questions for those who know:

1.  Is the left-join on a CTE apt to be more effecient than the version
that uses a correlated subquery in the SELECT clause?  

2.  Is there any performance difference between 

sum(ca1 +ca2 + exam) 
and 
sum(ca1) + sum(ca2) + sum(exam) 

I would expect the left join is faster than a correlated subquery, and
that fewer aggregates is better than more. 

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ordinary CTE containing sum()

[Darko Volaric]

I'm interested in this too (since I rely on it working). According to the
documentation this should be perfectly legal and seems like an arbitrary
limitation (or a bug). It says:

"An ordinary common table expression works as if it were a view that exists
for the duration of a single statement."

But it's not the case here.

On Sun, Feb 8, 2015 at 6:57 AM, Doug Currie  wrote:

> >
> > > In response to this SO question:
> > >
> > >
> >
> http://stackoverflow.com/questions/28377210/how-to-retrieve-rank-based-on-total-mark-in-sqlite-table
> > >
> > > I tried to formulate a query without temp tables using an ordinary
> > > CTE, but received an error "misuse of aggregate: sum()".
> >
>
> tonypdmtr  on SO posted
> a
> CTE solution; it is something like this, which works for me:
>
> with tt (S_id, total) as
>(select S_id, sum(ca1) + sum(ca2) + sum(exam) as total
>from t group by S_id
>union values (NULL, 0))
> select s.S_id, s.total,
>(select count(*)+1 from tt as r where r.total > s.total) as rank
>from tt as s where S_id is not NULL;
>
> But my question remains, why is the UNION necessary in the  CTE?
>
> why doesn't this work? ...
>
> with tt (S_id, total) as
>(select S_id, sum(ca1) + sum(ca2) + sum(exam) as total
>from t group by S_id)
> select s.S_id, s.total,
>(select count(*)+1 from tt as r where r.total > s.total) as rank
>from tt as s;
>
> e
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ordinary CTE containing sum()

[Doug Currie]

>
> > In response to this SO question:
> >
> >
> http://stackoverflow.com/questions/28377210/how-to-retrieve-rank-based-on-total-mark-in-sqlite-table
> >
> > I tried to formulate a query without temp tables using an ordinary
> > CTE, but received an error "misuse of aggregate: sum()".
>

tonypdmtr  on SO posted a
CTE solution; it is something like this, which works for me:

with tt (S_id, total) as
   (select S_id, sum(ca1) + sum(ca2) + sum(exam) as total
   from t group by S_id
   union values (NULL, 0))
select s.S_id, s.total,
   (select count(*)+1 from tt as r where r.total > s.total) as rank
   from tt as s where S_id is not NULL;

But my question remains, why is the UNION necessary in the  CTE?

why doesn't this work? ...

with tt (S_id, total) as
   (select S_id, sum(ca1) + sum(ca2) + sum(exam) as total
   from t group by S_id)
select s.S_id, s.total,
   (select count(*)+1 from tt as r where r.total > s.total) as rank
   from tt as s;

e
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ordinary CTE containing sum()

[James K. Lowden]

On Sat, 7 Feb 2015 12:31:37 -0500
Doug Currie  wrote:

> In response to this SO question:
> 
> http://stackoverflow.com/questions/28377210/how-to-retrieve-rank-based-on-total-mark-in-sqlite-table
> 
> I tried to formulate a query without temp tables using an ordinary
> CTE, but received an error "misuse of aggregate: sum()".

My standard answer is http://www.schemamania.org/sql/#rank.rows. 

You don't need a CTE; it's just a syntactic convenience.  Substituting
his table in my example, something like this should work: 

select S.id, S.total, count(lesser.total) as RANK
from (
 select id, sum(cal1 + cal2 + exam) as total 
 from T group by id 
) as S
join (
 select id, sum(cal1 + cal2 + exam) as total 
 from T group by id 
) as as lesser
on   S.SID >= lesser.SID
group by S.SID, S.total
order by S.SID;

Someone will doubtless be tempted to suggest that's inefficient in
SQLite, and that may be so.  (I haven't checked.)  It needed be,
though.  The system could detect the repeated subquery and evaluate it
once.  Not that it matters for any dataset of students and exams on the
planet!  :-)  

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] ordinary CTE containing sum()

[Doug Currie]

In response to this SO question:

http://stackoverflow.com/questions/28377210/how-to-retrieve-rank-based-on-total-mark-in-sqlite-table

I tried to formulate a query without temp tables using an ordinary CTE, but
received an error "misuse of aggregate: sum()".

This works:

sqlite> with tt (S_id, total) as
   ...>(select S_id, sum(ca1) + sum(ca2) + sum(exam) as total
   ...> from t group by S_id)
   ...> select * from tt ;
1|143
2|198
3|165
4|198
5|183

but with the same CTE this fails, even though the select statement after
the CTE works with an equivalent temporary table:

sqlite> with tt (S_id, total) as
   ...>(select S_id, sum(ca1) + sum(ca2) + sum(exam) as total
   ...> from t group by S_id)
   ...> select s.S_id, s.total,
   ...>   (select count(*)+1 from tt as r where r.total > s.total)
as rank
   ...>   from tt as s;
Error: misuse of aggregate: sum()

Any suggestions?

Thanks.

-- e
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users