SQL KEEP 窗口函数等价改写案例
一哥们出条sql题给我玩,将下面sql改成不使用keep分析函数的写法。
select deptno, ename, sal, hiredate, min(sal) keep(dense_rank first order by hiredate) over(partition by deptno) min_sal, max(sal) keep(dense_rank last order by hiredate) over(partition by deptno) max_sal from emp;
我第一次改错了,被这哥们喷菜鸡,我草。
-- 错误等价改写,逻辑不等价 with x as ( select e1.deptno, e1.ename, e1.sal, e1.hiredate, row_number() over (partition by DEPTNO order by HIREDATE) rn_first, row_number() over (partition by DEPTNO order by HIREDATE DESC) rn_last from EMP e1) select e.deptno, e.ename, e.sal, e.hiredate, x1.SAL, x2.SAL from emp e inner join x x1 on e.DEPTNO = x1.DEPTNO and x1.rn_first = 1 inner join x x2 on e.DEPTNO = x2.DEPTNO and x2.rn_last = 1;
我换了张数据量更大点的表测试下,发现上面改写是逻辑有问题,如果同一个组内有相同日期的,分组字段内有NULL值的,确实会导致SQL结果集不一致。
-- 将EMP表替换成EMPLOYEES,如果使用上面等价改写就错误了。 select DEPARTMENT_ID, FIRST_NAME, SALARY, HIRE_DATE, min(SALARY) keep(dense_rank first order by HIRE_DATE) over(partition by DEPARTMENT_ID) min_sal, max(SALARY) keep(dense_rank last order by HIRE_DATE) over(partition by DEPARTMENT_ID) max_sal from EMPLOYEES;
等价改写方案一、标量子查询改写,增加了分组字段内有NULL值的逻辑,和处理一个组内有相同日期的逻辑。
select e.DEPARTMENT_ID, e.FIRST_NAME, e.SALARY, e.HIRE_DATE, (select MIN_SALARY from (select DEPARTMENT_ID, MIN(SALARY) MIN_SALARY from (select DEPARTMENT_ID, SALARY, HIRE_DATE, dense_rank() over (PARTITION BY DEPARTMENT_ID ORDER BY HIRE_DATE) RN from EMPLOYEES) WHERE RN = 1 GROUP BY DEPARTMENT_ID) e1 where case when e1.DEPARTMENT_ID is null then 99999 else e1.DEPARTMENT_ID end = case when e.DEPARTMENT_ID is null then 99999 else e.DEPARTMENT_ID end) a_min, (select MAX_SALARY from (select DEPARTMENT_ID, MAX(SALARY) MAX_SALARY from (select DEPARTMENT_ID, SALARY, HIRE_DATE, dense_rank() over (PARTITION BY DEPARTMENT_ID ORDER BY HIRE_DATE DESC) RN from EMPLOYEES) WHERE RN = 1 GROUP BY DEPARTMENT_ID) e1 where case when e1.DEPARTMENT_ID is null then 99999 else e1.DEPARTMENT_ID end = case when e.DEPARTMENT_ID is null then 99999 else e.DEPARTMENT_ID end ) a_max FROM EMPLOYEES e;
差集比较后是等价的:
等价改写方案二、使用 first_value、last_value 窗口函数实现
select DEPARTMENT_ID, FIRST_NAME, SALARY, HIRE_DATE, first_value(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY HIRE_DATE range between unbounded preceding and unbounded following) AS min_sal, last_value(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY HIRE_DATE range between unbounded preceding and unbounded following ) AS max_sal from EMPLOYEES;
差集比较后也是是等价的:
等价改写方案三、使用 LEFT JOIN + 窗口函数实现
select a.department_id, a.first_name, a.salary, b.min_sal, b.max_sal from EMPLOYEES a left join (select min(case when rn1 = 1 then salary end) min_sal, max(case when rn2 = 1 then salary end) max_sal, department_id from (select salary, department_id, dense_rank() over (partition by department_id order by hire_date) rn1, dense_rank() over (partition by department_id order by hire_date desc) rn2 from EMPLOYEES) group by department_id) b on decode(a.department_id, null, 99999999, a.department_id) = decode(b.department_id, null, 99999999, b.department_id);
这个也是等价的,偷懒就不放对比差集了。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· Deepseek官网太卡,教你白嫖阿里云的Deepseek-R1满血版
· 2分钟学会 DeepSeek API,竟然比官方更好用!
· .NET 使用 DeepSeek R1 开发智能 AI 客户端
· DeepSeek本地性能调优
· 一文掌握DeepSeek本地部署+Page Assist浏览器插件+C#接口调用+局域网访问!全攻略