Percentile value calculations ?

  The goal is to get a 0.4%/1%/99%/99.6% etc percentile value of a given element, e.g. hourly temperature. At 8760 hours/year (8784 in a leap year) 30 or 40 years of data is quite a few records. Here's what I've done so far. Any additional optimization tips appreciated...

  Pass 1) Count the number of hours of suitable data in the date range specified by end-user. Big
  SELECT COUNT(VALUE) INTO XCOUNT WHERE blah blah blah

 Where conditions permit, I check a local summary table (*NOT* the data dictionary) instead. This is only "legal" in some situations. I mention all the index columns in the WHERE clause in pass 1, so there isn't much improvement possible here.

  Pass 2) Multiply XCOUNT by specified percentage (if <= 50%) And store to VCOUNT. Set up a cursor of data ORDER BY VALUE ASC and loop through cursor while cursor%ROWCOUNT < VCOUNT. If the requested percentage is > 50%, I set
  VCOUNT = XCOUNT * (1 - specified_percentile) and the cursor is ORDER BY VALUE DESC. At worst I traverse only half the cursor data set.

  Can anything else be done in pass 2? The "CONNECT BY" queries in the Oracle website hints/tips area look interesting. Unfortunately, they only seem to work for *UNIQUE* values. The temperature data does not work that way. I also tried setting up the cursor as...

  SELECT VALUE FROM
  (
  SELECT VALUE FROM
  blah, blah, blah
  ORDER BY VALUE ASC
  )
  WHERE ROWNUM <= VCOUNT

  Regardless of whether or not the logic would work, Oracle seems to freak out at the sight of an "ORDER BY" clause in a subquery, so that idea doesn't pan out. Are there any other angles I've missed? Received on Sat Jan 15 2000 - 01:14:38 CST