Re: Retreiving future record based on price movement

"mark" <mark.russel_at_hotmail.com> a écrit dans le message de news: 1141304089.084718.134680_at_j33g2000cwa.googlegroups.com...
| Hi,
|
| I have a table of closing prices and I want to write a query that
| returns for each row the EARLIEST FUTURE date where the closing price
| has gone up at least 20% from the current row.
|
| I want to search only future dates in the next month (or next 20 rows
| would be OK)
|
| I thought this could be done using analytic functions but can't seem to
| see how?
| The problem I see is that the window function needs to consider both
| the date and the price movement.
|
| Here is a sample data:
|
| CREATE TABLE prices
| AS
| SELECT TO_DATE('28-DEC-2001') day, 1.9 close_price FROM dual UNION ALL
| SELECT TO_DATE('31-DEC-2001') day, 1.3 close_price FROM dual UNION ALL
| SELECT TO_DATE( '2-JAN-2002') day, 1 close_price FROM dual UNION ALL
| SELECT TO_DATE( '3-JAN-2002') day, 1.1 close_price FROM dual UNION ALL
|
| SELECT TO_DATE( '4-JAN-2002') day, 1.05 close_price FROM dual UNION ALL
|
| SELECT TO_DATE( '7-JAN-2002') day, 1.2 close_price FROM dual UNION ALL
|
| SELECT TO_DATE( '8-JAN-2002') day, 1.25 close_price FROM dual UNION ALL
|
| SELECT TO_DATE( '9-JAN-2002') day, 1.55 close_price FROM dual UNION ALL
|
| SELECT TO_DATE('10-JAN-2002') day, 1.35 close_price FROM dual UNION ALL
|
| SELECT TO_DATE('11-JAN-2002') day, 1.6 close_price FROM dual UNION ALL
|
| SELECT TO_DATE('14-JAN-2002') day, 1.7 close_price FROM dual
|
|
| This seems like a curly one. Hope you can help.
|
| Many thanks,
| Mark.
|

SQL> select * from prices order by day;
DAY CLOSE_PRICE

----------- -----------
28-DEC-2001         1.9
31-DEC-2001         1.3
02-JAN-2002           1
03-JAN-2002         1.1
04-JAN-2002        1.05
07-JAN-2002         1.2
08-JAN-2002        1.25
09-JAN-2002        1.55
10-JAN-2002        1.35
11-JAN-2002         1.6
14-JAN-2002         1.7

11 rows selected.

SQL> select a.day, a.close_price, min(b.day) future   2 from prices a, prices b
  3 where b.close_price (+) >= 1.2 * a.close_price   4 and b.day (+) between a.day+1 and add_months(a.day,1)   5 group by a.day, a.close_price
  6 order by a.day
  7 /
DAY CLOSE_PRICE FUTURE

----------- ----------- -----------
28-DEC-2001         1.9
31-DEC-2001         1.3 11-JAN-2002
02-JAN-2002           1 07-JAN-2002
03-JAN-2002         1.1 09-JAN-2002
04-JAN-2002        1.05 09-JAN-2002
07-JAN-2002         1.2 09-JAN-2002
08-JAN-2002        1.25 09-JAN-2002
09-JAN-2002        1.55
10-JAN-2002        1.35 14-JAN-2002
11-JAN-2002         1.6
14-JAN-2002         1.7

11 rows selected.

Sometimes analytics don't solve an SQL issue. :-)

Regards
Michel Cadot Received on Thu Mar 02 2006 - 11:10:32 CST