[SQL고수되기] 여덟번째 이야기

출처 : 네이버지식인 노하우
http://kin.naver.com/knowhow/detail.nhn?d1id=8&dirId=8&docId=553053&qb=U1FM6rOg7IiY65CY6riwLeyXrOuNn+uyiOynuA==&enc=utf8&section=kin&rank=1&search_sort=0&spq=0&pid=goY3Qc5Y7twssZRccelssc--088148&sid=TovECl6Ri04AAH8Hfo8

어제 머리 아프게(복잡하게) 그 어렵다는 유효데이터 가공 작업을 해보았습니다.

많이 지치죠? 머리가 터질것 같은 분들도 계시고. 저 혼자생각이였으면 좋겠음 ^^

이제 부터는 한 요청(주제)에 대한 재정의(Defined) 방법에 대해 상황에 맞는 기법을

소개하면서 이야기 해나갈까합니다. 오늘부터 정말 쿼리 고수가 되기 위한 시작이니

잘 이해하셨으면 좋겠습니다.

 

요청) 개발자님 저 지난달 아르바이트생별 출근현황 다음과 같은 형태로 보고싶어요.

　　　┌─────┬────┬───┬──┬──┬──┬────

　　　│　사번　　│근무일수│수당합│ 1일│ 2일│ 3일│......

　　　├─────┼────┼───┼──┴──┴──┴────

　　　│　10010001│　20일　│ 20000│ √ │ 　 │ √ │......

　　　│　10010002│　25일　│ 50000│ √ │ √ │ √ │......

　　　│　10010003│　21일　│ 80000│ 　 │ √ │ √ │......

     참고) √는 일자별 출근 확인

 

  고객이 요청한 내용을 보면 지난달이라는 말이 있습니다. 또 출근현황부라는 말도 있고요

음 그림을 보니 사번,근무일수,수당합, 1일, 2일.... 등이 있네요

  정리해보면 고객은 출근현황이라고 했지만 월별,사원별 근무시간과 수당지급 현황이네요. 돈이 아까운 모양이에요. ㅎㅎㅎ(농담)

 

  그럼 분석해볼까요. 딱보니까 출퇴근형황 테이블을 활용하면될것 같은데요... 맞나요?

근데 우리가 아는(가지고있는) 정보로 가지고 있는건 딸랑 사번 뿐이네요. ~.~;

차례대로 분석해보죠

 

1) 정보확인 : 2010년 4월 선택시

SELECT * FROM WORK_INF WHERE ATTN_YMD LIKE'201004%';

 

2) 근무일수 : 출근했느냐? CHECK_IN is NULL??

SELECT emp_no
      ,count(CHECK_IN) as "출근일수1"
      ,sum(decode(to_char(check_in,'yyyymmdd'),attn_ymd,1,0)) as "출근일수2"
FROM WORK_INF
WHERE ATTN_YMD LIKE '201004%'
GROUP BY emp_no
ORDER BY 1

여기 한가지 확인하고 넘어가죠. 출근일수1과 출근일수2는 같다? 참일까요 거짓일까요?

그림으로 보면 참이네요. 그쵸? 그럼 참이죠?.................

다른 전제를 달아보겠습니다. 만약에 CHECK_IN이 문자 타입이고 날짜가 Not Null이여서

' '값을 입력되어있다.라면 어떨까요? count(*)는 null아니면 +1입니다. 당연히 문제겠죠.

이건 설계시에 잘못된 오류입니다. 정보 타입은 Date이지만 Column타입이 문자이고 Not Null

이기에 유효하지 않은 값인 ' '을 입력하였습니다. 제가 설명드리려고 억지로 만든게 맞지만 여기서 알아두셔야 할점은 명확한/정확한/유용한 정보의 생성이 중요한 점을 강조합니다.

이렇게 쿼리 확정하겠습니다.

SELECT emp_no
      ,count(CHECK_IN) as "출근일수"
FROM WORK_INF

