pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDisplaying first, last, count columns
3+ messages / 3 participants
[nested] [flat]
* Displaying first, last, count columns
@ 2006-06-21 14:55 Worky Workerson <worky.workerson@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Worky Workerson @ 2006-06-21 14:55 UTC (permalink / raw)
To: pgsql-sql
I'm having a bit of a brain freeze and can't seem to come up with
decent SQL for the following problem:
I have a table "t" of the form "time_occurred TIMESTAMP, prog_data
VARCHAR" and would like to create a query that outputs something of
the form "first_seen, last_seen, count, prog_data".
I have the current query which gets the first_seen and last_seen via
subqueries, ala
SELECT t1.time_occurred AS first_seen, t2.time_occurred AS last_seen,
t3.count, t1.prog_data
FROM t AS t1, t AS t2
WHERE t1.prog_data = t2.prog_data
AND t1.time_occurred IN (SELECT min(time_occurred) FROM t WHERE
prog_data = t1.prog_data)
AND t2.time_occurred IN (SELECT max(time_occurred) FROM t WHERE
prog_data = t1.prog_data)
but I can't seem to work out how to get the count of all the records
that have. I figure that this is probably a relatively common idiom
... can anyone suggest ways to go about doing this. Also, the
performance of this is pretty horrible, but I figure that creating a
column on t.prog_data should speed things up noticably, right?
Thanks!
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Displaying first, last, count columns
@ 2006-06-21 15:10 Aaron Bono <postgresql@aranya.com>
parent: Worky Workerson <worky.workerson@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Aaron Bono @ 2006-06-21 15:10 UTC (permalink / raw)
To: Worky Workerson <worky.workerson@gmail.com>; +Cc: pgsql-sql
I would suggest:
select
max(time_occurred) AS last_seen,
min(time_occurred) AS first_seen,
count(*),
prog_data
from t
group by
prog_data
I would also suggest you use inner joins rather than put all your tables in
the from and join in the where clause. It is much easier to read and
understand what you are trying to do. The query you have is not exactly the
same as what I put above but I bet the performance is bad because you have
inner queries that have constraints based on the outer query. I usually
avoid this as much as possible.
-Aaron Bono
On 6/21/06, Worky Workerson <worky.workerson@gmail.com> wrote:
>
> I'm having a bit of a brain freeze and can't seem to come up with
> decent SQL for the following problem:
>
> I have a table "t" of the form "time_occurred TIMESTAMP, prog_data
> VARCHAR" and would like to create a query that outputs something of
> the form "first_seen, last_seen, count, prog_data".
>
> I have the current query which gets the first_seen and last_seen via
> subqueries, ala
>
> SELECT t1.time_occurred AS first_seen, t2.time_occurred AS last_seen,
> t3.count, t1.prog_data
> FROM t AS t1, t AS t2
> WHERE t1.prog_data = t2.prog_data
> AND t1.time_occurred IN (SELECT min(time_occurred) FROM t WHERE
> prog_data = t1.prog_data)
> AND t2.time_occurred IN (SELECT max(time_occurred) FROM t WHERE
> prog_data = t1.prog_data)
>
> but I can't seem to work out how to get the count of all the records
> that have. I figure that this is probably a relatively common idiom
> ... can anyone suggest ways to go about doing this. Also, the
> performance of this is pretty horrible, but I figure that creating a
> column on t.prog_data should speed things up noticably, right?
>
> Thanks!
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Displaying first, last, count columns
@ 2006-06-21 15:21 Frank Bax <fbax@sympatico.ca>
0 siblings, 0 replies; 3+ messages in thread
From: Frank Bax @ 2006-06-21 15:21 UTC (permalink / raw)
To: pgsql-sql
At 10:55 AM 6/21/06, Worky Workerson wrote:
>I'm having a bit of a brain freeze and can't seem to come up with
>decent SQL for the following problem:
>
>I have a table "t" of the form "time_occurred TIMESTAMP, prog_data
>VARCHAR" and would like to create a query that outputs something of
>the form "first_seen, last_seen, count, prog_data".
>
>I have the current query which gets the first_seen and last_seen via
>subqueries, ala
>
>SELECT t1.time_occurred AS first_seen, t2.time_occurred AS last_seen,
>t3.count, t1.prog_data
>FROM t AS t1, t AS t2
>WHERE t1.prog_data = t2.prog_data
> AND t1.time_occurred IN (SELECT min(time_occurred) FROM t WHERE
>prog_data = t1.prog_data)
> AND t2.time_occurred IN (SELECT max(time_occurred) FROM t WHERE
>prog_data = t1.prog_data)
>
>but I can't seem to work out how to get the count of all the records
>that have. I figure that this is probably a relatively common idiom
>... can anyone suggest ways to go about doing this. Also, the
>performance of this is pretty horrible, but I figure that creating a
>column on t.prog_data should speed things up noticably, right?
Is this what you're looking for?
SELECT min(time_occurred) AS first_seen, max(time_occurred) AS last_seen,
count(*), prog_data from t group by prog_data;
Since this query has no WHERE or HAVING clause, this query will read the
entire table. There is nothing you can do to speed it up. If you have
enough RAM to hold the entire table (and appropriate setting to utilize
it), then a second (and subsequent) run of the query will be faster than
the first, but that's as good as it gets.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-06-21 15:21 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-06-21 14:55 Displaying first, last, count columns Worky Workerson <worky.workerson@gmail.com>
2006-06-21 15:10 ` Aaron Bono <postgresql@aranya.com>
2006-06-21 15:21 Re: Displaying first, last, count columns Frank Bax <fbax@sympatico.ca>
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