네이버 sql고수 이야기

으아~ Database와 동고동락한지 벌써 13년이네요.

정말 10년전과 비교하면 많이 변한듯합니다.

걱정이 되는 것은 글을 많이 보긴 했지만 집필해보진 않았기에 오타나 이야기 진행상에

미숙함 점이 있진 않을까 우려되기도 합니다. 하지만 조금이나마 시작하시는 분들에

도움이 되고자 한것이니 재미없고 따분한 내용일지라도 이쁘게 봐주시고 잘 봐주셨으면

하는 맘입니다.

 

지금까지 수많은 Database를 접해왔지만 제 입맛에 딱들어서 남다른 애정을 가지고

침구 처럼 지내온 DB가 있습니다. Oracle입니다.

Oracle잘하느냐? 물어보면 쿼리 잘짜느냐?  라고 물어보는 것이라고 봐도 무방할 것입니다.

물론 SQL이라는건 하나의 도구일 뿐이지만 다들 SQL(Query) 짜는데 혈안이 되어 있는것

같습니다.

 

그래서 수많은 중요한 내용이 있지만 그중에서 개발자들이 많이 접하게 되는 SQL에 대해서

이야기 해볼까합니다.

 

어떻게 하면 쿼리를 잘 짤수 있을까? 그러려면 어떤 책을 봐야하나? 또 어디서부타 시작해야하지? 그럼 응용은 어떻게 할까? 현재 내가 잘하는건가? 쿼리 만큼은 어떤 끝이 보이지 않는 무궁무진한 언어처럼 보입니다.

 

그 이유는 바로 방법만을 찾으려고 고집해서 일겁니다. 너무 막연한 얘기처럼 보이겠지만 지금 부터 제가 드리는 이야기가 그 생각을 바꾸어 드릴수 있으면 좋겠다. 이런 맘가짐으로 이 이야기에 서두를 시작합니다.

 

세상에는 수많은 유형의 정보들이 분유별로 존재합니다. 하지만 한가지 공통점은 종류가 무엇이든 상관없이 관계형 데이터베이스 저장고에 등록되고 관리됩니다. 그것이 Oracle이 되었던 MsSQL이 되었던 말입니다. 하지만 어디까지나 그것은 물리적으로 각 데이터베이스의 특징에 맞게 물리적으로 저장고(하드디스크)에 잘~~~ 보관하는데에 주 목적이 있습니다. 그럼 잘 저장되었는지 어떻게 확인할까요. 보통은 SELECT * FROM 테이블 이렇게 하실겁니다. 물론 Library를 활용한 검증을 하시는 분들도 계실겁니다. 앞에 경우던 뒤에 경우던 공통점은 물리적인 Entity의 확인일 뿐입니다.

 

저기요~~ SELECT ~ FROM 이거 SQL 질의아닌가요? 라고 반문 하실겁니다. 맞습니다. 하지만 정확히 말하면 물리적정보를 단순 조회하는 방법일 뿐이지 SQL의 주 목적이나 이유가 되지는 못한다는 것입니다. 그럼 SQL은 어떻게 표현하는게 맞을까요? 논리적인 Entity 정의 이것이 정확한 표현일 것입니다. 다음 그림을 보면서 한번 생각해 보도록 하겠습니다.

 

　　┌─────┐　┌─────┐　┌─────┐　　　　　┌─────┐

　　│ 테이블1　│　│ 테이블2　│　│ 테이블3　│　......　│ 테이블N　│

　　└─────┘　└─────┘　└─────┘　　　　　└─────┘

 

　　　A사용자 요청(SR-1) : 월별 생산현황을 보고싶다...

 　　 B사용자 요총(SR-2) : 일일 판매실적을 사원별로 보고싶다....

 

 위 두개의 요청사항(Service Request)에 대해 분석을 하게 될 것입니다. 분석결과

SR-1을 처리하기 위해서는 테이블1,테이블2 정보를 활용해야 할것으로 분석되었고, SR-2

를 처리하기 위해서는 테이블1,테이블2,테이블3 정보를 활용해야 할 것으로 분석되었습니다.

그럼 무엇을 위한 분석일까요? 바로 SR-1을 표현하기 위한 새로운 Entity를 정의한 결과라고

보면 좋을 것입니다. 그럼 새로운 Entity(=월별생산현황)를 만들기 위해서 어떤 항목들이

필요한지..등등 테이블 설계를 하드시 진행하게 됩니다. 하지만 중요한건 물리적으로 존재하는

테이블1,테이블2를 연결하여 그안에 정의된 내용을 기준으로 만들어야 한다는 전제가 았다는 점에서 다르다고 말할수 있습니다. 이처럼 실제물리적으로는 일자별로 가지고 있지만 표현해야하는 내용 년.월이라면 또 일별로 저장되어 있는 생산량을 월별 총생산량으로 표현해야 한다면

SQL을 작성해서 SUBSTR(생산일자,1,4)||'년'||SUBSTR(생산일자,5,2)||'월' 이런식으로 표현해야

할 것입니다. 또 그것이 이름을(Alias) "생산년월"로 정의하고자 할 것입니다.

이처럼 정의된(한) 내용을 표현하기위해 여러가지 방식과 방법으로 SQL이라는 DB 언어를 통해

표현하게 됩니다. 안타까운것은 테이블은 하나지만 요청자,개발자,사용자 등 수많은 Request에

대해 수만가지의 논리적인 정보들이 만들어지게 됩니다.

 

필요(요청)에 따라서 실시간으로 표현되고 사라지고 하는 논리적인 Entity들,바로 이것들을

만들기 위한 방법이 SQL언어라고 생각하시면 될것입니다. SQL 잘 짜라면 문법도 중요하지만

테이블 설계하는것과 똑같이 논리적인 분석 설계가 잘 되어야 쿼리를 잘 짜게 될것입니다.

쿼리를 빨리 짜기위해 모방하는 것은 좋습니다. 하지만 그게 계속 반복된다면 자기 생각이

아니기에 몇년후에 경력이 쌓여서도 언제나 복사&붙여넣기의 반복은 계속될 것입니다. 분석은

발했지만 어떻게 구현하지? 당연히 어려울수 있습니다. 이때 도움을 받던 사례를 보던 전개하

는 방법을 내가 설계한 내용을 기준으로 한다면 아~~ 이렇게 표현하면 되는구나 하고 자기

지식이 되는 것을 느끼게 될것입니다.

 

과학자는 실제 실험을 통해 문제를 해결하지만 수학자는 논리적인 이론을 통해 대부분의 문제를

해결해 나갑니다. 우리는 어느 쪽일까요?

이 문제의 해답을 여러분께 마기고 오늘은 이만 할까합니다.

 

 

첫번째 이야기에서 물리/논리 엔터티라는 말이 나왔었습니다.
이것에 대해 구체적으로 이야기를 해볼까합니다.

 

물론 쿼리를 잘 짜는것도 중요합니다. 그러나 가장 중요한 것은 물리적인 Entity입니다.
최초에 DB설계시에 논리적으로  RDBMS에 맞게 분석을 통해 잘 정리된(정형화된,구조화된,무결성의...)
데이터일수록 쿼리 설계 시 막강한 영향력을 발휘하게 됩니다.

 

쿼리를 짜려다 보니 헉, 이건 생각 못했네. 테이블 바꿔야겠어,,, 음 어떻게.,,
보통 이런 생각이나 말들 무지 많이 하게 됩니다.
그때, 그당시의 요청(요구)사항에 1;1로 Mapping되게 설계되었다면 그당시는 검토/승인
되겠지만 시간이 지나고 데이터가 쌓이고,,,, 돌아올수 없는 강을 건넜을때 위와 같은 일이 생긴다면
어떻게 할까요? 보통 예산을 잡고 Version Up이라는 명명하에 2차 설계에 들어가겠지요.
당연히 핑계아닌 핑계를 되면서,, 많이들 경험했을 것으로 생각됩니다.

 

앞에 경우는 그나마 당행입니다. 바꿀순 있기에. 하지만 더 난감한 경우는
아~~~주 오랜된 시스템의 경우입니다. 기본 10년이상 지났고 쌓인 데이터의 량이 테라바이트
(제 경우라면 현제 시스템이 8000기가바이트 Tablespace(TS)가 48개이고, 70%정도의 TS가

Used 85%입니다.)라면 얘기가 달라집니다. 그당시 순차적(Sequence Table) 방식이였다면

더더더더더욱 난감한 상황에 봉착하게 됩니다.

 

이처럼 잠깐의(잠깐이란 그리 짧진 않음 ^^) 실수로(물론 착오일수 있음) 넘겨버린 일이 다음엔
엄청난 결과로 돌아온다는 것을 DB 분석,설계하시는 분들은 명심 또 명심해야 할것입니다.

 

그럼 어떻게 하면 물리적 Entity(테이블) 설계에 도사가 될 수 있을까요.
이건 제 선입견 일 수 있지만 메모하는 습관을 가져야 한다고 생각합니다. 메모하는 습관?
너무 쉽지 않은가? 너무 단순하지 않은가? 라고 말씀하시는 분들도 계실것입니다. 혼자의 힘으로
독불장군이 되기 보다는 여러 사람들 설계담당자,사용자,일반고객,업무전문가 발생될 수 있는
또는 발생가능 일이나 사건, 내용에 대해 일일이 기록하고 이 내용을 기준으로 Entity를 순차적으로
정규화 시킨다면 그만큼의 리스크는 줄일 수 있을것입니다. 자기 자신의 짧은 경험이나 소견으로
Defined시킨다면 책임은 그담음 사람의 목으로 돌리게 되는 것입니다. 따라서 반드시 아는 길도
물어가라 라는 말처럼 기록하고 정리하고 문서화하고 달팽이 돌듯이 반복해야만 질 좋은 정보들이
생겨나게 될것입니다.

 

잠깐 쉬어가는 시간으로 실제로 2002년도경 제 경험을 얘기해볼까합니다. 공공기관 국방-조달 연계
시스템을 개발하기 위해 투입된 적이 있었습니다. 구축(SI)기간은 대략 4개월이고 순수하게 유저
보단 시스템간의 정보 연계가 목적이였기에 웹보다는 시스템 적인 작업이 많았습니다. 1차,2차,3차
....n차 테이블 설계하는데만 2달 반이 걸렸습니다. 실제 구현은 보름. 테스트(단위,사용자,통합)는
거의 한달정도 진행되었습니다. 물론 잘하시는 분들은 금방이였겠지만 기관 담당자들간의 이슈나
실제 시스템 간의 Risk, 또 연계 시스템간의 차이 등등 서로 다른 이견과 내용, 상황등이 있어
출장,회의,정리 이렇게만  2달이 넘게 소요되게 되었습니다. 하지만 다들 서두르지 않았던 것으로
기억합니다. 다행이 다들 DB설계의 중요성을 알았기에 가능했던 일이였던 것으로 기억됩니다.

후후 쉬어가는 시간이라고 말해놓고 너무 딱딱한 얘기였네요.

앞으로 몇번(대략 20~30 예상)이 될지 모르지만 이야기를 만들것입니다. 그때가 되면 이런 글(말)보단
실제 쿼리 테크닉이 이야기 될텐데 아마도 머리가 아프실 분들도 계실지 모르겠습니다. 단순 쿼리가
아니라 튜닝이 병행되는 쿼리라면 더더욱 난감하실수도 있으실겁니다.
하지만 기본이 갖춰진다면 아하~ 라는 감탄사가 나오실꺼라 믿어 의심하지 않습니다.
다시한번 말씀드리면 분명 구축된 물리적인 Entity와 설계하려는 논리적인 Enitty(SQL Query)는
다르다는 것을 말씀드고 싶습니다.

 

다음 이야기에서는 물리적인 Entity(테이블) 생성 잘하기에 대해 이야기해볼까 합니다.

 

내가 잘 만든 테이블 열 쿼리 안부럽다. 아자!! 화이팅

 

 

QL고수되기 - 세번째 이야기

 포그미(pugmi486) 

2010.05.15 12:27

테이블 설계에 대해서 이야기해볼까합니다.

앞으로 이야기할 내용은 쿼리가 잘 수행되기 위해 고려해야 할 내용이라는 점을 다시한번 말씀드립니다.

 

몇달전에 제가 현제 소속되어 있는 부서로 한통의 메일이 전달되었습니다. 현업(설비기술팀)에서 온 짜증이

섞인 내용이었습니다. 이과장님 저희 시스템으로 조회하면 RawData가 자 나오는데 A시스템을 -20기간 검

색하면 조회가 잘되지 않습니다. 어쩌구 저쩌구.. 빠른 조치 부막드립니다. 라는 한통의 비아냥 섞은 내용이였습니다. 그래서 분석에 들어갔습니다. 해당 내용을 두 시스템의 해당 테이블들이 생성되는 시점부터 관리되는 방법 및 테이블 구조까지 확인해 보았습니다. 억~~헉~~ 문제가 심각한 부분이 발견되었습니다. 조회가 잘된다는 시스템은 단순하게 정리된 정보를 메일 새벽에 전달받아 쌓아놓는 시스템이고 조회가 잘 안된다는 시스템은 1시간에 두번 File정보를 interface받아 원시테이블을 만들어내는 작업(Read,Write,ReWrite)을 반복적으로 수행하여 결과(Row)를 만들어 내는 시스템 이였습니다. 더 안타까운 점은 이렇게 생성된 정보를 앞에 조회 잘되는 시스템 전달해 준다는 점이였습니다. 조금 애석하고 답답한 생각이 들었습니다. 단순하게 조회만 주로 하시는 사용자 분들은 똑같은 정보 아니냐? 시스템 관리에 문제있는거 아니냐? 시스템 개발 및 관리하는 사람들 실력이 없는거 아니냐? 정말 당황스러웠습니다. A시스템 담당자들은 당하는 느낌이 들었습니다. 그렇게 따지면 A시스템 담당자 입장에서도 파일 줄때부터 아주~~ 기계적인(원시적인,가공되지 않은) 정보를 주지말고 그쪽에서 어느정도 정제된 정보를 주어야 하는거 아니냐! 라고 반문하고 싶을지도 모릅니다. 쩝 저도 시스템 관리자로써 조금은 슬픈 시스템 담당자들의 고통입니다.

 

