Thread: interval questions
I hope someone on the list can suggest a solution for me - given a table like CREATE TABLE EVENTS( stamp date, id varchar(16), event varchar(128) ); I'm trying to find the average age of the records. I've gotten as far as: SELECT DISTINCT ON(id) age(stamp) FROM EVENTS; Now, I need the DISTINCT ON(id), but that means I can't simply avg() the age: ERROR: Attribute events.id must be GROUPed or used in an aggregate function Can anyone suggest a solution? I could do the averaging myself, except that the output is non-trivial to parse: 7 mons 6 10:29 2 mons 30 07:43:38 3 mons 4 09:50:56 (To be accurate, my code has to get the days in each month right, etc., and it feels like I'm reinventing the wheel there.) Thanks in advance for any suggestions. -- Mike
Michael Blakeley <mike@blakeley.com> writes: > I'm trying to find the average age of the records. I've gotten as far as: > SELECT DISTINCT ON(id) age(stamp) FROM EVENTS; > Now, I need the DISTINCT ON(id), but that means I can't simply avg() the age: > ERROR: Attribute events.id must be GROUPed or used in an > aggregate function You don't say *why* you need DISTINCT ON, or exactly what output you are hoping to get (presumably not a straight average over all the table entries) ... but perhaps something like SELECT id, avg(age(stamp)) FROM events GROUP BY id; is what you need? regards, tom lane
* Michael Blakeley <mike@blakeley.com> [000601 19:09] wrote: > I hope someone on the list can suggest a solution for me - given a table like > > CREATE TABLE EVENTS( stamp date, id varchar(16), event varchar(128) ); > > I'm trying to find the average age of the records. I've gotten as far as: > SELECT DISTINCT ON(id) age(stamp) FROM EVENTS; > > Now, I need the DISTINCT ON(id), but that means I can't simply avg() the age: > ERROR: Attribute events.id must be GROUPed or used in an > aggregate function > > Can anyone suggest a solution? I could do the averaging myself, > except that the output is non-trivial to parse: > 7 mons 6 10:29 > 2 mons 30 07:43:38 > 3 mons 4 09:50:56 > (To be accurate, my code has to get the days in each month right, > etc., and it feels like I'm reinventing the wheel there.) > > Thanks in advance for any suggestions. Does this work for you: SELECT DISTINCT ON(id) avg(age(stamp)) FROM EVENTS group by id; ? -Alfred
Michael Blakeley wrote: > > CREATE TABLE EVENTS( stamp date, id varchar(16), event varchar(128) ); > > I'm trying to find the average age of the records. I've gotten as far as: > SELECT DISTINCT ON(id) age(stamp) FROM EVENTS; > > Now, I need the DISTINCT ON(id), but that means I can't simply avg() the age: > ERROR: Attribute events.id must be GROUPed or used in an > aggregate function > Interesting problem. Would this do it? select into temp_age id, sum(age(stamp)) as age_sum, count(id) from EVENTS group by id; followed by select avg(age_sum/count) from temp_age; Regards, Ed Loehr
At 10:21 PM -0500 6/1/2000, Ed Loehr wrote: >Michael Blakeley wrote: >> >> CREATE TABLE EVENTS( stamp date, id varchar(16), event varchar(128) ); >> >> I'm trying to find the average age of the records. I've gotten as far as: >> SELECT DISTINCT ON(id) age(stamp) FROM EVENTS; >> >> Now, I need the DISTINCT ON(id), but that means I can't simply >>avg() the age: >> ERROR: Attribute events.id must be GROUPed or used in an >> aggregate function >> > >Interesting problem. Would this do it? > > select into temp_age id, sum(age(stamp)) as age_sum, count(id) > from EVENTS group by id; > >followed by > > select avg(age_sum/count) from temp_age; I oversimplified - I left out the outer join, which I was performing in the wrong (non-unique id) direction. I wanted to query for the age of ids that have had events (recently, but I'll omit that part). The following is a little closer to what I was trying to do: CREATE TABLE IDS (id varchar(16) primary key, created date); SELECT DISTINCT ON(id) avg(age(IDS.created))) FROM EVENTS WHERE id=IDS.id; Reversing the join gives me unique ids, and allowed me to leave out the DISTINCT ON clause. So avg() now works, and gives me the single number I was after. Like: SELECT AVG(AGE(created))) FROM IDS WHERE id=EVENTS.id; Thanks for the help - it wasn't until I explained the problem properly that I figured it out :-). -- Mike