استفاده از Alias ها در دستورات تجمیع و عبارت های Group by و Having در پایگاه داده Oracle 23c

از 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;

معرفی امکانات Oracle 23c

مشاهده مشکل :

در نسخه های قبلی پایگاه داده، ما مجبور شدیم ارجاعات کامل به تعاریف ستون ها را در بندهای 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>

منبع مستند

مقالات مرتبط:

انتقال Text-Based JSON به JSON Data Type: بررسی پیش از مهاجرت در پایگاه داده Oracle 23c بررسی SQL DOAMIN در Oracle 23c بررسی شرط IF NOT EXISTS و IF EXISTS در دستورات DDL در پایگاه داده Oracle 23C پشتیبانی از Materialized View برای ANSI JOINS در پایگاه داده Oracle 23c