[SQL] triple self-join crawling

[T E Schmitz]

The following self join of a table containing 5800 records is crawling:

CREATE TABLE history
(
stock VARCHAR(30) NOT NULL,
day date NOT NULL,
open NUMERIC (6,1) NOT NULL,
high NUMERIC (6,1) NOT NULL,
low NUMERIC (6,1) NOT NULL,
close NUMERIC (6,1) NOT NULL,
volume NUMERIC (12) NOT NULL,
PRIMARY KEY (stock,day)
);


SELECT
history.stock, history.day, history.high, history.low,
MAX(past_week.high) AS week_high,
MAX(past_month.high) AS month_high
FROM history
INNER JOIN history AS past_month ON (past_month.stock = history.stock 
AND past_month.day < history.day AND past_month.day >= (history.day - 30))
INNER JOIN history AS past_week  ON (past_week.stock   = 
past_month.stock AND past_week.day < history.day AND past_week.day >= 
(history.day - 7))

GROUP BY  history.stock, history.day, history.high, history.low
ORDER BY history.stock, history.day DESC


How can I speed this up?


--


Regards,

Tarlika Elisabeth Schmitz

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

[SQL] better approach: case or join

[chester c young]

from id extracting name from several tables.  which is generally the
better approach?

select case when tab_tla='usr' then (select name from users where ... )
   case when tab_tla='con' then (select title from contents where
...)
endas name;

as versus

select  name
fromusers
where   tab_tla='usr' and ...
join
select  title
fromcontents
where   tab_tla='con' and ...

I assume the former since the optimizer does not examine whether the
entire where clause might be false before executing the query.

thanks,
chester


 

Don't pick lemons.
See all the new 2007 cars at Yahoo! Autos.
http://autos.yahoo.com/new_cars.html 

---(end of broadcast)---
TIP 1: 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] Help with sub query

[roopa perumalraja]

Hi all,
   
  I want the last value from the group of rows. The table 'index_prices' is
   
  index_code price_time price
   
  XYZ09:45:00   7.5
  XYZ09:46:00   7.4
  XYZ09:59:00   7.2
  XYZ10:00:00   7.3
  XYZ10:01:00   7.6
  XYZ10:02:00   7.3
  .
  .
  .
   
  The other table 'times' is for time interval
   
  snapshot_time
   
  10:00:00
  10:30:00
  11:00:00
   
  I want the last value of the price between the interval of time. So I do 
   
  select tm.index_code, tm.price_date, t.snapshot_time, last(tm.price)
from index_prices_200703 tm, times t where tm.index_code = 'XYZ' and 
tm.price_time >= (t.snapshot_time - '30 minute' :: interval)::time and 
tm.price_time < t.snapshot_time  
group by t.snapshot_time, tm.price_date, tm.index_code order by tm.index_code, 
tm.price_date, t.snapshot_time; 
   
  The last() doesn't work. Can this be done using sub query?
   
  Thanks in advance

 
-
Food fight? Enjoy some healthy debate
in the Yahoo! Answers Food & Drink Q&A.

Re: [SQL] Help with sub query

[Phillip Smith]

Add something like this to your query:

 

ORDER BY price_time LIMIT 1

 

Cheers,

~p

 

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of roopa perumalraja
Sent: Monday, 19 March 2007 13:56
To: pgsql-sql@postgresql.org
Subject: [SQL] Help with sub query

 

Hi all,

 

I want the last value from the group of rows. The table 'index_prices' is

 

index_code price_time price

 

XYZ09:45:00   7.5

XYZ09:46:00   7.4

XYZ09:59:00   7.2

XYZ10:00:00   7.3

XYZ10:01:00   7.6

XYZ10:02:00   7.3

.

.

.

 

The other table 'times' is for time interval

 

snapshot_time

 

10:00:00

10:30:00

11:00:00

 

I want the last value of the price between the interval of time. So I do 

 

select tm.index_code, tm.price_date, t.snapshot_time, last(tm.price)
from index_prices_200703 tm, times t where tm.index_code = 'XYZ' and
tm.price_time >= (t.snapshot_time - '30 minute' :: interval)::time and
tm.price_time < t.snapshot_time  
group by t.snapshot_time, tm.price_date, tm.index_code order by
tm.index_code, tm.price_date, t.snapshot_time; 

 

The last() doesn't work. Can this be done using sub query?

 

Thanks in advance

  

  _  

Food
  fight? Enjoy some healthy debate
in the Yahoo!
  Answers Food & Drink Q&A.



***Confidentiality and Privilege Notice***

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

[SQL] create view with check option

[Karthikeyan Sundaram]

Hi Everybody,

   I have 2 versions of postgres 8.1.0 is my production version and 8.2.1 
is my development version.


 I am trying to create a view in my development version (8.2.3)

  create view chnl_vw as select * from channel with check option;

  I am getting an error message:

[Error] Script lines: 1-1 --
ERROR: WITH CHECK OPTION is not implemented
Line: 1

  what does this mean?  I looked at the 8.2.1 manual and found the create 
view has check option.  But it says before 8.2 those options are 
unsupported.


  How can I make this command to work.

Regards
skarthi

_
5.5%* 30 year fixed mortgage rate. Good credit refinance. Up to 5 free 
quotes - *Terms 
https://www2.nextag.com/goto.jsp?product=10035&url=%2fst.jsp&tm=y&search=mortgage_text_links_88_h2a5d&s=4056&p=5117&disc=y&vers=910



---(end of broadcast)---
TIP 1: 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

Re: [SQL] create view with check option

[Tom Lane]

"Karthikeyan Sundaram" <[EMAIL PROTECTED]> writes:
>I am getting an error message:
> ERROR: WITH CHECK OPTION is not implemented

>what does this mean?

It seems perfectly clear to me ...

regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings