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
精彩评论