쿼리 최적화 문의드립니다 (union all) 0 2 1,170

by tae [SQL Query] union all [2018.10.18 17:56:59]


SQL.txt (29,282Bytes)

SQL 작성을 했는데 여러가지 값을 붙이다 보니까 과도한 union all로 

가독성이 많이 떨어진 쿼리입니다.ㅜ

추가 데이터가 있을때마다 반복적으로 union all을 붙이는 건 비효율적일 거 같은데

좋은 방법이 있을지 조언 부탁드립니다.

(관련 내용 첨부드립니다.)

by 마농 [2018.10.19 15:47:27]
SELECT Market_Code
     , Original_Order_Amt_USD
     , Shipped_Amt_USD_Total
     , Plan_to_Ship_Amt_USD
     , Shipped_Amt_USD_Total / (Original_Order_Amt_USD - Original_Order_Amt_USD) * 100 AS Shipped_Percentage_Total
     , Invoiced_Amt_USD
     , Plan_to_Invoice_Order_Amt_USD_Tot
     , Release_Plan_to_Invoice_Order_Amt_USD_Tot
     , Release_Shipped_Amt_USD_Total
     , CASE WHEN Release_Plan_to_Invoice_Order_Amt_USD_Tot = 0 THEN 0
            ELSE Shipped_Amt_USD_Total / Release_Plan_to_Invoice_Order_Amt_USD_Tot
        END AS Release_Shipped_Percentage
     , Plan_to_Invoice_Order_Amt_USD_Outstanding
     , Plan_to_Invoice_Order_Amt_USD_Overdue
  FROM (SELECT Market_Code
             , SUM(Original_Order_Amt_USD        * price) AS Original_Order_Amt_USD
             , SUM(Shipped_Amt_USD_Total         * price) AS Shipped_Amt_USD_Total
             , SUM(Plan_to_Ship_Amt_USD          * price) AS Plan_to_Ship_Amt_USD
             , SUM(Plan_to_Invoice_Order_Amt_USD * price) AS Plan_to_Invoice_Order_Amt_USD_Tot
             , SUM(CASE WHEN Status = '1' AND dt1 < dt2 THEN Plan_to_Invoice_Order_Amt_USD END * price) AS Plan_to_Invoice_Order_Amt_USD_Overdue
             , SUM(CASE WHEN Status = '1' AND dt1 > dt2 THEN Plan_to_Invoice_Order_Amt_USD END * price) AS Plan_to_Invoice_Order_Amt_USD_Outstanding
             , SUM(CASE WHEN Status = '1'               THEN Plan_to_Invoice_Order_Amt_USD END * price) AS Release_Plan_to_Invoice_Order_Amt_USD_Tot
             , SUM(CASE WHEN Status = '1'               THEN Shipped_Amt_USD_Total         END * price) AS Release_Shipped_Amt_USD_Total
          FROM (SELECT CASE
                            CASE WHEN SUBSTRING(DIM_IN.[Dimension Value Code], 1, 2) = 'FM' THEN 'FM'
                                 ELSE SUBSTRING(DIM_IN.[Dimension Value Code], 1, 1) END
                       WHEN 'A'  THEN 'Alternative Energy'
                       WHEN 'E'  THEN 'Others - Non-Core'
                       WHEN 'FM' THEN 'Magna Fluids'
                       WHEN 'F'  THEN 'Industrial MRO - Fluids'
                       WHEN 'H'  THEN 'Heavy Industries'
                       WHEN 'I'  THEN 'Infrastructure'
                       WHEN 'M'  THEN 'Marine'
                       WHEN 'O'  THEN 'OEM (Electronics/Appliances)'
                       WHEN 'P'  THEN 'Industrial MRO - Polymers'
                       WHEN 'T'  THEN 'Transportation'
                       ELSE '--None--' END AS Market_Code
                     , SL.QUANTITY                             AS Original_Order_Amt_USD
                     , SL.[QTY_ SHIPPED (BASE)]                AS Shipped_Amt_USD_Total
                     , SL.[QTY_ TO SHIP]                       AS Plan_to_Ship_Amt_USD
                     , SL.[Qty_ Invoiced (Base)]               AS Invoiced_Amt_USD
                     , SL.QUANTITY - SL.[Qty_ Invoiced (Base)] AS Plan_to_Invoice_Order_Amt_USD
                     , SL.[UNIT PRICE]
                     * CASE WHEN SH.[Currency Code] = '' THEN PEG.[EXCHANGE RATE] ELSE 1 END AS price
                     , Status
                     , CONVERT(VARCHAR(10), SH.[Requested Delivery Date], 23) dt1
                     , CONVERT(VARCHAR(10), getdate()                   , 23) dt2
                  FROM [ITW].[dbo].[ITW PP&F KOREA$SALES HEADER] AS SH
                 INNER JOIN [ITW].[dbo].[ITW PP&F KOREA$SALES LINE] AS SL
                    ON SH.NO_ = SL.[DOCUMENT NO_]
                  LEFT OUTER JOIN [ITW].[dbo].[ITW PP&F KOREA$ITEM] AS IT
                    ON SL.NO_ = IT.NO_
                  LEFT OUTER JOIN [ITW].[dbo].[ITW PP&F KOREA$DOCUMENT DIMENSION] AS DIM_IN
                    ON DIM_IN.[DOCUMENT NO_]   = SL.[DOCUMENT NO_]
                   AND DIM_IN.[LINE NO_]       = SL.[LINE NO_]
                   AND DIM_IN.[DIMENSION CODE] = 'INDUSTRY'
                 INNER JOIN [ITW].[dbo].[ITW PP&F KOREA$PEGGED RATE] AS PEG
                    ON PEG.YEAR = SUBSTRING(CONVERT(VARCHAR, SH.[Posting Date], 112), 1, 4)
                 WHERE SH.[DOCUMENT TYPE] = '1'
                   AND SL.[Quantity (Base)] > SL.[Qty_ Invoiced (Base)]
                   AND sh.No_ NOT IN ('SO013002046', 'SO013000387', 'SO013000810')
                ) a
         GROUP BY Market_Code
        ) a
;

 


by tae [2018.10.19 17:30:19]

정말 고맙습니다. 많은 걸 배우네요 하나하나 분석해 보며 공부하겠습니다.!

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입