计算除去部门最高工资，和最低工资的平均工资（字节跳动面试）
/*emp 表:id 员工 id ，deptno 部门编号，salary 工资核心是使用窗口函数降序和升序分别排一遍就取出了最高和最低其他延展: ｛规则：去掉两头的极端值｝裁判最后计分、计算部门平均薪资、门店销售员的销售额等等场景*/select deptno,avg(salary) as daily_salary_avgfrom (SELECT dimShopID as deptno,dimMemberID as id ,ROUND(SUM(AMT),0) AS salary ,rank()over(partition by dimShopID order by SUM(AMT)) as rank1,rank()over(partition by dimShopID order by SUM(AMT) desc ) as rank2FROM dw.fct_salesWHERE dimDateID = '0701' and dimMemberID <> 0GROUP BY dimShopID,dimMemberIDHAVING SUM(AMT) > 300) tempwhere rank1 > 1 and rank2 >1group by deptno;
如果觉得《计算除去部门最高工资 和最低工资的平均工资（字节跳动面试）》对你有帮助，请点赞、收藏，并留下你的观点哦！
