pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcar mileage summation / sledgehammer method
4+ messages / 4 participants
[nested] [flat]
* car mileage summation / sledgehammer method
@ 2000-08-16 21:26 Oliver Seidel <os10000@in-medias-res.com>
2000-08-17 01:32 ` Re: car mileage summation / sledgehammer method Francisco Hernandez <xy0xy0@earthlink.net>
0 siblings, 1 reply; 4+ messages in thread
From: Oliver Seidel @ 2000-08-16 21:26 UTC (permalink / raw)
To: pgsql-sql@hub.org, Francisco Hernandez <xy0xy0@earthlink.net>; Volker Paul <vpaul@dohle.com>
OK,
this is quite brutal and is going to be very expensive, but I think it
does what you want. The trick lies in the idea of joining a table with
itself. Thus, I first define a virtual copy of the table (in my case
"dup") and then produce a query that joins the table to this copy. Enjoy.
Oliver
detail=# \d mileage
Table "mileage"
Attribute | Type | Modifier
-----------+-----------+----------
miles | integer |
date | timestamp |
detail=# select * from mileage;
miles | date
-------+------------------------
5 | 2000-08-01 00:00:00+02
9 | 2000-08-02 00:00:00+02
4 | 2000-08-03 00:00:00+02
(3 rows)
detail=# \d dup
View "dup"
Attribute | Type | Modifier
-----------+-----------+----------
miles | integer |
date | timestamp |
View definition: SELECT mileage.miles, mileage.date FROM mileage;
detail=# select mileage.miles, mileage.date, sum(dup.miles) from mileage, dup where dup.date <= mileage.date group by mileage.date, mileage.miles order by mileage.date;
miles | date | sum
-------+------------------------+-----
5 | 2000-08-01 00:00:00+02 | 5
9 | 2000-08-02 00:00:00+02 | 14
4 | 2000-08-03 00:00:00+02 | 18
(3 rows)
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: car mileage summation / sledgehammer method
2000-08-16 21:26 car mileage summation / sledgehammer method Oliver Seidel <os10000@in-medias-res.com>
@ 2000-08-17 01:32 ` Francisco Hernandez <xy0xy0@earthlink.net>
0 siblings, 0 replies; 4+ messages in thread
From: Francisco Hernandez @ 2000-08-17 01:32 UTC (permalink / raw)
To: Oliver Seidel <os10000@in-medias-res.com>; pgsql-sql@hub.org; Volker Paul <vpaul@dohle.com>
great!
Thanks for taking the time to demonstrate this!
----- Original Message -----
From: "Oliver Seidel" <os10000@in-medias-res.com>
To: <pgsql-sql@hub.org>; "Francisco Hernandez" <xy0xy0@earthlink.net>;
"Volker Paul" <vpaul@dohle.com>
Sent: Wednesday, August 16, 2000 2:26 PM
Subject: car mileage summation / sledgehammer method
> OK,
>
> this is quite brutal and is going to be very expensive, but I think it
> does what you want. The trick lies in the idea of joining a table with
> itself. Thus, I first define a virtual copy of the table (in my case
> "dup") and then produce a query that joins the table to this copy. Enjoy.
>
> Oliver
>
>
> detail=# \d mileage
> Table "mileage"
> Attribute | Type | Modifier
> -----------+-----------+----------
> miles | integer |
> date | timestamp |
>
> detail=# select * from mileage;
> miles | date
> -------+------------------------
> 5 | 2000-08-01 00:00:00+02
> 9 | 2000-08-02 00:00:00+02
> 4 | 2000-08-03 00:00:00+02
> (3 rows)
>
> detail=# \d dup
> View "dup"
> Attribute | Type | Modifier
> -----------+-----------+----------
> miles | integer |
> date | timestamp |
> View definition: SELECT mileage.miles, mileage.date FROM mileage;
>
> detail=# select mileage.miles, mileage.date, sum(dup.miles) from mileage,
dup where dup.date <= mileage.date group by mileage.date, mileage.miles
order by mileage.date;
> miles | date | sum
> -------+------------------------+-----
> 5 | 2000-08-01 00:00:00+02 | 5
> 9 | 2000-08-02 00:00:00+02 | 14
> 4 | 2000-08-03 00:00:00+02 | 18
> (3 rows)
>
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: car mileage summation / sledgehammer method
@ 2000-08-17 11:55 Maarten Boekhold <Maarten.Boekhold@reuters.com>
2000-08-17 14:38 ` Re: car mileage summation / sledgehammer method Ross J. Reedstrom <reedstrm@rice.edu>
0 siblings, 1 reply; 4+ messages in thread
From: Maarten Boekhold @ 2000-08-17 11:55 UTC (permalink / raw)
To: Oliver Seidel <os10000@in-medias-res.com>; +Cc: pgsql-sql@hub.org; vpaul@dohle.com; xy0xy0@earthlink.net
> View definition: SELECT mileage.miles, mileage.date FROM mileage;
> detail=# select mileage.miles, mileage.date, sum(dup.miles) from
> mileage, dup
> where
> dup.date <= mileage.date group by mileage.date, mileage.miles
> order by mileage.date;
Now that's a bit stupid. Why create this view when you can do the same
thing with a table alias (I'm I calling this by the right name?).
select m.miles, m.date, sum(d.miles) from mileage m, mileage d
where.......
Maarten
-----------------------------------------------------------------
Visit our Internet site at http://www.reuters.com
Any views expressed in this message are those of the individual
sender, except where the sender specifically states them to be
the views of Reuters Ltd.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: car mileage summation / sledgehammer method
2000-08-17 11:55 Re: car mileage summation / sledgehammer method Maarten Boekhold <Maarten.Boekhold@reuters.com>
@ 2000-08-17 14:38 ` Ross J. Reedstrom <reedstrm@rice.edu>
0 siblings, 0 replies; 4+ messages in thread
From: Ross J. Reedstrom @ 2000-08-17 14:38 UTC (permalink / raw)
To: pgsql-sql@hub.org
On Thu, Aug 17, 2000 at 01:55:19PM +0200, Maarten Boekhold wrote:
>
> Now that's a bit stupid. Why create this view when you can do the same
> thing with a table alias (I'm I calling this by the right name?).
Now now, let's not call it stupid: how about 'not optimal'?
>
> select m.miles, m.date, sum(d.miles) from mileage m, mileage d
> where.......
>
This is the traditional way to do a self join, yes.
Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2000-08-17 14:38 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-08-16 21:26 car mileage summation / sledgehammer method Oliver Seidel <os10000@in-medias-res.com>
2000-08-17 01:32 ` Francisco Hernandez <xy0xy0@earthlink.net>
2000-08-17 11:55 Re: car mileage summation / sledgehammer method Maarten Boekhold <Maarten.Boekhold@reuters.com>
2000-08-17 14:38 ` Ross J. Reedstrom <reedstrm@rice.edu>
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