날짜 select 하기(재질문) 0 12 708

by 성공한다 [Oracle 기초] [2019.09.08 20:13:55]


안녕하세요..!

질문이 바뀌어서 다시 올리겠습니다 ..

select substr(hr_date,1.6) hr_date,

        ,emp_no,

       birthday,

        name

from student

where substr(hr_date,1.6)>='201901'

and name='홍길동'

hr_date emp_no birthday name
201901 111222 19900901 홍길동
201905 111222 19900901 홍길동
201907 111333 19900901 홍길동

  이렇게 select한 값을 ...

 

hr_date emp_no birthday name
201901 111222 19900901 홍길동
201902 111222 19900901 홍길동
201903 111222 19900901 홍길동
201904 111222 19900901 홍길동
201905 111222 19900901 홍길동
201907 111333 19900901 홍길동
201908 111333 19900901 홍길동
201909 111333 19900901 홍길동

 이렇게 월별 누적데이터로 select하고 싶습니다...

ADD_MONTHS함수를 써보았는데..잘안되네요 ㅠㅠ

감사합니다..

 

by 농부지기 [2019.09.09 09:42:32]

SQL작성하다가, 예제data를 다시 보니.. 좀 이상하네요.
이쁜 예제가 아니네요.
name이 동일해서, emp_no도 동일할줄 알았는데.. 아니군요.

원하는 조회결과의 규칙도 이해가 안되는데요.
emp_no(111222)는 결과가  1~5월이고
emp_no(111333)은 결과가 7~9월이고..  

뭔가 원하는 규칙이 맞는건지요?  아니뭔 원하는 규칙을 다시 기술해야 될거 같은데요.


by 우리집아찌 [2019.09.09 10:18:02]
--  WHERE SUBSTR(HR_DATE,1,6) >= '201901'  <= BETWEEN으로 쓰는경우는 없나요?
-- 가능하면 원본 데이터로 주세요.


WITH T AS (
SELECT '201901' hr_date , '111222' emp_no ,'19900901' birthday ,'홍길동' name FROM DUAL UNION ALL
SELECT '201905' , '111222' ,'19900901' ,'홍길동' FROM DUAL UNION ALL
SELECT '201907' , '111333' ,'19900901' ,'홍길동' FROM DUAL 
), COPY_T AS (
 SELECT TO_CHAR(ADD_MONTHS( MIN_DT , LEVEL - 1 ),'YYYYMM') DT 
   FROM (SELECT SYSDATE CURR_DT
              , MIN(TO_DATE(hr_date,'YYYYMM')) MIN_DT
          FROM T )
 CONNECT BY LEVEL <=  MONTHS_BETWEEN ( CURR_DT , MIN_DT ) + 1
)
 
 SELECT B.DT 
      , LAST_VALUE(A.emp_no IGNORE NULLS) OVER(ORDER BY B.DT) emp_no
      , LAST_VALUE(A.birthday IGNORE NULLS) OVER(ORDER BY B.DT) birthday
      , LAST_VALUE(A.name IGNORE NULLS) OVER(ORDER BY B.DT) name
   FROM T      A
      , COPY_T B
  WHERE A.hr_date(+) = B.DT
 

 


by 성공한다 [2019.09.09 13:36:39]

답변감사합니다..!

WITH T AS (
SELECT '201901' hr_date , '111222' emp_no ,'19900901' birthday ,'홍길동' name FROM DUAL UNION ALL
SELECT '201905' , '111222' ,'19900901' ,'홍길동' FROM DUAL UNION ALL
SELECT '201907' , '111333' ,'19900901' ,'홍길동' FROM DUAL UNION ALL
SELECT '201902' , '111225' ,'19890302' ,'이순신' FROM DUAL UNION ALL
SELECT '201903' , '111225' ,'19890302' ,'이순신' FROM DUAL UNION ALL
SELECT '201905' , '111225' ,'19890302' ,'이순신' FROM DUAL
), COPY_T AS (
 SELECT TO_CHAR(ADD_MONTHS( MIN_DT , LEVEL - 1 ),'YYYYMM') DT
   FROM (SELECT SYSDATE CURR_DT
              , MIN(TO_DATE(hr_date,'YYYYMM')) MIN_DT
          FROM T )
 CONNECT BY LEVEL <=  MONTHS_BETWEEN ( CURR_DT , MIN_DT ) + 1
)
 
 SELECT B.DT
      , LAST_VALUE(A.emp_no IGNORE NULLS) OVER(ORDER BY B.DT) emp_no
      , LAST_VALUE(A.birthday IGNORE NULLS) OVER(ORDER BY B.DT) birthday
      , LAST_VALUE(A.name IGNORE NULLS) OVER(ORDER BY B.DT) name
   FROM T      A
      , COPY_T B
  WHERE A.hr_date(+) = B.DT
이렇게 실행하였을때

DT emp_no birthday name
201901 111222 19900901 홍길동
201907 111333 19900901 홍길동
201908 111333 19900901 홍길동
201909 111333 19900901 홍길동
201902 111225 19890302 이순신
201903 111225 19890302 이순신
201904 111225 19890302 이순신
201905 111225 19890302 이순신
201906 111225 19890302 이순신

