Re: Table transform query
От | Philippe Lang |
---|---|
Тема | Re: Table transform query |
Дата | |
Msg-id | 6C0CF58A187DA5479245E0830AF84F4218CF26@poweredge.attiksystem.ch обсуждение исходный текст |
Ответ на | Table transform query ("Philippe Lang" <philippe.lang@attiksystem.ch>) |
Список | pgsql-sql |
> 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 casehere. 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
В списке pgsql-sql по дате отправления: