概要描述

遇到有客户反馈，某个包含窗口函数的sql语句，多次执行的结果不固定，本文给出解释说明。

原因是，分组字段 和 排序字段 如果有重复数据的话，无法确保rn=1的值是固定的。

详细说明

--创建一张3分桶的orc事务表
DROP TABLE IF EXISTS EMP;
CREATE TABLE EMP(
       EMPNO int,
       ENAME string,
       JOB string,
       MGR INT,
       HIREDATE DATE,
       SAL INT,
       COMM INT,
       DEPTNO INT
)CLUSTERED BY (empno) INTO 3 BUCKETS 
STORED AS ORC_TRANSACTION;

--插入4条数据
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,tdh_todate('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,tdh_todate('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,tdh_todate('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,tdh_todate('8-9-1981','dd-mm-yyyy'),1500,0,30);

执行如下sql语句，可以看到每次执行的结果并不是稳定的，ename有时是WARD有时是MARTIN。

select * from
(
    select
    ename,job, sal,
    row_number() over (partition by job order by sal ASC ) as rn from EMP
    WHERE job ='SALESMAN'
)
where rn =1;

原因就是 符合开窗函数 partition by job order by sal 的分组和排序中有2条数据，所以在外层 where rn=1 时 就无法唯一确定返回哪条数据。