Обсуждение: 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