QUALIFY介绍

1.qualify的作用

对开窗函数进行过滤，也可以对表字段、表达式进行过滤，同层可以使用别名
 

2.使用场景

2.1对开窗函数过滤
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)=1;

2.2对普通字段或表达式进行过滤
select id,name,dept_id as f, row_number() over(partition by dept_id order by salary) rwn from emp a
qualify dept_id=1;
 

3.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;
 

4.QUALIFY使用限制

4.1QUALIFY 子句中 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" int(11) 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;
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='三国';  -- 报错

4.2QUALIFY 子句中 Search_condition 不支持 or 条件连接子查询。
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_id=1 and dept_id in (select distinct dept_id from emp1 where dept_id=1);
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_id=1 or dept_id in (select distinct dept_id from emp1 where dept_id=2);  -- 报错

4.3qualify不支持和having同时出现
select dept_id,count(*) as dd from emp  group by dept_id having count(*)>1 qualify dept_id=1;
select dept_id,count(*) as dd from emp  qualify dept_id=1 group by dept_id having count(*)>1;  -- 报错

4.4qualify对普通字段、表达式过滤时，查询投影列中必须包含开窗函数
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;

4.5qualify和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;