From: pete@phillipsfamily.freeserve.co.uk
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us
Cc: kenzo@kennethambrose.com
Subject: Re: problem with subqueries
Date: Sun, 06 Oct 2002 11:13:41 +0000
Message-ID: <200210061113.g96BDfG10468@phillipsfamily.freeserve.co.uk> (raw)
References: <200210052219.g95MJVx01588@phillipsfamily.freeserve.co.uk>
<6763.1033866093@sss.pgh.pa.us>
Hi guys. Thanks for the rapid replies so far.
To answer some of the questions:
>you did not indicate an explicit join - or even a "from" clause for that
>matter- in the example of your create view statement.
My original post was a simplified version. Here is the actual view
creating statement:
create view monthord as select ord_date, extract (month from ord_date)
as month, extract (year from ord_date) as year,r_region,
number_of_items from orders,customer where ccode = codenum;
>But it appears to me that you are reinventing the wheel. Isn't this
>query the equivalent of a grouped aggregation
Yes - but again I was simplifying - I want to run a sub query for each
region, so I get output like this:
year month Reg1 Reg2 Reg3 Reg4
----- ----- ---- ---- ----- ----
1999 Jan 20 45 10 27
1999 Feb 30 43 18 37
...
2002 Oct 7 89 60 17
The subquery I have tried to run is actually this (there is probably a
way to do this all in SQL, but at present I would like to just
understand why my subqueries take so long).
-----start of script ---------------
#!/bin/sh
# prepare a query as shell variables
# set up timeframe
SDATE=`date --date '2 years ago' +'%Y-%m-01'`
EDATE=`date --date 'next month' +'%Y-%m-01'`
# use the QUERY env variable to build the full query
QUERY="select distinct year,month, "
for reg in `psql -U postgres -d product_db -c "select distinct r_code from regionlist order by r_code;"`
do
QUERY="$QUERY (select sum(monthcustomer.number_of_items) from monthcustomer where monthcustomer.year=m.year and monthcustomer.month=m.month and r_region like '$reg') as $reg,"
done
# now add a total column
QUERY="$QUERY (select sum(monthcustomer.number_of_items) from monthcustomer where ord_date >= '$SDATE' and ord_date < '$EDATE' and monthcustomer.year=m.year and monthcustomer.month=m.month) as TOTAL from monthcustomer m;"
# execute the query
psql -U postgres -d newmaggot -c "$QUERY"
-----end of script ---------------
As you can see, I get all the regions, and loop through them building
up a bunch of subqueries as I go along.
Hopefully this will answer some of your questions. I will try some of
the suggestions later on today (such as placing the distinct clause at
the end) but first I have to go and get some decorating done :-( (yes,
that sound you can hear is the crack of my wife's whip!).
Regards,
Pete
--
I do whatever the Voices tell me to do
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: pete@phillipsfamily.freeserve.co.uk, tgl@sss.pgh.pa.us, kenzo@kennethambrose.com
Subject: Re: problem with subqueries
In-Reply-To: <200210061113.g96BDfG10468@phillipsfamily.freeserve.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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