Q
Problem solve Get help with specific problems with your technologies, process and projects.

Weighted percentiles, part 2

From this table:

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?

Click to return to part 1.

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
;

Click to jump to part 3.

Dig Deeper on Using Oracle PL-SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close