DISTINCT Option for LISTAGG Aggregate

 

Category : Application Development_SQL

Oracle DB version : 19.3.0.0.0

 

تابع LISTAGG از ورژن 11gR2 به اوراکل اضافه شده است، و در ورژن های 12cR2 و 19c تغییراتی داشته است.

همانطور که در مثال زیر مشاهده می کنید، در خروجی تابع LISTAGG، مقادیر تکراری هم نمایش داده می شوند:

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

  FROM emp

GROUP BY deptno

ORDER BY deptno;

 

DEPTNO	EMPLYEES
10	CLARK,KING,MILLER,MILLER,MILLER
20	ADAMS,FORD,JONES,SCOTT,SMITH
30	ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

 

 

 

 

 

 

 

 

 

 

 

تا قبل از اوراکل 19c، برای حذف این مقادیر تکراری، راه حل های مختلفی وجود داشت که یکی از آنها استفاده از تابع ROW_NUMBER بود. ولی در اوراکل 19c، با اضافه شدن کلمه کلیدی DISTINCT به این تابع خیلی راحت میتوان مقادیر تکراری را از خروجی تابع LISTAGG حذف کرد.

 

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

FROM   emp

GROUP BY deptno

ORDER BY deptno;

 

DEPTNO	EMPLYEES
10	CLARK,KING,MILLER
20	ADAMS,FORD,JONES,SCOTT,SMITH
30	ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

 

 

 

 

 

بدست آوردن تعداد Transactionها در ثانیه بین دو snapshot

در دیتابیس اوراکل، تعداد Transactionها در هر ثانیه را، هم میتوان از AWR Report بدست آورد، هم با استفاده از کوئری. 

 

AWR Report:

 

 

 

 

 

 

 

 

 

Query:

WITH
    hist_snaps
    AS
        (SELECT instance_number,
                snap_id,
                ROUND (begin_interval_time, 'MI') begin_snap_time,
                ROUND (end_interval_time, 'MI') end_snap_time,
                  (  begin_interval_time
                   + 0
                   - LAG (begin_interval_time + 0)
                         OVER (PARTITION BY dbid, instance_number
                               ORDER BY snap_id))
                * 86400                              diff_time
           FROM dba_hist_snapshot),
    hist_stats
    AS
        (SELECT dbid,
                instance_number,
                snap_id,
                stat_name,
                  VALUE
                - LAG (VALUE)
                      OVER (PARTITION BY dbid, instance_number, stat_name
                            ORDER BY snap_id)    delta_value
           FROM dba_hist_sysstat
          WHERE stat_name IN ('user commits', 'user rollbacks'))
  SELECT sn.begin_snap_time,
         sn.end_snap_time,
         sn.instance_number,
         ROUND (SUM (delta_value) / 3600)     "TRANSACTIONS/S"
    FROM hist_snaps sn, hist_stats st
   WHERE st.instance_number = sn.instance_number
     AND st.snap_id = sn.snap_id
     AND sn.diff_time IS NOT NULL
     AND sn.instance_number = 2
     AND TO_CHAR (sn.end_snap_time, 'yyyy-mm-dd') = to_char(sysdate, 'yyyy-mm-dd')
GROUP BY sn.begin_snap_time, sn.end_snap_time, sn.instance_number
ORDER BY 1;

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Result Of above Query: