Re: [SQL] problem with subqueries
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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Suggestion: md5/crypt functions in sql
It would be very usefull to have these in sql, so that it is even easier to create tables with encrypted passwords. Regards, Aasmund Midttun Godal [EMAIL PROTECTED] - http://www.godal.com/ +47 40 45 20 46 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] problem with subqueries
On Sun, 6 Oct 2002 [EMAIL PROTECTED] wrote: > 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). Well, you're running subqueries for each row in monthcustomer because the distinct happens afterwards in your query. So if you've got 4 regions and 1 total and 100,000 rows in monthcustomer, you're looking at something on the order of 500,000 subqueries. Doing the distinct before that step should lower the number to ((#year/month combinations) * (#regions+1)). In any case, you may be better off with one of: a) Doing something programatic to turn a result set like: year|month|region|value 1999|Jan |1 |20 1999|Jan |2 |45 ... into the form you want. The above can be gotten by group by probably and would require no subqueries. b) Keeping a summary table that you update via triggers. This requires a bit of work to get the triggers, but it probably makes the query faster. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Suggestion: md5/crypt functions in sql
Aasmund Midttun Godal wrote: > It would be very usefull to have these in sql, so that it is even easier to create >tables with encrypted passwords. > See contrib/pgcrypto Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Suggestion: md5/crypt functions in sql
On Sun, 6 Oct 2002, Joe Conway wrote: > Aasmund Midttun Godal wrote: > > It would be very usefull to have these in sql, so that it is even easier to create >tables with encrypted passwords. > > > > See contrib/pgcrypto See also http://www.freebsddiary.org/postgresql-7.2.php which shows how I installed the above. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