그럼 무엇이 문제였을까요? 일단 A시스템에 90%이상의 문제가 있다라고 말하고 싶습니다. 테이블 설계시에 RDBM니 분석설계 방법론 등등 아주 중요합니다. 하지만 관리는 Oracle이라는 DB엔진이 하는 것이고 그것을 담당자들은 활용만 할뿐입니다. 이말은 Oracle자체의 Spec을 바꿀수는 없다는 것이지요. 1차,2차,3차 정규화 거치고 BCNF까지 거쳤다면 다 끝난 것일까요? 하핫 아닙니다. 이건 업무적인 관점에서 형상화시킨 개념 모델링에 불과합니다. 아주 중요한건 그럼 Oracle에 어떻게 탑제시키고 유지시키는가 하는 고민을 애햐 한다는 점입니다.

 

테이블 Create시에 굉장히 많은 상황을 고려하고 결정해야하지만 대부분의 개발자 분들은 전혀 고려하지 않는다는게 문제입니다. DBA는 통보만 받고 마는 사실 또한 애석할 따름입니다. 음..이 테이블은 마스터 형식의 테이블이니 한번 업무상 한번 생성하면 삭제도 없고 가끔 수정이 발생하겠군.. 어! 이 테이블은 행위정보로 매일매일 많은 량이 생성되겠군.. 헐! 수정은 일일기준인데 어떻게 수정하지? 음... 전제 지우고 재생성해야겠군... 등등 내가 생성하려는 정보가 어떤 어떻게 생성될 것이며 어떤 방식으로 유지될 것인지 여러분들이 메모하신 내용과 실제 사용자들 관점에서 한번은 생각해보고 판단하는 게 매우 중요한 것입니다.

 

이제 테이블을 만들어 볼까합니다. 업무별,유형별로 수많은 정보들이 만들어 지겠지만 딱 두가지 정도 경우만 예를 들어서 만들어 볼까합니다. 또 객체나 아주 큰 사이지의 정보는 제외토록 하겠습니다. 하나는 한번 생성되면 삭제는 없고 수정이 종종 발생하는 테이블이고 다른 하나는 앞에 생성된 테이블 기준으로 매일 정보가 수시로 발생되고 수정되고 삭제되고 경우입니다.

 

● 아르바이트생 정보

　　- 테이블명 : EMP_INF

　　- 내용 : 아르바이트생의 인적정보(이름,나이,주소,전화 등)를 관리한다.

　　- 특징 : Row기준으로 Unique한 정보들이고 한번 추가되면 삭제되지 않고 수정만 발생함

● 일별 출퇴근 정보

　　- 테이블명 : WORK_INF

　　- 내용 : 아르바이트생 별로 일일 CHECK IN/OUT 정보를 관리한다.

　　- 특징 : 행위 Entity로서 일일 n번 발생할 수 있고 기간에 종속되지 않고 Row개수는 증가

             될 수 있음

● 월별 시급 정보

　　- 테이블명 : PAY_INF

　　- 내용 : 아르바이트생 별로 월별 시급(월급) 정보를 관리한다.

　　- 특징 : 월별로 생성되며 수정은 존재하지 않고 월별 재생성 될수 있다.

※ 참고) 서실 테이블 설계시는 더 꼼꼼해야 하겠지만 오늘 제가 이야기 할 주제와는

          관련이 없어 생략하였습니다.

 

공부를 하시다 보면 PK,FK이런 말들이 나옵니다. 일단 FK는 개념적인 부분이라 만드는게 아니다 라는 말을 먼저 염두해두겠습니다. 자세한 내용은 앞으로 차차 설명드리겠습니다.

첫번째 아르바이트생 정보에서 PK는 무엇일까요? 지금까지 개발자들은 단순하게 Unique하다가  PK이다 하고 생각 하실겁니다. 맞습니까? (잠시 .... 시간흐름 ^^) 맞습니다.^^ PK는 Oracle이 Row를 Database관점에서 관리하기 위해 세우는 하나의 규칙에 불가합니다. 쉽게 말씀드려서 물리적인 Entity를 관리하기 위한 수단인 것입니다. 논리적인 Entity(앞으로는 그냥 쿼리라고 하겠습니다.)를 만들고자 할때는 의미 없는 내용일 수 있다는 이야기입니다. 단 검색시엔 PK라는 인덱스적인 특징때문에 활용될 수 도 있지만 그게 큰 의미는 없습니다. 오히려 대용량데이터 처리시에는 발목이 될 수도 있습니다. 검색은 나중에 다른 이야기에서 주제로 설명할것이니 지금은 그냥 넘어가겠습니다. 지금까지 제가 개발자적인 입장에서 PK를 무시했습니다. 그러나 Oracle에 관점에선 어떨까요. PK가없다면 그건 정확한 정보 관리에 어려움이 생길수 있습니다.

간혹 다른분들이 하신 이야기들 중에 구지 PK는 안잡으셔도 무방하다라는 말을 종종 보곤 합니다. 그건 Database를 고려하지않은 개발자 관점에서의 이야기라고 생각합니다. 무결성이 없는 데이터들은 쿼리를 설계시에도 너무 큰 리스크로 오기때문에 정확한 PK설정은 반드시 선행되어야합니다. 제가 앞에 이야기에서 내가 잘 만든 테이블 열 쿼리 안부럽다 라는 말씀을 드린 이유중에 한가지에 PK설정도 포함이 됩니다. 테이블 설계는 Database 관점이 99% 반영되어야하고 그것을 바탕으로 99% 개발자 생각이 반영된 쿼리가 완성되는 것입니다.

 

테이블 설계시 반드시 PK설정이 중요하기에 너무 긴 내용으로 설명드렸는데 조금 따분하셨을지도 모르겠습니다. 그럼 다시 원점으로 돌아가서 아르바이트생 정보의 PK는 무엇일까요?

주민번호? 생일? 이름? 아니면.....일련번호?(^^) 잘하시는분들은 바로 나오시겠지만 PK설정으로 대상 Tablespace가 결정되고 경우에 따라서는 Patition설정까지 될 수도 있습니다. 제 의견을 말씀드리면 이렇게 하는게 어떨까요.. 년도(2)+월(2)+일련번호(4) 이렇게 설계를 하면 나중에 PK만 가지고 년도별 아르바이트생 추이도 볼수있고 나아가서 각 유형별 통계시에도 기준값으로 활용될수 있어서 괜찮을듯합니다. 그럼 나머지 테이블 WORK_INF나 PAY_INF는 PK를 어떻게 설정하면 좋을까요? 이건 여러분 생각에 맞기겠습니다.

 

EMP_INF 테이블의 PK가 결정되었습니다. 단, 기타 컬럼들은 별도로 나열하지 않겠습니다.

그럼 타입은 어떻게 해야할까요? CHAR? VARCHAR2? NUMBER? CHAR가 맞습니다. VARCHAR2도되지 않나요? 라고 하시는분들도 계실겁니다. 네 틀리지 않습니다. 그렇지만 맞지는 않습니다. PK는 규칙입니다. 컬럼이 하나가 되었든 결합해서 두개가 되었든 어떤 규칙에 의해 정해지기에 반드시 라는 말을 부일수 있습니다. CHAR의 특징이 어떤가요? 반드시 자리수가 정해집니다. 제가 정한 PK는 8자리네요. 자리가 8자리인데 제가 모드로 2010050001 값은 넣으면 어떻게 될까요? 에러나겠지요? over flow~~ 또 20100501 이 값은 어떨까요? 또 하나더 10051 이건 어떨까요? 에러들은 없겠지만 검색이 안되겠지요? 자리 수가 적은 경우엔 뒤에 공백이 추가되여 Equal Join의 부정합에 의한 오류가 되고 자리수는 지켰지만 값의 부정합에 의한 오류가 생깁니다. 그렇다고 시스템 오류가 나진 않습니다. 단지 쿼리 구현시 짜증만 날 뿐입니다. 그래서 테이블정의서든 스크립트든 타입과 자리를 기재하고 설명(년도(2)+월(2)+일련번호(4))을 반드시 기재하여야 합니다.

 

사실 중요한 건 정의된 PK를 기준으로 Table생성을 해야 하는데 이때가 가장 중요한 부분이라 이야기가 길어질듯하여 다음 이야기에서 다루겠습니다.

 

이이야기를 보시는 분들은 앞에 여쭤본 WORK_INF나 PAY_INF의 PK에 대해서 생각해보세요.

반드시 생성하고자하는 테이블의 특징을 염두해 보시는게 좋습니다

 

SQL고수되기 - 네번째 이야기

이번 이야기에서는 저번에 이어 테이블을 Create할 것입니다.

그나저나 다른 두개 테이블에대해서 PK는 만들어오셨는지요. 참~~ 쉽죠~잉 ^^

가장 중요한건 EMP_INF 테이블하곤 관리 자체가 다르다는 점입니다.

그 이유는 PK의 첫번째가 시기정보가 들어간다는 점입니다.

이게 굉장히 중요한데요.. 앞으로 차차 설명드리겠습니다.

 

그럼 테이블을 만들어봐야 겠지요?

Table 생성은 보통 이렇게들 하시지요?

CREATE TABLE EMP_INF
(
    EMP_NO  CHAR    (8 ) Primary Key,  /* 사번   */
    SNAME   VARCHAR2(50),              /* 이름   */
    PHONE   VARCHAR2(20),              /* 핸드폰 */

    ....
)
문법적으론 맞는 스크립트입니다.

하지만 관리영역이 없죠?

또, 중요한 PK영역을 Default로 설정하고 있습니다.

또, 테이블의 특성에 맞는 Block에 대한 정의 또한 없습니다.

 

그럼 다시 만들어보겠습니다.

CREATE TABLE EMP_INF
(
    EMP_NO  CHAR    (8 ) Primary Key,  /* 사번   */
    SNAME   VARCHAR2(50),              /* 이름   */
    PHONE   VARCHAR2(20),              /* 핸드폰 */

    ...

    ...

    CONSTRAINT EMP_INF_PK PRIMARY KEY (EMP_NO)
    USING INDEX TABLESPACE TS00 PCTFREE 10
    STORAGE ( INITIAL 5M )
)
TABLESPACE TS01
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL        20M

            NEXT           20M

            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
NOLOGGING
NOCACHE
PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT )
NOMONITORING;

아시는 분들도 계실테지만 부분적인 설명을 드리겟습니다.

익숙하지만 첨보시는 분들도 계실테고 또 왜 하는지 모르고 팬턴 복사 하드시

Copy&Paste하셔서 사용하셨던 분들도 계셨을 겁니다.

상단에 EMP_INF_PK라고 따로 이름을 설정했습니다. Data Mining이나 튜닝등 관리적인 차원에서 관리하려면 반드시 이름을 명시하셔야합니다. 그래야 활용가능합니다. 왜냐하면 인덱스도 테이블이기 때문입니다. 여러분들이 SELECT 하시게되면 EMP_INF 테이블을 그대로 찾는 경우도 있지만(TABLE FULL SCAN) 대부분인덱스를 거쳐 검색(ROWID SEEK INDEX)하게됩니다. 저기요 바로 찾는게 더 빠르지 않을까요? 라고 말씀하시는 분들이 계실겁니다.(^^) 바로 찾는다는 얘기는 하드디스트의 트렉을 처음부터 끝까지 I/O한다는 말이됩니다. 어떨까요 짜증나겠죠? 그러나 인덱스를 활용하게되면 어떨까요? 참고로 인덱스는 DB가 START됨과 통시에 MEMORY에 Loading됩니다.

 

　　인덱스정보:

　　┌─────┬─┬─────┬───┐　　　┌───┬───┐

　　│INDEX Col1│..│INDEX ColN│ROWID │　->　│EMP_NO│ROWID │

　　└─────┴─┴─────┴───┘　　　└───┴───┘

 

인덱스 테이블에서 WHERE절에 해당하는 Col정보들을 검색하여 Buffer에 탑제하게 됩니다.(이걸 SCAN이라고 합니다.) 이후에 검색된 INDEX정보별로 저장되 있는 ROWID(저장위치)에 해당하는 실제 정보를 찾아들어가서 결과를 표시하게 됩니다. 이해하시는 분들도 계실테지만 조금 어렵죠?

검색 기법은 차차 이야기 할 예정이니 걱정마십시오. 중요한건 Table 생성을 잘해야 검새도 잘 할수 있다는 것입니다. ^^

그럼 인덱스 테이블(EMP_INF_PK)은 어디 생성될까요? 또 정보테이블(EMP_INF)은 어디 생성될까요? 바로 Tablespace라는 공간(데이터파일)에 생성됩니다. 근데 자세히 보시면 서로 다르다는 거 확인하셨나요? 그 이유가 멀까요? 관리되는 방법 자체가 다르기 때문입니다. 인덱스 테이블은 PCTFREE만 있고요 정보테이블은 PCTFREE와 더불어 PCTUSED라는 옵션이 하나더 있습니다.

굉장히 중요한 옵션중에 하나입니다. 매우매우 중요!! 별표 5개 ★★★★★!!!!

모든 테이블은 Block(8K)으로 구성됩니다.

 

　　　　　　　　　　　Block모형:

　　　　　　　　　　　　┌─────┐                   

　　　　　　　　　　　　│　　　　　│

　　　　　　　　　　──├─────┤<─　PCTFREE 영역(10%)

　　　　　　　　　　▲　│　　　　　│

　　　　　　　　　　│　│　　　　　│

　　　　　　　　　　│　│　　　　　│

　　　　　　INSERT영역　├─────┤<─　PCTUSED 영역 (40%)

　　　　　　　　　　│　│　　　　　│

　　　　　　　　　　│　│　　　　　│

　　　　　　　　　　▼　│　　　　　│

　　　　　　　　　　──└─────┘

 

INSERT질의가 수행되게 되면 위 그림에서 INSERT영역 사이에 등록됩니다. 그위에 PCTFREE 영역은 UPDATE시 활용되는 영역입니다. 또 PCTUSED 영역을 DELETE질의에 의한 데이터 삭제시 해당 BLOCK이 재사용될 수 있게 하는 표시부분으로 보시면 됩니다. 근데 인덱스테이블은 PCTFREE만 있는 이유는 인덱스정보는 실제 테이블이 삭제되더라도 절대 삭제되지 않습니다. 그래서 PCTFREE만 존재하는 것입니다. 강제로 잡으셔도 안됩니다.^^ 해결은 INDEX REBUILD 명령어로 해결하곤 합니다. 자세한 내용은 차차 설명드리도록 하겠습니다. 내용이 참 해깔리죠?

