SELECT item_id
, units
, price
FROM items
ORDER BY price
;
ITEM_ID UNITS PRICE
--------- ---------- ----------
1 4 1.99
3 6 2.29
4 11 2.49
2 1 2.99
...
...and so on, I can find the average price, min, max, etc. What I need to know is the fifth percentile and the 95th percentile of the price data, weighted by units. I have noticed that there are functions like percentile_disc. But my problem is that I have to weight the price by the units sold.
Do you have any suggestions in Oracle SQL?
Requires Free Membership to View
What if you want two (or more) such results on the same result-set row? Instead of having a WHERE clause that chooses one row (as in Query 2) you can apply the same logic to computing a new column that has a non-NULL value in only one row.
SELECT run.* -- Query 3
, CASE
WHEN .05 * grand_total
BETWEEN (running_total - units)
AND running_total
THEN price
END AS price_05
, CASE
WHEN .95 * grand_total
BETWEEN (running_total - units)
AND running_total
THEN price
END AS price_95
FROM ( -- Begin sub-query "run", same as query 1
SELECT item_id
, units
, price
, SUM (units)
OVER (ORDER BY price
RANGE UNBOUNDED PRECEDING)
AS running_total
, (
SELECT SUM (units)
FROM items
) AS grand_total
FROM items
) run
;
ITEM RUNNING GRAND PRICE PRICE
ID UNITS PRICE TOTAL TOTAL 05 95
----- ------ ------ -------- ------- ------ ------
1 4 1.99 4 22 1.99
3 6 2.29 10 22
4 11 2.49 21 22 2.49
2 1 2.99 22 22
We can SELECT from the result set of Query 3 to get both percentile values, and the other statistics, at the same time.
SELECT MIN (price) AS price_min -- Query 4 (final answer)
, MAX (price_05) AS price_05
, SUM (units * price) / SUM (units)
AS price_avg
, MAX (price_95) AS price_95
, MAX (price) AS price_max
FROM ( -- Begin sub-query "run", same as query 3
SELECT run.*
, CASE
WHEN .05 * grand_total
BETWEEN (running_total - units)
AND running_total
THEN price
END AS price_05
, CASE
WHEN .95 * grand_total
BETWEEN (running_total - units)
AND running_total
THEN price
END AS price_95
FROM ( -- Begin sub-query 4b, same as query 1
SELECT item_id
, units
, price
, SUM (units)
OVER (ORDER BY price
RANGE UNBOUNDED PRECEDING)
AS running_total
, (
SELECT SUM (units)
FROM items
) AS grand_total
FROM items
) run
) -- End sub-query 4a
;
This was first published in February 2004
Join the conversationComment
Share
Comments
Results
Contribute to the conversation