WHERE ATTN_YMD LIKE'201004%'

GROUP BY emp_no
ORDER BY 1;

 

 

3) 수당합 : 그냥 TIME_ALLOW의 합이네요

SELECT emp_no
      ,count(check_in) as "출근일수"
      ,sum(time_allow) as "수당합"
FROM WORK_INF
WHERE ATTN_YMD LIKE '201004%'
GROUP BY emp_no
ORDER BY 1

 

 

4) 일자별 출근표시 : ATTN_YMD 일자별 표현이네요

SELECT emp_no
      ,SUBSTR(ATTN_YMD,7,2) day

FROM WORK_INF

WHERE ATTN_YMD LIKE'201004%';

 

SELECT emp_no
      ,decode(substr(attn_ymd,7,2),'01','√') d01
      ,decode(substr(attn_ymd,7,2),'02','√') d02
      ,decode(substr(attn_ymd,7,2),'03','√') d03
      ,decode(substr(attn_ymd,7,2),'04','√') d04
      ,decode(substr(attn_ymd,7,2),'05','√') d05
      ,decode(substr(attn_ymd,7,2),'06','√') d06
      ,decode(substr(attn_ymd,7,2),'07','√') d07
      ,decode(substr(attn_ymd,7,2),'08','√') d08
      ,decode(substr(attn_ymd,7,2),'09','√') d09
      ,decode(substr(attn_ymd,7,2),'10','√') d10
      ,decode(substr(attn_ymd,7,2),'11','√') d11
      ,decode(substr(attn_ymd,7,2),'12','√') d12
      ,decode(substr(attn_ymd,7,2),'13','√') d13
      ,decode(substr(attn_ymd,7,2),'14','√') d14
      ,decode(substr(attn_ymd,7,2),'15','√') d15
      ,decode(substr(attn_ymd,7,2),'16','√') d16
      ,decode(substr(attn_ymd,7,2),'17','√') d17
      ,decode(substr(attn_ymd,7,2),'18','√') d18
      ,decode(substr(attn_ymd,7,2),'19','√') d19
      ,decode(substr(attn_ymd,7,2),'20','√') d20
      ,decode(substr(attn_ymd,7,2),'21','√') d21
      ,decode(substr(attn_ymd,7,2),'22','√') d22
      ,decode(substr(attn_ymd,7,2),'23','√') d23
      ,decode(substr(attn_ymd,7,2),'24','√') d24
      ,decode(substr(attn_ymd,7,2),'25','√') d25
      ,decode(substr(attn_ymd,7,2),'26','√') d26
      ,decode(substr(attn_ymd,7,2),'27','√') d27
      ,decode(substr(attn_ymd,7,2),'28','√') d28
      ,decode(substr(attn_ymd,7,2),'29','√') d29
      ,decode(substr(attn_ymd,7,2),'30','√') d30
      ,decode(substr(attn_ymd,7,2),'31','√') d31
FROM WORK_INF
WHERE ATTN_YMD LIKE '201004%';

 

