80% Rule Estimation Value in PL/SQL

Assume a range of values inserted in a schema table and in the end of the month i want to apply for these records (i.e. 2500 rows = numeric values) the algorithm: sort the values descending (from the smallest to highest value) and then find the 80% value of the sorted column.

In my example, if each row increases by one starting from 1, the 80% value will be the 2000 row=value (=2500-2500*20/10开发者_开发问答0). This algorithm needs to be implemented in a procedure where the number of rows is not constant, for example it can varries from 2500 to 1,000,000 per month

Hint: You can achieve this using Oracle's cumulative aggregate functions. For example, suppose your table looks like this:

     MY_TABLE
+-----+----------+
| ID  | QUANTITY |
+-----+----------+
|  A  |    1     |
|  B  |    2     |
|  C  |    3     |
|  D  |    4     |
|  E  |    5     |
|  F  |    6     |
|  G  |    7     |
|  H  |    8     |
|  I  |    9     |
|  J  |   10     |
+-----+----------+

At each row, you can sum the quantities so far using this:

SELECT
  id,
  quantity,
  SUM(quantity)
    OVER (ORDER BY quantity ROWS UNBOUNDED PRECEDING)
      AS cumulative_quantity_so_far
FROM
  MY_TABLE

Giving you:

+-----+----------+----------------------------+
| ID  | QUANTITY | CUMULATIVE_QUANTITY_SO_FAR |
+-----+----------+----------------------------+
|  A  |    1     |              1             |
|  B  |    2     |              3             |
|  C  |    3     |              6             |
|  D  |    4     |             10             |
|  E  |    5     |             15             |
|  F  |    6     |             21             |
|  G  |    7     |             28             |
|  H  |    8     |             36             |
|  I  |    9     |             45             |
|  J  |   10     |             55             |
+-----+----------+----------------------------+

Hopefully this will help in your work.

Write a query using the percentile_disc function to solve your problem. Sounds like it does what you want.

An example would be

select percentile_disc(0.8) within group (order by the_value)
from my_table