알아야할 개념 :
1. connect by level
예시 :
SELECT SYSDATE , LEVEL
FROM dual
CONNCET BY LEVEL <= 10 ;
(level 은 1부터 시작 )
2. date + level을 이용한 정수를 통한 날짜 연산
예시
SELECT SYSDATE + LEVEL-1 dt
FROM daul
CONNECT BY LEVEL <=10 ;
3. 날짜의 주차표시
IW : 월요일을 주의 시작으로 본다.
W : 매월 1일을 주의시작으로 본다 ( 1~ 7 일 : 1주차 8~14일 : 2주차 )
WW : 매년 1일을 주의 시작 [1~7일 1주차, 8~14일 2주차
예시 :
SELECT : TO_DATE('20190101' , 'YYYYMMDD' ) + LEVEL -1 dt,
TO_CHAR(TO_DATE('20190101' , 'YYYYYMMDD' ) + LEVEL -1 , 'day' ) day,
TO_CHAR(TO_DATE('20190101' , 'YYYYMMDD' ) + LEVEL -1 , 'W' ) w,
TO_CAHR(TO_DATE('20190101' , 'YYYYMMDD' ) + LEVEL - 1 , 'WW' ) ww ,
TO_CHAR(TO_DATE('20190101' , 'YYYYMMDD' ) + LEVEL - 1 , 'IW') iw
FROM dual
CONNECT BY LEVEL <= 20 ;
중요 ㅣ
1. W와 WW의 차이는
W : 오늘이 이번달의 몇번째 주인지 출력해보자
SELECT TO_CHAR(SYSDATE, 'W')
FROM dual ;
WW : 오늘이 올해의 몇 번째 주인지 출력하시오 (기준은 요일이네..)
SELECT TO_CHAR(SYSDATE, 'WW' )
FROM dual ;
ww와 iw의 차이는?
한주의 기준이 ( 일~토)인지 (월~일) 인지이다.
1월 1일이 해당년도 1주차인지 전년도 마지막 주차인지?
**TO_CHAR(SYSDATE, 'IW') => 매년 1월 1일의 요일이 (월화수목) 이면 1월 1일을포함하는
월요일부터 ~ 당해년도 일요일까지 1주차
매년 1월 1일의 요일이 (금토일) 이면 1월 1일을 포함하는 전년도 월요일부터 당해년도 일요일까지 전년도 마지막주차
4. D와 DAY
SELECT TO_DATE ('20190101' , 'YYYYMMDD' ) + LEVEL - 1 dt ,
TO_CHAR ( TO_DATE('20190101' , 'YYYYMMDD' ) + LEVEL - 1 , 'day' ) day ,
TO_CHAR ( TO_DATE('20190101' , 'YYYYMMDD' ) + LEVEL - 1 , 'd' ) d
FROM dual
CONNECT BY LEVEL < = 20 ;
day 는 그 날짜의 요일을 화요일 수요일 목요일 이렇게 표현을 해주고
d 는 일요일은 1 월요일은 2 화요일은 3 수요일은 4 목요일은 5 금요일은 6 토요일은 7 이런식으로 숫자로 간결하게 표현해준다.
5. 특정 년월의 모든 일자 구하기
SELECT TO_DATE('201905' , 'YYYYMM' ) + LEVEL - 1 dt
FROM dual
CONNECT BY LEVEL <= LAST_DAT(TO_DATE('201905' , 'YYYYMM' ) ) -
TO_DATE('201905' , 'YYYYMM' ) + 1 ;
LAST_DAY(TO_DATE('201905' , 'YYYYMM' ) 얘는 숫자 딱 하나가 아니라 날짜전체 yyyymm 이 나오기 때문에 여기서 TO_DATE('201905' , 'YYYYMM' ) 을 빼서 숫자 하나가 나오도록 해준다.
-> 이것을 더 간단하게 해주면
TO_CHAR (LAST_DAY(TO_DATE('201905' , 'YYYYMM' ) ) , 'dd' ) 이렇게 간단히 만들어줄 수 있다.
6. 일자의 주차, 요일 구하기
IW : 월요일을 주의 시작으로 본다. 그래서 IW 를 쓰는거다 요일을 묶어서 같은 주로 보려고
SELECT dt ,
TO_CHAR(dt , 'iw' ) iw,
TO_CHAR(dt , 'd') d,
TO_CHAR(dt , 'day') day
FROM
(SELECT TO_DATE('201905' , 'YYYYMM' ) + LEVEL -1 dt
FROM dual
CONNECT BY LEVEL <= LAST_DAY(TO_DATE('201905' , 'YYYYMM' ) ) -
TO_DATE('201905' , 'YYYYMM' ) + 1 )
)
7. 세로로 쭉 나열된 데이터를 짤라서 만들거다.
SELECT dt , w, mon , tue , wed, thu , fri , sat, sun : mon ~ sun 까지는 null 이겠지?
FROM
(
SELECT dt ,
TO_CHAR( dt, 'iw' ) iw ,
TO_CHAR ( dt , 'd' ) d ,
TO_CHAR ( dt , 'day' ) day
FROM
( SELECT TO_DATE('201905' , 'YYYYMM' ) + LEVEL - 1 dt ,
FROM dual
CONNECT BY LEVEL <= LAST_DAY(TO_DATE(201905' , 'YYYYMM' ) ) -
TO_DATE('201905' , 'YYYYMM' ) + 1
)
)
8. decode 함수를 써서 행을 옆으로 나열해준것
* iw 는 나중에 그룹으로 묶어주고 order by 처리해주려고 써준거얌 ㅎ
SELECT dt , iw , DECODE ( d, 2 , dt , ' ' ) mon ,
DECODE ( d, 3 , dt , ' ' ) tue ,
DECODE ( d, 4 , dt , ' ' ) wed,
DECODE ( d, 5, dt, ' ' ) thu ,
DECODE ( d, 6, dt, ' ' ) fri ,
DECODE ( d, 7, dt , ' ' ) sat,
DECODE ( d, 1, dt, ' ' ) sun
FROM
(
SELECT dt ,
TO_CHAR( dt, 'iw' ) iw ,
TO_CHAR ( dt , 'd' ) d ,
TO_CHAR ( dt , 'day' ) day
FROM
( SELECT TO_DATE('201905' , 'YYYYMM' ) + LEVEL - 1 dt ,
FROM dual
CONNECT BY LEVEL <= LAST_DAY(TO_DATE(201905' , 'YYYYMM' ) ) -
TO_DATE('201905' , 'YYYYMM' ) + 1
)
)
9. 주차로 (iw) 합치기
여기서 생각해야하는 논리 : iw 가 동일하면서 같은 컬럼(mon, tue, wed ,....) 에 들어가 있는 애는 1개다. 그러니까 18주에는 월요일이 한개고 19주에도 월요일이 한개며
18일에는 월요일 한개, 화요일한개 수요일한개... 이런식으로 구성되어 있다.
그래서,
iw 로 group by 를 한다면 iw 가 같은 애들은 월요일에 한개 화요일에 한개 즉 각각 한개씩만 존재하기 때문에( NULL제외) max 나 min 을 한다면 각 주차에 18, 19 20...주차에 해당하는 dt는 각 칼럼에 각 한개씩만 존재하게 된다 그래서 group by iw 로 해주고 가장 작은 값만 출력하면된다.
SELECT iw , MIN( DECODE ( d , 2, dt , ' ' ) ) mon ,
MIN ( DECODE ( d , 3, dt, ' ' ) ) tue,
MIN ( DECODE ( d , 4, dt, ' ' ) ) wed,
MIN ( DECODE ( d , 5, dt, ' ' ) ) thu ,
MIN ( DECODE ( d , 6, dt, ' ' ) ) fri,
MIN ( DECODE ( d, 7 , dt , ' ' ) ) sat,
MIN ( DECODE ( d, 1 , dt , ' ' ) ) sun
FROM
(
SELECT dt, TO_CHAR( dt, 'iw' ) iw , TO_CHAR(dt, 'd' ) d
FROM
(
SELECT TO_DATE('201905' , 'YYYYMM' ) + (level-1) dt
FROM dual
CONNECT BY LEVEL <= LAST_DAY ( TO_DATE( '201905' , 'YYYYMM' ) )
- TO_DATE ( '201905' , 'YYYYMM' ) + 1 ) )
)
)
GROUP BY iw
order by iw
10. 생각해봐야할거는 지금 데이터는 전혀 움직이지 않는데 만약 일요일이 앞으로 나온다면?
일월화수목금토일로 된다면?
IW : 월요일을 주의 시작으로 본다.
그러면 이번주의 일요일의 주차수는 다음주의 주차수에 포함 된다고 할 수 있다.
즉 이번주가 3주차인데 이번주 일요일은 4주차에 속한다.
주차기준이 월요일부터라서 ( IW) 발생하는 문제여서 일요일을 다음주차로 인식시켜주면 된다.
월화수목금토는 동일한데 모든 일요일을 전부 -> 다음 주차로 인식시켜주면 된다.
SELECT DECODE( d , 1 , w + 1 , w ) --iw ,
MIN ( DECODE ( d, 1 , dt , ' ' ) ) sun,
MIN( DECODE ( d , 2, dt , ' ' ) ) mon ,
MIN ( DECODE ( d , 3, dt, ' ' ) ) tue,
MIN ( DECODE ( d , 4, dt, ' ' ) ) wed,
MIN ( DECODE ( d , 5, dt, ' ' ) ) thu ,
MIN ( DECODE ( d , 6, dt, ' ' ) ) fri,
MIN ( DECODE ( d, 7 , dt , ' ' ) ) sat
FROM
(
SELECT dt, TO_CHAR( dt, 'iw' ) iw , TO_CHAR(dt, 'd' ) d
FROM
(
SELECT TO_DATE('202005' , 'YYYYMM' ) + (level-1) dt
FROM dual
CONNECT BY LEVEL <= LAST_DAY ( TO_DATE( '202005' , 'YYYYMM' ) )
- TO_DATE ( '202005' , 'YYYYMM' ) + 1 ) )
)
)
GROUP BY DECODE( d , 1 , w+1 , w ) --iw
order by DECODE ( d, 1, w+1 , w ) ; --iw
**** 새롭게 알게된 것
DECODE 함수에서 꼭 첫번째 인자에 나오는애로 반환이 되지 않아도 된다는 것
여기서처럼 ( DECODE ( d , 1 , w+1 , W )
즉 d가 1일때 d를 뭐로 결정해주는것이 아니라 w에 1을 더한것으로 w를 변경시켜 줄 수 있다.
그런데 이거 예전에 문제풀다가 sal을 update 해줘야할때도 그냥 sal + 200 이라고 만써줬어 내 생각에는 w = w+1 이라거나 sal = sal + 200 이렇게 써줘야 할거같은데 = 표시는 그냥 생략하고 결과값이 될 값만 써주는군. !
최종 :
SELECT MIN(DECODE(d, 1, day)) sun, MIN(DECODE(d, 2, day)) mon,
MIN(DECODE(d, 3, day)) tue, MIN(DECODE(d, 4, day)) wed,
MIN(DECODE(d, 5, day)) thu, MIN(DECODE(d, 6, day)) fri,
MIN(DECODE(d, 7, day)) sat
FROM
(SELECT TO_DATE('202002', 'YYYYMM') + LEVEL-1 day,
TO_CHAR(TO_DATE('202002', 'YYYYMM') + LEVEL-1, 'd') d,
TO_CHAR(TO_DATE('202002', 'YYYYMM') + LEVEL-1, 'iw') iw
FROM dual
CONNECT BY LEVEL <= TO_CHAR(LAST_DAY(TO_DATE('202002', 'YYYYMM')), 'DD'))
GROUP BY DECODE(d, 1, iw+1, iw)
ORDER BY DECODE(d, 1, iw+1, iw);
댓글
댓글 쓰기