Op 21 jul 2013, om 11:27 heeft Mikael het volgende geschreven:
Hi Igor,
Ah I just noticed how you wrote your query and it delivers for it
indeed.
Here's an arbitrary example verifying its works.
Neat - thanks!
sqlite3 test.sqlite
create table categories (id number);
insert into categories (id) values (5),(10),(15);
create table ot (v number);
insert into ot (v) values (2),(4),(6),(8),(14),(16),(20);
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 c;
Mikael
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
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
