mandelia
asked on
Need to do these three steps in one
I need to find correlation of two stocks. I have there price data in a table in the form.
Date stock Last_price
1/1/2005 nifty 123.23
1/1/2005 kmb 8.5
1/2/2005 nifty 132.32
1/2/2005 kmb 8.6
I can get what i need from these three steps, But i need to do it in one.
PLease help. Need it Urgently
-----step one -------------
create view list1
as
select row_number() over (order by date asc) as rank, stock, date, last_price "Price" from data where stock = 'nifty index'
and datepart(weekday,date) = 2
union all
select row_number() over (order by date asc) as rank, stock, date, last_price "Price" from data where stock = 'kmb in equity'
and datepart(weekday,date) = 2
------step two -----------
create view list2
as
select x.stock "stock", x.date "date", log(x.Price / y.price) "Price" from list1 x, list1 y
where x.rank = y.rank + 1 and x.stock = y.stock
------ step three ----------
SELECT
(COUNT(*)*SUM(x.price*y.pr ice)-SUM(x .price)*SU M(y.price) )/(
SQRT(COUNT(*)*SUM(SQUARE(x .price))-S QUARE(SUM( x.price))) *
SQRT(COUNT(*)*SUM(SQUARE(y .price))-S QUARE(SUM( y.price))) )
correlation
FROM list2 x JOIN list2 y ON x.date=y.date
WHERE x.stock='kmb in equity' AND y.stock='nifty index'
Date stock Last_price
1/1/2005 nifty 123.23
1/1/2005 kmb 8.5
1/2/2005 nifty 132.32
1/2/2005 kmb 8.6
I can get what i need from these three steps, But i need to do it in one.
PLease help. Need it Urgently
-----step one -------------
create view list1
as
select row_number() over (order by date asc) as rank, stock, date, last_price "Price" from data where stock = 'nifty index'
and datepart(weekday,date) = 2
union all
select row_number() over (order by date asc) as rank, stock, date, last_price "Price" from data where stock = 'kmb in equity'
and datepart(weekday,date) = 2
------step two -----------
create view list2
as
select x.stock "stock", x.date "date", log(x.Price / y.price) "Price" from list1 x, list1 y
where x.rank = y.rank + 1 and x.stock = y.stock
------ step three ----------
SELECT
(COUNT(*)*SUM(x.price*y.pr
SQRT(COUNT(*)*SUM(SQUARE(x
SQRT(COUNT(*)*SUM(SQUARE(y
correlation
FROM list2 x JOIN list2 y ON x.date=y.date
WHERE x.stock='kmb in equity' AND y.stock='nifty index'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you haven't already, try creating an index on the field "stock" in the table "data", this could speed up your query.
ASKER
Yes i did that right after my post and it did help.
But as you said and i feel the same way: I think this can be optimized.
To give you a bigger picture, Every second we can save in this query can save me 3 Hrs of time in total
But as you said and i feel the same way: I think this can be optimized.
To give you a bigger picture, Every second we can save in this query can save me 3 Hrs of time in total
ASKER
If you want i can start a new question and ask the same
Opening a new question may invite new people to take a look. Maybe you could explain a bit more in detail what this query is supposed to do, and how the tables are structured, it will help people in understanding the end goal.
ASKER
OKey i will open up a new question
ASKER
It has become a bit slow.
Can we somehow optimize it. It takes nearly 6-7 secs for four years of data.
Can we somehow optimize and bring it within 3-4 secs. Lesser than that will be a bonus.
But this solution has worked in a great way