计算除去部门最高工资，和最低工资的平均工资（字节跳动面试）

计算除去部门最高工资，和最低工资的平均工资（字节跳动面试）

/* emp 表: id 员工 id ，deptno 部门编号，salary 工资 核心是使用窗口函数降序和升序分别排一遍就取出了最高和最低 其他延展: ｛规则：去掉两头的极端值｝裁判最后计分、计算部门平均薪资、门店销售员的销售额等等场景 */

select  deptno
       ,avg(salary) as daily_salary_avg
from 
(
	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 rank2
	FROM  dw.fct_sales
	WHERE dimDateID =  '20170701' 
			and dimMemberID <> 0
	GROUP BY  dimShopID
	         ,dimMemberID
	HAVING SUM(AMT) > 300
) temp
where rank1 > 1 and rank2 >1
group by deptno;