Need a MYSQL function to calculate a percentile by group

My data is in columns A-E here


Each reviewer (id in column C) assigned a score (column E) to companies (id in column B)

I need to calculate a percentile of that score within each reviewer. E.g. if reviewer X reviewed 4 companies and gave them scores of 1,2,3,4 they will get percentiles of 0, 0.333, 0.666, 1

Further requirements are:

1) I need to be able to upload this function and use it for different datasets in statements like this

select *, MyPercentile(reviewer_id, score) from rating

2) NULL scores should return NULL percentiles

3) Those NULLs should also be completely ignored in a group, i.e. imagine there are fewer values there

4) In column F of the spreadsheet, I provided the Excel calculation of those percentiles. However, I do not like how Excel handles ties. I want the percentiles for tied values to be based on the average, not on minimum, i.e.

--- If all values in the group are identical, everybody gets a percentile of 0.5 (not 0, as in Excel)

--- If in a large group half of the values = 1 and other half = 2, then the first half gets the percentile of 0.25 and the second gets 0.75

I provided the desired values for the first two reviewers in the MyPercentile column.

I also attached the screenshot with the version of MySQL and phpmyadmin.

Please say something meaningful in your proposal, not just the generic "I am an expert and I can do it" stuff.

Kemahiran: Pemasukan Data, Excel, MySQL, phpMyAdmin

Lihat lagi: Need someone to add members in my fb group, i need web design & seo content to keep in my website, i need to write a letter to my friend he passed grade 12 i write a letter to him about my 3rd time failure in grade 10 and my fe, i need a marketing company to market my consumer products in sri lanka, i am a learner in grade 11 i need help i am worried about my future i want to become a lawyer how can i get a bursary in kzn, i need a partner with strong sales background in my security company, i am in need of a website design for my new skincare company, php mysql function dropdown, asp mysql function, writing mysql function, need mysql assistance, php pulldown menu mysql function table, function calculate pip values mql4, mysql function vb6 binary, source php mysql function, autoresponder need opt function, javascript function calculate due date, need find function html, function calculate apr odd days, php mysql hourly calculate daily, sql function calculate date

Tentang Majikan:
( 108 ulasan ) ARLINGTON, United States

ID Projek: #16277939

7 pekerja bebas membida secara purata $27 untuk pekerjaan ini


A proposal has not yet been provided

$30 USD dalam sehari
(67 Ulasan)

As per your requirements you want to implement it on web or in excel? need to discuss more with you. Regards Atiar T.

$35 USD dalam 4 hari
(13 Ulasan)
$25 USD dalam sehari
(10 Ulasan)

Sir i read your initial requirements and i have some question regarding to your [url removed, login to view] we discuss it on chat ?

$25 USD dalam sehari
(2 Ulasan)

Hello Sir, I have more than 6 years of experience in web development and web designing. i will provide you service until you get 100% satisfied. I'm ready to start the work on early basis and will complete it soo Lagi

$25 USD dalam sehari
(1 Ulasan)

I can code your mysql query, please contact to start right away. Relevant Skills and Experience MYSQL

$25 USD dalam sehari
(0 Ulasan)

A proposal has not yet been provided

$25 USD dalam sehari
(0 Ulasan)