그럼 우리 만들려고 하는 테이블 기준으로 실제 구현내용에 대해 설명드릴께요. 중요한 내용은 테이블별 특징을 정의한 부분을 알고 계셔야 이해가 빠르실겁니다.

 

EMP_INF 테이블의 특징이 어땠었지요? 한번 추가되면 삭제되지 않고 수정만 발생함 이랬던걸로 기억합니다. 삭제가 없고 수정만 발생한다면 PCTFREE(Default 10으로 함)와 PCTUSED(Default 40으로 함)는 어떻게 하면 좋을까요? 당연히 삭제가 없으니 PCTUSED가 높을 필요는 없겠죠? 반대로 PCTFREE는 어떨까요 조금 높아도 되겠지요? 물론 수정이 빈번하지 않다면 그냥 Default로 해도 무방합니다. 그럼 중요한점 머가 좋아질까요? 바로 Block의 단편화 현상이 많이 없어지게 됩니다. 쉽게 말씀드리면 삭제가 됬다는 것은 Block안에 내용이 살아졌다는 얘기입니다. 그럼 비어있겠지요? 다시 사용하면 되겠네요.라고 말하시는 분들도 계실겁니다. 절대 그렇지 않습니다. 오라클은 현재 진행형이라 지금 처리하는 BLOCK위주로 INSERT가 진행됩니다. 하지만 BLOCK 사용량이 PCTUSED 이하가되면 다시 사용할 수 있게됩니다. PAY_INF처럼 수정이 없고 재생성될 수 있다 상황에서 PCTUSED가 40이라면 어떨까요?아주 운이없게 41% 사용됬다면 그BLOCK을 재사용이 안되겠지요? 만약 과거 데이터라면 더더욱 비참하겠네요. 하지만 PCTUSED가 80이라면 조금만 삭제되도 재사용하게됩니다. 이말은 그만큼 단편화되는 일이 적어지게 되는것입니다.

 

이처럼 첫 테이블 생성시에 잘못된 설계로 인해 SELECT시 너무 많은 BLOCK이 읽어지게 된다면 그만큼 서버에도 부하가 가게 되는 것입니다.

 

마지막으로 INITIAL 부분을 검토해보겠습니다. 이건 멀까요? 말그대로 최조 생성시 크기입니다.

너무 작다면 어떨가요? 데이터 10만건을 INSERT중에 공간이 작다면 늘리고 진행해야 겠지요? 뚝딱하면 공간이 늘어나는 것(EXTENTS)도 아니고 또 테이블 TRANSACTION 중에 빈번히 EXTENT가 발생한다면 그만큼 서버의 부하도 커지겠지요? 또 NEXT가 너무 작아도 마찬가지겠지요? 따라서 생성되는 량을 예상하여 잡는게 좋을것입니다.

 

후 오늘 정말 중요한 내용을 쉽게 설명하려했지만그래도 조금 어려운 부분이 없지않아 있었던듯 합니다.

 

그냥 글보시지 마시고 일별 출퇴근 정보(WORK_INF) 와 월별 시급 정보(PAY_INF) 테이블에 대해 시간 되시면 한번 검토해보시면 좋을듯 합니다.

 

다음 이야기는 테이블 생성 후 관리에 대해 이야기해보겠습니다.

 

SQL고수되기-다섯번째 이야기

지난 이야기에서 생성한 테이블 정보들입니다.

 

1.아르바이트생 정보

CREATE TABLE EMP_INF
(
    EMP_NO  CHAR    (8 ),  /* 사번:년(2)+월(2)+일번(4) */
    SNAME   VARCHAR2(50),  /* 이름   */
    PHONE   VARCHAR2(20),  /* 핸드폰 */

    ...

    ...

    CONSTRAINT EMP_INF_PK PRIMARY KEY (EMP_NO)
    USING INDEX TABLESPACE TS00 PCTFREE 10
    STORAGE ( INITIAL 5M )
)
TABLESPACE TS01
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL        20M

            NEXT           20M

            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
NOLOGGING
NOCACHE
PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT )
NOMONITORING;

2.일별 출퇴근 정보

CREATE TABLE WORK_INF
(
    ATTN_YMD  CHAR(8 ),  /* 출근일자:년월일 */
    EMP_NO    CHAR(8 ),  /* 사번:년(2)+월(2)+일번(4) */
    CHECK_IN  DATE,      /* 출근일시 */

    CHECK_OUT DATE,      /* 퇴근일시 */

    ...

    CONSTRAINT WORK_INF_PK PRIMARY KEY (ATTN_YMD,EMP_NO,CHECK_IN)
    USING INDEX TABLESPACE TS00 PCTFREE 10
    STORAGE ( INITIAL 10M )
)
TABLESPACE TS01
PCTUSED    40
PCTFREE    20
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL        20M

            NEXT           20M

            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
NOLOGGING
NOCACHE
PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT )
NOMONITORING;

3.월별 시급 정보

CREATE TABLE PAY_INF
(
    PAY_YM  CHAR  (8 ),  /* 출근일자:년(4)+월(2)+구분(MM/YY) */
    EMP_NO  CHAR  (8 ),  /* 사번:년(2)+월(2)+일번(4) */

    SALARY  NUMBER(12),  /* 실급여액 */
    ...

    CONSTRAINT PAY_INF_PK PRIMARY KEY (PAY_YM,EMP_NO)
    USING INDEX TABLESPACE TS00 PCTFREE 10
    STORAGE ( INITIAL 10M )
)
TABLESPACE TS01
PCTUSED    80
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL        20M

            NEXT           20M

            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
NOLOGGING
NOCACHE
PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT )
NOMONITORING;

여러분들이 생각하셨던 내용과 비슷할껍니다. 그러나 운영하다보면 어려움이 많이 것은 사실입니다. 당장 1~2년은 가능하지만 10년을 바라본다면 걱정이되는 것은 당연할것입니다. 그러나 애석한 점은 모든 개발자,관리자가 그점을 생각하지 않는다는데 큰 문제가 있습니다. 똑 일이 닥치고 나면 전전긍긍 대응하려하지만 참으로 어려운 일입니다. 저또한 그랬으니깐 말이죠.^^

 

  한 예로 집계성 통계정보가 몇년이 지나면서 검색에 어려움이 발생하였습니다. 테이블 크기가 몇기가바이트에 다다르면서 한계에 봉착한 것입니다. 더더군다나 아주 잘 된 설계이지만 수수로 발생되는 재생성건에다 불특정 유저의 수정사항이 연속해서 발생하면서 주/월단위 추이 조회시 많은 어려움이 있었습니다. 간단하지만 먼저 수행한 행동이 일일 Analyze 실행이였습니다. Oracle에서 제공하는 DBMS_UTIL.ANALYZE라는 기능을 사용하여 수행되도록 하였습니다. 또 이와 더부러 관리상의 어려움은 있지만 INDEX  ~ REBUILD 기능을 특정 주기마다 수행하여 Table을 최적화 시켰습니다. 몇몇은 안정화 되었지만 일일 집계되는 테이블에 대해서는 해결이 되지 않아 Partition Table 기법과 무리가되더라도 집계성 테이블이기에 IOT INDEX Table기법을 활용하여 새로 도입하여 해결했던 적이 있습니다.

 

  이처럼 그냥 생성만 하고 SELECT하면 되겠지 하는 생각에 앞얘기는 경험해 나가면서 해결하려는 생각이 많은듯 합니다. 또 그렇게 교육도 받고요. 이게 어쩔수 없는 상황이지만 그래도 Table생성 할 때 한번이라도 고민해서 생성하게되면 조금은 도움이 되지 않을까 생각이듭니다. 위에 테이블 생성기법중 Partition이나 IOT 기법은 예산도 많이 들어갈 수 있고 잘못 관리되면 서버 부하도 일으킬수 있습니다. 따라서 아주 신중히 고려해야 수행해야 하고 경헙이 있는 사람의 의견을 듣는게 좋을듯합니다. 왜냐면 이 기법들은 대부분 크게 활용되는 경우는 없기 때문입니다. 통신회사나 금융권에서 사내시스템으로 아주 큰 대용량 처리시에만 필요한 부분이 대부분일 것이기 때문입니다. 그러나 ANALYZE나 INDEX REBUILD는 매일은 아니더라도 실행계획은 수립하셔서 사용하시면 좋을듯 합니다.

 

마지막으로 TABLE 생성 쿼리 하단에 NOLOGGING 옵션이 있습니다. 쉽게 말해서 Table 나아가서 Database에 영향을 미치는 변경에 대해 Redo Log관리를 하게되는 데 이를 안하겠다는 뜻입니다. 과연 안될까요? 그냥 조금만 관리(Log남기기)해 달라는 뜻으로 보시면 됩니다. 간혹 이놈의 로그가 너무 많이 싸여서 짜증날 수도 있음.

 

오늘은 여기까지만 이야기하겠습니다. 머리아프시죠? 헉 나마 그런가.. ^^ 으악! 내일 월요일이네요. 오늘 마무리들 잘하시고요 안녕히들 주므세요. 다음 이야기부턴 인제 SQL(Query) 잘짜는 법에대해 특정 상황과 주제별로 실제 활용되는 기법을 예로들어 이야기 할겁니다. 그전 제가 이야기 했던 1~5번 이야기를 조금 이해하시면 큰 도움이 되리라 생각듭니다.

 

아자~ 화이팅

SQL고수되기 - 여섯번째이야기

오늘부터 어제 만든 테이블들을 활용하여 쿼리(논리Entity)를 만들어보겠습니다.
그전에 논리 Entity에 대한 정의에 대해 상세하게 다뤄볼까합니다.

 

WORK_INF(출퇴근정보) -------------------------------------------

출근일자　　사번　　 　출근일시　　　　　　　퇴근일시
(ATTN_YMD)　(SNO)　　　(CHECK_IN)　　　　　　(CHECK_OUT)
----------------------------------------------------------------
20100401　　10010001　2010-04-01 08:40:24　2010-04-01 17:54:36

20100401　　10010002　2010-04-01 09:11:00　2010-04-01 17:36:00
20100401　　10010003　2010-04-01 08:57:48　2010-04-01 17:25:48
20100401　　10010004　2010-04-01 08:25:24　2010-04-01 17:40:12
20100401　　10010005　2010-04-01 09:02:36　2010-04-01 17:38:24
20100401　　10010006　2010-04-01 08:23:00　2010-04-01 19:05:24
20100401　　10010007　2010-04-01 08:23:00　2010-04-01 19:19:48
20100401　　10010008　2010-04-01 08:58:24　2010-04-01 17:07:12
20100401　　10010009　2010-04-01 09:09:48　2010-04-01 19:45:36
20100401　　10010010　2010-04-01 08:47:00　2010-04-01 17:54:00

----------------------------------------------------------------

위 표는 2001.04.01 출근현황으로 제가 임의로 생성하였습니다.

 

SELECT *

FROM WORK_INF

WHERE ATTN_YMD LIKE '201004%'

이렇게 조회하면 위 표대로 나오겠지요? 이건 단순한 물리적Entity조회입니다.

 

조금 바꿔볼까요. 요청을 하나 만들어 보겠습니다.

원하는 날짜에 해당하는 명단을 다음 형태로 보고싶다.

-> 일자(요일) | 사번 | 출근시간 | 근무시각(시) | 지각시각(분)

                     (시:분:초)

예)  4/1(목) 10010001 08:40:24　8시간 30분 15분

 

Entity 분석을 해보도록 하겠습니다.

　　　　　테이블정보　　　　　　　파생정보

항목　　　(원시정보,물리Entity)　(가공정보,논리Entity)

--------------------------------------------------------------------------------

근무일자　20100401　　　　　　　　SUBSTR(ATTN_YMD,5,2)||'/'||SUBSTR(ATTN_YMD,7,2)

　　사번  10010001　　　　　　　　사번

출근시간  2010-04-0108:40:24　　　TO_CHAR(CHECK_IN,'hh:mi:ss')

근무시각  None　　　　　　　　　　(CHECK_OUT-CHECK_IN)*24

지각시각  None　　　　　　　　　　CEIL((CHECK_IN-

　　　　　　　　　　　　　　　　　TO_DATE(ATTN_YMD||'09','yyyymmddhh'))*24*60)

파생정보중 사번는 물리정보 그대로 사용되었습니다. 하지만 나머지 정보들은 재정의(defined)되었습니다. 바꿔말하면 새로운 정보를 생성하였다 라고 말할수 있는것입니다. 쉽게 말해서 데이터베이스에 TO_CHAR(CHECK_IN,'hh:mi:ss') 이런 정보는 없다는 이야기입니다. 종종 Defined Query라는 표현을 보신적이 있으실 겁니다. 이말이 위에 말하고 같다라고 보시면 정확한 표현이 될것입니다.

정리하면 내가 재정의한(재가만든) 항목(Column)은 사번을 제외하고 "SUBSTR(ATTN_YMD,5,2)||'/'||SUBSTR(ATTN_YMD,7,2)", "TO_CHAR(CHECK_IN,'hh:mi:ss')", "(CHECK_OUT-CHECK_IN)*24", "CEIL((CHECK_IN-TO_DATE(ATTN_YMD||'09','yyyymmddhh'))*24*60)" 이렇습니다. 컬럼 처럼 안보이시죠? 그래서 Alias를 줘서 보이좋게 만들어보겠습니다.

 

SUBSTR(ATTN_YMD,5,2)||'/'||SUBSTR(ATTN_YMD,7,2) as "근무일자"

사번

TO_CHAR(CHECK_IN,'hh:mi:ss') as "출근시간"

(CHECK_OUT-CHECK_IN)*24 as "근무시간(시)"

CEIL((CHECK_IN-TO_DATE(ATTN_YMD||'09','yyyymmddhh'))*24*60) as "지각시각(분)"

 

이렇게 표현되겠네요. as ""는 이름을 명명하는것이지 컬럼은 아닌 것입니다.

 

