[Oracle] 오라클 SQL 튜닝, 집계함수, 누적 구하기, JOIN, GROUP BY

2012년 1월에 팔린 상품의 일별 상품별 판매수량과 판매금액 및 각각의 랭킹 구하기


* 결과값


* 쿼리1

SELECT O.ORD_DT "판매일자", 

            I.ITEM_ID "상품ID",

            I.ITEM_NM "상품명",

            SUM(O.ORD_ITEM_QTY) "판매수량",

            SUM(O.ORD_ITEM_QTY*P.PRICE) "판매금액",

            RANK() OVER(PARTITION BY O.ORD_DT ORDER BY SUM(O.ORD_ITEM_QTY) DESC) "판매수량랭킹",

            RANK() OVER(PARTITION BY O.ORD_DT ORDER BY SUM(O.ORD_ITEM_QTY*P.PRICE) DESC

           "판매금액랭킹"

  FROM ORD_ITEM O  

   LEFT OUTER JOIN ITEM I

    ON O.ITEM_ID = I.ITEM_ID,

  (

    SELECT P.ITEM_ID,

                SUM(P.SALE_PRICE) PRICE

      FROM UITEM_PRICE P

    GROUP BY P.ITEM_ID

  ) P

  WHERE I.ITEM_ID = P.ITEM_ID

      AND O.ORD_DT BETWEEN '20120101' AND '20120131'

  GROUP BY O.ORD_DT, I.ITEM_ID, I.ITEM_NM

  ORDER BY O.ORD_DT, I.ITEM_ID;


* 실행계획

-----------------------------------------------------------------------------------------------------------------

| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

-----------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                     |                |      1 |        |   2852 |00:00:01.78 |   43431 |

|   1 |  SORT ORDER BY                       |                |      1 |    501 |   2852 |00:00:01.78 |   43431 |

|   2 |   WINDOW SORT                        |                |      1 |    501 |   2852 |00:00:01.78 |   43431 |

|   3 |    WINDOW SORT                       |                |      1 |    501 |   2852 |00:00:01.77 |   43431 |

|   4 |     HASH GROUP BY                    |                |      1 |    501 |   2852 |00:00:01.77 |   43431 |

|*  5 |      HASH JOIN                       |                |      1 |    535K|   1546K|00:00:01.28 |   43431 |

|   6 |       NESTED LOOPS                   |                |      1 |     92 |     92 |00:00:00.01 |      12 |

|   7 |        TABLE ACCESS FULL             | ITEM           |      1 |     92 |     92 |00:00:00.01 |       7 |

|   8 |        VIEW PUSHED PREDICATE         |                |     92 |      1 |     92 |00:00:00.01 |       5 |

|*  9 |         FILTER                       |                |     92 |        |     92 |00:00:00.01 |       5 |

|  10 |          SORT AGGREGATE              |                |     92 |      1 |     92 |00:00:00.01 |       5 |

|  11 |           TABLE ACCESS BY INDEX ROWID| UITEM_PRICE    |     92 |      1 |    130 |00:00:00.01 |       5 |

|* 12 |            INDEX RANGE SCAN          | UITEM_PRICE_PK |     92 |      1 |    130 |00:00:00.01 |       3 |

|  13 |       TABLE ACCESS BY INDEX ROWID    | ORD_ITEM       |      1 |    535K|   1546K|00:00:00.73 |   43419 |

|* 14 |        INDEX RANGE SCAN              | ORD_ITEM_X01   |      1 |    535K|   1546K|00:00:00.20 |    5815 |

-----------------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   5 - access("O"."ITEM_ID"="I"."ITEM_ID")

   9 - filter((COUNT(*)>0 AND '20120101'<='20120131'))

  12 - access("P"."ITEM_ID"="I"."ITEM_ID")

  14 - access("O"."ORD_DT">='20120101' AND "O"."ORD_DT"<='20120131')


※ JOIN 전에 상품+주문 테이블을 GROUP BY 해주는 게 더 효율적


* 쿼리2

SELECT O.ORD_DT "판매일자",

            O.ITEM_ID "상품ID",

            O.ITEM_NM "상품명",

            O.QTY "판매수량",

            P.PRICE*O.QTY "판매금액",

            RANK() OVER(PARTITION BY ORD_DT ORDER BY O.QTY DESC) "판매수량랭킹",

            RANK() OVER(PARTITION BY ORD_DT ORDER BY P.PRICE*O.QTY DESC) "판매금액랭킹"

  FROM

      (

            SELECT O.ORD_DT, 

                        I.ITEM_ID,

                  I.ITEM_NM,

                  SUM(O.ORD_ITEM_QTY) QTY

        FROM ORD_ITEM O  

         LEFT OUTER JOIN ITEM I

           ON O.ITEM_ID = I.ITEM_ID

    WHERE O.ORD_DT BETWEEN '20120101' AND '20120131'

    GROUP BY O.ORD_DT, I.ITEM_ID, I.ITEM_NM

  ) O,

  (

            SELECT P.ITEM_ID, 

                       SUM(P.SALE_PRICE) PRICE

              FROM UITEM_PRICE P

            GROUP BY P.ITEM_ID

       ) P  

WHERE O.ITEM_ID = P.ITEM_ID

ORDER BY O.ORD_DT, O.ITEM_ID;


* 실행계획

---------------------------------------------------------------------------------------------------------------

| Id  | Operation                          | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

---------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                   |                |      1 |        |   2852 |00:00:01.70 |   43429 |

|   1 |  SORT ORDER BY                     |                |      1 |  46047 |   2852 |00:00:01.70 |   43429 |

|   2 |   WINDOW SORT                      |                |      1 |  46047 |   2852 |00:00:01.69 |   43429 |

|   3 |    WINDOW SORT                     |                |      1 |  46047 |   2852 |00:00:01.69 |   43429 |

|*  4 |     HASH JOIN                      |                |      1 |  46047 |   2852 |00:00:01.69 |   43429 |

|   5 |      VIEW                          |                |      1 |     92 |     92 |00:00:00.01 |       3 |

|   6 |       HASH GROUP BY                |                |      1 |     92 |     92 |00:00:00.01 |       3 |

|   7 |        TABLE ACCESS BY INDEX ROWID | UITEM_PRICE    |      1 |    130 |    130 |00:00:00.01 |       3 |

|   8 |         INDEX FULL SCAN            | UITEM_PRICE_PK |      1 |    130 |    130 |00:00:00.01 |       1 |

|   9 |      VIEW                          |                |      1 |  46047 |   2852 |00:00:01.68 |   43426 |

|  10 |       HASH GROUP BY                |                |      1 |  46047 |   2852 |00:00:01.68 |   43426 |

|* 11 |        HASH JOIN RIGHT OUTER       |                |      1 |    535K|   1546K|00:00:01.25 |   43426 |

|  12 |         TABLE ACCESS FULL          | ITEM           |      1 |     92 |     92 |00:00:00.01 |       7 |

|  13 |         TABLE ACCESS BY INDEX ROWID| ORD_ITEM       |      1 |    535K|   1546K|00:00:00.73 |   43419 |

|* 14 |          INDEX RANGE SCAN          | ORD_ITEM_X01   |      1 |    535K|   1546K|00:00:00.20 |    5815 |

---------------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   4 - access("O"."ITEM_ID"="P"."ITEM_ID")

  11 - access("O"."ITEM_ID"="I"."ITEM_ID")

  14 - access("O"."ORD_DT">='20120101' AND "O"."ORD_DT"<='20120131')


 그닥 극적인 효과는 없지만 조회하는 ORD_DT 범위가 넓어질수록 효과가 나타남


2012년 1월에 팔린 상품의 일별 매장별 판매수량과 판매금액 및 누적 판매금액 구하기


* 결과값


* 쿼리

SELECT O.ORD_DT "판매일시", 

            MAX(O.BRANCH_CD) "매장코드",

            SUM(I.ORD_ITEM_QTY) "판매수량",

            SUM(I.ORD_ITEM_QTY*P.PRICE) "판매금액",

            SUM(SUM(I.ORD_ITEM_QTY*P.PRICE)) OVER(ORDER BY O.ORD_DT 

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) "누적"

  FROM ORD O,

           ORD_ITEM I,

           (

                  SELECT P.ITEM_ID,

                              SUM(P.SALE_PRICE) PRICE

                    FROM UITEM_PRICE P

                  GROUP BY P.ITEM_ID

          ) P

WHERE I.ITEM_ID = P.ITEM_ID

    AND O.ORD_NO = I.ORD_NO

    AND I.ITEM_ID = P.ITEM_ID

    AND O.ORD_DT BETWEEN '20120101' AND '20120131'

    AND O.BRANCH_CD = :BRANCH_CD

GROUP BY O.ORD_DT, O.BRANCH_CD

ORDER BY O.ORD_DT;


* 실행계획

-------------------------------------------------------------------------------------------------------------

| Id  | Operation                        | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

-------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                 |                |      1 |        |     31 |00:00:00.33 |   25978 |

|   1 |  WINDOW BUFFER                   |                |      1 |      3 |     31 |00:00:00.33 |   25978 |

|   2 |   SORT GROUP BY                  |                |      1 |      3 |     31 |00:00:00.33 |   25978 |

|*  3 |    HASH JOIN                     |                |      1 |    749 |   7683 |00:00:00.32 |   25978 |

|   4 |     VIEW                         |                |      1 |     92 |     92 |00:00:00.01 |       3 |

|   5 |      HASH GROUP BY               |                |      1 |     92 |     92 |00:00:00.01 |       3 |

|   6 |       TABLE ACCESS BY INDEX ROWID| UITEM_PRICE    |      1 |    130 |    130 |00:00:00.01 |       3 |

|   7 |        INDEX FULL SCAN           | UITEM_PRICE_PK |      1 |    130 |    130 |00:00:00.01 |       1 |

|   8 |     NESTED LOOPS                 |                |      1 |        |   7683 |00:00:00.32 |   25975 |

|   9 |      NESTED LOOPS                |                |      1 |    749 |   7683 |00:00:00.31 |   22873 |

|* 10 |       TABLE ACCESS BY INDEX ROWID| ORD            |      1 |    305 |   3134 |00:00:00.29 |   16579 |

|* 11 |        INDEX RANGE SCAN          | ORD_X01        |      1 |  62331 |    625K|00:00:00.08 |    2354 |

|* 12 |       INDEX RANGE SCAN           | ORD_ITEM_PK    |   3134 |      2 |   7683 |00:00:00.01 |    6294 |

|  13 |      TABLE ACCESS BY INDEX ROWID | ORD_ITEM       |   7683 |      2 |   7683 |00:00:00.01 |    3102 |

-------------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   3 - access("I"."ITEM_ID"="P"."ITEM_ID")

  10 - filter("O"."BRANCH_CD"=:BRANCH_CD)

  11 - access("O"."ORD_DT">='20120101' AND "O"."ORD_DT"<='20120131')

  12 - access("O"."ORD_NO"="I"."ORD_NO")


※  Index에서 Table Random Access하는 과정(11→10)에서 비효율이 발생 했으므로 Index 조정이 필요

by 정미나 2018.01.14 16:42