计算部门平均工资

计算部门平均工资

问题:
计算部门的平均工资,要求去掉部门的最高和最低工资,如果部门的最高和最低工资有相同的,则去掉一个即可;

1- 数据准备

t_emp_info: uuid(员工id), dept(部门), salary(薪水)

--数据准备
WITH t_emp_info AS (
SELECT * FROM (
  VALUES  (1001, '研发部', 16000 )
        , (1002, '市场部', 17000 ) , (1014, '产品部', 14000 )
        , (1003, '销售部', 11000 ) , (1015, '销售部', 16000 )
        , (1004, '研发部', 15000 ) , (1016, '研发部', 19000 )
        , (1005, '销售部', 12000 ) , (1017, '销售部', 11000 )
        , (1006, '研发部', 21000 ) , (1018, '产品部', 17000 )
        , (1007, '产品部', 16000 ) , (1019, '产品部', 15000 )
        , (1008, '研发部', 18000 ) , (1020, '研发部', 14000 )
        , (1009, '市场部', 17000 ) , (1021, '市场部', 13000 )
        , (1010, '产品部', 16000 ) , (1022, '产品部', 13000 )
        , (1011, '销售部', 10000 ) , (1023, '销售部', 12000 )
        , (1012, '研发部', 18000 ) , (1024, '销售部', 13000 )
        , (1013, '市场部', 15000 ) , (1025, '研发部', 16000 ) 
        
) AS table_name(uuid, dept, salary)
)

2- 代码实现

-- 1. 按部门工资排序, 正排和倒排
SELECT 
      uuid, dept, salary
    , ROW_NUMBER() OVER(PARTITION BY dept ORDER BY salary) AS rn1 -- 正排, 最低工资为 1
    , ROW_NUMBER() OVER(PARTITION BY dept ORDER BY salary desc ) AS rn2 -- 倒排, 最高工资为 1
FROM t_emp_info ;

uuid	dept	salary	rn1	rn2
1018	产品部	17000	6	1
1010	产品部	16000	4	2
1007	产品部	16000	5	3
1019	产品部	15000	3	4
1014	产品部	14000	2	5
1022	产品部	13000	1	6
1002	市场部	17000	3	1
1009	市场部	17000	4	2
1013	市场部	15000	2	3
1021	市场部	13000	1	4
…	…	…	…	…

--2. 去除最高工资和最低工资, 求部门平均工资
SELECT 
      dept, AVG(salary) salary_avg
FROM (
    SELECT 
          uuid, dept, salary
        , ROW_NUMBER() OVER(PARTITION BY dept ORDER BY salary) AS rn1
        , ROW_NUMBER() OVER(PARTITION BY dept ORDER BY salary desc ) AS rn2
    FROM t_emp_info
) a
WHERE rn1 != 1 AND rn2 != 1  -- 去除最高工资和最低工资
GROUP BY dept 
;

去除最高工资和最低工资

uuid	dept	salary	rn1	rn2
1010	产品部	16000	4	2
1007	产品部	16000	5	3
1019	产品部	15000	3	4
1014	产品部	14000	2	5
1009	市场部	17000	4	2
1013	市场部	15000	2	3
1016	研发部	19000	7	2
1012	研发部	18000	5	3
1008	研发部	18000	6	4
1025	研发部	16000	3	5
…	…	…	…	…

求部门平均工资

dept	salary_avg
产品部	15250.0
市场部	16000.0
研发部	17000.0
销售部	11800.0

end