꿈꾸는 개발자, DBA 커뮤니티 구루비

LAG 함수를 이용한 수량 차감 로직 질문

by 잭키올 [SQL Query] [2017.12.29 05:21:53]

1313.PNG (46,436Bytes)

1414.PNG (31,286Bytes)

안녕하세요.

기준에 맞추어 수량을 차감하는 로직을 세우려고 합니다.
제가 만든 쿼리는 While 문으로 계속 수량을 Update 하면서 차감하는 로직을 세웠는데요.

이 방법보다 Lag 함수를 이용하여 만들면 더 편하지 않을까 생각이 됩니다.
Lag 함수가 익숙치 않아서 그런지 잘 사용을 못하겠어서 고수님들께 질문을 드립니다.

DROP TABLE PRAC.DBO.TB_SCP_DMD
DROP TABLE PRAC.DBO.TB_SCP_RST_RTF
 
CREATE TABLE PRAC.DBO.TB_SCP_DMD
( SALES_TEAM_CD  NVARCHAR(50)
, CNTRY_CD       NVARCHAR(50)
, PORT_CD      NVARCHAR(50)
, ORD_NO       NVARCHAR(50)
, ORD_QTY      INT
, YYYY         NVARCHAR(4)
, MM         NVARCHAR(2)
, WK         NVARCHAR(2)
)
CREATE TABLE PRAC.DBO.TB_SCP_RST_RTF
( SALES_TEAM_CD  NVARCHAR(50)
, CNTRY_CD       NVARCHAR(50)
, PORT_CD      NVARCHAR(50)
, RTF_QTY      INT
, YYYY         NVARCHAR(4)
, MM         NVARCHAR(2)
, WK         NVARCHAR(2)
)
 

 
INSERT INTO PRAC.DBO.TB_SCP_DMD
SELECT 'JP' AS [SALES_TEAM_CD], 'JP' AS [CNTRY_CD], 'HKT' AS [PORT_CD], '000001' AS [ORD_NO], '560' AS [ORD_QTY], '2017' AS [YYYY], '11' AS [MM], '44' AS [WK]   UNION ALL
SELECT 'JP' AS [SALES_TEAM_CD], 'JP' AS [CNTRY_CD], 'HKT' AS [PORT_CD], '000002' AS [ORD_NO], '640' AS [ORD_QTY], '2017' AS [YYYY], '11' AS [MM], '45' AS [WK]   UNION ALL
SELECT 'JP' AS [SALES_TEAM_CD], 'JP' AS [CNTRY_CD], 'HKT' AS [PORT_CD], '000003' AS [ORD_NO], '510' AS [ORD_QTY], '2017' AS [YYYY], '11' AS [MM], '46' AS [WK] UNION ALL
SELECT 'JP' AS [SALES_TEAM_CD], 'JP' AS [CNTRY_CD], 'HKT' AS [PORT_CD], '000004' AS [ORD_NO], '230' AS [ORD_QTY], '2017' AS [YYYY], '11' AS [MM], '47' AS [WK] UNION ALL
SELECT 'JP' AS [SALES_TEAM_CD], 'JP' AS [CNTRY_CD], 'HKT' AS [PORT_CD], '000005' AS [ORD_NO], '450' AS [ORD_QTY], '2017' AS [YYYY], '12' AS [MM], '48' AS [WK] UNION ALL
SELECT 'JP' AS [SALES_TEAM_CD], 'JP' AS [CNTRY_CD], 'FLO' AS [PORT_CD], '000006' AS [ORD_NO], '100' AS [ORD_QTY], '2017' AS [YYYY], '12' AS [MM], '49' AS [WK] UNION ALL
SELECT 'US' AS [SALES_TEAM_CD], 'US' AS [CNTRY_CD], 'NYK' AS [PORT_CD], '000007' AS [ORD_NO], '200' AS [ORD_QTY], '2017' AS [YYYY], '11' AS [MM], '46' AS [WK]   UNION ALL
SELECT 'US' AS [SALES_TEAM_CD], 'US' AS [CNTRY_CD], 'NYK' AS [PORT_CD], '000008' AS [ORD_NO], '300' AS [ORD_QTY], '2017' AS [YYYY], '11' AS [MM], '47' AS [WK]   UNION ALL
SELECT 'US' AS [SALES_TEAM_CD], 'US' AS [CNTRY_CD], 'NYK' AS [PORT_CD], '000009' AS [ORD_NO], '100' AS [ORD_QTY], '2017' AS [YYYY], '12' AS [MM], '49' AS [WK] UNION ALL
SELECT 'US' AS [SALES_TEAM_CD], 'US' AS [CNTRY_CD], 'FLO' AS [PORT_CD], '000010' AS [ORD_NO], '500' AS [ORD_QTY], '2017' AS [YYYY], '12' AS [MM], '49' AS [WK] 
 
