Grouping Too Closely

Поиск
Список
Период
Сортировка
От Thomas F. O'Connell
Тема Grouping Too Closely
Дата
Msg-id BBA34C36-E1D7-4166-A47F-BA0D563C48B9@sitening.com
обсуждение исходный текст
Ответы Re: Grouping Too Closely
Re: Grouping Too Closely
Список 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


В списке pgsql-sql по дате отправления: