SQL Oracle 查询来比较公司每项工作的男性与女性薪资

Question

为每个 Job_ID 返回一行，并包含以下列:

• 作业作业 ID

• 女性在该职位的平均服务年限(四舍五入到一年的十分之一)

• 该工作的女性平均工资

• 男性在该职位的平均服务年限(四舍五入到一年的十分之一)

• 该工作的男性平均工资

• 该职位的男性和女性平均工资差异，正数表示女性平均工资较高，负数表示男性平均工资较高。

表格

HR.员工

Name           Null     Type         
-------------- -------- ------------ 
EMPLOYEE_ID    NOT NULL NUMBER(6)    
FIRST_NAME              VARCHAR2(20) 
LAST_NAME      NOT NULL VARCHAR2(25) 
EMAIL          NOT NULL VARCHAR2(25) 
PHONE_NUMBER            VARCHAR2(20) 
HIRE_DATE      NOT NULL DATE         
JOB_ID         NOT NULL VARCHAR2(10) 
SALARY                  NUMBER(8,2)  
COMMISSION_PCT          NUMBER(2,2)  
MANAGER_ID              NUMBER(6)    
DEPARTMENT_ID           NUMBER(4)    
GENDER                  CHAR(1) 

到目前为止我所拥有的，但这为我需要 M & F 列的每个 job_id 返回一个 M & F 行

SELECT gender, job_id, ROUND(AVG(salary),0) as avg_job_salary,
(SELECT ROUND(AVG(salary),0)
FROM hr.employees 
WHERE gender = 'M') AS avg_m_salary, (SELECT ROUND(AVG(salary),0) 
FROM hr.employees 
WHERE gender = 'F') AS avg_f_salary,
ROUND(AVG(days_of_svc/365),1) AS avg_years_svc
FROM (SELECT  job_id, salary, gender, (SYSDATE-hire_date) AS  days_of_svc 
FROM hr.employees)
GROUP BY job_id,gender
ORDER BY job_id, gender;

或版本 2

SELECT gender, job_id, ROUND(AVG(salary),0) as avg_job_salary, 
((SELECT ROUND(AVG(salary),0)
FROM hr.employees 
WHERE gender = 'F') - (SELECT ROUND(AVG(salary),0) 
FROM hr.employees 
WHERE gender = 'M')) as diff,
ROUND(AVG(days_of_svc/365),1) AS avg_years_svc
FROM (SELECT  job_id, salary, gender, (SYSDATE-hire_date) AS  days_of_svc 
FROM hr.employees)
GROUP BY job_id,gender
ORDER BY job_id, gender;

预期结果行示例

JOB_ID    F_AVG_LENGTH F_AVG_SAL M_AVG_LENGTH M_AVG_SAL DIFFERENCE
------    ------------ --------- ------------ --------- ----------
MAILCLERK         24.1     48000         23.4     47000       1000
CASHIER            4.6     12000          4.4     13500      -1500

Answer 1

在我的机器上，我在架构中制作了 HR.EMPLOYEES 的副本，并将其命名为 HR_EMPLOYEES。然后我添加了一个 GENDER 列，因为在我的 Oracle 副本上，HR.EMPLOYEES 表没有 GENDER 列。我在该列中填充了我的最佳猜测，仅供测试之用。

在 Oracle 11 中，您可以使用 PIVOT 操作，这使工作变得更容易。我将平均任期除以 365.25，因此它以年而不是天来表示。请注意，有许多工作要么没有男性，要么没有女性，因此有很多 NULL 结果。我假设您也希望它们显示 - 否则它们可以被忽略。

select job_id, round(F_AVG_TENURE_D/365.25, 1)    as f_avg_length, 
               round(F_AVG_SALARY)                as f_avg_salary,
               round(M_AVG_TENURE_D/365.25, 1)    as m_avg_length, 
               round(M_AVG_SALARY)                as m_avg_salary,       
               round(F_AVG_SALARY - M_AVG_SALARY) as avg_sal_diff
from  ( 
        select job_id, gender, sysdate - hire_date as tenure, salary
        from   hr_employees
      )
pivot (avg(tenure) as avg_tenure_d, avg(salary) as avg_salary 
                                                for gender in ('F' as F, 'M' as M))
order by avg_sal_diff desc nulls last, job_id   --  ORDER BY is optional
;

输出:

JOB_ID     F_AVG_LENGTH F_AVG_SALARY M_AVG_LENGTH M_AVG_SALARY AVG_SAL_DIFF
---------- ------------ ------------ ------------ ------------ ------------
SH_CLERK           11.2         3511          9.9         2973          538
ST_MAN             12.3         7467         10.3         7000          467
ST_CLERK           10.5         2883         10.8         2743          140
PU_CLERK           11.6         2833           10         2700          133
AD_VP              11.1        17000         15.8        17000            0
SA_REP             10.3         8244         10.6         8471         -228
SA_MAN             10.3        12000         10.9        12333         -333
IT_PROG            10.2         4500         10.5         6600        -2100
AC_ACCOUNT                                   14.4         8300
AC_MGR             14.4        12008
AD_ASST            13.1         4400
AD_PRES                                      13.4        24000
FI_ACCOUNT                                   11.2         7920
FI_MGR             14.2        12008
HR_REP             14.4         6500
MK_MAN                                       12.7        13000
MK_REP             11.2         6000
PR_REP                                       14.4        10000
PU_MAN                                       13.9        11000

19 rows selected.