이처럼 저장되어 있는 정보를 그대로 사용하는것이 아니라 어떤 상황에 맞게 재정의해서 새로운 표현을 만들어 내는것을 Query라고 말하는 것입니다. 예를들어 표현해야하는 항목이 급여액 항목이라면 어떨까요? 한 컬럼을 표현하기 위해 조인부터/계산공식까지 수많은 업무로직이 적용될 것입니다. 또 핸드폰 청구금액을 산출한다. 라고 하면 더더더 엄청난 내용으로 시간대별 적용 등등 수많은 로직을 적용해서 하나의 항목을 만들어 내게 될것입니다.

 

그러나 이전에 이런 것들을 만들어 만들어 내기 위한 원시정보(물리적정보)가 정상적이지 못하다면 그걸 토대로 가공되는 정보 또한 보장될 수 없는 정보가 될것입니다.

 

단순하게 SELECT,INSERT,UPDATE,DELETE 하는 쿼리를 문법에 의한 생성으로 한다면 절대 업무적인 관점에서 접근하기란 어렵게됩니다. 어떤 개발자는 경제학과를 나와서 IT업계에서 일하게 된 경우가 있다고 합시다. 어떨까요. DB에 대한 경험이 부족하기에 어려울꺼라 생각이 드실겁니다. 절대아닙니다. 오히려 업무에 필요한 새로운 유형의 정보들은 만들어내는 능력은 훨씬 탁월할것입니다. 이렇게 표현해야 하는데 어떻게 하면좋을까. 생각을 가지고 서적을 뒤지고 검색을 해서 만들어 냈다면 그건 자기만의 노하우가 될 것입니다. 하지만 그 반대경우라면 자기의 생각과 분석이 적용되지 않은 Entity라면 그건 누가 짠 기능을 그냥 보기만 한 수준에 그치게 되는 것입니다.

 

쿼리를 짜는데 있어 정답은 업습니다. 개발자가 100명이면 같은 결과에 생성쿼리는 다 다를수 있습니다. 경험에 차이, 생각에 차이, 처리방식의 차이 등등.. 하지만 100명이 만든 쿼리가 다 같다면 ^^ 어떨까요? 한번 곰곰히 생각해보세요.

 

 오늘 아주 간단하게 예를 들어 이야기 하였습니다. 물론 문법을 알면 조금 빠르겠지만 게시판만 짤게 아니기 때문에 논리적인 Entity를 만들어 내는 과정을 자기만의 생각으로 만들어 나가는 과정이 매우 중요합니다. 여러 상황을 접해보고 그상황에서 발생한 또는 발생할 수 있는 내용들은 재정의 해 보는게 쿼리 전문가로 가는 지름길이라고 확신합니다.

 

SQL고수되기 - 일곱번째이야기

CREATE TABLE시에 Tablespace부분 문제 생기실수도 있으실듯 없어서리..

제가 깜밖했네요 이렇게 만들시면 됩니다.

 

CONN SYSTEM;

 

CREATE TABLESPACE TS00
DATAFILE 'D:\oracle\product\10.2.0\oradata\oracle\TS00.dbf'
SIZE 1000M AUTOEXTEND OFF
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
;

CREATE TABLESPACE TS01
DATAFILE 'D:\oracle\product\10.2.0\oradata\oracle\TS01.dbf'
SIZE 2000M AUTOEXTEND OFF
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
;

반드시 dba권한 계정으로 conn하시고요 Datafile 경로는 바꾸시면됩니다.

 

어제 논리Entity 이야기 시작했는데 좀 이려웠엇는지 모르겠네요

 

오늘은 어제 봤던 데이터 한번 만들어 볼겁니다.

테이블을 분석해! 라고 하면 대부분 어떤가요. 테이블명 확인하고 컬럼확인하고

PK,Index등등 대부분 물리적인 정보만 확인하는게 다 일겁니다.

DBA가 아닌이상 무의미한 해석이죠. 그렇다고 이게 필요없다는 얘기는 아닙니다.

구지 분석하지 말고 그냥 ERD만 옆에 떡하니 붙어놔도 될일이라는 뜻이죠.

가장 중요한건 컬럼별로 내용분석이 가장 중요합니다. 어떤 제약사항으로 만들어 졌으면

자리수마다 어떤 특징은 없는지. 또 어떤 형태로 저장되어 있는지 등등 그테이블에 들어있는

정보들의 형태나 관계성 이중성 Row간의 관계(Join) 등의 분석이 잘 되어있는 사람은 확실히 처리하는 능력이나 시간이 월등하게 차이가 날수있습니다.

 

그럼 직접 정보를 만들어 보겠습니다.

제가 임의로 만들 정보를 10명의 아르바이트 생의 4월 출퇴근현황부를 만들것입니다.

추가적으로 월이 바뀌면 자동 잔여수당에 대한 처리가 되도록 할것입니다.

 

출퇴근부 테이블 스키마:

사번    : CHAR(8), 년도(yy)+월(mm)+일련번호(4)

출근일자: CHAR(8), 년월일

출근일시: DATE, 년월일시분초

퇴근일시: DATE, 년월일시분초

수당    : NUMBER(12)

 

회사내에 10명의 아르바이트생이 있고 출근은 9시까지이며 퇴근은 오후 6시이다. 

시간외 근무시간이 2시간 이상이면 추가로 1만원 고정지급한다.(내맘대로결정^^)

또, 휴일(토,일) 출근하여 8시간(+1시간 점심) 이상 근무시 수당으로 10만원 고정

지급한다.(이런 아르바이트면 바로 달려가겠음 ㅎㅎ)

Entity를 만들어 보겠습니다.

 

1)4월 데이터 생성

SELECT rownum

FROM  ALL_OBJECTS
WHERE ROWNUM <= to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'dd')

 

2)4월 10명데이터 확장

* ADD_MONTHS(날짜,증감수) : 잘짜 데이터의 월증감

* LAST_DAY(날짜)          : 해당일자의 마지막날짜(말일)

* TO_CHAR(날짜,형식)      : 날짜의 형식별 문자표현

  -> to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'dd') : 마지막일자(=일수)

* CEIL(실수값)            : 무조건 올림 (<->Trunc)

* LPAD(값,자리수,채울값)  : 자리수 채우기

SELECT rownum
      ,LPAD(ceil(rownum/to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'dd')),4,'0')
FROM  ALL_OBJECTS
WHERE ROWNUM <= to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'dd') * 10

 

3)10명의사원의 사번만들기

SELECT rownum
      ,to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'yymm')||
       LPAD(ceil(rownum/to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'dd')),4,'0') emp_no
FROM  ALL_OBJECTS
WHERE ROWNUM <= to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'dd') * 10

 

4) 츨근일자 만들기

* row_number() over(partition by 기준컬럼 Order by 정렬순서) : 기준컬럼별 정렬순서에

  순차적으로 Serial 부여(=일련번호)
...

      ,to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'yyyymm')||
       lpad(row_number() over(partition by ceil(rownum/to_char(LAST_DAY(

ADD_MONTHS(sysdate,-1)),'dd'))
order by rownum),2,'0') attn_ymd
...

5) 출근일시만들기

* DBMS_RANDOM.VALUE : 1보다 작은 Ramdom 실수 반환

  -> round(DBMS_RANDOM.VALUE,2)/24 : 1시간보다 작은 분값

: 출퇴근시간을 다르게하기위해

: 운없으면 지각도 할 수 있음

....

      ,to_date(to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'yyyymm')||
       lpad(row_number() over(partition by ceil(rownum/to_char(LAST_DAY(

ADD_MONTHS(sysdate,-1)),'dd'))
order by rownum),2,'0')||
            '082000','yyyymmddhh24miss')+

       (round(DBMS_RANDOM.VALUE,2)/24) CHECK_IN
....

 

6)퇴근일시만들기

....

,to_date(to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'yyyymm')||
 lpad(row_number() over(partition by ceil(rownum/to_char(LAST_DAY(

ADD_MONTHS(sysdate,-1)),'dd'))
order by rownum),2,'0')||
         '180000','yyyymmddhh24miss')+(round(DBMS_RANDOM.VALUE,2)/24)+
 trunc(DBMS_RANDOM.VALUE+0.49)*2/24 check_out
....

 

7) 주말정보생성 및 보너스 임의선정

* TRUNC(실수값) : 무조건 버림 ( <-> CEIL )

* Case When 비교 THEN true일때설정값 ELSE false일때설정값 end

....

,trunc(DBMS_RANDOM.VALUE+0.49) bonus_chk
,case when to_char(to_date(to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'yyyymm')||
           lpad(row_number() over(partition by ceil(rownum/to_char(LAST_DAY(

ADD_MONTHS(sysdate,-1)),'dd'))
order by rownum),2,'0')||
           '082000','yyyymmddhh24miss')+

           (round(DBMS_RANDOM.VALUE,2)/24),'d') in ('1','7')
      then '1'
      else '0'
 end holly_job
....

 

8) 1~7까지 생성 Entity기준 완성쿼리

SELECT SNO /* 사번 */
,ATTN_YMD /* 출근일자 */
      ,decode(holly_job,'0',check_in,decode(bonus_chk,1,check_in))   check_in   /* 출근일시 */
      ,decode(holly_job,'0',check_out,decode(bonus_chk,1,check_out)) check_out  /* 퇴근일시 */
,0 TIME_ALLOW /* 수당 */
FROM  (
        SELECT rownum
              ,to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'yymm')||
               LPAD(ceil(rownum/to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'dd')),4,'0') sno
              ,to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'yyyymm')||
               lpad(row_number() over(partition by ceil(rownum/to_char(LAST_DAY(

ADD_MONTHS(sysdate,-1)),'dd'))
order by rownum),2,'0') attn_ymd
              ,to_date(to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'yyyymm')||
                       lpad(row_number() over(partition by ceil(rownum/to_char(LAST_DAY(

ADD_MONTHS(sysdate,-1)),'dd'))
order by rownum),2,'0')||
                       '082000','yyyymmddhh24miss')+(round(DBMS_RANDOM.VALUE,2)/24) CHECK_IN
              ,to_date(to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'yyyymm')||
                       lpad(row_number() over(partition by ceil(rownum/to_char(LAST_DAY(

ADD_MONTHS(sysdate,-1)),'dd'))
order by rownum),2,'0')||
                       '180000','yyyymmddhh24miss')+(round(DBMS_RANDOM.VALUE,2)/24)+
               trunc(DBMS_RANDOM.VALUE+0.49)*2/24 check_out
              ,trunc(DBMS_RANDOM.VALUE+0.49) bonus_chk
              ,case when to_char(to_date(to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'yyyymm')||
                         lpad(row_number() over(partition by ceil(rownum/to_char(LAST_DAY(

ADD_MONTHS(sysdate,-1)),'dd'))
order by rownum),2,'0')||
'082000','yyyymmddhh24miss')+(round(DBMS_RANDOM.VALUE,2)/24),'d')

                         in ('1','7')
                    then '1'
                    else '0'
               end holly_job
        FROM  ALL_OBJECTS
        WHERE ROWNUM <= to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'dd') * 10
      )
;

RSLT:

9) 등록(INSERT)

INSERT INTO WORK_INF(EMP_NO,ATTN_YMD,CHECK_IN,CHECK_OUT,TIME_ALLOW)

SELECT SNO /* 사번 */
      ,ATTN_YMD /* 출근일자 */
      ,decode(holly_job,'0',check_in,decode(bonus_chk,1,check_in))   check_in   /* 출근일시 */
      ,decode(holly_job,'0',check_out,decode(bonus_chk,1,check_out)) check_out  /* 퇴근일시 */
      ,0 TIME_ALLOW /* 수당 */
FROM  (
        SELECT rownum
              ,to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'yymm')||
               LPAD(ceil(rownum/to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'dd')),4,'0') sno
              ,to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'yyyymm')||
               lpad(row_number() over(partition by ceil(rownum/to_char(LAST_DAY(

ADD_MONTHS(sysdate,-1)),'dd'))
order by rownum),2,'0') attn_ymd
              ,to_date(to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'yyyymm')||
                       lpad(row_number() over(partition by ceil(rownum/to_char(LAST_DAY(

ADD_MONTHS(sysdate,-1)),'dd'))
order by rownum),2,'0')||
                       '082000','yyyymmddhh24miss')+(round(DBMS_RANDOM.VALUE,2)/24) CHECK_IN
              ,to_date(to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'yyyymm')||
                       lpad(row_number() over(partition by ceil(rownum/to_char(LAST_DAY(

ADD_MONTHS(sysdate,-1)),'dd'))
order by rownum),2,'0')||
                       '180000','yyyymmddhh24miss')+(round(DBMS_RANDOM.VALUE,2)/24)+
               trunc(DBMS_RANDOM.VALUE+0.49)*2/24 check_out
              ,trunc(DBMS_RANDOM.VALUE+0.49) bonus_chk
              ,case when to_char(to_date(to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'yyyymm')||
                         lpad(row_number() over(partition by ceil(rownum/to_char(LAST_DAY(

ADD_MONTHS(sysdate,-1)),'dd'))
order by rownum),2,'0')||
'082000','yyyymmddhh24miss')+(round(DBMS_RANDOM.VALUE,2)/24),'d')

                         in ('1','7')
                    then '1'
                    else '0'
               end holly_job
        FROM  ALL_OBJECTS
        WHERE ROWNUM <= to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'dd') * 10
      )
;

 

10) 월별수당계산

    - 월초가되면 전달 급여액 계산전에 일별 수당계산을 한다.

    - 수당 계산 로직

      ① 시간외 근무시간이 2시간 이상이면 추가로 1만원 고정지급한다.

      ② 휴일(토,일) 출근하여 8시간(+1시간 점심) 이상 근무시 수당으로 10만원 고정지급

    - Entity 생성 : 사번,일자별(Group by)

      SELECT EMP_NO
            ,ATTN_YMD
            ,case when to_char(to_date(attn_ymd,'yyyymmdd'),'d') in ('1','7')
                  then case when ceil((CHECK_OUT-CHECK_IN)*24*60)-540 > 0

then 100000else 0 end
                  else case when ceil((CHECK_OUT-CHECK_IN)*24*60)-540-120 > 0

