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

쿼리신님들 도와주십시요

by 임동진 [SQL Query] [2018.04.23 19:00:31]

1. 가입일 : 2011년 1월 1일 종료일 : 2011년 12월 30일

2. 1.의 기간 동안 A001회원 월 1회 100원 납부

3. 얻고 싶은 데이터

: 1.의 기간동안 연속으로 돈을 내지 않는 경우의 수 중 가장 많은(MAX) 경우의 수 를 결과로 얻고 싶습니다.

아래의 표 처럼 데이터가 존재 하고

회원번호	납입일	납입금
A001	2011-01-01	100
A001	2011-02-01	100
A001	2011-03-01	100
A001	2011-06-01	100
A001	2011-07-01	100
A001	2011-09-01	100
A001	2011-10-01	100
A001	2011-11-01	100
A001	2011-12-01	100

결과적으로 얻고 싶은 데이터 입니다. 좋은 방법이 있을까요? ㅠㅠ

회원번호	1월	2월	3월	4월	5월	6월	7월	8월	9월	10월	11월	12월	결과
A001	100	100	100	0	0	100	100	0	100	100	100	100	2
A002	100	100	100	100	100	100	100	100	100	100	100	100	0
A003	100	0	0	0	0	100	100	100	100	100	0	0	4

by 임동진 [2018.04.23 20:00:09]

참고로 오라클 입니다. ㅠㅠㅠㅠ

by 우리집아찌 [2018.04.24 10:34:12]

WITH T (USER_ID , DT , AMT ) AS (
SELECT 'A001' , TO_DATE( '20110101' ,'YYYYMMDD') , 100 FROM DUAL UNION ALL
SELECT 'A001' , TO_DATE( '20110201', 'YYYYMMDD') , 100 FROM DUAL UNION ALL
SELECT 'A002' , TO_DATE( '20110101' ,'YYYYMMDD') , 100 FROM DUAL UNION ALL
SELECT 'A002' , TO_DATE( '20110301' ,'YYYYMMDD') , 100 FROM DUAL UNION ALL
SELECT 'A003' , TO_DATE( '20110101' ,'YYYYMMDD') , 100 FROM DUAL UNION ALL
SELECT 'A003' , TO_DATE( '20110201' ,'YYYYMMDD') , 100 FROM DUAL UNION ALL
SELECT 'A003' , TO_DATE( '20110901' ,'YYYYMMDD') , 100 FROM DUAL UNION ALL
SELECT 'A003' , TO_DATE( '20111201' ,'YYYYMMDD') , 100 FROM DUAL 
)
SELECT * 
 FROM  (            
        SELECT USER_ID , MM , AMT , MAX(EMPTY_CNT) OVER(PARTITION BY USER_ID) MAX_EMPTY_CNT
          FROM (SELECT USER_ID , MM , AMT
                     , COUNT(*) OVER(PARTITION BY USER_ID , EMPTY_VAL ) EMPTY_CNT
                 FROM (SELECT USER_ID , TO_CHAR(A.DT,'MONTH') MM , AMT 
                            , CASE WHEN AMT IS NULL THEN ROWNUM END  - SUM(CASE WHEN AMT IS NULL THEN 1 END ) OVER(PARTITION BY USER_ID ORDER BY A.DT )  EMPTY_VAL
                        FROM (SELECT ADD_MONTHS(TO_DATE('20110101'),LEVEL-1) AS DT FROM DUAL CONNECT BY LEVEL <= 12 ) A
                              LEFT OUTER JOIN
                              T B
                              PARTITION BY (B.USER_ID) 
                              ON A.DT = B.DT 
                         )
               )
         )PIVOT ( MIN(AMT) FOR MM IN (  '1월 ' , '2월 ' , '3월 ' , '4월 ' , '5월 ', '6월 ', '7월 ', '8월 ', '9월 ', '10월', '11월' , '12월' )) ;

by 우리집아찌 [2018.04.24 10:37:05]

SELECT '''' || TO_CHAR(SYSDATE,'MONTH') || '''' FROM DUAL

결과값이 '4월 ' 이렇게 나오네요.. 뒤에 빈칸이 붙는데 몰라서 헤메었네요..

