Forum OpenACS Q&A: Response to Postgres feature questions

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?