pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
car 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>
  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-17 01:32  Francisco Hernandez <xy0xy0@earthlink.net>
  parent: Oliver Seidel <os10000@in-medias-res.com>
  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>
  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 14:38  Ross J. Reedstrom <reedstrm@rice.edu>
  parent: Maarten Boekhold <Maarten.Boekhold@reuters.com>
  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