Обсуждение: Aggregates

Hi,
    If I have a table with users and a table with messages, is it
possible to have a query that returns user.* as well as one extra column
with the number of messages they have posted and the data and time of
the last message? At the moment I am using a subquery to do this,
however it seems suboptimal. Is there a better way?
- Naz.

Naz Gassiep wrote:
> Hi,
>     If I have a table with users and a table with messages, is it
> possible to have a query that returns user.* as well as one extra column
> with the number of messages they have posted and the data and time of
> the last message? At the moment I am using a subquery to do this,
> however it seems suboptimal. Is there a better way?

Not really. You have three separate queries really:
1. User details
2. Total number of messages posted
3. Details on last message posted

Unless you have a messaging-summary table that you keep up-to-date with
triggers you're looking at a three-part query.
--
   Richard Huxton
   Archonet Ltd

On Jun 21, 2007, at 09:22, Richard Huxton wrote:

> Naz Gassiep wrote:
>> Hi,
>>     If I have a table with users and a table with messages, is it
>> possible to have a query that returns user.* as well as one extra
>> column
>> with the number of messages they have posted and the data and time of
>> the last message? At the moment I am using a subquery to do this,
>> however it seems suboptimal. Is there a better way?
>
> Not really. You have three separate queries really:
> 1. User details
> 2. Total number of messages posted
> 3. Details on last message posted
>
> Unless you have a messaging-summary table that you keep up-to-date
> with triggers you're looking at a three-part query.

Certainly except for the user details it could be a single GROUP BY
with several aggregate functions, something like:

   select user.userid, count(*), max(message.datetime)
     from user join message using (userid)
     group by user.userid;

But if userid is UNIQUE, then so is user.*.  You can't do something
like GROUP BY USER.*, but you can group by all the user columns
you're actually interested in selecting:

   select userid, user.name, user.address, count(*), max
(message.datetime)
     from user join message using (userid)
     group by userid, user.name, user.address;

As to whether this is faster or prettier than a subquery, I dunno.

- John D. Burger
   MITRE

John D. Burger wrote:
>
> On Jun 21, 2007, at 09:22, Richard Huxton wrote:
>
>> Naz Gassiep wrote:
>>> Hi,
>>>     If I have a table with users and a table with messages, is it
>>> possible to have a query that returns user.* as well as one extra column
>>> with the number of messages they have posted and the data and time of
>>> the last message? At the moment I am using a subquery to do this,
>>> however it seems suboptimal. Is there a better way?
>>
>> Not really. You have three separate queries really:
>> 1. User details
>> 2. Total number of messages posted
>> 3. Details on last message posted
>>
>> Unless you have a messaging-summary table that you keep up-to-date
>> with triggers you're looking at a three-part query.
>
> Certainly except for the user details it could be a single GROUP BY with
> several aggregate functions, something like:
>
>   select user.userid, count(*), max(message.datetime)
>     from user join message using (userid)
>     group by user.userid;

Ah, but this just includes the time of the last message, not its data.

--
   Richard Huxton
   Archonet Ltd

Richard Huxton wrote:

> Ah, but this just includes the time of the last message, not its data.

Oops, I read the OP's question as "date and time", rather than "data
and time".  Nevermind. :)

- John D. Burger
   MITRE