질문자의 의도를 제대로 이해된건지 모르겠는데

현재까지 매수가 5 개고 매도가 3 개이면 현재 보유수량은 2개가 되는게 맞는거 아닌지요

매수 매도 구분자에 따라 그동안에 누적치를 서로 상쇄해 줘야 맞을꺼같은데요.....????

WITH T AS(
SELECT 2006782 COL1, 1 COL2, 3 COL3 FROM DUAL UNION ALL
SELECT 2006783 COL1, 2 COL2, 2 COL3 FROM DUAL UNION ALL
SELECT 2006784 COL1, 2 COL2, 5 COL3 FROM DUAL UNION ALL
SELECT 2006785 COL1, 1 COL2, 1 COL3 FROM DUAL UNION ALL
SELECT 2006786 COL1, 2 COL2, 8 COL3 FROM DUAL UNION ALL
SELECT 2006787 COL1, 1 COL2, 1 COL3 FROM DUAL UNION ALL
SELECT 2006788 COL1, 2 COL2, 1 COL3 FROM DUAL UNION ALL
SELECT 2006789 COL1, 2 COL2, 1 COL3 FROM DUAL
)
SELECT
        max(CURRENT_JEOGO) AS MAX_CNT
  FROM
     (
        SELECT
                COL1,
                SUM(SUM_IBGO) OVER(ORDER BY COL1) -
                SUM(SUM_CHULGO) OVER(ORDER BY COL1) AS CURRENT_JEOGO
          FROM
             (
                SELECT
                    COL1,
                    SUM(CASE WHEN COL2 = 1 THEN COL3 ELSE 0 END )  AS SUM_IBGO,
                    SUM(CASE WHEN COL2 = 2 THEN COL3 ELSE 0 END )  AS SUM_CHULGO
                FROM T
                GROUP BY COL1
             )
     )       

쿼리 정말 감사합니다..

제의도는 상쇄개념은 아니구여

매수든 매도든 최대수량을 구하는건데 단, 연속적인 매매구분일경우는 서로 더해서 최대값을 구하는거에여

그래서 여기서 데이타를 보면 매도일경우에 2006783  2006784  번순번처럼 연속적일경우는 7개로 계산을 했을떄를 말하구여 그런데 2006786번이 8개이므로 7개보다는 높은 숫자이므로 최대보유수량이 8개가 나와야 하거든여..

WITH T AS(
SELECT 2006782 COL1, 1 COL2, 3 COL3 FROM DUAL UNION ALL
SELECT 2006783 COL1, 2 COL2, 2 COL3 FROM DUAL UNION ALL
SELECT 2006784 COL1, 2 COL2, 5 COL3 FROM DUAL UNION ALL
SELECT 2006785 COL1, 1 COL2, 1 COL3 FROM DUAL UNION ALL
SELECT 2006786 COL1, 2 COL2, 8 COL3 FROM DUAL UNION ALL
SELECT 2006787 COL1, 1 COL2, 1 COL3 FROM DUAL UNION ALL
SELECT 2006788 COL1, 2 COL2, 1 COL3 FROM DUAL UNION ALL
SELECT 2006789 COL1, 2 COL2, 7 COL3 FROM DUAL UNION ALL
SELECT 2006790 COL1, 2 COL2, 1 COL3 FROM DUAL
) 
     
SELECT
   MAX(SUM_COL3) AS MAX_COL3
   FROM
         (
             SELECT
                      COL1,
                      COL2,
                      COL3,
                      SUM(COL3)OVER(PARTITION BY COL1 - ROWNUM ORDER BY COL1) AS SUM_COL3        
                  FROM T
                  WHERE COL2 = 1
                  UNION ALL
                  SELECT
                      COL1,
                      COL2,
                      COL3,
                      SUM(COL3)OVER(PARTITION BY COL1 - ROWNUM ORDER BY COL1) AS SUM_COL3        
                  FROM T
                  WHERE COL2 = 2   
   )

WITH deal AS
(
SELECT 2006782 dealno, 1 buy_sell, 3 deal_qty FROM dual
UNION ALL SELECT 2006783, 2, 2 FROM dual
UNION ALL SELECT 2006784, 2, 5 FROM dual
UNION ALL SELECT 2006785, 1, 1 FROM dual
UNION ALL SELECT 2006786, 2, 8 FROM dual
UNION ALL SELECT 2006787, 1, 1 FROM dual
UNION ALL SELECT 2006788, 2, 1 FROM dual
UNION ALL SELECT 2006789, 2, 1 FROM dual
)
SELECT MAX(SUM(deal_qty)) deal_qty_max
  FROM (
        SELECT dealno, buy_sell, deal_qty
             , ROW_NUMBER() OVER(ORDER BY dealno) rn1
             , ROW_NUMBER() OVER(PARTITION BY buy_sell ORDER BY dealno) rn2
          FROM deal
        )
 GROUP BY buy_sell, rn1 - rn2
;

마농님 동호님 감사합니다.^^ 많은 도움되었습니다.