Link to home
Start Free TrialLog in
Avatar of mandelia
mandeliaFlag for India

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.price)-SUM(x.price)*SUM(y.price))/(
   SQRT(COUNT(*)*SUM(SQUARE(x.price))-SQUARE(SUM(x.price)))*
   SQRT(COUNT(*)*SUM(SQUARE(y.price))-SQUARE(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'
ASKER CERTIFIED SOLUTION
Avatar of Otana
Otana

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mandelia

ASKER

It works Fine, But there is a new problem now.
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
Avatar of Otana
Otana

If you haven't already, try creating an index on the field "stock" in the table "data", this could speed up your query.
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
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.
OKey i will open up a new question