pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTable transform query
5+ messages / 3 participants
[nested] [flat]
* Table transform query
@ 2007-09-18 13:12 Philippe Lang <philippe.lang@attiksystem.ch>
2007-09-18 13:49 ` Re: Table transform query Philippe Lang <philippe.lang@attiksystem.ch>
2007-09-18 13:51 ` Re: Table transform query Rodrigo De León <rdeleonp@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Philippe Lang @ 2007-09-18 13:12 UTC (permalink / raw)
To: pgsql-sql
Hi,
I'm trying to find out how to transform this kind of table data (history
of rental data in a firm):
date serial delivery
----------------------
1 1 L
1 2 L
1 3 L
2 1 R
2 2 R
2 4 L
3 5 L
3 3 R
3 4 R
4 1 L
... into this:
serial dateL dateR
--------------------
1 1 2
1 4
2 1 2
3 1 3
4 2 3
5 3
Basically, data on table 1 means:
- on date 1, product with serial 1 is sent to the customer
- on date 1, product with serial 2 is sent to the customer
- on date 1, product with serial 3 is sent to the customer
- on date 2, product with serial 1 comes back
...
On table 2, data means:
- Product with serial 1 is sent to the customer on date 1, and comes
back on date 2
- Product with serial 1 is sent to the customer on date 4, and hasn't
come back yet
...
Do you think there is a generic SQL solution to this problem, like
crosstab or pivot table?
I'm thinking of doing things in a "procedural" plpgsql manner...
Any idea is welcome.
Thanks!
Philippe
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Table transform query
2007-09-18 13:12 Table transform query Philippe Lang <philippe.lang@attiksystem.ch>
@ 2007-09-18 13:49 ` Philippe Lang <philippe.lang@attiksystem.ch>
1 sibling, 0 replies; 5+ messages in thread
From: Philippe Lang @ 2007-09-18 13:49 UTC (permalink / raw)
To: pgsql-sql
pgsql-sql-owner@postgresql.org wrote:
> Hi,
>
> I'm trying to find out how to transform this kind of table data
> (history of rental data in a firm):
...
I have answred my own question: yes, there is a pure SQL solution, with
a subselect:
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);
-------------
select
f.serial,
f.date as dateL,
( select f2.date from foo as f2
where f2.serial = f.serial
and f2.date > f.date
and f2.delivery = 'R'
order by f2.date asc
limit 1
) as dateR
from foo as f
where f.delivery = 'L'
order by f.serial, f.date
-------------
I'm not sure if we could use a self-join here...
Cheers,
Philippe Lang
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Table transform query
2007-09-18 13:12 Table transform query Philippe Lang <philippe.lang@attiksystem.ch>
@ 2007-09-18 13:51 ` Rodrigo De León <rdeleonp@gmail.com>
2007-09-19 08:13 ` Re: Table transform query Nis Jørgensen <nis@superlativ.dk>
1 sibling, 1 reply; 5+ messages in thread
From: Rodrigo De León @ 2007-09-18 13:51 UTC (permalink / raw)
To: Philippe Lang <philippe.lang@attiksystem.ch>; +Cc: pgsql-sql
On 9/18/07, Philippe Lang <philippe.lang@attiksystem.ch> wrote:
> ... into this:
>
>
> serial dateL dateR
> --------------------
> 1 1 2
> 1 4
> 2 1 2
> 3 1 3
> 4 2 3
> 5 3
SELECT t1.serial, t1.DATE AS datel, t2.DATE AS dater
FROM t t1 LEFT JOIN t t2 ON( t1.serial = t2.serial
AND t1.DATE < t2.DATE)
WHERE t1.delivery = 'L'
AND ( t2.delivery = 'R'
OR t2.delivery IS NULL)
ORDER BY t1.serial
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Table transform query
2007-09-18 13:12 Table transform query Philippe Lang <philippe.lang@attiksystem.ch>
2007-09-18 13:51 ` Re: Table transform query Rodrigo De León <rdeleonp@gmail.com>
@ 2007-09-19 08:13 ` Nis Jørgensen <nis@superlativ.dk>
2007-09-21 08:25 ` Re: Table transform query Philippe Lang <philippe.lang@attiksystem.ch>
0 siblings, 1 reply; 5+ messages in thread
From: Nis Jørgensen @ 2007-09-19 08:13 UTC (permalink / raw)
To: pgsql-sql
Rodrigo De León skrev:
> On 9/18/07, Philippe Lang <philippe.lang@attiksystem.ch> wrote:
>> ... into this:
>>
>>
>> serial dateL dateR
>> --------------------
>> 1 1 2
>> 1 4
>> 2 1 2
>> 3 1 3
>> 4 2 3
>> 5 3
>
> SELECT t1.serial, t1.DATE AS datel, t2.DATE AS dater
> FROM t t1 LEFT JOIN t t2 ON( t1.serial = t2.serial
> AND t1.DATE < t2.DATE)
> WHERE t1.delivery = 'L'
> AND ( t2.delivery = 'R'
> OR t2.delivery IS NULL)
> ORDER BY t1.serial
This only works if (serial, delivery) is unique - which it doesn't
appear to be, from the solution posted by Philippe himself (which does a
LIMIT 1 in the subquery).
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.
Nis
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Table transform query
2007-09-18 13:12 Table transform query Philippe Lang <philippe.lang@attiksystem.ch>
2007-09-18 13:51 ` Re: Table transform query Rodrigo De León <rdeleonp@gmail.com>
2007-09-19 08:13 ` Re: Table transform query Nis Jørgensen <nis@superlativ.dk>
@ 2007-09-21 08:25 ` Philippe Lang <philippe.lang@attiksystem.ch>
0 siblings, 0 replies; 5+ messages in thread
From: Philippe Lang @ 2007-09-21 08:25 UTC (permalink / raw)
To: Nis Jørgensen <nis@superlativ.dk>; 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 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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-09-21 08:25 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-09-18 13:12 Table transform query Philippe Lang <philippe.lang@attiksystem.ch>
2007-09-18 13:49 ` Philippe Lang <philippe.lang@attiksystem.ch>
2007-09-18 13:51 ` Rodrigo De León <rdeleonp@gmail.com>
2007-09-19 08:13 ` Nis Jørgensen <nis@superlativ.dk>
2007-09-21 08:25 ` Philippe Lang <philippe.lang@attiksystem.ch>
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