데이터베이스 질문과 답변
SQL 작성을 했는데 여러가지 값을 붙이다 보니까 과도한 union all로
가독성이 많이 떨어진 쿼리입니다.ㅜ
추가 데이터가 있을때마다 반복적으로 union all을 붙이는 건 비효율적일 거 같은데
좋은 방법이 있을지 조언 부탁드립니다.
(관련 내용 첨부드립니다.)
by 마농
[2018.10.19 15:47:27]
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 | 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]
정말 고맙습니다. 많은 걸 배우네요 하나하나 분석해 보며 공부하겠습니다.!
) 버튼을 클릭하여 작성 하시면 됩니다.