SELECT emp_no
      ,max(decode(substr(attn_ymd,7,2),'01',decode(check_in,'','','√'))) d01
      ,max(decode(substr(attn_ymd,7,2),'02',decode(check_in,'','','√'))) d02
      ,max(decode(substr(attn_ymd,7,2),'03',decode(check_in,'','','√'))) d03
      ,max(decode(substr(attn_ymd,7,2),'04',decode(check_in,'','','√'))) d04
      ,max(decode(substr(attn_ymd,7,2),'05',decode(check_in,'','','√'))) d05
      ,max(decode(substr(attn_ymd,7,2),'06',decode(check_in,'','','√'))) d06
      ,max(decode(substr(attn_ymd,7,2),'07',decode(check_in,'','','√'))) d07
      ,max(decode(substr(attn_ymd,7,2),'08',decode(check_in,'','','√'))) d08
      ,max(decode(substr(attn_ymd,7,2),'09',decode(check_in,'','','√'))) d09
      ,max(decode(substr(attn_ymd,7,2),'10',decode(check_in,'','','√'))) d10
      ,max(decode(substr(attn_ymd,7,2),'11',decode(check_in,'','','√'))) d11
      ,max(decode(substr(attn_ymd,7,2),'12',decode(check_in,'','','√'))) d12
      ,max(decode(substr(attn_ymd,7,2),'13',decode(check_in,'','','√'))) d13
      ,max(decode(substr(attn_ymd,7,2),'14',decode(check_in,'','','√'))) d14
      ,max(decode(substr(attn_ymd,7,2),'15',decode(check_in,'','','√'))) d15
      ,max(decode(substr(attn_ymd,7,2),'16',decode(check_in,'','','√'))) d16
      ,max(decode(substr(attn_ymd,7,2),'17',decode(check_in,'','','√'))) d17
      ,max(decode(substr(attn_ymd,7,2),'18',decode(check_in,'','','√'))) d18
      ,max(decode(substr(attn_ymd,7,2),'19',decode(check_in,'','','√'))) d19
      ,max(decode(substr(attn_ymd,7,2),'20',decode(check_in,'','','√'))) d20
      ,max(decode(substr(attn_ymd,7,2),'21',decode(check_in,'','','√'))) d21
      ,max(decode(substr(attn_ymd,7,2),'22',decode(check_in,'','','√'))) d22
      ,max(decode(substr(attn_ymd,7,2),'23',decode(check_in,'','','√'))) d23
      ,max(decode(substr(attn_ymd,7,2),'24',decode(check_in,'','','√'))) d24
      ,max(decode(substr(attn_ymd,7,2),'25',decode(check_in,'','','√'))) d25
      ,max(decode(substr(attn_ymd,7,2),'26',decode(check_in,'','','√'))) d26
      ,max(decode(substr(attn_ymd,7,2),'27',decode(check_in,'','','√'))) d27
      ,max(decode(substr(attn_ymd,7,2),'28',decode(check_in,'','','√'))) d28
      ,max(decode(substr(attn_ymd,7,2),'29',decode(check_in,'','','√'))) d29
      ,max(decode(substr(attn_ymd,7,2),'30',decode(check_in,'','','√'))) d30
      ,max(decode(substr(attn_ymd,7,2),'31',decode(check_in,'','','√'))) d31
FROM WORK_INF
WHERE ATTN_YMD LIKE '201004%'
GROUP BY emp_no
ORDER BY 1

 

전개해 나가는 과정을 보시면 한번에 하는게 아니라 부분적으로 여러개의 컬럼들을
활용하여 발전시켜 나가는 것을 볼수 있습니다.

 

-> 일자만들기 : substr(attn_ymd,7,2) day
-> 일자별표시 : decode(substr(attn_ymd,7,2),'01','√')
-> 일자별 출근표시 : decode(substr(attn_ymd,7,2),'31',decode(check_in,'','','√'))

 

