Calculate percentile of price in the same cities and hoods per cust-CodePudding

This is my cust table:

rn	cust_id	price	street_id	city_id
1	2468	100	1	16
2	1234	200	1	16
3	5678	300	1	16
4	7890	20	5	9
5	2346	70	5	9
6	4532	10	5	9

I want to add a "percentile" column with this calculation per hood id and city_id, for example:

100/(100 200 300)= 0.16667
200/(100 200 300)= 0.33333
300/(100 200 300)= 0.500

output:

rn	cust_id	price	street_id	city_id	percentile
1	2468	100	1	16	0.16667
2	1234	200	1	16	0.33333
3	5678	300	1	16	0.50
4	7890	20	5	9	0.2
5	2346	70	5	9	0.7
6	4532	10	5	9	0.1

I thought to use the percentile function but it's not working.

If someone has a good idea it will be amazing :)

CodePudding user response：

In MySQL 8.0, you can use the division between price and a window function that computes the sum over each partition of <city_id, street_id>.

SELECT *, price / SUM(price) OVER(PARTITION BY city_id, street_id) AS percentile 
FROM tab

Check the demo here.

In MySQL 5.X, you need a subquery to compute your maximum values for each partition, then join back with your original table and apply the division.

SELECT tab.*, tab.price / cte.total_price AS percentile
FROM       tab 
INNER JOIN (SELECT city_id, street_id, 
                   SUM(price) AS total_price
            FROM tab
            GROUP BY city_id, street_id) cte
        ON tab.city_id = cte.city_id AND tab.street_id = cte.street_id

Check the demo here.