by 마농 [2018.04.24 11:06:19]

WITH t AS
(
SELECT 'A001' id, '2011-01-01' dt, 100 v FROM dual
UNION ALL SELECT 'A001', '2011-02-01', 100 FROM dual
UNION ALL SELECT 'A001', '2011-03-01', 100 FROM dual
UNION ALL SELECT 'A001', '2011-06-01', 100 FROM dual
UNION ALL SELECT 'A001', '2011-07-01', 100 FROM dual
UNION ALL SELECT 'A001', '2011-09-01', 100 FROM dual
UNION ALL SELECT 'A001', '2011-10-01', 100 FROM dual
UNION ALL SELECT 'A001', '2011-11-01', 100 FROM dual
UNION ALL SELECT 'A001', '2011-12-01', 100 FROM dual
UNION ALL SELECT 'A002', '2011-01-01', 100 FROM dual
UNION ALL SELECT 'A002', '2011-02-01', 100 FROM dual
UNION ALL SELECT 'A002', '2011-03-01', 100 FROM dual
UNION ALL SELECT 'A002', '2011-04-01', 100 FROM dual
UNION ALL SELECT 'A002', '2011-05-01', 100 FROM dual
UNION ALL SELECT 'A002', '2011-06-01', 100 FROM dual
UNION ALL SELECT 'A002', '2011-07-01', 100 FROM dual
UNION ALL SELECT 'A002', '2011-08-01', 100 FROM dual
UNION ALL SELECT 'A002', '2011-09-01', 100 FROM dual
UNION ALL SELECT 'A002', '2011-10-01', 100 FROM dual
UNION ALL SELECT 'A002', '2011-11-01', 100 FROM dual
UNION ALL SELECT 'A002', '2011-12-01', 100 FROM dual
UNION ALL SELECT 'A003', '2011-01-01', 100 FROM dual
UNION ALL SELECT 'A003', '2011-06-01', 100 FROM dual
UNION ALL SELECT 'A003', '2011-07-01', 100 FROM dual
UNION ALL SELECT 'A003', '2011-08-01', 100 FROM dual
UNION ALL SELECT 'A003', '2011-09-01', 100 FROM dual
UNION ALL SELECT 'A003', '2011-10-01', 100 FROM dual
)
SELECT id
     , NVL(MIN(DECODE(mm, '01', v)), 0) m01
     , NVL(MIN(DECODE(mm, '02', v)), 0) m02
     , NVL(MIN(DECODE(mm, '03', v)), 0) m03
     , NVL(MIN(DECODE(mm, '04', v)), 0) m04
     , NVL(MIN(DECODE(mm, '05', v)), 0) m05
     , NVL(MIN(DECODE(mm, '06', v)), 0) m06
     , NVL(MIN(DECODE(mm, '07', v)), 0) m07
     , NVL(MIN(DECODE(mm, '08', v)), 0) m08
     , NVL(MIN(DECODE(mm, '09', v)), 0) m09
     , NVL(MIN(DECODE(mm, '10', v)), 0) m10
     , NVL(MIN(DECODE(mm, '11', v)), 0) m11
     , NVL(MIN(DECODE(mm, '12', v)), 0) m12
     , GREATEST( MAX(MONTHS_BETWEEN(dt0, dt1))
               , MAX(MONTHS_BETWEEN(dt2, dt0))
               ) - 1 cnt
  FROM (SELECT id, v
             , SUBSTR(dt, 6, 2) mm
             , TO_DATE(dt, 'yyyy-mm-dd') dt0
             , TO_DATE(LAG (dt, 1, '2010-12-01') OVER(PARTITION BY id ORDER BY dt), 'yyyy-mm-dd') dt1
             , TO_DATE(LEAD(dt, 1, '2012-01-01') OVER(PARTITION BY id ORDER BY dt), 'yyyy-mm-dd') dt2
          FROM t
         WHERE dt BETWEEN '2011-01-01' AND '2011-12-31'
        )
 GROUP BY id
;

by 깨복숭이 [2018.04.24 11:54:39]

