You can also use this form: select id, a, b, 1.0 * a / b as c from ( select id, (select sum(v) from ot as ot1 where ot1.v > categories.id) as a, (select sum(v) from ot as ot2 where ot2.v < categories.id) AS b from categories order by +id ) order by c;
> -----Original Message----- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Clemens Ladisch > Sent: Sunday, 21 July, 2013 07:43 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Anything like "select 7 as a, 8 as b, a / b as > c; " possible whatsoever? > > RSmith wrote: > > On 2013/07/21 12:01, E.Pasma wrote: > >> Only the execution plan of this query is not optimal: > >> 0|0|0|SCAN TABLE categories (~1000000 rows) > >> 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1 > >> 1|0|0|SCAN TABLE ot AS ot1 (~333333 rows) > >> 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2 > >> 2|0|0|SCAN TABLE ot AS ot2 (~333333 rows) > >> 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 3 > >> 3|0|0|SCAN TABLE ot AS ot1 (~333333 rows) > >> 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 4 > >> 4|0|0|SCAN TABLE ot AS ot2 (~333333 rows) > >> 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 5 > >> 5|0|0|SCAN TABLE ot AS ot1 (~333333 rows) > >> 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 6 > >> 6|0|0|SCAN TABLE ot AS ot2 (~333333 rows) > >> 0|0|0|USE TEMP B-TREE FOR ORDER BY > > > > Well yes, the plan does not read like one would expect an optimal > plan > > to read like - but to the purpose of the original request there is > no > > more-optimal a plan, is there? > > SQLite always attempts to flatten such subqueries: > <http://www.sqlite.org/optoverview.html#flattening> > > However, this can be counterproductive when the values in the subquery > are subquery lookups themselves; storing the values once in a > temporary > table might be faster. > > To prevent SQLite from flattening, break one of the rules from the > link > above; for example, add an OFFSET clause: > > select id, a, b, 1.0 * a / b as c from > ( > select > id, > (select sum(v) from ot as ot1 where ot1.v > categories.id) as a, > (select sum(v) from ot as ot2 where ot2.v < categories.id) AS b > from categories > limit -1 offset 0 > ) > order by c; > > 1|0|0|SCAN TABLE categories (~1000000 rows) > 1|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2 > 2|0|0|SCAN TABLE ot AS ot1 (~333333 rows) > 1|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 3 > 3|0|0|SCAN TABLE ot AS ot2 (~333333 rows) > 0|0|0|SCAN SUBQUERY 1 (~4294967295 rows) > 0|0|0|USE TEMP B-TREE FOR ORDER BY > > > Regards, > Clemens > _______________________________________________ > 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
