Oracle 统计分析函数使用示例

1.使用  listagg() WITHIN GROUP ()  将多行字段合并成一行

--LISTAGG() WITHIN GROUP()函数的使用
SELECT A.ID                       AS "ID",
       A.SECURITY_CODE            AS "SECURITYCODE",
       A.SECURITY_CODE_CHECK_FLAG AS "SECCODECHECKFLAG",
       A.CUSTOMER_CODE            AS "CUSTOMERCODE",
       A.REDCODE                  AS "REDCODE",
       A.OPENID,
       A.SCAN_ID                  AS "RPREDCODESCAN",
       A.APPROVAL_TYPE            AS "APPROVALTYPE",
       A.STATE                    AS "STATE",
       A.FIRST_REVIEWER           AS "FIRSTREVIEWER",
       A.FIRST_LIMIT_DATE         AS "FIRSTLIMITDATE",
       A.FIRST_HANDLE_DATE        AS "FIRSTHANDLEDATE",
       A.FIRST_OVERTIME           AS "FIRSTOVERTIME",
       A.FIRST_HANDLE_BY          AS "FIRSTHANDLEBY",
       /*A.FIRST_HANDLER_NAME AS "FIRSTHANDLERNAME",*/
       A.FIRST_OPINION      AS "FIRSTOPINION",
       A.SECOND_REVIEWER    AS "SECONDREVIEWER",
       A.SECOND_LIMIT_DATE  AS "SECONDLIMITDATE",
       A.SECOND_HANDLE_DATE AS "SECONDHANDLEDATE",
       A.SECOND_OVERTIME    AS "SECONDOVERTIME",
       A.SECOND_HANDLE_BY   AS "SECONDHANDLEBY",
       /* A.SECOND_HANDLER_NAME AS "SECONDHANDLERNAME",*/
       A.SECOND_OPINION      AS "SECONDOPINION",
       A.FEEDBACK_ID         AS "FEEDBACK.ID",
       A.PROC_INS_ID         AS "PROCINSID",
       A.CREATE_DATE         AS "CREATEDATE",
       A.CREATE_BY           AS "CREATEBY.ID",
       A.UPDATE_BY           AS "UPDATEBY.ID",
       A.UPDATE_DATE         AS "UPDATEDATE",
       A.DEL_FLAG            AS "DELFLAG",
       A.REMARKS             AS "REMARKS",
       B.USER_NAME           AS "FEEDBACK.USERNAME",
       B.MOBILE              AS "FEEDBACK.MOBILE",
       B.SELLER_NAME         AS "FEEDBACK.SELLERNAME",
       B.FANGWEI_LABEL_PHOTO AS "FEEDBACK.FANGWEILABELPHOTO",
       B.UPLOAD_PHOTO_TWO    AS "FEEDBACK.UPLOADPHOTOTWO",
       B.UPLOAD_PHOTO_THREE  AS "FEEDBACK.UPLOADPHOTOTHREE",
       C.NICK_NAME           AS "FEEDBACK.FOCUSUSER.NICKNAME",
       C.HEAHIMGURL          AS "FEEDBACK.FOCUSUSER.HEAHIMGURL",
       D.UPDATE_DATE         AS "RPREDCODESCAN.UPDATEDATE",
       D.ADDR                AS "RPREDCODESCAN.ADDR",
       D.PROVINCEVAL         AS "RPREDCODESCAN.PROVINCEVAL",
       D.CITYVAL             AS "RPREDCODESCAN.CITYVAL",
       E.MATERIAL_CODE       AS "MATERIAL.MATERIALCODE",
       E.MATERIAL_NAME       AS "MATERIAL.MATERIALNAME",
       G.NAME                AS "CUSTOMERNAME",
       M.CURRENTTODO         AS "CURRENTTODO"
  FROM RP_APPROVAL_PROCESS A
  LEFT JOIN RP_FOCUS_USER C
    ON A.OPENID = C.OPEN_ID
  LEFT JOIN MATERIAL E
    ON A.SECURITY_CODE = E.W_ID
  LEFT JOIN BP_DEALER_RP G
    ON A.CUSTOMER_CODE = G.DEAL_CODE, RP_APPROVAL_FEEDBACK B,
 RP_REDCODE_SCAN D, (SELECT T.ID,
               LISTAGG(T.USER_NAME, ',') WITHIN GROUP(ORDER BY T.ID) CURRENTTODO
          FROM (SELECT I.USER_NAME, P.ID
                
                  FROM RP_APPROVAL_PROCESS P
                  LEFT JOIN ACT_RU_TASK H
                    ON P.PROC_INS_ID = H.PROC_INST_ID_
                  LEFT JOIN RP_USER_REGISTER I
                    ON I.PHONE_NUMBER = H.ASSIGNEE_
                 ORDER BY P.ID DESC) T
         GROUP BY T.ID) M
 WHERE M.ID = A.ID
   AND A.FEEDBACK_ID = B.ID
   AND A.SCAN_ID = D.ID;