도움 주셔서 고맙습니다

큰도움이 되었습니다

by 신이만든지기 [2018.04.24 12:07:54]

WITH T AS (
    SELECT 'a001' ID, TO_DATE('2011-01-01') PAY_DT, 100 PAY_AMT FROM DUAL UNION ALL
    SELECT 'a001' ID, TO_DATE('2011-02-01') PAY_DT, 100 PAY_AMT FROM DUAL UNION ALL
    SELECT 'a001' ID, TO_DATE('2011-03-01') PAY_DT, 100 PAY_AMT FROM DUAL UNION ALL
    SELECT 'a001' ID, TO_DATE('2011-04-01') PAY_DT, 100 PAY_AMT FROM DUAL UNION ALL
    SELECT 'a001' ID, TO_DATE('2011-07-01') PAY_DT, 100 PAY_AMT FROM DUAL UNION ALL
    SELECT 'a001' ID, TO_DATE('2011-09-01') PAY_DT, 100 PAY_AMT FROM DUAL UNION ALL
    SELECT 'a001' ID, TO_DATE('2011-10-01') PAY_DT, 100 PAY_AMT FROM DUAL UNION ALL
    SELECT 'a001' ID, TO_DATE('2011-11-01') PAY_DT, 100 PAY_AMT FROM DUAL UNION ALL
    SELECT 'a001' ID, TO_DATE('2011-12-01') PAY_DT, 100 PAY_AMT FROM DUAL
)
SELECT ID
     , MAX(DECODE(MM, '01', PAY_AMT)) MON01
     , MAX(DECODE(MM, '02', PAY_AMT)) MON02
     , MAX(DECODE(MM, '03', PAY_AMT)) MON03
     , MAX(DECODE(MM, '04', PAY_AMT)) MON04
     , MAX(DECODE(MM, '05', PAY_AMT)) MON05
     , MAX(DECODE(MM, '06', PAY_AMT)) MON06
     , MAX(DECODE(MM, '07', PAY_AMT)) MON07
     , MAX(DECODE(MM, '08', PAY_AMT)) MON08
     , MAX(DECODE(MM, '09', PAY_AMT)) MON09
     , MAX(DECODE(MM, '10', PAY_AMT)) MON10
     , MAX(DECODE(MM, '11', PAY_AMT)) MON11
     , MAX(DECODE(MM, '12', PAY_AMT)) MON12
     , MAX(GAP) RESULT
  FROM ( SELECT DT
              , ID
              , PAY_AMT
              , LAG( DT ) OVER( PARTITION BY ID ORDER BY PAY_AMT DESC, DT ASC ) BEFORE_DT
              , MONTHS_BETWEEN( DT, LAG( DT ) OVER( PARTITION BY ID ORDER BY PAY_AMT DESC, DT ASC )) - 1 GAP
              , TO_CHAR(DT, 'MM') MM
          FROM ( SELECT T2.DT
                      , T2.ID
                      , NVL( T1.PAY_AMT, 0 ) PAY_AMT
                  FROM T T1
                     , ( SELECT ID
                              , DT
                          FROM ( SELECT DISTINCT ID
                                           FROM T ) A
                             , ( SELECT    ADD_MONTHS( TO_DATE( :BEGIN_DT, 'YYYY-MM-DD' ), LEVEL - 1 ) DT
                                      FROM DUAL
                                CONNECT BY LEVEL <=
                                               MONTHS_BETWEEN( TO_DATE( '2011-12-01', 'YYYY-MM-DD' )
                                                             , TO_DATE( '2011-01-01', 'YYYY-MM-DD' )
                                                             )
                                               + 1 ) B ) T2
                 WHERE T1.ID(+) = T2.ID
                   AND T1.PAY_DT(+) = T2.DT ))
    GROUP BY ID

저는 좀 복잡하게 만든 것 같네요.

by 깨복숭이 [2018.04.24 13:27:55]

50만 회원의 10년치의 위의 데이터를 추출해야하는데

속도가 안나오네요 ㅠㅠ

다들 도움 주셔서 너무 고맙습니다.

댓글등록

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

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

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

데이터베이스 질문과 답변