INSERT INTO PRAC.DBO.TB_SCP_RST_RTF
SELECT 'JP' AS [SALES_TEAM_CD], 'JP' AS [CNTRY_CD], 'HKT' AS [PORT_CD], '520' AS [RTF_QTY], '2017' AS [YYYY], '11' AS [MM], '44' AS [WK]  UNION ALL
SELECT 'JP' AS [SALES_TEAM_CD], 'JP' AS [CNTRY_CD], 'HKT' AS [PORT_CD], '670' AS [RTF_QTY], '2017' AS [YYYY], '11' AS [MM], '45' AS [WK]  UNION ALL
SELECT 'JP' AS [SALES_TEAM_CD], 'JP' AS [CNTRY_CD], 'HKT' AS [PORT_CD], '230' AS [RTF_QTY], '2017' AS [YYYY], '11' AS [MM], '47' AS [WK]  UNION ALL
SELECT 'JP' AS [SALES_TEAM_CD], 'JP' AS [CNTRY_CD], 'HKT' AS [PORT_CD], '450' AS [RTF_QTY], '2017' AS [YYYY], '12' AS [MM], '48' AS [WK]  UNION ALL
SELECT 'JP' AS [SALES_TEAM_CD], 'JP' AS [CNTRY_CD], 'FLO' AS [PORT_CD], '100' AS [RTF_QTY], '2017' AS [YYYY], '12' AS [MM], '49' AS [WK]  UNION ALL
SELECT 'US' AS [SALES_TEAM_CD], 'US' AS [CNTRY_CD], 'NYK' AS [PORT_CD], '200' AS [RTF_QTY], '2017' AS [YYYY], '11' AS [MM], '46' AS [WK]  UNION ALL
SELECT 'US' AS [SALES_TEAM_CD], 'US' AS [CNTRY_CD], 'NYK' AS [PORT_CD], '300' AS [RTF_QTY], '2017' AS [YYYY], '11' AS [MM], '47' AS [WK]   UNION ALL
SELECT 'US' AS [SALES_TEAM_CD], 'US' AS [CNTRY_CD], 'NYK' AS [PORT_CD], '100' AS [RTF_QTY], '2017' AS [YYYY], '12' AS [MM], '49' AS [WK]   UNION ALL
SELECT 'US' AS [SALES_TEAM_CD], 'US' AS [CNTRY_CD], 'FLO' AS [PORT_CD], '500' AS [RTF_QTY], '2017' AS [YYYY], '12' AS [MM], '49' AS [WK]

by 마농 [2017.12.29 14:26:29]

SELECT a.sales_team_cd
     , a.cntry_cd
     , a.port_cd
     , a.yyyy
     , a.mm
     , a.ord_no
     , a.wk
     , a.ord_qty
     , a.x
     , b.y
     , CASE WHEN a.x             < b.y THEN 0
            WHEN a.x - a.ord_qty < b.y THEN a.x - b.y
	          ELSE a.ord_qty
	      END rmn_ord_qty
  FROM (SELECT sales_team_cd, cntry_cd, port_cd, yyyy, mm
             , ord_no, wk, ord_qty
             , SUM(ord_qty) OVER(PARTITION BY sales_team_cd, cntry_cd, port_cd, yyyy, mm ORDER BY ord_no) x
          FROM tb_scp_dmd
        ) a
  LEFT OUTER JOIN
       (SELECT sales_team_cd, cntry_cd, port_cd, yyyy, mm
             , SUM(rtf_qty) y
          FROM tb_scp_rst_rtf
         GROUP BY sales_team_cd, cntry_cd, port_cd, yyyy, mm
        ) b
    ON a.sales_team_cd = b.sales_team_cd
   AND a.cntry_cd      = b.cntry_cd
   AND a.port_cd       = b.port_cd
   AND a.yyyy          = b.yyyy
   AND a.mm            = b.mm
;

SELECT a.sales_team_cd
     , a.cntry_cd
     , a.port_cd
     , a.yyyy
     , a.mm
     , a.wk
     , a.rtf_qty
     , a.x
     , b.y
     , CASE WHEN a.x             < b.y THEN 0
            WHEN a.x - a.rtf_qty < b.y THEN a.x - b.y
	          ELSE a.rtf_qty
	      END rmn_ord_qty
  FROM (SELECT sales_team_cd, cntry_cd, port_cd, yyyy, mm
             , wk, rtf_qty
             , SUM(rtf_qty) OVER(PARTITION BY sales_team_cd, cntry_cd, port_cd, yyyy, mm ORDER BY wk) x
          FROM tb_scp_rst_rtf
        ) a
  LEFT OUTER JOIN
       (SELECT sales_team_cd, cntry_cd, port_cd, yyyy, mm
             , SUM(ord_qty) y
          FROM tb_scp_dmd
         GROUP BY sales_team_cd, cntry_cd, port_cd, yyyy, mm
        ) b
    ON a.sales_team_cd = b.sales_team_cd
   AND a.cntry_cd      = b.cntry_cd
   AND a.port_cd       = b.port_cd
   AND a.yyyy          = b.yyyy
   AND a.mm            = b.mm
;

by 잭키올 [2017.12.29 17:39:10]

쿼리를 보고 대단하다는 말 밖에 떠오르지 않았습니다.

왜 재귀쿼리나 lag 함수 밖에 생각을 못하는건지 ㅜㅜ 이런식으로 누적 금액으로 해도 되는거였군요..

참 어렵네요..

감사하고 존경합니다. 마농님

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변