获得最高和最低的第25个百分位数平均值 [英] get top and bottom 25th percentile average

问题描述

我有一张表，上面有员工名单以及他们出售的单位数量.

I have a table with list of employees and the number of units that they have sold.

我想获得销售量最高的25％平均单位和销售量最低的25％平均单位.

I want to get the top 25 percentile Avg units sold and Bottom 25 percentile Avg units sold.

我已经创建了我的数据的表示形式 SLQ小提琴

I have created a representation of my data SLQ Fiddle

我真的不知道该如何开始?我看到的所有示例都是针对SQL Server而非MySQL.这就是我的想法.

I really have no idea how to start on this? All the examples i see are for SQL Server and not MySQL. Here is what i am thinking.

我想要25个百分点，并且不能限制25个项目.基本上，它涉及:

I want 25 percentile and cant limit to 25 items. Basically it would involve:

1) #_of_employees = The number of total employees. 

2) #_of_employees_in_25_percentile =  #_of_employees*0.25 

3) Calculate the sum of the units sold by the top/bottom 25 percentile (limit #_of_employees_in_25_percentile)

4) Divide the sum by #_of_employees_in_25_percentile to get the average.

如何在MySQL中高效地完成所有这些工作?

How can all this be done efficiently in MySQL?

推荐答案

这是一个使用了我学到的诡计的技巧的解决方案

This is a solution that uses a devious trick I learned from this question.

SELECT id, unit_sold, n * 100 / @total AS percentile
FROM (
  SELECT id, unit_sold, @total := @total + unit_sold AS n
  FROM mydata, (SELECT @total := 0) AS total
  ORDER BY unit_sold ASC
) AS t

SQL提琴.

这篇关于获得最高和最低的第25个百分位数平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！