dificulties with views and aggregates
От | Ulf Mehlig |
---|---|
Тема | dificulties with views and aggregates |
Дата | |
Msg-id | 200001041613.RAA11073@pandora3.localnet обсуждение исходный текст |
Список | pgsql-general |
Hello out there, There is a line "Allow DISTINCT on views" in the TODO of 6.5.3; I think my problem below is due to this ... is there a workaround other than creating a "real" table instead of a view? Is there any "schedule" for the "view"-problems (just an interested question, by no means intended to be offensive! I'm quite happy with postgreSQL so far :-) Thank you, Ulf P.S.: please CC: me in case of an answer, I'm not on the list at the moment! ---------------------------------------------------------------------- My problem is: I have a table with a date and a time column combined with a measurement value. db=> create table test (d date, t time, val float8); CREATE [... insert something ...] Now I want to calculate the mean daily maximal span of the recorded values for each month. I create a view: db=> create view span as db-> select date_part('year', d) as yr, db-> date_part('month', d) as mon, db-> max(val)-min(val) as vdiff db-> from test group by d; CREATE Now I try to calculate the average span: db=> select yr, mon, avg(vdiff) db-> from span group by yr, mon order by 1, 2; yr mon avg ----+---+------------------ 1997 1 1.62315789473684 1997 1 3.17684210526316 1997 1 2.66842105263158 [...] There should be only one tupel for each year/month combination returned. I is also not possible to select e.g. yr "distict": db=> select distinct yr from span; yr ---- 1997 1997 1997 If there is only a "date_part()"-column in the view, "distinct" works: db=> create view dpt as db-> select date_part('month', d) as mon from test; CREATE db=> select distinct * from dpt; mon --- 1 2 3 -- ====================================================================== Ulf Mehlig <umehlig@zmt.uni-bremen.de> Center for Tropical Marine Ecology/ZMT, Bremen, Germany ----------------------------------------------------------------------
В списке pgsql-general по дате отправления: