SQL KEEP 視窗函式等價改寫案例

小至尖尖發表於2024-05-30
SQL函式

一哥們出條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;

最終等價改寫的SQL，增加了分組欄位內有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;

差集比較後是等價的：

相關文章