이렇게 출력이되네요 ㅠㅠ특정한사람만 조건걸었을때는 월별로 잘 출력이되는데 ㅠㅠ

hr_date의 원본데이터는 20190101 이런형식입니다.(vachar2)


by 우리집아찌 [2019.09.09 15:02:38]
-- sql 이 좀 지저분해졌네요.
-- 수정햇어요

WITH T AS (
SELECT '201901' hr_date , '111222' emp_no ,'19900901' birthday ,'홍길동' name FROM DUAL UNION ALL
SELECT '201905' , '111222' ,'19900901' ,'홍길동' FROM DUAL UNION ALL
SELECT '201907' , '111333' ,'19900901' ,'홍길동' FROM DUAL UNION ALL
SELECT '201902' , '111225' ,'19890302' ,'이순신' FROM DUAL UNION ALL
SELECT '201903' , '111225' ,'19890302' ,'이순신' FROM DUAL UNION ALL
SELECT '201905' , '111225' ,'19890302' ,'이순신' FROM DUAL
), COPY_T AS (
SELECT A.* , B.*
  FROM ( SELECT TO_CHAR(ADD_MONTHS( min_dt , LEVEL - 1 ),'YYYYMM') DT 
          FROM (SELECT SYSDATE CURR_DT
                     , MIN(TO_DATE(hr_date,'YYYYMM')) min_dt
                  FROM T )
       CONNECT BY LEVEL <=  MONTHS_BETWEEN ( CURR_DT , MIN_DT ) + 1 ) A
     , ( SELECT name , MIN(hr_date) min_dt FROM T GROUP BY name ) B 
)

SELECT *
  FROM ( SELECT B.dt   
              , LAST_VALUE(A.emp_no IGNORE NULLS) OVER(ORDER BY B.name , B.dt) emp_no
              , LAST_VALUE(A.birthday IGNORE NULLS) OVER( ORDER BY B.name ,  B.dt) birthday
              , LAST_VALUE(A.name IGNORE NULLS) OVER(ORDER BY B.name , B.dt) name
              , MIN_DT                                    -- 수정
           FROM T       A
              , COPY_T  B
          WHERE A.hr_date(+)  = B.DT
            AND A.name(+)     = B.name
        ) T 
 WHERE DT >= MIN_DT  -- 수정
 ORDER BY name , dt

 


by 성공한다 [2019.09.09 17:08:36]

답변감사드립니다....!

근데 원데이터를 넣고 하니,, 데이터에 상관없이 201901~201909부터 출력이 됩니다 ㅠㅠ

뭐가 잘못된건지 ㅠㅠ

 

WITH T AS (

select SUBSTR(hr_date,1,6)hr_date,emp_no,birthday,name
from student
where SUBSTR(hr_date,1,6)>='201901'

), COPY_T AS (
SELECT A.* , B.*
  FROM ( SELECT TO_CHAR(ADD_MONTHS( min_dt , LEVEL - 1 ),'YYYYMM') DT
          FROM (SELECT SYSDATE CURR_DT
                     , MIN(TO_DATE(hr_date,'YYYYMM')) min_dt
                  FROM T )
       CONNECT BY LEVEL <=  MONTHS_BETWEEN ( CURR_DT , MIN_DT ) + 1 ) A
     , ( SELECT name , MIN(hr_date) min_dt FROM T GROUP BY name ) B
)
 
SELECT *
  FROM ( SELECT B.dt  
              , LAST_VALUE(A.emp_no IGNORE NULLS) OVER(ORDER BY B.name , B.dt) emp_no
              , LAST_VALUE(A.birthday IGNORE NULLS) OVER( ORDER BY B.name ,  B.dt) birthday
              , LAST_VALUE(A.name IGNORE NULLS) OVER(ORDER BY B.name , B.dt) name
           FROM T       A
              , COPY_T  B
          WHERE A.hr_date(+)  = B.DT
            AND A.name(+)     = B.name
        ) T
 WHERE emp_no is not null
 ORDER BY name , dt

 

원본데이터 : student

select SUBSTR(hr_date,1,6)hr_date,emp_no,birthday,name
from student
where SUBSTR(hr_date,1,6)>='201901'

hr_date emp_no birthday name
201907 111222 19900101 홍길동
201901 777999 19880101 이순신
201904 888999 19880101 이순신
201902 333444 19890301 안중근

 

 

 

 


by 우리집아찌 [2019.09.09 17:27:04]

수정했어요


by 성공한다 [2019.09.10 16:15:27]

답변 정말 감사합니다...!

만약 동일한 년월에 두개의 emp_no가 존재하니깐 마지막 emp_no가 출력이 됩니다 ㅠㅠ

 

