Oracle 统计分析函数使用示例

本是古典何须时尚 2021-12-05 22:17 337阅读 0赞

**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;