pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedGrouping Too Closely
5+ messages / 3 participants
[nested] [flat]
* Grouping Too Closely
@ 2005-06-23 19:16 Thomas F. O'Connell <tfo@sitening.com>
0 siblings, 2 replies; 5+ messages in thread
From: Thomas F. O'Connell @ 2005-06-23 19:16 UTC (permalink / raw)
To: pgsql-sql
I have a table that looks like this:
CREATE TABLE my_table (
pkey serial PRIMARY KEY,
fkey int NOT NULL REFERENCES my_other_table( pkey ),
uid int NOT NULL REFERENCES user( pkey ),
seq1 int,
seq2 int
);
Basically, for each fkey that exists in my_table, there is a sequence
represented by seq1, which covers every record corresponding to a
given fkey. Then there is a subset of records covered by seq2, which
increments over the course of a given fkey, but might span multiple
records.
E.g.,
pkey | fkey | uid | seq1 | seq2
---------------------------------------
1 | 1 | 1 | 1 | 1
2 | 1 | 2 | 2 | 1
...
What I'd like to be able to do is select all records corresponding to
the minimum value of seq1 for each value of seq2 corresponding to a
given fkey (with a lower bound on the value of seq2).
My first attempt looked like this:
SELECT fkey, uid, seq2
FROM my_table
WHERE seq2 > 2
GROUP BY fkey, seq2, uid, seq1
HAVING seq1 = min( seq1 )
but this groups too closely to return the desired results.
My next attempt looked like this (where I use the shorthand for min
in the subquery):
SELECT fkey, uid, seq2
FROM my_table AS mt1
WHERE mt1.seq2 > 2
AND ( mt1.uid, hh1.seq1 ) IN (
SELECT mt2.player_id, mt2.order_no
FROM my_table AS mt2
WHERE mt2.fkey = mt1.fkey
AND mt2.seq2 = mt1.seq2
GROUP BY mt2.seq1, mt2.uid
ORDER BY mt2.seq1 ASC
LIMIT 1
)
GROUP BY mt1.holdem_game_id, mt1.holdem_round_type_id, mt1.player_id
This seems like it works, but it is abominably slow, running on the
order of days across 1.5 million rows rather than the seconds (or
preferably milliseconds) I'd prefer.
I have this annoying feeling that I'm overlooking a reasonably
efficient in-between query.
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
Strategic Open Source: Open Your i™
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Grouping Too Closely
@ 2005-06-23 20:03 Russell Simpkins <russellsimpkins@hotmail.com>
parent: Thomas F. O'Connell <tfo@sitening.com>
1 sibling, 0 replies; 5+ messages in thread
From: Russell Simpkins @ 2005-06-23 20:03 UTC (permalink / raw)
To: tfo@sitening.com; pgsql-sql
I'm not sure if this is the best thing to do in all occasions, but I have
found a great speed increase using unions over group by.
select fkey, uid, seq2 from mytable where seq2 > 2 and seq1 = ( select
min(seq1) from mytable);
union
select fkey, uid, seq2 from mytable where seq2 > 2 and seq1 = ( select
min(seq1) from mytable);
order by fkey, uid, seq2;
the union clause with remove your duplicates for you as you were doing with
your group by.
using min on large tables can cause problems. you may want to do your select
min(seq1) from mytable or even have a trigger function after insert/update
that checks the new value against the current lowest stored in another
table.
not sure if this helps, but i hope it does.
russ
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Grouping Too Closely
@ 2005-06-25 04:22 Greg Sabino Mullane <greg@turnstep.com>
parent: Thomas F. O'Connell <tfo@sitening.com>
1 sibling, 1 reply; 5+ messages in thread
From: Greg Sabino Mullane @ 2005-06-25 04:22 UTC (permalink / raw)
To: pgsql-sql; +Cc: tfo@sitening.com
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
> What I'd like to be able to do is select all records corresponding to
> the minimum value of seq1 for each value of seq2 corresponding to a
> given fkey (with a lower bound on the value of seq2).
I'm not sure how uid figures in, but would this do what you want?:
SELECT fkey, uid, seq2, min(seq1)
FROM my_table
WHERE seq2 > 2
GROUP BY fkey, uid, seq2
ORDER BY 1,2,3;
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200506250019
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iD8DBQFCvNuWvJuQZxSWSsgRAhPZAJ9ssJx1/9x1Ngr9i/fThpKkDwMq1wCg5TW0
fKHUuoUBrXx+0/5hRegpXF4=
=obPq
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Grouping Too Closely
@ 2005-06-25 05:32 Thomas F. O'Connell <tfo@sitening.com>
parent: Greg Sabino Mullane <greg@turnstep.com>
0 siblings, 1 reply; 5+ messages in thread
From: Thomas F. O'Connell @ 2005-06-25 05:32 UTC (permalink / raw)
To: Greg Sabino Mullane <greg@turnstep.com>; +Cc: pgsql-sql
This doesn't give me quite what I'm looking for because I need there
to be only one of each possible value of seq2 to be returned for each
value of fkey.
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
Strategic Open Source: Open Your i™
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On Jun 24, 2005, at 11:22 PM, Greg Sabino Mullane wrote:
>> What I'd like to be able to do is select all records corresponding to
>> the minimum value of seq1 for each value of seq2 corresponding to a
>> given fkey (with a lower bound on the value of seq2).
>
> I'm not sure how uid figures in, but would this do what you want?:
>
> SELECT fkey, uid, seq2, min(seq1)
> FROM my_table
> WHERE seq2 > 2
> GROUP BY fkey, uid, seq2
> ORDER BY 1,2,3;
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Grouping Too Closely
@ 2005-06-25 06:40 Greg Sabino Mullane <greg@turnstep.com>
parent: Thomas F. O'Connell <tfo@sitening.com>
0 siblings, 0 replies; 5+ messages in thread
From: Greg Sabino Mullane @ 2005-06-25 06:40 UTC (permalink / raw)
To: tfo@sitening.com; +Cc: pgsql-sql
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
> This doesn't give me quite what I'm looking for because I need there
> to be only one of each possible value of seq2 to be returned for each
> value of fkey.
Then perhaps just:
SELECT fkey, seq2, min(seq1)
FROM my_table
WHERE seq2 > 2
GROUP BY fkey, seq2
ORDER BY 1,2,3;
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200506250237
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iD8DBQFCvPwJvJuQZxSWSsgRAtcHAKDzl67Va8ABP4qyNpvFtWDpjmT/iwCg3D5J
kJMkDaELNZREh5+7OyJ/FSU=
=SChT
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-06-25 06:40 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-23 19:16 Grouping Too Closely Thomas F. O'Connell <tfo@sitening.com>
2005-06-23 20:03 ` Russell Simpkins <russellsimpkins@hotmail.com>
2005-06-25 04:22 ` Greg Sabino Mullane <greg@turnstep.com>
2005-06-25 05:32 ` Thomas F. O'Connell <tfo@sitening.com>
2005-06-25 06:40 ` Greg Sabino Mullane <greg@turnstep.com>
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