then 10000end else 0 end

             end time_allow
      FROM  WORK_INF

      WHERE attn_ymd like '201004%'

 

11) 수당적용

    UPDATE WORK_INF a

    SET TIME_ALLOW =

       (SELECT case when to_char(to_date(attn_ymd,'yyyymmdd'),'d') in ('1','7')
                    then case when ceil((CHECK_OUT-CHECK_IN)*24*60)-540 > 0

then 100000 else 0 end
                    else case when ceil((CHECK_OUT-CHECK_IN)*24*60)-540-120 > 0

then 10000 else 0 end

               end
        FROM  WORK_INF b

        WHERE a.emp_no= b.emp_no

        AND   a.attn_ymd = b.attn_ymd

        AND b.attn_ymd like '201004%')

 

  참고) 물론 Procedure나 Pro-C를 활용해서 Cursor ~ Fetch 방식으로 할수 도 있습니다.

        이와 관련해서 기회가 된다면 다른 이야기 주제로 처러방식에 대해 이야기할

        예정입니다,

 

  지금까지 살펴본 내용이 이해를 못하실수도 있습니다. 내용이 어려워서 그럴수도 있고

개념이 어려울수도 있습니다. 중요한 것은 이처럼 SELECT,INSERT,UPDATE,DELETE 질의 시

논리적으로 Entity를 접근해야 하는 경우가 거의 대부분입니다. 하지만 이건 문법이나 기술

로는 답을 낼수가 없습니다. 중요한건 업무적인 이해와 잘 만들어진 테이블하에서 만들어질

수 있는 부분이기에 모르시더라도 아 이렇게 전개되는구나 하고 인지하시면 좋습니다. 아니

인지하려고 노력하셨으면 좋겠습니다. 참고로 지금까지 한 쿼리는 복작하기만 하지 By Row

기준의 아주 단순한 쿼리라고 봐도 무방할것입니다. 집계라는지 부분범위처리 처럼 집합을

대상으로 새로운 식별자를 만들어내는 과정이 없기에 한번씩 따라해보시는 걸로 만족하시면

좋을듯합니다. 포기하지 마시고요. 그냥 읽어보지 마시고요. 꼭 해보시라는 뜻헤서 함수 정의

부터 내용 설명까지 단계별로 진행한 것이니 부디 꼭 해보시길 바랍니다.

  또, 경우가 되시는 분들은 EMP_INF나 PAY_INF에 대해서 위처럼 만들순 없지만 어떻게 만들어

질것인지 한번 고민해 보시는 것도 아주 많은 도움이 되리라 판단됩니다.

 

다음 이야기부턴 여러분들이 실제로 해보고 싶으셨던 퍄턴별 SELECT질의에 대해 구체적으로 사례를 통해 접근해보겠습니다.

 

수고하셨습니다. 비는 오지만 좋은 하루 되십시오.

SQL고수되기 - 여덟번째이야기

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

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

이제 부터는 한 요청(주제)에 대한 재정의(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시간│
      ......

 

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

SQL고수되기 - 아홉번째이야기

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

문제) 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정보(아르바이트생)를 만들어볼까합니다.

 

그럼 수고하세요.

 

 

SQL고수되기 - 쉬는타임이야기 - 1

그냥 쉬어가는 이야기시간 입니다.

잡단 시간은 아니고요. 그냥 재밌는 쿼리 만들어볼까해서요

 

달력한번 만들어 볼까요?

어느분이 요청하셔서 만들어주긴했는데 재밌을듯 해서요 ^^

 

보통은 프로젝트마다 CALD_MASTER(달력) 정보를 가지고 있습니다.

없으면 만드시면 좋고요 ^^ 월/일/주/ 경우에따라 Shift근무구분까지 참 다양한데요.

조금 빨리 만들려고 만들어 놓고 하는데요

 

그것보다는 지금 시간에는 Entity만들어 보는 시간을 가지려고 합니다.

한번 여러분들도 따라해보세요.

 

STEP1) Entity 만들기

달력을 만들기 위해서 필요한 항목이 머가 있을까요. 그림으로 그려볼까요

 

　2010년 5월

　┌──┬──┬──┬──┬──┬──┬──┐

　│　일│　월│　화│　수│　목│　금│　토│　←요일구분 ( 1(일) ~ 7(토) )

　├──┼──┼──┼──┼──┼──┼──┤

　│　　│　　│　　│　　│　　│　　│　01│　←1주

　├──┼──┼──┼──┼──┼──┼──┤　　↕간격 7일

　│　02│　03│　04│　05│　06│　07│　08│　←2주

　├──┼──┼──┼──┼──┼──┼──┤　　↕간격 7일

　│　09│　10│　11│　12│　13│　14│　15│　←3주

　├──┼──┼──┼──┼──┼──┼──┤　　↕간격 7일

　│　16│　17│　18│　19│　20│　21│　22│　←4주

　├──┼──┼──┼──┼──┼──┼──┤　　↕간격 7일

　│　23│　24│　25│　26│　27│　28│　29│　←5주

　├──┼──┼──┼──┼──┼──┼──┤　　↕간격 7일

　│　30│　31│　　│　　│　　│　　│　　│　←6주

　└──┴──┴──┴──┴──┴──┴──┘

　　　　　　↑마지막일자

 

정리는 제가 했지만 여러분들도 한번 생각(고민)해 보셨지요?

으하하 드뎌 다음주면 월드컵입니다. 아자! 화이팅! 대한민국! 짝짝짝~짝짝!(지성~.~)

 

위에 그림과 정리해놓은거 보시면 가장 첫번째 머가 필요할까요? 고민중.....

날짜지요? 월별일자. 그러면 1~N이니까 마지막일자를 알아야겠네요.

다음은요? 요일이요 구한 일자가 어느 요일인지.

다음은요? 주지요? 몇주차인지..

이렇게만 구하면 달력완성됩니다. 아차 위 그림을 보니까 꼭 테이블 같죠?

그럼 PK(식별자)는 멀까요?? ............................................................

.......................................................................................

.......................................................................................

월+주구분이 되겠네요. <- 제일중요합니다. 

물리적인 테이블만 식별자가 중요한게 아니에요. 논리적인 테이블에서 제일 중요한건 식별자

입니다. 그래야 레코드(Row)를 표현할수 있습니다. 100만건이 10줄이 될수도 있고, 100줄이 될수도 있고, 때론 100만 그대로 일수도 있고, 이처럼 지금 줄수가 만들어진 기준이 먼가요? 바로 식별자에 의한 정의된 결과입니다. 다시한번 강조 논리Entity(쿼리)의 핵심은 식별자 생성입니다. 이게 쉬워보이지만 굉장히 어려울수 있습니다. 아 차차 이야기 하면서 알아나가시면 되요.

아~~ 또 이상한 대로 빠졌음. ^^다시 달만들기로 슝~~

 

월+주 구분인데 정보가 있나요...? 멀기준으로 할까요..? 잠시 고요한~~~ 누군가 아무거나!!!

오우 브라보 정답입니다. 그냥 만드시면 돼요 ^^ㅋㅋ

전 Oracle Library중에 종종 쓰이는 ALL_OBJECTS로 만들겁니다. 이건 테이블이 하나도 생성된게 없어도 Oracle자체가 가지고 있는 정보이기에 그 Row건수만 수십만줄입니다.

 

1) 날짜만들기 :

SELECT rownum days
FROM ALL_OBJECTS
WHERE rownum <= ( last_day(to_date('201005'||'01','yyyymmdd'))-
                 to_date('201005'||'01','yyyymmdd') )+1

 

2) 요일구하기

SELECT '201005'||LPAD(rownum,2,'0') days
      ,to_char(to_date('201005'||LPAD(rownum,2,'0'),'yyyymmdd'),'d') week_day_div
      ,decode(to_char(to_date('201005'||LPAD(rownum,2,'0'),'yyyymmdd'),'d'),'1','일'
,'2','월'
,'3','화'
,'4','수'
,'5','목'
,'6','금'
,'7','토')
        week_days_nm
FROM ALL_OBJECTS
WHERE rownum <= ( last_day(to_date('201005'||'01','yyyymmdd'))-
                 to_date('201005'||'01','yyyymmdd') )+1

 

3) 주구분만들기

이건 조금 생각을 해봐야하는데.. 규칙을 찾아보죠 날짜사이는 +1이고, 주사이는 +7이고...

　월　월　화　수　목　금　토　　　　　　월　월　화　수　목　금　토

　1　 2　 3　 4　 5　 6　 7 　←요일→  (일 - 주구분)

　　　　　　　　　　　　　01　←1주 →　　　　　　　　　　　　　-6　

　02　03　04　05　06　07　08　←2주 →　 1　 1　 1　 1　 1　 1　1　

　09　10　11　12　13　14　15　←3주 →　8　 8　 8　 8　 8　 8　8　

 

위 그림처럼 주구분 - 날짜들을 해당 요일구분을 뺴면 어떤 동일한 Gap(간격)이 보이시나요?

7차이로 동일하죠.. 또, 같은 주끼리는 같은 값을 갖고요. 그이유는 날짜도 +1씩증가하고 요일구분도 +1씩 증가하기 때문이지요 거기에 주간 차이는 7이니 말이죠..

이렇게 구한 로직을 쿼리로 형상화 시켜보죠

SELECT '201005'||LPAD(rownum,2,'0') days
      ,to_char(to_date('201005'||LPAD(rownum,2,'0'),'yyyymmdd'),'dd') day_div
      ,to_char(to_date('201005'||LPAD(rownum,2,'0'),'yyyymmdd'),'d') week_days_div
      ,to_char(to_date('201005'||LPAD(rownum,2,'0'),'yyyymmdd'),'dd')-
       to_char(to_date('201005'||LPAD(rownum,2,'0'),'yyyymmdd'),'d') week_div
FROM ALL_OBJECTS
WHERE rownum <= ( last_day(to_date('201005'||'01','yyyymmdd'))-
                 to_date('201005'||'01','yyyymmdd') )+1

 

 

4) 식별자정의-1

만드려는 식별자는 주구분입니다. 주구분은 항목이 어떻게되죠 일~토 요일별 날짜정보를 가지고 있죠? 그럼 만들어보죠

SELECT to_char(to_date('201005'||LPAD(rownum,2,'0'),'yyyymmdd'),'dd')-
       to_char(to_date('201005'||LPAD(rownum,2,'0'),'yyyymmdd'),'d') week_div
      ,'201005'||LPAD(rownum,2,'0') days
      ,'' Sun_day
      ,'' Mon_day
      ,'' Tues_day
      ,'' Wed_day
      ,'' Thu_day
      ,'' Fri_day
      ,'' Sat_day
FROM ALL_OBJECTS
WHERE rownum <= ( last_day(to_date('201005'||'01','yyyymmdd'))-
                 to_date('201005'||'01','yyyymmdd') )+1

 

5) 식별자정의-2

만든 주별 테이블에 컬럼정보를 넣어보죠. Sun_Day컬럼은 DAYS의 요일값이 1(일요일)이면 되고 Mon_Day는 2(월요일)이면 되고.... 이러식으로 넣으면 다음과 같이 되겠네요

SELECT to_char(to_date('201005'||LPAD(rownum,2,'0'),'yyyymmdd'),'dd')-
       to_char(to_date('201005'||LPAD(rownum,2,'0'),'yyyymmdd'),'d') week_div
      ,decode(to_char(to_date('201005'||LPAD(rownum,2,'0'),'yyyymmdd'),'d'),'1',

              to_char(to_date('201005'||LPAD(rownum,2,'0'),'yyyymmdd'),'dd')) Sun_day
      ,decode(to_char(to_date('201005'||LPAD(rownum,2,'0'),'yyyymmdd'),'d'),'2',

              to_char(to_date('201005'||LPAD(rownum,2,'0'),'yyyymmdd'),'dd')) Mon_day
      ,decode(to_char(to_date('201005'||LPAD(rownum,2,'0'),'yyyymmdd'),'d'),'3',

              to_char(to_date('201005'||LPAD(rownum,2,'0'),'yyyymmdd'),'dd')) Tues_day
      ,decode(to_char(to_date('201005'||LPAD(rownum,2,'0'),'yyyymmdd'),'d'),'4',

              to_char(to_date('201005'||LPAD(rownum,2,'0'),'yyyymmdd'),'dd')) Wed_day
      ,decode(to_char(to_date('201005'||LPAD(rownum,2,'0'),'yyyymmdd'),'d'),'5',

              to_char(to_date('201005'||LPAD(rownum,2,'0'),'yyyymmdd'),'dd')) Thu_day
      ,decode(to_char(to_date('201005'||LPAD(rownum,2,'0'),'yyyymmdd'),'d'),'6',

              to_char(to_date('201005'||LPAD(rownum,2,'0'),'yyyymmdd'),'dd')) Fri_day
      ,decode(to_char(to_date('201005'||LPAD(rownum,2,'0'),'yyyymmdd'),'d'),'7',

              to_char(to_date('201005'||LPAD(rownum,2,'0'),'yyyymmdd'),'dd')) Sat_day
FROM ALL_OBJECTS
WHERE rownum <= ( last_day(to_date('201005'||'01','yyyymmdd'))-
                 to_date('201005'||'01','yyyymmdd') )+1

그림 보시면 DAYS컬럼이 SUN_DAY~SAT_DAY사이게 정확하게 넣어진것을 보실수 있으실겁니다.

 

6) 달력만들기

이제 만들준비가 되었습니다. 위 그림보시면 WEEK_DIV가 1인게 7개행이지요? WEEK_DIV가 식별자 이므로 7개행이 1개의 행으로 표시가 되어야 식별자의 규정에 위배되지 않겠지요. SUN_DAY의경우 MAX()하면 어떻게 될까요 7행중에 한개만 02값이 있고 나머진 없죠. 따라서 02가 되겠지요? 다른 요일정보도 마찬가지 일겁니다. 그럼 만들어보겠습니다.

