مقدمه

تابع LISTAGG در نسخه 11gr2  پایگاه داده اوراکل در خصوص تجمیع ساده  String ها باهم معرفی گردید. در نسخه 12cR2  به این تابع قابلیت جدیدی در مواجه با رشته ای با طول زیاد (OverFlow Error )  اضافه گردید. حالا در نسخه 19C قابلیت حذف عبارت های تکراری (Distinct) در تابع LISTAGG دررشته ایجاد شده اضافه گردیده است که در این مستند مورد بررسی قرار می گیرد.

آماده سازی محیط تست :

— 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 A40

SELECT deptno, LISTAGG(ename, ‘,’) WITHIN GROUP (ORDER BY ename) AS employees

FROM   emp

GROUP BY deptno

ORDER BY deptno;

    DEPTNO EMPLOYEES

———- —————————————-

        10 CLARK,KING,MILLER

        20 ADAMS,FORD,JONES,SCOTT,SMITH

        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

3 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 A40

SELECT deptno, LISTAGG(ename, ‘,’) WITHIN GROUP (ORDER BY ename) AS employees

FROM   emp

GROUP BY deptno

ORDER BY deptno;

    DEPTNO EMPLOYEES

———- —————————————-

        10 CLARK,KING,MILLER,MILLER,MILLER

        20 ADAMS,FORD,JONES,SCOTT,SMITH

        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

3 rows selected.

SQL>

 همانطور که مشاهده می کنید در خروجی مربوط به آپارتمان 10 افراد با نام MILLER تکرار شده اند و اگر بخواهیم رشته ای از نام های منحصر به فرد در هر دپارتمان داشته باشیم چه باید کرد؟

راهکارها قبل از نسخه 19c:

تا قبل از نسخه 19c برای حل این مسئله راهکارهای متعددی می توان طراحی کرد که دو نمونه کد از انها را در ادامه مشاهده خواهید کرد.

استفاده از ROW_NUMBER:

COLUMN employees FORMAT A40

SELECT e2.deptno, LISTAGG(e2.ename, ‘,’) WITHIN GROUP (ORDER BY e2.ename) AS employees

FROM   (SELECT e.*,

               ROW_NUMBER() OVER (PARTITION BY e.deptno, e.ename ORDER BY e.empno) AS myrank

        FROM   emp e) e2

WHERE  e2.myrank = 1

GROUP BY e2.deptno

ORDER BY e2.deptno;

    DEPTNO EMPLOYEES

———- —————————————-

        10 CLARK,KING,MILLER

        20 ADAMS,FORD,JONES,SCOTT,SMITH

        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

3 rows selected.

SQL>

استفاده از تابع DISTINCT :

COLUMN employees FORMAT A40

SELECT e2.deptno, LISTAGG(e2.ename, ‘,’) WITHIN GROUP (ORDER BY e2.ename) AS employees

FROM   (SELECT DISTINCT e.deptno, e.ename

        FROM   emp e) e2

GROUP BY e2.deptno

ORDER BY e2.deptno;

    DEPTNO EMPLOYEES

———- —————————————-

        10 CLARK,KING,MILLER

        20 ADAMS,FORD,JONES,SCOTT,SMITH

        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

3 rows selected.

SQL>

در نسخه 19c  و استفاده از راهکار ایجاد شده:

 استفاده از عبارت در تابع  LISTAGG در هنگام فراخوانی آن

COLUMN employees FORMAT A40

SELECT deptno, LISTAGG(DISTINCT ename, ‘,’) WITHIN GROUP (ORDER BY ename) AS employees

FROM   emp

GROUP BY deptno

ORDER BY deptno;

    DEPTNO EMPLOYEES

———- —————————————-

        10 CLARK,KING,MILLER

        20 ADAMS,FORD,JONES,SCOTT,SMITH

        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

3 rows selected.

SQL>

در نسخه 19c به طور پیش فرض ( اگر از عبارت DISTINCT ) استفاده نشود، از عبارت ALL استفاده می شود حتی اگر عبارت ALL تایپ نشده باشد.

SELECT deptno, LISTAGG(ALL ename, ‘,’) WITHIN GROUP (ORDER BY ename) AS employees

FROM   emp

GROUP BY deptno

ORDER BY deptno;

    DEPTNO EMPLOYEES

———- —————————————-

        10 CLARK,KING,MILLER,MILLER,MILLER

        20 ADAMS,FORD,JONES,SCOTT,SMITH

        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

3 rows selected.

SQL>