[SQL 고수되기] 아홉번째 이야기

출처 : 네이버지식인 노하우
http://kin.naver.com/knowhow/detail.nhn?d1id=8&dirId=8&docId=553087&qb=U1FMIOqzoOyImOuQmOq4sCDslYTtmYnrsojsp7g=&enc=utf8&section=kin&rank=1&search_sort=0&spq=0&pid=go1vx35Y7t0ssskYj7wssc--063939&sid=TozeLPJwjE4AABooIow

지난 이야기에서 함 해보시라고 한거 해보셨지? ^^

문제) 4월 출퇴근형황 정보를기준으로 다음처럼 요일별 근무시간합을 조회해보세요
　　　┌────┬───┬───┬───┬───┬───┬───┬───┐
　　　│　사번　│　일　│　월　│　화　│　수　│　목　│　금　│　토　│
　　　├────┼───┼───┼───┴───┴───┴───┴───┤
　　　│10010001│10시간│10시간│10시간│10시간│10시간│10시간│10시간│
      ......

전 이렇게 했습니다.

SELECT emp_no "사번"
      ,sum(decode(week_gbn,'1',work_times))||' Hr' "일"
      ,sum(decode(week_gbn,'2',work_times))||' Hr' "월"
      ,sum(decode(week_gbn,'3',work_times))||' Hr' "화"
      ,sum(decode(week_gbn,'4',work_times))||' Hr' "수"
      ,sum(decode(week_gbn,'5',work_times))||' Hr' "목"
      ,sum(decode(week_gbn,'6',work_times))||' Hr' "금"
      ,sum(decode(week_gbn,'7',work_times))||' Hr' "토"
FROM  (
        SELECT emp_no
              ,to_char(to_date(attn_ymd,'yyyymmdd'),'d') week_gbn  /* 요일구분 */
              ,decode(Check_in,'',0,ceil((check_out-check_in)*24)) work_times  /* 근무시간 */
        FROM WORK_INF
        WHERE ATTN_YMD like '201004%'
      )
GROUP BY SNO

 

결과)

 

요일구분)

 step1) 컬럼선택

        -> attn_ymd: 출근일자(년월일:문자)

 step2) 요일정보 획득을 위한 타입변환

        -> to_date(attn_ymd,'yyyymmdd')

 step3) 요일정보 획득

        -> to_char(to_date(attn_ymd,'yyyymmdd'),'d')

           참고) TO_CHAR(날짜정보,'형식') : 특정 형식(년,월,일,요일,시간...)의 문자정보 리턴
 

근무시간)

 step1) 컬럼선택

        -> check_out,check_in: 출퇴근일시(Date타입)

 step1) 로직구성 : 출근일시가 null이면 0 아니면 근무시간 계산한다.

                   단, check_out이 null이면 자동 0이됨( 퇴근잘 직으삼 ^^)

        -> (check_out-check_in)*24: 날짜간의 계산은 일이므로 시간단위로 바꿔줌
        -> Ceil((check_out-check_in)*24): 정수형으로 바꿔줌
        -> decode(Check_in,'',0,Ceil((check_out-check_in)*24))
           참고) Oracle상에서 ''(빈문자열)은 Null입니다. 공백(' ')과는 다름

 

그럼 하나만 더 확장해볼까요.

┌────┬───┬───┬───┬───┬───┬───┬───┬────┐
│　사번　│　일　│　월　│　화　│　수　│　목　│　금　│　토　│　전체　│
├────┼───┼───┼───┴───┴───┴───┴───┴────┤
│10010001│10시간│10시간│10시간│10시간│10시간│10시간│10시간│1000시간│

"전체"라는 항목이 추가되었습니다.

어떻게 하면 좋을까요? 10초면 나오죠?(^^)

개발자 생각중 : 전체니까 일~토인데.....아하~ 그냥 조건없이 합치면 되겠네!!

그렇습니다. 아주 간단한 생각이죠 물론 조건이 없는건 아니죠 앞에 서두를 빼먹은듯

아르바이트생병,월별은 기본 전제니까 빼고 한 말(독백)인듯하네요. 아무튼 맞는 로직입니다.

그럼 구현해볼까요.

 

SELECT emp_no "사번"
      ,sum(decode(week_gbn,'1',work_times))||' Hr' "일"
      ,sum(decode(week_gbn,'2',work_times))||' Hr' "월"
      ,sum(decode(week_gbn,'3',work_times))||' Hr' "화"
      ,sum(decode(week_gbn,'4',work_times))||' Hr' "수"
      ,sum(decode(week_gbn,'5',work_times))||' Hr' "목"
      ,sum(decode(week_gbn,'6',work_times))||' Hr' "금"
      ,sum(decode(week_gbn,'7',work_times))||' Hr' "토"
      ,sum(work_times)||' Hr' "전체"
FROM  (
        SELECT emp_no
              ,to_char(to_date(attn_ymd,'yyyymmdd'),'d') week_gbn  /* 요일구분 */
              ,decode(Check_in,'',0,ceil((check_out-check_in)*24)) work_times  /* 근무시간 */
        FROM WORK_INF
        WHERE ATTN_YMD like '201004%'
      )
GROUP BY SNO

 

결과)

 

 

간단하죠?

 

논리적으로 많은 구현을 해보시는게 정말 좋다는 것을 다시한번 말씀드립니다.

그래서 WORK_INF 1~3월 데이터 한번 만들어보세요. 또 현재기준 5월 데이터도 만들어보시면

좋고요.^^

 

다음 이야기에선 또하나의 Table정보(아르바이트생)를 만들어볼까합니다.

 

그럼 수고하세요.