Re: Hack around lack of CORRESPONDING BY in EXCEPT?
От | Lucas Adamski |
---|---|
Тема | Re: Hack around lack of CORRESPONDING BY in EXCEPT? |
Дата | |
Msg-id | 001c01c31858$46967500$11f5ec0c@LADAMSKI обсуждение исходный текст |
Ответ на | Re: Hack around lack of CORRESPONDING BY in EXCEPT? (Stephan Szabo <sszabo@megazone23.bigpanda.com>) |
Список | pgsql-performance |
Stephan, Bingo! That worked perfectly, thank you! I was considering something like that, but couldn't figure out the syntax offhand to join two events tables in that fashion. Didn't realize you could alias a table as well! Thanks again, Lucas. > -----Original Message----- > From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com] > Sent: Wednesday, May 07, 2003 3:59 PM > To: Lucas Adamski > Cc: Postgresql Performance Mailing list (E-mail) > Subject: Re: [PERFORM] Hack around lack of CORRESPONDING BY in EXCEPT? > > > > On Wed, 7 May 2003, Lucas Adamski wrote: > > > I wrote it originally as: > > > > SELECT tracking.pk,events.data1,events.data2 FROM > tracking,events WHERE > > tracking.event_fk = event.pk EXCEPT (SELECT > events.data1,events.data2 FROM > > events WHERE event.type = 10) > > > > because each of these subqueries restricts the dataset > greatly before doing > > the join. I've simplified the actual problem (as the real > code has a bunch > > of extraneous stuff that makes it even more obtuse), but > essentially, the > > tracking table maintains a record of the last record type > that was entered. > > The type is incremented for each batch of events that is > loaded. In this > > case, I'm assuming that the latest batch is type=10 (or > 5000, or 100000), > > and the tracking table references a small subset of previous events > > (possibly of types 1-9 in this example). This particular > query is supposed > > to return all tracking.pk's that are present in the > previous batches (types) > > but not in the latest batch (10). I didn't mean to make it > quite so obtuse, > > sorry. :) > > Maybe something like nominally like (quickly done so possibly wrong > again): > > select tracking.pk, events.data1, events.data2 from > tracking,events where not exists (select * from events e where > e.type=10 and e.data1=events.data1 and e.data2=events.data2) > and tracking.event_fk=event.pk > > Get all tracking/event combinations, not including those > where the data1/2 > matches that of an event with type 10. > > That might give dups if there are multiple events rows with > that pk for > different types (but not 10). > >
В списке pgsql-performance по дате отправления: