쿼리 질문입니다. 0 5 293

by 똑똑 [SQL Query] ORACLE 갯수계산 [2021.01.14 10:52:41]


  001 002 003 004 005
AAA   1     0
BBB 15 0 0 23  
CCC 0   0 0  
DDD   16     0
EEE     3    
FFF 1     0  
GGG 3        

TRUNC( SUM( DECODE( TECH.ITSQF , '001' ,  TO_DATE( E_DAY ) - TO_DATE( S_DAY ) )  ) / 365 ) A

총일수 / 365로 계산하여 id별로 해당 코드에 해당하는 년도를 계산했습니다.

0이면 1년이하

1이면 1년

2면 2년

...

5이상이면 5년이상 이런식으로 생각하고 쿼리를 짜보다가 어려움을 겪고있어서 질문을 드립니다.

  001 002 003 004 005
1년 이하 1 1 2 2 2
1년 1 1      
2년          
3년 1   1    
4년          
5년 이상 1 1   1  
합계 4 3 3 3 2

이런 형태로 만들려고 합니다.

밑에 표로 테이블을 하나 더 구성해야하는지도 같이 질문드립니다.

현재 테이블 값에는 1년이하 1년... 5년이상의 값이 있는 테이블은 없어서요. 새로 테이블을 생성해서 짜야하는지 잘 모르겠습니다.

답변주시면 감사하겠습니다.

 

by 마농 [2021.01.14 15:27:26]

1. TO_DATE 함수 사용 시 포멧 지정
 - 포멧 지정 안하면 오류 가능성 높습니다.
 - 포멧을 꼭 지정해 주세요.
2. 일수 계산은
 - e_day - s_day + 1
 

WITH tech AS
(
SELECT 'AAA' cd, '002' itsqf, '20200101' s_day, '20201231' e_day FROM dual --  1
UNION ALL SELECT 'AAA', '005', '20200201', '20201231' FROM dual --  0
UNION ALL SELECT 'BBB', '001', '20060101', '20201231' FROM dual -- 15
UNION ALL SELECT 'BBB', '002', '20200201', '20201231' FROM dual --  0
UNION ALL SELECT 'BBB', '003', '20200201', '20201231' FROM dual --  0
UNION ALL SELECT 'BBB', '004', '19980101', '20201231' FROM dual -- 23
UNION ALL SELECT 'CCC', '001', '20200201', '20201231' FROM dual --  0
UNION ALL SELECT 'CCC', '003', '20200201', '20201231' FROM dual --  0
UNION ALL SELECT 'CCC', '004', '20200201', '20201231' FROM dual --  0
UNION ALL SELECT 'DDD', '002', '20050101', '20201231' FROM dual -- 16
UNION ALL SELECT 'DDD', '005', '20200201', '20201231' FROM dual --  0
UNION ALL SELECT 'EEE', '003', '20180101', '20201231' FROM dual --  3
UNION ALL SELECT 'FFF', '001', '20200101', '20201231' FROM dual --  1
UNION ALL SELECT 'FFF', '004', '20200201', '20201231' FROM dual --  0
UNION ALL SELECT 'GGG', '001', '20180101', '20201231' FROM dual --  3
)
SELECT NVL(a.y_nm, '합계') y_nm
     , COUNT(DECODE(b.itsqf, '001', 1)) "001"
     , COUNT(DECODE(b.itsqf, '002', 1)) "002"
     , COUNT(DECODE(b.itsqf, '003', 1)) "003"
     , COUNT(DECODE(b.itsqf, '004', 1)) "004"
     , COUNT(DECODE(b.itsqf, '005', 1)) "005"
  FROM (SELECT LEVEL - 1 y
             , CASE WHEN LEVEL = 1 THEN '1년 미만'
                    WHEN LEVEL = 6 THEN '5년 이상'
                    ELSE (LEVEL-1) || '년' END y_nm
          FROM dual
         CONNECT BY LEVEL <= 6
        ) a
     , (SELECT cd
             , itsqf
             , LEAST(5, FLOOR((TO_DATE(e_day, 'yyyymmdd') - TO_DATE(s_day, 'yyyymmdd') + 1) / 365)) y
          FROM tech
        ) b
 WHERE a.y = b.y(+)
 GROUP BY ROLLUP((a.y, a.y_nm))
 ORDER BY a.y
;

 


by 똑똑 [2021.01.14 19:19:29]

답변 정말 감사드립니다. 답변을 주셔서 문제 해결에 있어서 도움이 많이 되었습니다.

정말 감사드립니다.

쿼리를 적용하던 중 문제가 생겼습니다.

WITH tech AS
(
SELECT 'AAA' cd, '001' itsqf, '20200131' s_day, '20210103' e_day FROM dual --  0
UNION ALL SELECT 'AAA', '001', '20190101', '20210103' FROM dual --  2
UNION ALL SELECT 'BBB', '001', '20200105', '20210120' FROM dual --  1
)
SELECT NVL(a.y_nm, '합계') y_nm
     , COUNT(DECODE(b.itsqf, '001', 1)) "001"
  FROM (SELECT LEVEL - 1 y
             , CASE WHEN LEVEL = 1 THEN '1년 미만'
                    WHEN LEVEL = 5 THEN '4년 이상'
                    ELSE (LEVEL-1) || '년' END y_nm
          FROM dual
         CONNECT BY LEVEL <= 5
        ) a
     , (SELECT cd
             , itsqf
             , LEAST(5, FLOOR((TO_DATE(e_day, 'yyyymmdd') - TO_DATE(s_day, 'yyyymmdd') + 1) / 365)) y
          FROM tech
        ) b
 WHERE a.y = b.y(+)
 GROUP BY ROLLUP((a.y, a.y_nm))
 ORDER BY a.y;
  001
1년미만 1
1년 1
2년 1
3년 0
4년이상 0
합계 3

현재 이런 상태로 조회가 되고있습니다.

제가 뽑아야 하는 데이터는 아래의 표 처럼 출력이 되어야 하는데요.

이 경우에 어디를 손 봐야할지 알려주시면 감사하겠습니다.

  001
1년미만 0
1년 1
2년 1
3년 0
4년이상 0
합계 2

바쁜 시간 내주셔서 답변을 달아주심에 정말 감사드립니다!


by 마농 [2021.01.15 07:54:51]

왜 그래야 하는지? 설명이 없네요?
한참 고민했습니다. itsqf 별로 1차 집계가 필요해 보입니다.
출력 기준을 1년 줄였네요? 그러면 LEAST 함수의 인수도 수정이 필요합니다.

WITH tech AS
(
SELECT 'AAA' cd, '001' itsqf, '20200131' s_day, '20210103' e_day FROM dual --  0
UNION ALL SELECT 'AAA', '001', '20190101', '20210103' FROM dual --  2
UNION ALL SELECT 'BBB', '001', '20200105', '20210120' FROM dual --  1
)
SELECT NVL(a.y_nm, '합계') y_nm
     , COUNT(DECODE(b.itsqf, '001', 1)) "001"
  FROM (SELECT LEVEL - 1 y
             , CASE WHEN LEVEL = 1 THEN '1년 미만'
                    WHEN LEVEL = 5 THEN '4년 이상'
                    ELSE (LEVEL-1) || '년' END y_nm
          FROM dual
         CONNECT BY LEVEL <= 5
        ) a
     , (SELECT cd
             , itsqf
--           , LEAST(5, FLOOR(   (TO_DATE(e_day, 'yyyymmdd') - TO_DATE(s_day, 'yyyymmdd') + 1) / 365)) y
             , LEAST(4, FLOOR(SUM(TO_DATE(e_day, 'yyyymmdd') - TO_DATE(s_day, 'yyyymmdd') + 1) / 365)) y
          FROM tech
         GROUP BY cd, itsqf    -- 그룹바이 및 SUM 추가
        ) b
 WHERE a.y = b.y(+)
 GROUP BY ROLLUP((a.y, a.y_nm))
 ORDER BY a.y
;

 


by 똑똑 [2021.01.15 09:46:35]

자세한 설명을 빠뜨렸던 점 죄송합니다.

LEAST함수 안에서 SUM을 추가하고 GROUP BY를 해주어야 했던거였네요.

마농님 덕분에 지식을 얻어갑니다. 바쁘신 와중에도 답변을 달아주셔서 정말 감사드립니다. 


by 마농 [2021.01.15 10:00:29]

수정해 놓고 수정전 쿼리를 올렸었네요.
LEAST 부분 다시 확인하세요.
LEAST(5, 가 아니라 LEAST(4, 입니다.

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