قابلیت distinct در استفاده از LISTAGG در اوراکل 19c

مقدمهتابع LISTAGG در نسخه 11gr2 پایگاه داده اوراکل در خصوص تجمیع ساده String ها باهم معرفی گردید. در نسخه 12cR2 به این تابع قابلیت جدیدی در مواجه با رشته ای با طول زیاد (OverFlow Error ) اضافه گردید. حالا در نسخه 19C قابلیت حذف عبارت های تکراری (Distinct) در رشته ایجاد شده اضافه گردیده است که در این مستند مورد بررسی قرار می گیرد.آماده سازی محیط تست :-- DROP TABLE EMP PURGE;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));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;استفاده از تابع LISTAGG در حالت عادی :COLUMN employees FORMAT A40SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employeesFROM empGROUP BY deptnoORDER BY deptno;DEPTNO EMPLOYEES---------- ----------------------------------------10 CLARK,KING,MILLER20 ADAMS,FORD,JONES,SCOTT,SMITH30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD3 rows selected.SQLحالا فرض کنید عبارت های تکراری با دستورات زیر وارد جدول شود و سپس خروجی دستور بالا را مشاهده خواهیم کرد.INSERT INTO emp VALUES (9998,'MILLER','ANALYST',7782,to_date('23-1-1982','dd-mm-yyyy'),1600,NULL,10);INSERT INTO emp VALUES (9999,'MILLER','MANADER',7782,to_date('23-1-1982','dd-mm-yyyy'),1500,NULL,10);COMMIT;COLUMN employees FORMAT A40SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employeesFROM empGROUP BY deptnoORDER BY deptno;DEPTNO EMPLOYEES---------- ----------------------------------------10 CLARK,KING,MILLER,MILLER,MILLER20 ADAMS,FORD,JONES,SCOTT,SMITH30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD3 rows selected.SQL>همانطور که مشاهده می کنید در خروجی مربوط به آپارتمان 10 افراد با نام MILLER تکرار شده اند و اگر بخواهیم رشته ای از نام های منحصر به فرد در هر دپارتمان داشته باشیم چه باید کرد؟راهکارها قبل از نسخه 19c:تا قبل از نسخه 19c برای حل این مسئله راهکارهای متعددی می توان طراحی کرد که دو نمونه کد از انها را در ادامه مشاهده خواهید کرد.استفاده از ROW_NUMBER:COLUMN employees FORMAT A40SELECT e2.deptno, LISTAGG(e2.ename, ',') WITHIN GROUP (ORDER BY e2.ename) AS employeesFROM (SELECT e.*,ROW_NUMBER() OVER (PARTITION BY e.deptno, e.ename ORDER BY e.empno) AS myrankFROM emp e) e2WHERE e2.myrank = 1GROUP BY e2.deptnoORDER BY e2.deptno;DEPTNO EMPLOYEES---------- ----------------------------------------10 CLARK,KING,MILLER20 ADAMS,FORD,JONES,SCOTT,SMITH30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD3 rows selected.SQL>استفاده از تابع DISTINCT :COLUMN employees FORMAT A40SELECT e2.deptno, LISTAGG(e2.ename, ',') WITHIN GROUP (ORDER BY e2.ename) AS employeesFROM (SELECT DISTINCT e.deptno, e.enameFROM emp e) e2GROUP BY e2.deptnoORDER BY e2.deptno;DEPTNO EMPLOYEES---------- ----------------------------------------10 CLARK,KING,MILLER20 ADAMS,FORD,JONES,SCOTT,SMITH30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD3 rows selected.SQL>در نسخه 19c و استفاده از راهکار ایجاد شده:استفاده از عبارت در تابع LISTAGG در هنگام فراخوانی آنCOLUMN employees FORMAT A40SELECT deptno, LISTAGG(DISTINCT ename, ',') WITHIN GROUP (ORDER BY ename) AS employeesFROM empGROUP BY deptnoORDER BY deptno;DEPTNO EMPLOYEES---------- ----------------------------------------10 CLARK,KING,MILLER20 ADAMS,FORD,JONES,SCOTT,SMITH30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD3 rows selected.SQL>در نسخه 19c به طور پیش فرض ( اگر از عبارت DISTINCT ) استفاده نشود، از عبارت ALL استفاده می شود حتی اگر عبارت ALL تایپ نشده باشد.SELECT deptno, LISTAGG(ALL ename, ',') WITHIN GROUP (ORDER BY ename) AS employeesFROM empGROUP BY deptnoORDER BY deptno;DEPTNO EMPLOYEES---------- ----------------------------------------10 CLARK,KING,MILLER,MILLER,MILLER20 ADAMS,FORD,JONES,SCOTT,SMITH30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD3 rows selected.SQL>