Andrew,
For some reason, your original query just wouldn't leave me
alone. Before I go on, I owe you an apology. What I didn't
realize in your query is that you're actually producing a
table of values. In that event, your self-join is hardly "ugly".
In fact, it qualifies as elegant.
However, looking at a couple of Oracle books for some brain-joggers
led me to some functions I had not used before:
LEAD and LAG. These are intended to do exactly
what you need to do.
I created a table:
CREATE TABLE dlb_stocks (
id NUMBER NOT NULL,
day_date DATE NOT NULL,
price NUMBER(18,3) NOT NULL,
PRIMARY KEY (id, day_date )
);
and populated it with 720,000 rows...
BEGIN
FOR i IN 1..10000 LOOP
INSERT INTO dlb_stocks
(id, day_date, price)
SELECT i,
TRUNC(SYSDATE) - (ROWNUM - 1),
MOD( object_id, 9 ) * 10
FROM all_objects
WHERE rownum < 100;
END LOOP;
END;
/
-- get rid of Saturday and Sunday values
DELETE from dlb_stocks WHERE TO_CHAR( day_date, 'dy') IN ('sat','sun');
COMMIT;
And analyzed it to let the cost-based optimizer "be all it can be":
ANALYZE TABLE dlb_stocks COMPUTE STATISTICS
FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS;
And ran the new and improved query:
column id format 99999 heading 'ID'
column todays_date format a9 heading 't_date'
column todays_price format 999.9 heading 't_price'
column yesterdays_date format a9 heading 'y_date'
column yesterdays_price format 999.9 heading 'y_price'
column price_diff format 999.9 heading 'Diff'
column price_change format 0.90 heading 'Ratio'
SELECT id, day_date as todays_date, price AS todays_price,
LEAD( day_date, 1, NULL) OVER (ORDER BY day_date DESC) AS yesterdays_date,
LEAD( price, 1, 0 ) OVER (ORDER BY day_date DESC) AS yesterdays_price,
(price - LEAD( price, 1, 0) OVER (ORDER BY day_date DESC)) AS price_diff,
TO_CHAR(DECODE( LEAD( price, 1, 0) OVER (ORDER BY day_date DESC), 0, NULL,
price / ( LEAD( price, 1, 0) OVER (ORDER BY day_date DESC) ) ), '90.00') AS price_change
FROM dlb_stocks
WHERE id = 1
ORDER BY day_date DESC;
And got the following output
ID t_date t_price y_date y_price Diff Ratio
------ --------- ------- --------- ------- ------ ------------------
1 15-OCT-01 50.0 12-OCT-01 70.0 -20.0 0.71
1 12-OCT-01 70.0 11-OCT-01 80.0 -10.0 0.88
1 11-OCT-01 80.0 10-OCT-01 20.0 60.0 4.00
...
With some gorgeous statistics:
71 rows selected.
Elapsed: 00:00:00.11
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=71 Bytes=852)
1 0 WINDOW (BUFFER)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'DLB_STOCKS' (Cost=4 Ca
rd=71 Bytes=852)
3 2 INDEX (RANGE SCAN DESCENDING) OF 'SYS_C004382' (UNIQUE
) (Cost=3 Card=71)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
6444 bytes sent via SQL*Net to client
1207 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
71 rows processed
My only response was sweeeet. I suspect you'd see even better
results by specifying ORGANIZATION INDEX when building the
table or by constructing an index on id/price/day_date so that the
index alone is sufficient to read the data you want.
I know you didn't really intend for your query to generate so much interest,
but this particular solution seems to perform VERY well. Do you agree?
