From: nha <lyondif02@free.fr>
To: PgSQL-sql <pgsql-sql@postgresql.org>
Subject: Re: Need magical advice for counting NOTHING
Date: Sat, 25 Jul 2009 02:26:38 +0200
Message-ID: <4A6A513E.2090507@free.fr> (raw)
In-Reply-To: <bd36f99e0907230123w755f0efclf36c6bb303b9fcbd@mail.gmail.com>
References: <4A67EEB2.1070302@gmx.net>
<bd36f99e0907230123w755f0efclf36c6bb303b9fcbd@mail.gmail.com>
Hello,
Le 23/07/09 10:23, Glenn Maynard a écrit :
> On Thu, Jul 23, 2009 at 1:01 AM, Andreas<maps.on@gmx.net> wrote:
>> SELECT user_name, log_type_fk, COUNT(log_type_fk)
>> FROM log
>> JOIN users ON (user_id = user_fk)
>> WHERE (ts IS BETWEEN sometime AND another)
>> GROUP BY user_name, log_type_fk
>> ORDER BY user_name, log_type_fk
> [...]
> SELECT user_name, log_type.log_type, sum((log_type_fk IS NOT
> NULL)::integer) AS count
> FROM users JOIN log_type ON (true) LEFT JOIN log ON (user_id = user_fk
> AND log.log_type_fk = log_type.log_type)
> GROUP BY user_name, log_type.log_type
> ORDER BY user_name, log_type.log_type;
> [...]
In the same direction as Andreas Krestchmer and Glenn Maynard (thanks to
the latter for DDL assumed statements), the following query should also
suit:
SELECT user_name, log_type, COUNT(log_type_fk)
FROM (users CROSS JOIN log_type)
LEFT JOIN log ON (user_id = user_fk AND log_type_id = log_type_fk)
WHERE (ts IS BETWEEN sometime AND another)
GROUP BY user_name, log_type
ORDER BY user_name, log_type
It is syntactically nearer the original query and includes no class
operator. Here are the two main ideas:
- Building all the possible couples of user name and log type by
cross-joining users and log_type tables;
- Counting rows in log table matching each couple (user, log_type) from
the previous cross-join (LEFT JOIN ensures that each row of the table on
the left is mined).
While it is formally assumed that user_id and log_type_id are
respectively keys for users and log_type tables, it is semantically
admitted here that user_name identifies user_id in users table and
log_type identifies log_type_id in log_type table.
Regards.
--
nha / Lyon / France.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: lyondif02@free.fr
Subject: Re: Need magical advice for counting NOTHING
In-Reply-To: <4A6A513E.2090507@free.fr>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox