از Oracle 23c به بعد می توانیم از نام مستعار ستون در بندهای GROUP BY و HAVING یا موقعیت ستون در شرط GROUP BY استفاده کنیم.
برای بررسی این موضوع، محیط آزمایش را آماده می کنیم.
drop table if exists emp purge;
drop table if exists dept purge;
create table dept (
deptno number(2) constraint pk_dept primary key,
dname varchar2(14),
loc varchar2(13)
) ;
create table emp (
empno number(4) constraint pk_emp primary key,
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2) constraint fk_deptno references dept
);
insert into dept values (10,’ACCOUNTING’,’NEW YORK’);
insert into dept values (20,’RESEARCH’,’DALLAS’);
insert into dept values (30,’SALES’,’CHICAGO’);
insert into dept values (40,’OPERATIONS’,’BOSTON’);
insert into emp values (7369,’SMITH’,’CLERK’,7902,to_date(’17-12-1980′,’dd-mm-yyyy’),800,null,20);
insert into emp values (7499,’ALLEN’,’SALESMAN’,7698,to_date(’20-2-1981′,’dd-mm-yyyy’),1600,300,30);
insert into emp values (7521,’WARD’,’SALESMAN’,7698,to_date(’22-2-1981′,’dd-mm-yyyy’),1250,500,30);
insert into emp values (7566,’JONES’,’MANAGER’,7839,to_date(‘2-4-1981′,’dd-mm-yyyy’),2975,null,20);
insert into emp values (7654,’MARTIN’,’SALESMAN’,7698,to_date(’28-9-1981′,’dd-mm-yyyy’),1250,1400,30);
insert into emp values (7698,’BLAKE’,’MANAGER’,7839,to_date(‘1-5-1981′,’dd-mm-yyyy’),2850,null,30);
insert into emp values (7782,’CLARK’,’MANAGER’,7839,to_date(‘9-6-1981′,’dd-mm-yyyy’),2450,null,10);
insert into emp values (7788,’SCOTT’,’ANALYST’,7566,to_date(’13-JUL-87′,’dd-mm-rr’)-85,3000,null,20);
insert into emp values (7839,’KING’,’PRESIDENT’,null,to_date(’17-11-1981′,’dd-mm-yyyy’),5000,null,10);
insert into emp values (7844,’TURNER’,’SALESMAN’,7698,to_date(‘8-9-1981′,’dd-mm-yyyy’),1500,0,30);
insert into emp values (7876,’ADAMS’,’CLERK’,7788,to_date(’13-JUL-87′, ‘dd-mm-rr’)-51,1100,null,20);
insert into emp values (7900,’JAMES’,’CLERK’,7698,to_date(‘3-12-1981′,’dd-mm-yyyy’),950,null,30);
insert into emp values (7902,’FORD’,’ANALYST’,7566,to_date(‘3-12-1981′,’dd-mm-yyyy’),3000,null,20);
insert into emp values (7934,’MILLER’,’CLERK’,7782,to_date(’23-1-1982′,’dd-mm-yyyy’),1300,null,10);
commit;
در نسخه های قبلی پایگاه داده، ما مجبور شدیم ارجاعات کامل به تعاریف ستون ها را در بندهای GROUP BY و HAVING تکرار کنیم.
در مثال زیر میخواهیم یک نسخه قالببندی شده از نام دپارتمان را ارائه کنیم و تعداد کارمندان بخشهایی را که بیش از ۳ کارمند دارند، دریافت کنیم. توجه داشته باشید که باید مرجع “initcap(d.dname)” را در بند GROUP BY و مرجع “count(*)” را در عبارت HAVING تکرار کنیم.
select initcap(d.dname) as department, count(*) as amount
from dept d
join emp e on d.deptno = e.deptno
group by initcap(d.dname)
having count(*) > 3;
DEPARTMENT AMOUNT
————– ———-
Research ۵
Sales ۶
SQL>
در این مورد، هر دو مرجع ستون بسیار کوچک هستند، اما در برخی موارد مانند توابع تحلیلی و عبارات موردی، آنها می توانند بسیار بزرگ باشند، که این تکرار را بسیار سخت می کند.
جهت دریافت خدمات مشاوره، آموزش و نگهداری پایگاه داده اوراکل با ما در ارتباط باشد
از Oracle 23c به بعد می توانیم از نام مستعار ستون در بندهای GROUP BY و HAVING استفاده کنیم. در این مثال، به جای تکرار مرجع “initcap(d.dname)” در بند GROUP BY، از نام مستعار ستون “department” استفاده می کنیم. به همین ترتیب، به جای تکرار مرجع “count(*)” در عبارت HAVING، از نام مستعار ستون “مقدار” استفاده می کنیم.
select initcap(d.dname) as department, count(*) as amount
from dept d
join emp e on d.deptno = e.deptno
group by department
having amount > 3;
DEPARTMENT AMOUNT
————– ———-
Research ۵
Sales ۶
SQL>
ما دیگر نیازی به تکرار تعاریف ستون های پیچیده در بندهای GROUP BY و HAVING نداریم.
از Oracle 23c به بعد می توانیم از موقعیت ستون در بند GROUP BY استفاده کنیم. این عملکرد به طور پیش فرض فعال نیست، بنابراین باید در سطح Session یا System فعال شود. در اینجا ما آن را برای Session خود فعال می کنیم.
alter session set group_by_position_enabled=true;
در این مثال، به جای تکرار مرجع “initcap(d.dname)” در بند GROUP BY، از موقعیت ستون “۱” استفاده می کنیم. ما به دلایل واضح نمیتوانیم از موقعیت ستون در عبارت HAVING استفاده کنیم، زیرا پایگاه داده نمیداند موقعیت ستون چیست و عدد تحت اللفظی چیست.
select initcap(d.dname) as department, count(*) as amount
from dept d
join emp e on d.deptno = e.deptno
group by 1
having amount > 3;
DEPARTMENT AMOUNT
————– ———-
Research ۵
Sales ۶
SQL>