From: Philippe Lang <philippe.lang@attiksystem.ch>
To: Nis Jørgensen <nis@superlativ.dk>
To: pgsql-sql@postgresql.org
Subject: Re: Table transform query
Date: Fri, 21 Sep 2007 10:25:36 +0200
Message-ID: <6C0CF58A187DA5479245E0830AF84F4218CF26@poweredge.attiksystem.ch> (raw)
References: <6C0CF58A187DA5479245E0830AF84F4218CF1F@poweredge.attiksystem.ch>
<a55915760709180651p4df78cfm59dc219bdd6729ce@mail.gmail.com>
<fcqlnd$9u3$1@sea.gmane.org>
> A take on a self-join:
>
> SELECT t1.serial, t1.date as dateL, MIN(t2.date) as dateR FROM t t1
> LEFT JOIN t t2 ON t1.serial = t2.serial AND t1.date < t2.date AND
> t2.delivery = 'R'
> WHERE t1.delivery = 'L'
> GROUP BY t1.serial, t1.date
>
> Whether this is any clearer, or runs faster, than the correlated
> subquery (which could be simplified by using MIN instead of LIMIT 1)
> is up for debate and test, respectively.
Hi Nis,
Thanks for your tip with the "MIN" operator.
I always imagined a self-join solution was faster than a query with a subselect. With a quick test, it seems to be the case here.
CREATE TABLE foo (
serial integer,
delivery character(1),
date integer
);
INSERT INTO foo (serial, delivery, date) VALUES (1, 'L', 1); INSERT INTO foo (serial, delivery, date) VALUES (2, 'L', 1); INSERT INTO foo (serial, delivery, date) VALUES (3, 'L', 1); INSERT INTO foo (serial, delivery, date) VALUES (1, 'R', 2); INSERT INTO foo (serial, delivery, date) VALUES (2, 'R', 2); INSERT INTO foo (serial, delivery, date) VALUES (4, 'L', 2); INSERT INTO foo (serial, delivery, date) VALUES (5, 'L', 3); INSERT INTO foo (serial, delivery, date) VALUES (3, 'R', 3); INSERT INTO foo (serial, delivery, date) VALUES (4, 'R', 3); INSERT INTO foo (serial, delivery, date) VALUES (1, 'L', 4);
-- Subselect
SELECT
f.serial,
f.date as dateL,
( SELECT MIN(f2.date)
FROM foo AS f2
WHERE f2.serial = f.serial
AND f2.date > f.date
AND f2.delivery = 'R'
) AS dateR
FROM foo AS f
WHERE f.delivery = 'L'
ORDER BY f.serial, f.date
-- Self-join
SELECT
t1.serial,
t1.date as dateL,
MIN(t2.date) as dateR
FROM foo t1
LEFT JOIN foo t2
ON t1.serial = t2.serial
AND t1.date < t2.date
AND t2.delivery = 'R'
WHERE t1.delivery = 'L'
GROUP BY t1.serial, t1.date
ORDER BY t1.serial, t1.date
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: philippe.lang@attiksystem.ch, nis@superlativ.dk
Subject: Re: Table transform query
In-Reply-To: <6C0CF58A187DA5479245E0830AF84F4218CF26@poweredge.attiksystem.ch>
* 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