Re: [SQL] SQL Subqueries on each result row
On Wed, Sep 23, 2009 at 6:33 PM, Jim wrote:
> On Sep 23, 5:43 am, AnthonyV wrote:
> > Hello,
> >
> > I have a table like :
> >
> >date|value
> > ---
> > 2009-09-19 | 1
> > 2009-09-20 | 2
> > 2009-09-21 | 6
> > 2009-09-22 | 9
> > 2009-09-23 | 1
> >
> > I'd like a request which gives me the sum of each last n days.
> > For example, if I want the sum of each 3 days, I want this result:
> >
> >date| sum_value
> > ---
> > 2009-09-19 | 1 (sum from 2009-09-17 to 2009-09-19)
> > 2009-09-20 | 3 (sum from 2009-09-18 to 2009-09-20)
> > 2009-09-21 | 9 (sum from 2009-09-19 to 2009-09-21)
> > 2009-09-22 | 17 (sum from 2009-09-20 to 2009-09-22)
> > 2009-09-23 | 16 (sum from 2009-09-21 to 2009-09-23)
> >
> > I try to make a subquery which is apply on each row of a query, but it
> > does work.
> >
> > Has anybody an idea?
> >
> > Thanks in advance!
> >
> > Anthony
>
> How about the following?
>
> BEGIN ;
>
> CREATE TABLE z (
> the_date date not null
> ,value integer not null
> ) ;
>
> INSERT INTO z VALUES('2009-09-19',1) ;
> INSERT INTO z VALUES('2009-09-20',2) ;
> INSERT INTO z VALUES('2009-09-21',6) ;
> INSERT INTO z VALUES('2009-09-22',9) ;
> INSERT INTO z VALUES('2009-09-23',1) ;
>
> SELECT z.the_date, SUM(z2.value)
> FROM z
> LEFT JOIN z z2
> ON z2.the_date IN (
> z.the_date
> ,z.the_date-'1 day'::interval
> ,z.the_date-'2 day'::interval
> )
> GROUP BY 1
> ORDER BY 1
> ;
>
> ROLLBACK ;
>
> output:
>
> the_date | sum
> +-
> 2009-09-19 | 1
> 2009-09-20 | 3
> 2009-09-21 | 9
> 2009-09-22 | 17
> 2009-09-23 | 16
> (5 rows)
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
Hi,
The only problem that crops to mind with the above solution is that it
assumes that the dates will always be contiguous...well to be fair the
original request did not mention otherwise either!.. in any case the
solution below should work also with non-consecutive dates. I am afraid
though that it might be a hogger with larger datasets - I just tried out the
first idea that hit me, so use with caution.
CREATE TABLE index_dates AS
SELECT--The result of this is a relation with the dates, values and
'index numbers' - placed in a temp table (you may also create
a view instead so that it is updated all the time - your call)
z.the_date, z.value, z3.indx
FROM
z,
( SELECT--This select generates the 'index number' for each
date, 1 being assigned to the smallest date
z2.the_date, count(z.the_date) AS indx
FROM
z,
z AS z2
WHERE
z.the_date <= z2.the_date
GROUP BY
z2.the_date ) AS z3
WHERE
z.the_date = z3.the_date;
SELECT--same query as suggested by Jim but run on the temp table and
joined on the index numbers instead of dates
index_dates.the_date, sum(z2.value)
FROM
index_dates
LEFT JOIN index_dates z2
ON z2.indx IN (index_dates.indx, index_dates.indx-1, index_dates.indx-2)
GROUP BY index_dates.the_date
ORDER BY index_dates.the_date;
DROP TABLE index_dates;
RESULT:
DATE| SUM
---
2009-09-19|1
2009-09-20|3
2009-09-21|9
2009-09-24|17{inserted instead of 22 Sep}
2009-10-25|16{inserted instead of 23 Sep}
Regs,
Mark
[SQL] simple (?) join
Hi folks. I have two tables create table orders ( o_id serial primary key ... ); create table orders_log ( ol_id serial primary key, o_id int4 not null references orders(o_id), ol_timestamp timestamp, ol_user, ); How can I select all from orders and the last (latest) entry from the orders_log? Cheers -- Gary Stainburn Gary's Haircut 700 Please visit http://www.justgiving.com/Gary-Stainburn/ to help me raise money for Cancer Research - in return I'll have my head shaved -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] simple (?) join
You mean to list the complete orders table and for each of its records, the corresponding record on the orders_log with the latest ol_timestamp? SELECT * FROM orders_log main JOIN ( SELECT orders.*, MAX(orders_log.ol_timestamp) as latest FROM orders NATURAL JOIN orders_log GROUP BY orders.* ) subquery ON main.ol_timestamp = subquery.latest AND main.o_id = subquery.o_id This query is untested, but could you give it a try? Then tell me the results. NB - I am not sure if it is legal to use * on a GROUP BY clause, but if it isnt please kindly substitute by orders.o_id, orders.next_field, etc... Best, Oliveiros - Original Message - From: "Gary Stainburn" To: Sent: Thursday, September 24, 2009 4:16 PM Subject: [SQL] simple (?) join Hi folks. I have two tables create table orders ( o_id serial primary key ... ); create table orders_log ( ol_id serial primary key, o_id int4 not null references orders(o_id), ol_timestamp timestamp, ol_user, ); How can I select all from orders and the last (latest) entry from the orders_log? Cheers -- Gary Stainburn Gary's Haircut 700 Please visit http://www.justgiving.com/Gary-Stainburn/ to help me raise money for Cancer Research - in return I'll have my head shaved -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] simple (?) join
Hmm...no, it seems, it is not allowable to use orders.* on a GROUP BY clause. Unless you've defined for the table something called an ordering operator. If you didn't, you'll have to include all the fields from the orders table in the GROUP BY clause HTH Best, Oliveiros - Original Message - From: "Oliveiros C," To: "Gary Stainburn" ; Sent: Thursday, September 24, 2009 6:17 PM Subject: Re: [SQL] simple (?) join You mean to list the complete orders table and for each of its records, the corresponding record on the orders_log with the latest ol_timestamp? SELECT * FROM orders_log main JOIN ( SELECT orders.*, MAX(orders_log.ol_timestamp) as latest FROM orders NATURAL JOIN orders_log GROUP BY orders.* ) subquery ON main.ol_timestamp = subquery.latest AND main.o_id = subquery.o_id This query is untested, but could you give it a try? Then tell me the results. NB - I am not sure if it is legal to use * on a GROUP BY clause, but if it isnt please kindly substitute by orders.o_id, orders.next_field, etc... Best, Oliveiros - Original Message - From: "Gary Stainburn" To: Sent: Thursday, September 24, 2009 4:16 PM Subject: [SQL] simple (?) join Hi folks. I have two tables create table orders ( o_id serial primary key ... ); create table orders_log ( ol_id serial primary key, o_id int4 not null references orders(o_id), ol_timestamp timestamp, ol_user, ); How can I select all from orders and the last (latest) entry from the orders_log? Cheers -- Gary Stainburn Gary's Haircut 700 Please visit http://www.justgiving.com/Gary-Stainburn/ to help me raise money for Cancer Research - in return I'll have my head shaved -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] simple (?) join
On Thu, 24 Sep 2009 16:16:36 +0100, Gary Stainburn wrote about [SQL] simple (?) join: >create table orders ( >o_id serial primary key >... >); > >create table orders_log ( >ol_id serial primary key, >o_id int4 not null references orders(o_id), >ol_timestamp timestamp, >ol_user, >); > >How can I select all from orders and the last (latest) entry from the >orders_log? SELECT * FROM orders WHERE o_id IN (SELECT o_id FROM orders_log WHERE ol_timestamp = (SELECT MAX(ol_timestamp) FROM orders_log)); No joins required. -- Regards, Dave [RLU #314465] === david.w.n...@ntlworld.com (David W Noon) === -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] simple (?) join
David W Noon wrote: On Thu, 24 Sep 2009 16:16:36 +0100, Gary Stainburn wrote about [SQL] simple (?) join: create table orders ( o_id serial primary key ... ); create table orders_log ( ol_id serial primary key, o_id int4 not null references orders(o_id), ol_timestamp timestamp, ol_user, ); How can I select all from orders and the last (latest) entry from the orders_log? SELECT * FROM orders WHERE o_id IN (SELECT o_id FROM orders_log WHERE ol_timestamp = (SELECT MAX(ol_timestamp) FROM orders_log)); No joins required. I don't think that is what he is requesting. I read it he also wants the timestamp included in the result set A nested query Select orders.*, (SELECT MAX(ol_timestamp) FROM orders_log where orders_log.o_id = orders.oid) >From orders Still another option is using a join Select orders.*, ol_timestamp From orders left join (SELECT MAX(ol_timestamp), o_id FROM orders_log group by o_id) as JoinQuery on JoinQuery.o_id = orders.o_id The second one should be faster
Re: [SQL] simple (?) join
On Thu, 24 Sep 2009 16:15:07 -0400, justin wrote about Re: [SQL] simple (?) join: > > >David W Noon wrote:On Thu, 24 Sep 2009 16:16:36 +0100, Gary Stainburn >wrote about [SQL] simple (?) join: > > create table orders ( >o_id serial primary key >... >); > >create table orders_log ( >ol_id serial primary key, >o_id int4 not null references orders(o_id), >ol_timestamp timestamp, >ol_user, >); > >How can I select all from orders and the last (latest) entry from the >orders_log? > >SELECT * FROM orders >WHERE o_id IN (SELECT o_id FROM orders_log > WHERE ol_timestamp = (SELECT MAX(ol_timestamp) FROM orders_log)); > >No joins required. > >I don't think that is what he is requesting. I read it he also wants >the timestamp included in the result set That shouldn't be too much of a problem. >A nested query > >Select >orders.*, >(SELECT MAX(ol_timestamp) FROM orders_log where orders_log.o_id = > orders.oid) >From orders That won't give the desired results. I don't think the SQL parser will even accept it. >Still another option is using a join > >Select >orders.*, ol_timestamp >From orders >left join (SELECT MAX(ol_timestamp), o_id FROM orders_log group by > o_id) as JoinQuery on JoinQuery.o_id = orders.o_id That won't give the desired results either. If you change "left" to "inner" you will be closer though. Both of your queries will retrieve the entire orders table with a timestamp of some sort from the orders_log table. >The second one should be faster Try this for an elegant approach: WITH maxi AS (SELECT MAX(ol_timestamp) AS ts FROM orders_log) SELECT o.*, maxi.ts FROM orders AS o WHERE o.o_id IN (SELECT o_id FROM orders_log WHERE ol_timestamp = maxi.ts); This caches the latest timestamp in a temporary table so that it can both appear in the result set and be used to constrain the selection from the orders_log table. -- Regards, Dave [RLU #314465] === david.w.n...@ntlworld.com (David W Noon) === -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] simple (?) join
On Thursday 24 Sep 2009, Gary Stainburn wrote: > Hi folks. > > I have two tables > > create table orders ( > o_id serial primary key > ... > ); > > create table orders_log ( > ol_id serial primary key, > o_id int4 not null references orders(o_id), > ol_timestamp timestamp, > ol_user, > ); > > How can I select all from orders and the last (latest) entry from the > orders_log? Does this do what you want? You would have to do some tricks if you also want orders that don't have any entry in the orders_log table to be displayed. select ol.ol_id,ol.o_id,ol.ol_timestamp from orders o natural join orders_log ol where (ol.o_id,ol.ol_timestamp) in (select o_id,max(ol_timestamp) from orders_log group by o_id); Regards, -- Raju -- Raj Mathurr...@kandalaya.org http://kandalaya.org/ GPG: 78D4 FC67 367F 40E2 0DD5 0FEF C968 D0EF CC68 D17F PsyTrance & Chill: http://schizoid.in/ || It is the mind that moves -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