WITH T AS (
SELECT '201901' hr_date , '111222' emp_no ,'19900901' birthday ,'홍길동' name FROM DUAL UNION ALL
SELECT '201905' , '111222' ,'19900901' ,'홍길동' FROM DUAL UNION ALL
SELECT '201907' , '111334' ,'19900901' ,'홍길동' FROM DUAL UNION ALL
SELECT '201907' , '111333' ,'19900901' ,'홍길동' FROM DUAL UNION ALL
SELECT '201902' , '111225' ,'19890302' ,'이순신' FROM DUAL UNION ALL
SELECT '201903' , '111225' ,'19890302' ,'이순신' FROM DUAL UNION ALL
SELECT '201905' , '111225' ,'19890302' ,'이순신' FROM DUAL
), COPY_T AS (
SELECT A.* , B.*
  FROM ( SELECT TO_CHAR(ADD_MONTHS( min_dt , LEVEL - 1 ),'YYYYMM') DT
          FROM (SELECT SYSDATE CURR_DT
                     , MIN(TO_DATE(hr_date,'YYYYMM')) min_dt
                  FROM T )
       CONNECT BY LEVEL <=  MONTHS_BETWEEN ( CURR_DT , MIN_DT ) + 1 ) A
     , ( SELECT name , MIN(hr_date) min_dt FROM T GROUP BY name ) B
)
 
SELECT *
  FROM ( SELECT B.dt  
              , LAST_VALUE(A.emp_no IGNORE NULLS) OVER(ORDER BY B.name , B.dt) emp_no
              , LAST_VALUE(A.birthday IGNORE NULLS) OVER( ORDER BY B.name ,  B.dt) birthday
              , LAST_VALUE(A.name IGNORE NULLS) OVER(ORDER BY B.name , B.dt) name
              , MIN_DT                                   
           FROM T       A
              , COPY_T  B
          WHERE A.hr_date(+)  = B.DT
            AND A.name(+)     = B.name
        ) T
 WHERE DT >= MIN_DT 
 ORDER BY name , dt
-----

질문이 계속 바뀌어서 죄송합니다 ㅠㅠ


by 마농 [2019.09.10 08:46:09]

201901 보다 큰 조건으로 자료를 가져오는데.
이순신은 2월부터 시작하는데.
혹시 이순신에게 201901 보다 작은 자료가 존재한다면? (예 : 201811 이순신)
이순신에 대해서 결과값에 201901 이 나와야 할 것 같은데 어떻게 생각하시나요?
1. 애초에 201901 보다 큰것만 가져와야 하므로 201902 부터 시작이다.
2. 아니다. 이미 201811 부터 시작되므로 201901 부터 시작해야 한다.


by 성공한다 [2019.09.10 16:08:35]

이순신이 201901보다 적은 자료가 존재한다면 포함이 안됩니다.

일자 조건은 201901부터 입니다..!

201901보다 적은 데이터는 포함이 안됩니당..!


by 마농 [2019.09.10 20:02:01]

동일월에 동일인이 두건 이상인 경우
어떻게 처리해야 하는지를 명확하게 알려주셔야죠.
질문을 명확하게 해주셔야 합니다. 항상 질문이 부족합니다.


by 성공한다 [2019.09.10 21:47:14]

죄송합니다...!저도 헷갈렸네요 ㅠㅠ

동일월에 동일인이 2건 이상일때

ex)

hr_date emp_no birthday name
201903 111222 19910101 홍길동
201903 222333 19910101 홍길동
201905 444555 19910101 홍길동
201907 666777 19910101 홍길동

결과는

hr_date emp_no birthday name
201903 111222 19910101 홍길동
201903 222333 19910101 홍길동
201904 111222 19910101 홍길동
201904 222333 19910101 홍길동
201905 444555 19910101 홍길동
201906 444555 19910101 홍길동
201907 666777 19910101 홍길동
201908 666777 19910101 홍길동
201909 666777 19910101 홍길동

감사합니다.


by 마농 [2019.09.11 08:44:02]
WITH t AS
(
SELECT '20190301' hr_date, '111222' emp_no, '19910101' birthday, '홍길동' name FROM dual
UNION ALL SELECT '20190302', '222333', '19910101', '홍길동' FROM dual
UNION ALL SELECT '20190501', '444555', '19910101', '홍길동' FROM dual
UNION ALL SELECT '20190701', '666777', '19910101', '홍길동' FROM dual
UNION ALL SELECT '20190701', '888999', '19910101', '이순신' FROM dual
)
SELECT TO_CHAR(ADD_MONTHS(sdt, lv-1), 'yyyymm') hr_ym
     , emp_no
     , birthday
     , name
  FROM (SELECT sdt
             , emp_no
             , birthday
             , name
             , NVL(
               MIN(ADD_MONTHS(sdt, -1))
               OVER(PARTITION BY birthday, name ORDER BY sdt
               RANGE BETWEEN INTERVAL '1' MONTH FOLLOWING AND UNBOUNDED FOLLOWING)
               , TRUNC(sysdate, 'mm')) edt
          FROM (SELECT TRUNC(TO_DATE(hr_date, 'yyyymmdd'), 'mm') sdt
                     , emp_no, birthday, name
                  FROM t
                 WHERE hr_date > '201901'
                )
        )
     , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 12)
 WHERE lv <= MONTHS_BETWEEN(edt, sdt) + 1
 ORDER BY birthday, name, hr_ym, emp_no
;

 

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