QUALIFY介绍

1.开窗函数的过滤

1.1.测试表
drop table if exists emp;
CREATE TABLE emp (
      id int,
      name varchar(30) NOT NULL,
      gender varchar(30) NOT NULL,
      sex int NOT NULL,
      salary int NOT NULL,
      dept_id int NOT NULL
    );
insert into emp values(1,'项羽','元帅',1,9000,1);
insert into emp values(2,'关羽','大将',1,4000,2);
insert into emp values(3,'张飞','中将',1,3000,2);
insert into emp values(4,'唐僧','领队',1,800,3);
insert into emp values(5,'悟空','侍卫',1,700,3);
insert into emp values(6,'刘邦','元帅',1,6000,1);
select * from emp;
select id, name, dept_id, salary, row_number() over(partition by dept_id order by salary)  rwn from emp;

1.2.取每个部门薪水最少得员工信息
Where
select id,name,dept_id as f, salary, row_number() over(partition by dept_id order by salary) rwn from emp a where row_number() over(partition by dept_id order by salary)=1;
select * from (select id,name,dept_id as f, salary,row_number() over(partition by dept_id order by salary) rwn from emp )a where rwn=1;
Having
select id,name,dept_id as f,salary, row_number() over(partition by dept_id order by salary) rwn from emp a having row_number() over(partition by dept_id order by salary)=1;
Qualify
select id,name,dept_id as f, salary,row_number() over(partition by dept_id order by salary) rwn from emp a qualify row_number() over(partition by dept_id order by salary)=1;

2.QUALIFY的使用
2.1.对开窗函数过滤
select id,name,dept_id as f,salary, row_number() over(partition by dept_id order by salary) rwn from emp a qualify row_number() over(partition by dept_id order by salary)=1;
2.2.对字段或普通函数进行过滤
select id,name,dept_id as f,salary, row_number() over(partition by dept_id order by salary) rwn from emp a qualify dept_id=1;
select id,name,dept_id as f,salary, row_number() over(partition by dept_id order by salary) rwn from emp a qualify substr(dept_id,1,1)=1;
3.QUALIFY支持使用别名
Qualify
select id,name,dept_id as f, row_number() over(partition by dept_id order by salary) rwn from emp a qualify rwn=1;
select id,name,dept_id as f, row_number() over(partition by dept_id order by salary) rwn from emp a qualify f=1;
Where
select id,name,dept_id as f, row_number() over(partition by dept_id order by salary) rwn from emp where f=1;
Having
select dept_id,count(*) as cont from emp where id <10 group by dept_id having cont=2;

如果需要where和having子句中支持别名，可以使用set _t_gcluster_support_alias_dependent=1;

4.QUALIFY使用限制
4.1.QUALIFY 子句中 Search_condition 不支持 blob 和 long blob 类型的列存在。
drop table if exists emp1;
CREATE TABLE "emp1" (
 "id" int(11) NOT NULL,
 "name" varchar(30) NOT NULL,
 "gender" varchar(30) NOT NULL,
 "sex" int(11) NOT NULL,
 "salary" int(11) NOT NULL,
 "dept_id" blob  NOT NULL,
 "dept_name" blob NOT NULL,
 PRIMARY KEY ("id")
) ENGINE=EXPRESS DEFAULT CHARSET=utf8mb4 TABLESPACE='sys_tablespace' ;
insert into emp1 values(1,'项羽','元帅',1,9000,1,'楚汉');
insert into emp1 values(2,'关羽','大将',1,4000,2,'三国');
insert into emp1 values(3,'张飞','中将',1,3000,2,'三国');
insert into emp1 values(4,'唐僧','领队',1,800,3 ,'西游记');
insert into emp1 values(5,'悟空','侍卫',1,700,3 ,'西游记');
insert into emp1 values(6,'刘邦','元帅',1,6000,1,'楚汉');
select * from emp1;
desc emp1;
-- 非开窗函数的过滤
select id,name,dept_id as f,dept_name,row_number() over(partition by dept_id order by salary) rwn from emp1 a qualify dept_name='三国';
-- 开窗函数的过滤
select id,name,dept_id as f,dept_name,row_number() over(partition by dept_id order by salary) rwn from emp1 a qualify rwn='三国';