SELECT DISTINCT (REORDER(P_SRC_STR => T.VENDOR_CODES)) GS_NAME_PATH,
                T.VEN_CNT
  FROM (SELECT PN.GS_NAME_PATH || '@' || LISTAGG(PN.VENDOR_CODE, '@') WITHIN GROUP(ORDER BY PN.VENDOR_CODE) VENDOR_CODES,
               PN.VEN_CNT + 5 VEN_CNT
          FROM PV_NCAL PN
         WHERE PN.VEN_CNT > 1
         GROUP BY PN.GS_NAME_PATH || '@', PN.VEN_CNT) T;

2.lead() over()分析函数的用法总结（查询当前行向下偏移n行对应的结果）

--lead() over()分析函数的用法总结
SELECT *
  FROM (SELECT (T1.NEXT_RECORD_START_DATE - T1.END_DATE) DATE_DIFF,
               T1.NEXT_RECORD_END_DATE,
               T1.NEXT_RECORD_START_DATE,
               T1.END_DATE
          FROM (SELECT T.START_DATE,
                       T.END_DATE,
                       LEAD(T.START_DATE,
                            1,
                            TO_DATE('2019-07-15', 'YYYY-MM-DD')) OVER(ORDER BY T.START_DATE) NEXT_RECORD_START_DATE,
                       LEAD(T.END_DATE,
                            1,
                            TO_DATE('2019-07-15', 'YYYY-MM-DD')) OVER(ORDER BY T.START_DATE) NEXT_RECORD_END_DATE
                  FROM WORK_TIME_RANGE T
                 WHERE T.ID = 0
                   AND T.END_DATE IS NOT NULL
                 ORDER BY T.START_DATE) T1) T2
 WHERE T2.NEXT_RECORD_START_DATE IS NULL
    OR T2.DATE_DIFF > 1;

3.lag() over()分析函数的用法总结（查询当前行向上偏移n行对应的结果）

SELECT *
  FROM (SELECT (T1.NEXT_RECORD_START_DATE - T1.END_DATE) DATE_DIFF,
               T1.NEXT_RECORD_END_DATE,
               T1.NEXT_RECORD_START_DATE,
               T1.END_DATE
          FROM (SELECT T.START_DATE, T.END_DATE, lag(T.START_DATE, 1) OVER(ORDER BY T.START_DATE) NEXT_RECORD_START_DATE,
               LEAD(T.END_DATE, 1) OVER(ORDER BY T.START_DATE) NEXT_RECORD_END_DATE
          FROM WORK_TIME_RANGE T
         WHERE T.ID = 0
           AND T.END_DATE IS NOT NULL
         ORDER BY T.START_DATE) T1) T2
 WHERE T2.NEXT_RECORD_START_DATE IS NULL
    OR T2.DATE_DIFF > 1;

4.count(field) over(partition by ...)分析函数的用法总结（展示每一行中，根据VENDOR_CODE分组的数量）

--count(field) over(partition by ...)分析函数的用法总结（展示每一行中，根据VENDOR_CODE分组的数量）
	SELECT A.PROJECT_CODE,
            TO_NUMBER(A.VENDOR_CODE),
            A.VEN_CNT,
            COUNT(*) OVER(PARTITION BY PROJECT_CODE) PRO_CNT,
            VENDOR_NAME
       FROM (SELECT PROJECT_CODE,
                    VENDOR_CODE,
                    COUNT(*) OVER(PARTITION BY VENDOR_CODE) VEN_CNT,
                    VENDOR_NAME
               FROM (SELECT DISTINCT * FROM PROJECT_VENDOR)) A
      WHERE VEN_CNT > 4;