Обсуждение: Aggregates and Views
Is it possible to use an aggregate on a view that contains an aggregate? I can't seem to get this to work: (cut & paste into psql to reproduce) CREATE TABLE x ( n int ); INSERT INTO x VALUES(5); INSERT INTO x VALUES(5); INSERT INTO x VALUES(5); INSERT INTO x VALUES(10); INSERT INTO x VALUES(10); INSERT INTO x VALUES(10); INSERT INTO x VALUES(15); INSERT INTO x VALUES(15); INSERT INTO x VALUES(15); INSERT INTO x VALUES(15); INSERT INTO x VALUES(20); INSERT INTO x VALUES(20); SELECT count(*) from x; -- Correctly returns 12 CREATE VIEW y AS SELECT n, count(*) AS total FROM x GROUP BY n; SELECT * FROM y; -- Correctly returns (5,3), (10,3), (15,4), (20,2) -- So far so good -- but if I do this: SELECT count(*) FROM y; -- Instead of getting 4, I get 3,3,4,2 SELECT sum(total) FROM y; -- Returns nonsense values 0, 9, 12, 8 DROP TABLE x; DROP VIEW y; (end cut & paste) Thanks, Mark
Mark Volpe <volpe.mark@epamail.epa.gov> writes: > Is it possible to use an aggregate on a view > that contains an aggregate? Not at present. Views are implemented by a rewriter that tries to transform your query into another SQL query, and since aggregates of aggregates are not possible, it doesn't work. In current sources I actually get an error from your second example: regression=# SELECT sum(total) FROM y; ERROR: Aggregate function calls may not be nested For the same sort of reason, GROUPed views don't play nice with an outer query that specifies different grouping (ie, has a GROUP clause of its own, or perhaps an aggregate). What we need in order to fix this is subselects in FROM clauses; if the rewriter could transform your query into SELECT sum(total) FROM (SELECT n, count(*) AS total FROM x GROUP BY n) then everything would just work (and the rewriter would get a lot simpler, too ;-)). We don't have subselects in FROM yet, but I hope to see them in 7.1, or 7.2 at the latest. regards, tom lane