4.2.QUALIFY对普通字段、普通函数过滤时，查询投影列或qualify子句中必须包含开窗函数
select id,name.dept_id from emp qualify dept_id=1;
select id,name,dept_id, row_number() over(partition by dept_id order by salary) rwn from emp a  qualify dept_id=1;
select id,name,dept_id  from emp a qualify  dept_id=1 and row_number() over(partition by dept_id order by salary)=1;

4.3. 如果group by和QUALIFY同时出现，那么开窗函数中的列要求和group by中的列要一致。要符合严格的group by
select dept_id,count(*) from emp a group by dept_id qualify row_number() over(partition by dept_id order by id)=1;
select dept_id,count(*) from emp a group by dept_id, id qualify row_number() over(partition by dept_id order by id)=1;

4.4.QUALIFY不支持和having同时出现
select dept_id,id,row_number() over(partition by dept_id order by id) as rwn from emp group by dept_id,id having dept_id=1 qualify rwn=1;
select dept_id,id,row_number() over(partition by dept_id order by id) as rwn from emp group by dept_id,id qualify rwn=1 having dept_id=1 ;

4.5. QUALIFY和where在同一层同时使用时，where要排在前面过滤
select id,name,dept_id, row_number() over(partition by dept_id order by salary) rwn from emp a qualify id=1 where dept_id=1;
select id,name,dept_id, row_number() over(partition by dept_id order by salary) rwn from emp a where dept_id=1 qualify id=1;

4.6.窗口函数不支持用in/not in子查询过滤，但非开窗函数可以
窗口函数
select id,name,dept_id as f,row_number() over(partition by dept_id order by salary) rwn from emp a qualify rwn in (select a from t1) ;
select id,name,dept_id as f,row_number() over(partition by dept_id order by salary) rwn from emp a qualify row_number() over(partition by dept_id order by salary) in (select a from t1) ;
select id,name,dept_id as f,row_number() over(partition by dept_id order by salary) rwn from emp a qualify rwn not in (select a from t1) ;
select id,name,dept_id as f,row_number() over(partition by dept_id order by salary) rwn from emp a qualify row_number() over(partition by dept_id order by salary)  not in (select a from t1) ;
非窗口函数
select id,name,dept_id as f,row_number() over(partition by dept_id order by salary) rwn from emp a qualify  f in (select a from t1) ;
select id,name,row_number() over(partition by dept_id order by salary) rwn from emp a qualify  dept_id in (select a from t1) ;
select id,name,row_number() over(partition by dept_id order by salary) rwn from emp a qualify  substr(dept_id,1,1) in (select a from t1) ;
4.7.QUALIFY 子句中 Search_condition 不支持 or 条件连接in子查询。
select id,name,dept_id as f,row_number() over(partition by dept_id order by salary) rwn from emp a qualify rwn=1 or dept_id in (select distinct dept_id from emp where dept_id=1);

-- and连接in子查询
select id,name,dept_id as f,row_number() over(partition by dept_id order by salary) rwn from emp a qualify rwn=1 and dept_id in (select distinct dept_id from emp where dept_id=1);

-- or连接等值子查询
select id,name,dept_id as f,row_number() over(partition by dept_id order by salary) rwn from emp a qualify rwn=1 or dept_id = (select distinct dept_id from emp where dept_id=1);

-- or连接not in 子查询
select id,name,dept_id as f,row_number() over(partition by dept_id order by salary) rwn from emp a qualify rwn=1 or dept_id not in (select distinct dept_id from emp where dept_id=1);

-- or连接 in定值
select id,name,dept_id as f,row_number() over(partition by dept_id order by salary) rwn from emp a qualify rwn=1 or dept_id in (1,2);