用赋值SQL计算平均值

Assume I have a table and it has the following columns:

假设我有一个表,它有以下列:

assignment_id, employee.id, employee_salary and employee_performance

assignment_id,employee.id,employee_salary和employee_performance

(assignment_id - int, employee.id - int, employee_salary - int, employee_performance - varchar).

(assignment_id - int,employee.id - int,employee_salary - int,employee_performance - varchar)。

The column employee_performance consists of the following values: excellent, good, average, bad, null. I want to assign integers to the values and calculate average performance of an employee across different jobs. How can I do that?

employee_performance列包含以下值:excellent,good,average,bad,null。我想为这些值分配整数,并计算不同工作中员工的平均绩效。我怎样才能做到这一点?

For example, some employee has completed two assignments and his results are: excellent and good. I assign 10 to excellent and 9 to good and receive an average of 9.

例如,一些员工完成了两项任务,结果是:优秀和良好。我将10分为优秀分配,9分为良好分配,平均分为9分。

assignment_id, employee.id, employee_salary employee_performance
1                   1            100,000          excellent
2                   1            100,000          excellent
3                   1            100,000          good
4                   4             50,000          good
5                   3             75,000          null

Null means that an assignment is not yet completed.

I want to assign integers to employee_performance. For example, excellent - 10, good - 9, etc.

我想将整数分配给employee_performance。例如,优秀 - 10,好 - 9等

Result should:

eployee.id         average_performance
1                       9,7
3                       9

epmployee.id = 4 is not included, as he does not have completed assignments.

epmployee.id = 4不包括在内,因为他没有完成作业。

2 个解决方案

#1

You can use case. Something like this:

你可以使用案例。像这样的东西:

select employee_id,
       avg(case when employee_performance = 'excellent' then 4.0
                when employee_performance = 'good' then 3.0
                when employee_performance = 'average' then 2.0
                when employee_performance = 'bad' then 1.0
            end) as avg_performance
from table
group by employee_id;

#2

You can create a 2 column table that contains each performance (i.e. excellent, good, etc) and its corresponding value.

您可以创建一个包含每个性能(即优秀,良好等)及其对应值的2列表。

------------------------------
|performance     |      value|
------------------------------
|excellent       |       10  |
------------------------------
|good            |       9   |
------------------------------

Once you have that table you can do a simple join and group by to get the average value per employee.

拥有该表后,您可以进行简单的连接并分组,以获得每位员工的平均价值。

select id, avg(pv.value) 
from employees e
left join performance_values pv 
  on pv.performance = e.employee_performance
group by id

#1