SELECT to_char(to_date('201005'||LPAD(rownum,2,'0'),'yyyymmdd'),'dd')-
       to_char(to_date('201005'||LPAD(rownum,2,'0'),'yyyymmdd'),'d') week_div
      ,max(decode(to_char(to_date('201005'||LPAD(rownum,2,'0'),'yyyymmdd'),'d'),'1',

                  to_char(to_date('201005'||LPAD(rownum,2,'0'),'yyyymmdd'),'dd'))) Sun_day
      ,max(decode(to_char(to_date('201005'||LPAD(rownum,2,'0'),'yyyymmdd'),'d'),'2',

                  to_char(to_date('201005'||LPAD(rownum,2,'0'),'yyyymmdd'),'dd'))) Mon_day
      ,max(decode(to_char(to_date('201005'||LPAD(rownum,2,'0'),'yyyymmdd'),'d'),'3',

                  to_char(to_date('201005'||LPAD(rownum,2,'0'),'yyyymmdd'),'dd'))) Tues_day
      ,max(decode(to_char(to_date('201005'||LPAD(rownum,2,'0'),'yyyymmdd'),'d'),'4',

                  to_char(to_date('201005'||LPAD(rownum,2,'0'),'yyyymmdd'),'dd'))) Wed_day
      ,max(decode(to_char(to_date('201005'||LPAD(rownum,2,'0'),'yyyymmdd'),'d'),'5',

                  to_char(to_date('201005'||LPAD(rownum,2,'0'),'yyyymmdd'),'dd'))) Thu_day
      ,max(decode(to_char(to_date('201005'||LPAD(rownum,2,'0'),'yyyymmdd'),'d'),'6',

                  to_char(to_date('201005'||LPAD(rownum,2,'0'),'yyyymmdd'),'dd'))) Fri_day
      ,max(decode(to_char(to_date('201005'||LPAD(rownum,2,'0'),'yyyymmdd'),'d'),'7',

                  to_char(to_date('201005'||LPAD(rownum,2,'0'),'yyyymmdd'),'dd'))) Sat_day
FROM ALL_OBJECTS
WHERE rownum <= ( last_day(to_date('201005'||'01','yyyymmdd'))-
                 to_date('201005'||'01','yyyymmdd') )+1
GROUP BY to_char(to_date('201005'||LPAD(rownum,2,'0'),'yyyymmdd'),'dd')-
         to_char(to_date('201005'||LPAD(rownum,2,'0'),'yyyymmdd'),'d')
ORDER BY 1

 

이렇게하면 나만의 새롭게 정의된 달력이 만들어 졌습니다.

어때요? 멋진가요? 제 프로필에 이런말이 있습니다. "무결점의 잘 정리된 정보는 무한한 창의력을 발휘한다" 이말은 어떤 말인지 차차 아시게 될것입니다.

 

쉬는타임이였는데 재밌으셨나 모르겠어요. 다음엔 다른 재밌는 쉬는 타임을 갖겠습니다.

그럼 오늘 하루 마무리 잘하세요.

SQL고수되기 - 쉬는타임이야기 - 2

쉬어가는 타입 두번째입니다. 열번째를 진행해야하지만 조금 더 논리Entity에 익숙해지시라고

출퇴근현황정보를 오라클에서 TOP-N방식으로 조회하는 기법에 대해 설명해볼까합니다.

보통 Inline View 방식이라고도 하는데요. 표현만 요란하지 알고보면 간단한 기법입니다.

 

여러분들이 아주 많이 보셨던 예가 있습니다. 아하 지식iN에도 있네요 검색하시게되면 화면 하단에 다음 Page이동 Navi처리부분이 있습니다.

 

 

 

이런거 자주보게되는데요.

직접만들어 볼까요.

 

상품 일일출고 내역을 한번에 10개씩 나우어 보여달라. 라고하는 요청이 있다면 어떻게 하면 될까요.

간단하게 테이블만들어서 해보죠

CREATE TABLE TMP_INF

AS

SELECT 'P'||LPAD(rownum,4,'0') code
      ,rownum||'번제품' name
      ,round(DBMS_RANDOM.VALUE*10000,-3) price
      ,ceil(DBMS_RANDOM.VALUE*100) qty
FROM  ALL_OBJECTS
WHERE ROWNUM < 100

1~99번쨰제품까지 등록이 되겠지요?

그러면 화면에 1~10, 11~20, 21~30 .. 이런식으로 검색해서 표시해주면될테고요

그럼 쿼리 만들어보죠

 

1) 원시데이터 (테이블정보)

SELECT * FROM TMP_INF

 

- 표시되는 순서는 보통 저장된 순서입니다.

 

2) 데이터가공(재정의)

SELECT code

      ,name

      ,price

      ,qty

FROM  TMP_INF

ORDER BY price,qty

- 저는 가격과 수량으로 정렬(재정의하였습니다.)

 

여기서 질문 한가지. 위에 보이는 그림 아니 위에처럼 보이는(정렬순서포함)
테이블이 있던가요? 물론 TMP_INF이지만요

결론을 말씀드리면 제가 재정의한 Entity입니다.

그럼 부분 처리해보죠

SELECT rownum seq
      ,a.*
FROM  (
        SELECT code
              ,name
              ,trim(to_char(price,'999,990')) price
              ,qty
        FROM  TMP_INF a
        ORDER BY a.price desc,qty desc
      ) a

 

굵은 글씨로 표시한 부분이 InLine View라고 지칭합니다. SubQuery로 표현하시면 안됩니다.

굵은 글씨로 표시한 부분을 CREATE or REPLACE VIEW TMP_INF_V ~절로 만들면 VIEW가 됩니다.

꼭 TMP_INF_V(View)는 Table처럼 보이게되지요.

이처럼 제가 제가정의한 Inline View에 단순 순서번호로 별칭을 달았습니다.

 

그럼 이 inline view를 고객이 원하는 방식으로 표현해보죠

SELECT a.*
FROM  (
        SELECT rownum seq
              ,a.*
        FROM  (
                SELECT code
                      ,name
                      ,trim(to_char(price,'999,990')) price
                      ,qty
                FROM  TMP_INF a
                ORDER BY a.price desc,qty desc
              ) a
      ) a
WHERE seq BETWEEN 1+(&cnt*(&page-1)) AND &cnt*&page

참고] &cnt : 페이지당 개수

      &page: 페이지번호

&cnt,&page만 바꾸시면 원하는 페이지별 정보를 표시할수 있겠지요?

 

예를들어 &cnt=10, &page=2이면 다음과 같습니다.

 

보통 게시판이나 목록조회시에 많이 사용되는 기법입니다.

물론 방법도 다양합니다.

rownum~~order by 대신 row_number() over(order by ~)도 있고요

아무튼 제가 다시 말씀드리면 반드시 정답은 없습니다.

만들기 나름이죠.. 말그대로 창작입니다. ^^

 

그럼 다음 이야기에서 봐요~~ 주말 잘들 보네시고요

SQL고수되기 - 쉬는타임이야기 - 3

쉬어가는 타입 세번째입니다. 어제(열두번째) 이야기에서 만들었던 쿼리 항목에 하나더 추가해서 만들어 볼까합니다.

 

┌────┬───┬────────┬──────────────┬────┬──┬──┐

│　사번　│이　름│　핸드폰　　　　│거주지　　　　　　　　　　　│　수당　│시간│Rank│

├────┼───┼────────┼──────────────┼────┼──┼──┤

│10040001│김길동│　011-6291-9928 │대구 북구 침산3동 650∼663　│ 920,000│ 300│　1 │

 

Rank 항목 보이시죠? 이거어떻게만들까요? ... 생각중이시죠.. 다들.... ^^

일단 머에 대한 Rank인지 부터 결정하죠. 수당으로 정하죠. 그다음은 무엇으로 해야할까요.

방식을 결정해야 겠지요? 랭킹 어떤식으로 정하시나요? 보통 10년전에는.. 아니 20세에는..

두가지로 생각해보죠 첫번째는 내가 직접 Rank 만들기, 두번째는 Oracle이 만들어주기

 

첫번째) TOP-N방식 부분범위 처리 방식

SELECT b.emp_no

      ,a.sname

      ,a.phone

      ,a.address

      ,b.allows

      ,b.times

      ,rownum rank
FROM  EMP_INF a

     ,(

        SELECT emp_no
              ,trim(to_char(sum(time_allow),'9,999,990')) allows
              ,sum(ceil((check_out-check_in)*24)) times
        FROM  WORK_INF
        WHERE attn_ymd like '201004%'
        GROUP BY emp_no
        HAVING sum(time_allow) > 600000
        ORDER BY allows desc
      ) b

WHERE a.emp_no = b.emp_no

ORDER BY allows desc, address, sname

;

간단하죠? 앞 이야기에서 다뤘던 내용이라 쉽게 이해하시리라 믿습니다.

 

두번째) 오라클리 만들어주기

SELECT b.emp_no

      ,a.sname

      ,a.phone

      ,a.address

      ,b.allows

      ,b.times

      ,rank() over(order by allows desc)       rank1

      ,rank() over(order by allows desc,times) rank2
FROM  EMP_INF a

     ,(

        SELECT emp_no
              ,trim(to_char(sum(time_allow),'9,999,990')) allows
              ,sum(ceil((check_out-check_in)*24)) times
        FROM  WORK_INF
        WHERE attn_ymd like '201004%'
        GROUP BY emp_no

        HAVING sum(time_allow) > 600000

      ) b

WHERE a.emp_no = b.emp_no

ORDER BY allows desc, address, sname;

 

두 차이점 보이시죠. 단순 rownum를 이용해서 처리시는 Allows(수당)에 대해 동일 Rank인 경우 처리불가입니다. 하지만 Rank 함수를 쓰면 다양하게 처리할 수 있습니다.

 

하나 더 해볼까요.. 그럼 그냥Rank말고 지역별 Rank 항목 결정해보죠. 아..지역별이라.. 어떻게 할까요.

어떻게 보면 아주 간단합니다. Oracle이 만들어 주니까 그냥 주문만 하면 되겠지요 ^^

SELECT b.emp_no

      ,a.sname

      ,a.phone

      ,a.address

      ,b.allows

      ,b.times

      ,rank() over(order by allows desc)       rank1

      ,rank() over(order by allows desc,times) rank2
      ,rank() over(partition by substr(address,1,2) order by allows desc,times) rank3
FROM  EMP_INF a

     ,(

        SELECT emp_no
              ,trim(to_char(sum(time_allow),'9,999,990')) allows
              ,sum(ceil((check_out-check_in)*24)) times
        FROM  WORK_INF
        WHERE attn_ymd like '201004%'
        GROUP BY emp_no

        HAVING sum(time_allow) > 600000

      ) b

WHERE a.emp_no = b.emp_no

ORDER BY allows desc, address, sname;

 

partition by 보이시죠. 이걸 풀어보면 말그대로 문제가 됩니다.

rank() over(partition by substr(address,1,2)order by allows desc,times)

Rank를 보여주되 수당,시간 순으로 지역별(부분별=by partition)로 구분한다.

추가적으로 비슷한 기능을 하는함수중에 dense_rank()라는 함수도 있습니다. 

 

오늘 내용정리하면 함수를 사용하면 수동으로 처리할때보다 쉽고 빨라서 종종활용되고 합니다. 그러나 여러분께서 명심하실 부분이있습니다. 처음에 오라클이 만들어준다고 했습니다. 어떻게 만들어 줄까요. 메모리상에서 무언가를 처리하겠지요. 건수가 많다면 이또한 결과시간에 영향을 미칠수 있습니다.

^^ 쓰시지말라는 얘기는 아닙니다. 인지하고 계시면 좋다는 얘기입니다.

 

주말 잘들 보내시고요.

수고하셨습니다.

SQL고수되기 - 쉬는타임이야기 - 4

쉬어가는 타임 네번째입니다. 오늘 트랜드(Trend)정보 생성에 대해 이야기 해볼까 합니다.
월별,년별 수당지급현황을 주기별 변화 추이 그래프 조회처럼 실제 업무상에서 많이 쓰이고
있습니다. 간단하지만 활용될 수 있는 아르바이트생별 월별 수당 추이 조회 쿼리를 만들어
보겠습니다.

앞에 3번째 쉬어가는 타임에 만들었던 쿼리와 비슷 할겁니다. 만드는 목적에 따라 몇몇 항목의
변화가 있을 뿐이지 기본 정보 조회는 같다고 보면 됩니다. 즉, 조회한 결과에 대해 순위를 보던지
추이를 보던지 월별 통계를 보던지 가공된 기본 정보를 활용하는 목적에 따라 함수할용,복제 등의
테크닉이 적용되는 것입니다. 기본이 바탕에 살을 더하는 그럼, 경험을 더하는 것입니다.

그럼 우리가 만들 쿼리에 대한 결과를 먼저 보겠습니다.

 

 

보시면 월별 수당과 전달 증감추이가 표현되어 있습니다.

참고로 엑셀로 간당하게 추이그래프 그려보았습니다.

이처럼 많은 부분에서 활용되는 추이정보에 대해 간단하게 알아보겠습니다.

그럼 단계별로 쿼리만들어 보겠습니다. 쿼리와 결과를 보시고 SELECT절의 각 항목별 재정의(가공)

방법를 보시면 쉽게 이해가 되실겁니다. 혹시 힘드시거나 추가 설며잉 필요하신분은 의견란에 내용

기재해 주시면 답변드리겠습니다.

 

STEP1) 목적에 맞게  원시데이터가공

SELECT a.emp_no
      ,b.sname
      ,substr(attn_ymd,1,6) yymm
      ,ceil((check_out-check_in)*24) work_times
      ,a.time_allow
FROM  WORK_INF a
     ,EMP_INF b
WHERE b.emp_no = a.emp_no
AND   a.attn_ymd like '2010%'
AND   a.time_allow != 0

 

STEP2) 가공된 원시데이터로 사원별,월별 식별자 생성

SELECT a.emp_no
      ,max(b.sname) sname
      ,substr(attn_ymd,1,6) yymm
      ,sum(ceil((check_out-check_in)*24)) work_times
      ,sum(a.time_allow) time_allow
FROM  WORK_INF a
     ,EMP_INF b
WHERE b.emp_no = a.emp_no
AND   a.attn_ymd like '2010%'
AND   a.time_allow != 0
GROUP BY a.emp_no,substr(attn_ymd,1,6)

 

STEP3) 사원별,월별 추이정보 생성

SELECT sname||'('||emp_no||')' sname
      ,yymm
      ,work_times
      ,time_allow
      ,lag(time_allow,1) over(partition by sname order by yymm) b_time_allow
