On 21-07-2013 12:01, E.Pasma wrote:
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
It's not optiomal, but would this be better, or worse?
select id, sum(a) as a, sum(b) as b, 1.0 * sum(a) / sum(b) as c FROM
(
select
id,
(case when ot.v>categories.id then ot.v else 0 end) as a,
(case when ot.v<categories.id then ot.v else 0 end) as b
from categories
inner join ot
)
group by id
;
0|0|0|SCAN TABLE categories (~1000000 rows)
0|1|1|SCAN TABLE ot (~1000000 rows)
0|0|0|USE TEMP B-TREE FOR GROUP BY
i suspect that this wil depend on the number of rows in both tables, but
i dont know how that will influence this query ;)
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