5) 결과표시
SELECT emp_no
      ,count(check_in) as "출근일수"
      ,to_char(sum(time_allow),'9,999,990') as "수당합"
      ,max(decode(substr(attn_ymd,7,2),'01',decode(check_in,'','','√'))) d01
      ,max(decode(substr(attn_ymd,7,2),'02',decode(check_in,'','','√'))) d02
      ,max(decode(substr(attn_ymd,7,2),'03',decode(check_in,'','','√'))) d03
      ,max(decode(substr(attn_ymd,7,2),'04',decode(check_in,'','','√'))) d04
      ,max(decode(substr(attn_ymd,7,2),'05',decode(check_in,'','','√'))) d05
      ,max(decode(substr(attn_ymd,7,2),'06',decode(check_in,'','','√'))) d06
      ,max(decode(substr(attn_ymd,7,2),'07',decode(check_in,'','','√'))) d07
      ,max(decode(substr(attn_ymd,7,2),'08',decode(check_in,'','','√'))) d08
      ,max(decode(substr(attn_ymd,7,2),'09',decode(check_in,'','','√'))) d09
      ,max(decode(substr(attn_ymd,7,2),'10',decode(check_in,'','','√'))) d10
      ,max(decode(substr(attn_ymd,7,2),'11',decode(check_in,'','','√'))) d11
      ,max(decode(substr(attn_ymd,7,2),'12',decode(check_in,'','','√'))) d12
      ,max(decode(substr(attn_ymd,7,2),'13',decode(check_in,'','','√'))) d13
      ,max(decode(substr(attn_ymd,7,2),'14',decode(check_in,'','','√'))) d14
      ,max(decode(substr(attn_ymd,7,2),'15',decode(check_in,'','','√'))) d15
      ,max(decode(substr(attn_ymd,7,2),'16',decode(check_in,'','','√'))) d16
      ,max(decode(substr(attn_ymd,7,2),'17',decode(check_in,'','','√'))) d17
      ,max(decode(substr(attn_ymd,7,2),'18',decode(check_in,'','','√'))) d18
      ,max(decode(substr(attn_ymd,7,2),'19',decode(check_in,'','','√'))) d19
      ,max(decode(substr(attn_ymd,7,2),'20',decode(check_in,'','','√'))) d20
      ,max(decode(substr(attn_ymd,7,2),'21',decode(check_in,'','','√'))) d21
      ,max(decode(substr(attn_ymd,7,2),'22',decode(check_in,'','','√'))) d22
      ,max(decode(substr(attn_ymd,7,2),'23',decode(check_in,'','','√'))) d23
      ,max(decode(substr(attn_ymd,7,2),'24',decode(check_in,'','','√'))) d24
      ,max(decode(substr(attn_ymd,7,2),'25',decode(check_in,'','','√'))) d25
      ,max(decode(substr(attn_ymd,7,2),'26',decode(check_in,'','','√'))) d26
      ,max(decode(substr(attn_ymd,7,2),'27',decode(check_in,'','','√'))) d27
      ,max(decode(substr(attn_ymd,7,2),'28',decode(check_in,'','','√'))) d28
      ,max(decode(substr(attn_ymd,7,2),'29',decode(check_in,'','','√'))) d29
      ,max(decode(substr(attn_ymd,7,2),'30',decode(check_in,'','','√'))) d30
      ,max(decode(substr(attn_ymd,7,2),'31',decode(check_in,'','','√'))) d31
FROM WORK_INF
WHERE ATTN_YMD LIKE '201004%'
GROUP BY emp_no
ORDER BY 1

 

오늘 이야기한 기법은 단순하게 Row(행) 데이터를 식별자 재정의를 통해 만드는 한 케이스에
대해 살펴보앗습니다. 어려워보이지만 EMP_NO별,일자별에서 EMP_NO별로 GROUP BY 절을 통해
식별자를 바꾼것이 전부입니다. 식별자가 N->1로 변경되었으니 다른 정보들도 종류와 내용에
따라 COUNT,MAX,SUM 등의 집계함수를 통해 컬럼정보를 표현하는 것입니다.

 

첫 쿼리 테크닉이였는데 조금은 어려우셨던 분들도 계셨을겁니다. 그래서 제가 복습차원에서 문제를 하나 내보았습니다. 가능하신분들은 한번 만들어 보시면 좋을듯합니다.
문제) 4월 출퇴근형황 정보를기준으로 다음처럼 요일별 근무시간합을 조회해보세요
　　　┌────┬───┬───┬───┬───┬───┬───┬───┐
　　　│　사번　│　일　│　월　│　화　│　수　│　목　│　금　│　토　│
　　　├────┼───┼───┼───┴───┴───┴───┴───┤
　　　│10010001│10시간│10시간│10시간│10시간│10시간│10시간│10시간│
      ......

 

오늘 수고 많으셨습니다. 그럼 다음 이야기 시간에 뵙겠습니다.