FROM (
       SELECT a.emp_no
             ,max(b.sname) sname
             ,substr(attn_ymd,1,6) yymm
             ,sum(ceil((check_out-check_in)*24)) work_times
             ,sum(a.time_allow) time_allow
       FROM  WORK_INF a
            ,EMP_INF b
       WHERE b.emp_no = a.emp_no
       AND   a.attn_ymd like '2010%'
       AND   a.time_allow != 0
       GROUP BY a.emp_no,substr(attn_ymd,1,6)
     )

 

STEP4) 사원별,월별 추이정보 열가공

SELECT sname
      ,work_times
      ,decode(yymm,'201001',time_allow) "1월수당"
      ,decode(yymm,'201001',allow_rate) "1월추이"
      ,decode(yymm,'201002',time_allow) "2월수당"
      ,decode(yymm,'201002',allow_rate) "2월추이"
      ,decode(yymm,'201003',time_allow) "3월수당"
      ,decode(yymm,'201003',allow_rate) "3월추이"
      ,decode(yymm,'201004',time_allow) "4월수당"
      ,decode(yymm,'201004',allow_rate) "4월추이"
FROM (
       SELECT sname||'('||emp_no||')' sname
              ,yymm
              ,work_times
              ,time_allow
              ,lag(time_allow,1) over(partition by sname order by yymm) b_time_allow
              ,decode(lag(time_allow,1) over(partition by sname order by yymm),'','-',
                      round((lag(time_allow,1) over(partition by sname order by yymm)

-time_allow)/time_allow*100)) allow_rate
        FROM (
               SELECT a.emp_no
                     ,max(b.sname) sname
                     ,substr(attn_ymd,1,6) yymm
                     ,sum(ceil((check_out-check_in)*24)) work_times
                     ,sum(a.time_allow) time_allow
               FROM  WORK_INF a
                    ,EMP_INF b
               WHERE b.emp_no = a.emp_no
               AND   a.attn_ymd like '2010%'
               AND   a.time_allow != 0
               GROUP BY a.emp_no,substr(attn_ymd,1,6)
             )
      )

 

STEP4) 추이정보 완성

SELECT sname  "알바생"
      ,sum(work_times)||'hr' "총시간"
      ,max(decode(yymm,'201001',trim(to_char(time_allow,'9,999,999'))||'('||allow_rate||')')) "1월"
      ,max(decode(yymm,'201002',trim(to_char(time_allow,'9,999,999'))||'('||allow_rate||')')) "2월"
      ,max(decode(yymm,'201003',trim(to_char(time_allow,'9,999,999'))||'('||allow_rate||')')) "3월"
      ,max(decode(yymm,'201004',trim(to_char(time_allow,'9,999,999'))||'('||allow_rate||')')) "4월"
      ,max(decode(yymm,'201005',trim(to_char(time_allow,'9,999,999'))||'('||allow_rate||')')) "5월"
      ,max(decode(yymm,'201006',trim(to_char(time_allow,'9,999,999'))||'('||allow_rate||')')) "6월"
      ,max(decode(yymm,'201007',trim(to_char(time_allow,'9,999,999'))||'('||allow_rate||')')) "7월"
      ,max(decode(yymm,'201008',trim(to_char(time_allow,'9,999,999'))||'('||allow_rate||')')) "8월"
      ,max(decode(yymm,'201009',trim(to_char(time_allow,'9,999,999'))||'('||allow_rate||')')) "9월"
      ,max(decode(yymm,'201010',trim(to_char(time_allow,'9,999,999'))||'('||allow_rate||')')) "10월"
      ,max(decode(yymm,'201011',trim(to_char(time_allow,'9,999,999'))||'('||allow_rate||')')) "11월"
      ,max(decode(yymm,'201012',trim(to_char(time_allow,'9,999,999'))||'('||allow_rate||')')) "12월"
FROM (
       SELECT sname||'('||emp_no||')' sname
              ,yymm
              ,work_times
              ,time_allow
              ,lag(time_allow,1) over(partition by sname order by yymm) b_time_allow
              ,decode(lag(time_allow,1) over(partition by sname order by yymm),'','-',
                      round((lag(time_allow,1) over(partition by sname order by yymm)

-time_allow)/time_allow*100)) allow_rate
        FROM (
               SELECT a.emp_no
                     ,max(b.sname) sname
                     ,substr(attn_ymd,1,6) yymm
                     ,sum(ceil((check_out-check_in)*24)) work_times
                     ,sum(a.time_allow) time_allow
               FROM  WORK_INF a
                    ,EMP_INF b
               WHERE b.emp_no = a.emp_no
               AND   a.attn_ymd like '2010%'
               AND   a.time_allow != 0
               GROUP BY a.emp_no,substr(attn_ymd,1,6)
             )
      )
GROUP BY sname

 

SQL고수되기 - 열번째이야기

이번 이야기에선 앞으로 많이 활용되어질 아르바이트생정보에 대해 만들어볼까합니다.

종종 발생하는 Data Migration 방식을 보여드리고저 하는데 이땐 우편번호 정보가 필요합니다.

전부 다 가지고 계신게 아니기에 그냥 간단하게 주조를 고정시키고 만드는 방법과

랜덤하게 주소를 만드는 방법 두가지로 만들겠습니다.

 

참고로 우편정보 필요하신분들은 하단 의견란에 글남기시면

메일로 우편번호 생성 스크립트 보내드리겠습니다.

 

그럼 전에 생성했던 EMP_INF 테이블구조부터 확인하죠

CREATE TABLE EMP_INF
(
    EMP_NO  CHAR    (8 ),  /* 사번:년(2)+월(2)+일번(4) */
    SNAME   VARCHAR2(50),  /* 이름   */
    PHONE   VARCHAR2(20),  /* 핸드폰 */
    ADDRESS VARCHAR2(100),  /* 주소 */
    CONSTRAINT EMP_INF_PK PRIMARY KEY (EMP_NO)
    USING INDEX TABLESPACE TS00 PCTFREE 10
    STORAGE ( INITIAL 5M )
)
TABLESPACE TS01
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL        20M
            NEXT           20M
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
NOLOGGING
NOCACHE
PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT )
NOMONITORING;

 

첫번째로 출퇴근부 정보를 기준으로 사번하고 이름 만들겠습니다.

INSERT INTO EMP_INF(EMP_NO,SNAME)
SELECT EMP_NO
      ,decode(emp_no,'10040001', '김길동'
                    ,'10040002', '이미자'
                    ,'10040003', '박영철'
                    ,'10040004', '최순이'
                    ,'10040005', '정철수'
                    ,'10040006', '강민수'
                    ,'10040007', '조철이'
                    ,'10040008', '윤미애'
                    ,'10040009', '장남수'
                    ,'10040010', '임남정') SNAME
FROM WORK_INF
GROUP BY EMP_NO
ORDER BY 1
;

COMMIT;
단순하죠. 그냥 사번별로 이름 지정했습니다.

 

사실 자음구분자로 동적 이름 수십만 이상 아니 그이상도 임의의 테스트용

데이터를 정합성있게 만들어낼수 있지만 그건 생략하겠습니다.

다음에 기회되면 데이터 일괄처리 방식에 대해 이야기해보겠습니다.

 

그럼 다음은 사번별로 핸드폰과 주소 함 만들어보겠습니다.

UPDATE EMP_INF a
SET (PHONE,ADDRESS) =
    (SELECT PHONE,ADDRESS
     FROM  (
             SELECT emp_no
                   ,'011-'||
                    RPAD(CEIL(DBMS_RANDOM.VALUE*10000),4,'0')||'-'||
                    RPAD(CEIL(DBMS_RANDOM.VALUE*10000),4,'0') phone
                   ,decode(rownum,1 ,'대구 북구 침산3동 650∼663'
,2 ,'대전 동구 하소동'
,3 ,'서울 서대문구 충정로3가 동아일보사건물'
,4 ,'서울 송파구 마천2동 200-(68∼76)'
,5 ,'서울 관악구 신림2동 104∼117'
,6 ,'서울 마포구 합정동 430∼445'
,7,'서울 동작구 노량진동'
,8 ,'서울 영등포구 신길4동 158∼212'
,9 ,'서울 성동구 도선동 신한넥스텔'
,10,'서울 강동구 둔촌2동 163∼169')
             FROM  EMP_INF
           ) b
     WHERE b.emp_no = a.emp_no )
;

COMMIT;

 

주소도 그냥 일련번호 순으로 임의로 지정했습니다.

그런데 전화번호는 조금 다르죠. '011-' 고정시켜놓고요 나머지 부분은 4자리-4자리 형식에 맞게

Random하게 생성시켰습니다. 유효한 데이터 생성을 위해 야깐에 기교를 부렸습니다.

그리고 반드시 COMMIT 잊지마십시오.

 

그럼 두번째 주소마저 랜덤하게 만들어 보겠습니다.

CREATE TABLE EMP_INF_TMP
AS
SELECT /*+ use_hash(a,b) */
       a.emp_no
      ,a.phone
      ,b.address
FROM  (
        SELECT emp_no
              ,'011-'||

               RPAD(CEIL(DBMS_RANDOM.VALUE*10000),4,'0')||'-'||

               RPAD(CEIL(DBMS_RANDOM.VALUE*10000),4,'0') phone
              ,CEIL(DBMS_RANDOM.VALUE*100000/(rownum*2)) seq
        FROM  EMP_INF
      ) a
     ,(
        SELECT row_number() over(order by zipcode) seq
              ,SIDO||' '||GUGUN||' '||DONG||' '||BUNJI address
        FROM  ZIP_MASTER
      ) b
WHERE b.seq = a.seq
;

 

UPDATE EMP_INF a
SET (PHONE,ADDRESS) =
    (SELECT PHONE,ADDRESS
     FROM  EMP_INF_TMP b
     WHERE b.emp_no = a.emp_no )
;

 

COMMIT;

 

DROP TABLE EMP_INF_TMP PURGE;

 

임의 테이블생성하고 적용하고 삭제하고. 불필요한 부분일수 있지만

일괄UPDATE질의시 유용하게 활용될수 있습니다.

그리고 중간 스크립트 중 힌트 쓰인부분있습니다.

자세한 내용은 차차 설명드릴것이니 그때 이해하시면 됩니다.

 

만들어진 EMP_INF 확인

SELECT *
FROM  EMP_INF

 

지난 이야기시간에도 말씀드렸지만 다시한번 말씀드리겠습니다.
쿼리를 잘 짜려면 테이블 구조만 가지고는 부족합니다.
유효한 정확한 데이터를 만들수 잇어야하고요(테스트라고 할지라도)
또 만들어진 데이터에 대해서도 내용 분석을 할 수 있어야합니다.

다시한번 우편정보 필요하신분들은 의견란에 남겨주세요 메일로 생성 스크립트 보내드릴께요
오늘 수고 많으셨습니다.

SQL고수되기 - 열한번째이야기

안녕하세요. 오랜만에 이야기를 시작합니다. 오늘 할 이야기는 지난번 이야기에서 잠깐 이야기 나누웠던

통계현황 조회에 대해 구체적으로 이야기해볼까합니다.

이거 기억하시죠?

┌────┬───┬───┬───┬───┬───┬───┬───┬────┐
│　사번　│　일　│　월　│　화　│　수　│　목　│　금　│　토　│　전체　│
├────┼───┼───┼───┴───┴───┴───┴───┴────┤
│10010001│10시간│10시간│10시간│10시간│10시간│10시간│10시간│1000시간│
조금 간단한 내용일수도 있지만 오늘 구체적으로 3가지 방법론 적인 접근을 통해 이야기하려고 합니다.

먼저 결과부터 보여드리고 시작하겠습니다.

 

 

파란색 Block은 사원(아르바이트생)별 2010년 01월 근무시간과 추가근무수당입니다.

빨간색 Block은 월합계입니다.

간단하지만 월통계 처리시 많이 볼수있는 내용입니다. 항목별 소계/합계 등...

그럼 이런식으로 결과를 어떤방법이있을까요. 수많은 방식이 있지만 I/O기준으로 구분하여

3가지 정도로 이야기하려고 합니다.

 

그 첫번째는 부분 범위 처리방식입니다. 쉽계 설명드리면 위처럼 Block으로 구분하여 부분적으로 처리하는 방식입니다. 이는 아주 오랜전붜 활용되어 왔던 방식입니다. 물론 그당시엔 집계함수가 없어서 일수도 있습니다.

파랑색 Block 질의)

SELECT max(b.sname)||'('||a.emp_no||')' sname
      ,trim(to_char(sum(ceil((check_out-check_in)*24)),'9,990')) work_time
      ,trim(to_char(sum(time_allow),'99,999,990')) allows
FROM  WORK_INF a
     ,EMP_INF b
WHERE b.emp_no = a.emp_no
AND   a.attn_ymd like '201001%'
GROUP BY substr(attn_ymd,1,6),a.emp_no;

 

빨간색 Block 질의)

SELECT substr(attn_ymd,1,6)||'-합계' sname
      ,trim(to_char(sum(ceil((check_out-check_in)*24)),'9,990')) work_time
      ,trim(to_char(sum(time_allow),'99,999,990')) allows
FROM  WORK_INF a
WHERE a.attn_ymd like '201001%'
GROUP BY substr(attn_ymd,1,6);

 

파랑색 Block + 빨간색 Block)

SELECT max(b.sname)||'('||a.emp_no||')' sname
      ,trim(to_char(sum(ceil((check_out-check_in)*24)),'9,990')) work_time
      ,trim(to_char(sum(time_allow),'99,999,990')) allows
FROM  WORK_INF a
     ,EMP_INF b
WHERE b.emp_no = a.emp_no
AND   a.attn_ymd like '201001%'
GROUP BY substr(attn_ymd,1,6),a.emp_no
UNION ALL

SELECT substr(attn_ymd,1,6)||'-합계' sname
      ,trim(to_char(sum(ceil((check_out-check_in)*24)),'9,990')) work_time
      ,trim(to_char(sum(time_allow),'99,999,990')) allows
FROM  WORK_INF a
WHERE a.attn_ymd like '201001%'
GROUP BY substr(attn_ymd,1,6)

;

 

