???

2 Shweik:

синтаксис, допустим, не самый красивый
результат - правильный, имхо


db=# create table ttt(a int, b int, c int);
CREATE TABLE
db=# insert into ttt values(1, 2, 3);
INSERT 248305 1
db=# insert into ttt values(2, 4, 5);
INSERT 248306 1
db=# insert into ttt values(3, 6, 7);
INSERT 248307 1
db=# select a, (select sum(b) from ttt t2 where t2.a <= t.a) as b, (select sum(c) from ttt t2 where t2.a<=t.a) as c from ttt t order by a;
a | b | c
---+----+----
1 | 2 | 3
2 | 6 | 8
3 | 12 | 15
(3 rows)
db=# explain
db-# select a, (select sum(b) from ttt t2 where t2.a <= t.a) as b, (select sum(c) from ttt t2 where t2.a<=t.a) as c from ttt t order by a;
QUERY PLAN
-----------------------------------------------------------------------------
Sort (cost=69.83..72.33 rows=1000 width=4)
Sort Key: a
-> Seq Scan on ttt t (cost=0.00..20.00 rows=1000 width=4)
SubPlan
-> Aggregate (cost=23.33..23.33 rows=1 width=4)
-> Seq Scan on ttt t2 (cost=0.00..22.50 rows=333 width=4)
Filter: (a <= $0)
-> Aggregate (cost=23.33..23.33 rows=1 width=4)
-> Seq Scan on ttt t2 (cost=0.00..22.50 rows=333 width=4)
Filter: (a <= $0)
(10 rows)

db=# create index ttt_a_idx on ttt(a);
CREATE INDEX
db=# explain
db-# select a, (select sum(b) from ttt t2 where t2.a <= t.a) as b, (select sum(c) from ttt t2 where t2.a<=t.a) as c from ttt t order by a;
QUERY PLAN
--------------------------------------------------------------------------
Sort (cost=1.05..1.06 rows=3 width=4)
Sort Key: a
-> Seq Scan on ttt t (cost=0.00..1.03 rows=3 width=4)
SubPlan
-> Aggregate (cost=1.04..1.04 rows=1 width=4)
-> Seq Scan on ttt t2 (cost=0.00..1.04 rows=1 width=4)
Filter: (a <= $0)
-> Aggregate (cost=1.04..1.04 rows=1 width=4)
-> Seq Scan on ttt t2 (cost=0.00..1.04 rows=1 width=4)
Filter: (a <= $0)
(10 rows)
db=# explain
db-# select t1.a, sum(t2.b), sum(t2.c) from ttt t1, ttt t2 where t2.a<=t1.a group by t1.a;
QUERY PLAN
-------------------------------------------------------------------------------
Aggregate (cost=4.26..4.29 rows=1 width=16)
-> Group (cost=4.26..4.27 rows=3 width=16)
-> Sort (cost=4.26..4.26 rows=3 width=16)
Sort Key: t1.a
-> Nested Loop (cost=0.00..4.23 rows=3 width=16)
Join Filter: ("inner".a <= "outer".a)
-> Seq Scan on ttt t1 (cost=0.00..1.03 rows=3 width=4)
-> Seq Scan on ttt t2 (cost=0.00..1.03 rows=3 width=12)
(8 rows)