위 질의에 문제점은 무엇일까요? 없나요? 음... 또 생각중이시죠.... 제가 첨에 기준을 멀로 한다고 했지요? I/O기준이라 말씀드렸습니다. 즉, WORK_INF라는 테이블의 똑같은 정보(201001)를 두번 I/O하고 있습니다.

불필요한 I/O Scan인 것입니다. 하지만 애석하게도 지금도 이 방법이 활용되는 곳이 굉장히 많습니다. 만약 대상 건수가 100건이라면? 아니 1000만건이라면 어떨까요.. 물론 서버가 좋다면 병렬(Pararell)방식으로 할수있지만 이 또한 서버가 제한적이라면 함부로 쓸수도 없는 노릇입니다. 그래서, 나온 기법이 Catecian조인 기법입니다. 일명 묻지마조인입니다.

 

그럼 다음으로 Catecian 기법에 대해 알아보겟습니다. 일명 복제기법이라고도 하는데요 다음 쿼리를 보시면 아하 하시는 분들도 계실꺼고, 이미 알고 계신분들도 계실겁니다.

원시정보가공)
SELECT div
      ,substr(attn_ymd,1,6) attn_ym
      ,emp_no
      ,ceil((check_out-check_in)*24) work_time
      ,time_allow allows
FROM  WORK_INF a
     ,(SELECT rownum div FROM ALL_OBJECTS WHERE rownum < 3)
WHERE a.attn_ymd like '201001%'
ORDER BY check_in,emp_no,div;

보시면 똑같은 정보가 두개씩 표시되어 있는것을 보실수 있을겁니다. 일명 복제입니다.

1은 사원별 2는 전체합계 이렇게 구분하기 위해 2개로 복제하엿습니다. 하지만 WORK_INF는 한번만 I/O Scan 하였습니다.

 

집계하기)

SELECT decode(div,1,a.emp_no,substr(attn_ymd,1,6)) emp_no
      ,trim(to_char(sum(ceil((check_out-check_in)*24)),'9,990')) work_time
      ,trim(to_char(sum(time_allow),'99,999,990')) allows
FROM  WORK_INF a
     ,(SELECT rownum div FROM ALL_OBJECTS WHERE rownum < 3)
WHERE a.attn_ymd like '201001%'
GROUP BY decode(div,1,a.emp_no,substr(attn_ymd,1,6))

보시면 식별자가눈에 뜨을 것입니다. 일면 내가 정의한 Entity입니다.

 

결과만들기)

SELECT decode(b.sname,'',a.emp_no||'-합계',b.sname||'('||a.emp_no||')') sname
      ,work_time
      ,allows
FROM  (
        SELECT decode(div,1,a.emp_no,substr(attn_ymd,1,6)) emp_no
              ,trim(to_char(sum(ceil((check_out-check_in)*24)),'9,990')) work_time
              ,trim(to_char(sum(time_allow),'99,999,990')) allows
        FROM  WORK_INF a
             ,(SELECT rownum div FROM ALL_OBJECTS WHERE rownum < 3)
        WHERE a.attn_ymd like '201001%'
        GROUP BY decode(div,1,a.emp_no,substr(attn_ymd,1,6))
      ) a
     ,EMP_INF b
WHERE b.emp_no(+) = a.emp_no

결과는 동일하지만 WORK_INF를 한번만 I/O하여 처리한게 보이실겁니다. 하지만 이것도 불필요한 ALL_OBJECTS라는 테이블을 사용하였습니다. 물론 DUAL이라는 Dummy테이블을 사용해도 되지만 이또만 테이블이기에 불필요한 I/O가 발생한것과 같습니다. 이 방법 역시 아주 많은 곳에서 활용되는 방식입니다.

 

다음 세번째는 위 두가지 방식을 Oracle DB에서 내부적으로 처리하는 방식에 대해 이야기하겠습니다.

일명 집계함수하는 방식입니다. ROLLUP,CUBE...사용해 보신분들도 계시고 들어보신 분들도 계실겁니다. 이것이 처리되는 방식은 앞서 두가지 방식을 내가 하는게 아니라 Oracle이 해준다고 보시면 됩니다.

SELECT decode(GROUPING(a.emp_no),1,substr(attn_ymd,1,6)||'-합계'
,max(b.sname)||'('||a.emp_no||')') sname
      ,trim(to_char(sum(ceil((check_out-check_in)*24)),'9,990')) work_time
      ,trim(to_char(sum(time_allow),'99,999,990')) allows
FROM  WORK_INF a
     ,EMP_INF b
WHERE b.emp_no = a.emp_no
AND   a.attn_ymd like '201001%'
GROUP BY substr(attn_ymd,1,6),rollup(a.emp_no);

 

간단하죠. 단순하게 내가 합치려는 항목에 대해 Rollup을 걸어주는겁니다. 지금은 사원별로 합계니까 사번에 rollup을 걸었습니다. 이떄 합계와 사원별을 구분할수 있게 하는 키워드가 있습니다. GROUPING이라는 키워드입니다. 이것이 1이면 합계고 0이면 원시값입니다. 위에서는 GROUPING(a.emp_no)값이 1이면 201001 합계이고 0이면 사원별 정보가 되는 것입니다. 실행계획을 보면 훨씬 간단한 것을 보실수 있을겁니다.

 

오늘은 간단하지만 많이 활용되는 통계기법 3가지를 설명드렸습니다. ORACLE 7,8,9....11

각 버젼별로 활용가능한 기법들을 적용하시면 유용하게 활용될수 있는기법입니다.

 

그럼 이야기를 마무리하는 김에 한가지 문제를 내보겠습니다. 다음 그림처럼 쿼리를 만들어보세요.꼭!!!! 그냥 생각만 해보셔도 괜찮습니다.^^ 월별년통계입니다.

 

시간 되시는 분들은 꼭 해보세요
오늘 수고하셨습니다

 

SQL고수되기 - 열두번째이야기

지난 이야기(어제^^) 마지막에 내드렸던 거 해보셨는지요.. 힘드신분들은 생각만 해보샤도 도움이 되실겁니다. 아하~ 이런느낌.. 이렇구나.. 하지만 쿼리에 정답은 없습니다. 10명이면 조금씩 다를수 있으니까. 어느질의가 조금도 효율적인가 하는 점에서 차이가 날것입니다.

 

SELECT decode(GROUPING(a.emp_no),1,'월별합계'
,max(b.sname)||'('||a.emp_no||')') sname
      ,sum(decode(substr(attn_ymd,1,6),'201001',ceil((check_out-check_in)*24))) "201001(WT)"
      ,sum(decode(substr(attn_ymd,1,6),'201001',time_allow))                    "201001(ALW)"
      ,sum(decode(substr(attn_ymd,1,6),'201002',ceil((check_out-check_in)*24))) "201002(WT)"
      ,sum(decode(substr(attn_ymd,1,6),'201002',time_allow))                    "201002(ALW)"
      ,sum(decode(substr(attn_ymd,1,6),'201003',ceil((check_out-check_in)*24))) "201003(WT)"
      ,sum(decode(substr(attn_ymd,1,6),'201003',time_allow))                    "201003(ALW)"
      ,sum(decode(substr(attn_ymd,1,6),'201004',ceil((check_out-check_in)*24))) "201004(WT)"
      ,sum(decode(substr(attn_ymd,1,6),'201004',time_allow))                    "201004(ALW)"
      ,sum(decode(substr(attn_ymd,1,6),'201005',ceil((check_out-check_in)*24))) "201005(WT)"
      ,sum(decode(substr(attn_ymd,1,6),'201005',time_allow))                    "201005(ALW)"
      ,sum(decode(substr(attn_ymd,1,6),'201006',ceil((check_out-check_in)*24))) "201006(WT)"
      ,sum(decode(substr(attn_ymd,1,6),'201006',time_allow))                    "201006(ALW)"
      ,sum(decode(substr(attn_ymd,1,6),'201007',ceil((check_out-check_in)*24))) "201007(WT)"
      ,sum(decode(substr(attn_ymd,1,6),'201007',time_allow))                    "201007(ALW)"
      ,sum(decode(substr(attn_ymd,1,6),'201008',ceil((check_out-check_in)*24))) "201008(WT)"
      ,sum(decode(substr(attn_ymd,1,6),'201008',time_allow))                    "201008(ALW)"
      ,sum(decode(substr(attn_ymd,1,6),'201009',ceil((check_out-check_in)*24))) "201009(WT)"
      ,sum(decode(substr(attn_ymd,1,6),'201009',time_allow))                    "201009(ALW)"
      ,sum(decode(substr(attn_ymd,1,6),'201010',ceil((check_out-check_in)*24))) "201010(WT)"
      ,sum(decode(substr(attn_ymd,1,6),'201010',time_allow))                    "201010(ALW)"
      ,sum(decode(substr(attn_ymd,1,6),'201011',ceil((check_out-check_in)*24))) "201011(WT)"
      ,sum(decode(substr(attn_ymd,1,6),'201011',time_allow))                    "201011(ALW)"
      ,sum(decode(substr(attn_ymd,1,6),'201012',ceil((check_out-check_in)*24))) "201012(WT)"
      ,sum(decode(substr(attn_ymd,1,6),'201012',time_allow))                    "201012(ALW)"
FROM  WORK_INF a
     ,EMP_INF b
WHERE b.emp_no = a.emp_no
AND   a.attn_ymd like '2010%'
GROUP BY substr(attn_ymd,1,4),rollup(a.emp_no);

 

201005~201012 컬럼들은 나중에 정보가 생성되면 자동 조회되겟지요?

중요한건 식별자가 바뀌었습니다.

substr(attn_ymd,1,6) -> substr(attn_ymd,1,4) 집계기준 컬럼은 사원별로 동일하죠?

이처럼 조금은 간단할수도 있는 내용이지만 생각의 차이로 결과는 만들어집니다.

 

오늘 이야기주제는 Subquery입니다. Subquery란 말은 참쉽습니다. 간단하게 쿼리안에 쿼리? 또는 쿼리 안에 삽입된 쿼리? 맞는 말입니다. 근데 중요한 것은 사용하는 방식이 무엇인가에 따라 그 쓰임새는 매우 중요해 집니다. 오늘은 SubQuery기법 중에서도 그 첫번째로 부분범위 처리방식 중에 하나인 InLine View방식에 대해 이야기 해볼까합니다. 그럼 바로 실제 예를 들어서 해보죠.

 

2010년 4월 시간외수당 총액이 60만원보다 큰 아르바이트생을 다음과 같은 형태로 조회할수 있게 해주세요.

단, 수당 내림차순,거주지,이름 순으로 출력

이런 요청이 온다면 어떻게 할까요. 단계별 진행해보죠.

┌────┬───┬────────┬──────────────┬────┬──┐

│　사번　│이　름│　핸드폰　　　　│거주지　　　　　　　　　　　│　수당　│시간│

├────┼───┼────────┼──────────────┼────┼──┤

│10040001│김길동│　011-6291-9928 │대구 북구 침산3동 650∼663　│ 920,000│ 300│

 

STEP1) Output설계1 : 인적사항조회

SELECT emp_no

      ,sname

      ,phone

      ,address

FROM  EMP_INF;

 

STEP2) Output설계2 : 4월수당현황

SELECT emp_no
      ,sum(time_allow) allows1
      ,trim(to_char(sum(time_allow),'9,999,990'))  allows2
      ,sum(ceil((check_out-check_in)*24)) times
FROM  WORK_INF
WHERE attn_ymd like '201004%'
GROUP BY emp_no;

※ allows1,2 컬럼이있습니다. allows2의 경우 요청사항에 맞게 천단위 컴마(,) 형식 표시하였습니다.

   이처럼 반드시 Output설계시는 요청사항에 맞게 작업하여야 합니다.

 

STEP3) Output표시 : 요청사항완료

SELECT b.emp_no

      ,a.sname

      ,a.phone

      ,a.address

      ,b.allows

      ,b.times

FROM  EMP_INF a

     ,(

        SELECT emp_no
              ,trim(to_char(sum(time_allow),'9,999,990')) allows
              ,sum(ceil((check_out-check_in)*24)) times
        FROM  WORK_INF
        WHERE attn_ymd like '201004%'
        GROUP BY emp_no

        HAVING sum(time_allow) > 600000

      ) b

WHERE a.emp_no = b.emp_no

ORDER BY allows desc, address, sname;

 

간단하지만 아주 큰 의미를 담고 있습니다.

그럼 다음처럼 바꿔볼까요.

SELECT b.emp_no
      ,max(a.sname) sname
      ,max(a.phone) phone
      ,max(a.address) address
      ,trim(to_char(sum(b.time_allow),'9,999,990')) allows
      ,ceil(sum((check_out-check_in)*24)) times
FROM  EMP_INF a
     ,WORK_INF b
WHERE a.emp_no = b.emp_no
AND   b.attn_ymd like '201004%'
GROUP BY b.emp_no
HAVING sum(b.time_allow) > 600000
ORDER BY allows desc, address, sname;

 

위 최종 두쿼리의 결과는 다음과 같이 똑같습니다.

 

어느게 더 간단해 보이나요? 단계별로 풀어쓴 첫번쨰인가요? 아니면 야깐의 테크닉으로 짠 아래 쿼리인가요?
둘다 B_Tree구조의 Nested Join입니다. 정확히 말하면 위 쿼리는 View방식의 Nested Join이 맞을듯 싶습니다.

 

그럼 두쿼리 실행계획 살짝 보고 넘어갈까요?

첫번째 쿼리)

 

두번째 쿼리)

 

Row기준 Cost비용에 대한 Byte처리가 차이가 날겁니다. 581과 609... 그렇죠. 건수가 작아서 비교가 조금은 힘들수도 있지만 InLine View방식의 조금은 효율적이라는 것은 알수 있을겁니다.

 

그럼 InLine View를 활용하면 좋을때는 언제일까요. n:m 관계라는가 1;n관계에서 n쪽의 건수가 굉장히 차이나게 많은 경우에 유용하게 활용될 수 있습니다.

 

앞으로 많은 Subquery 이야기하겠지만 그중에서는 가장 많이 활용되는 inLine view에 대해 이야기해보았습니다. 여러분들도 지금 하고 계신 쿼리에 한번 적용해보시면 어떨까요...

 

수고하셧습니다. 안녕히들주므세요