2012.06.08 11:16

SQL 에서 비트연산을 사용하자.

프로그램을 하면서 자주 접하게 되는 용어가 연산자란 것이다. 산술연산자, 논리연산자, 기타 등등의 많은 연산자에 대해 공부하게되고 그것들을 이용하여 단순한 덧셈 뺄셈의 사칙연산 부터 각각의 연산 기능을 응용한 복잡한 transaction 처리까지 많은 일을 하게된다. 뭐 오늘 하고 싶은 이야기는 이러 저러한 복잡한 이야기 보다는 디비에서 데이타 처리 할 때 이러 저러한 요인으로 인해 상태값 혹은 코드 값 같은 것을 가지게 되는데 그런 처리를 보다 간소하게 명료하게(? 컴퓨터 연산으로는 명료하지만 사람 눈에는 복잡할 수 있는) 세련되게 처리해 보자는 뜻에서 몇 자 적어보는 것이다. 


DB 테이블을 설계할 때 혹은 운영 중인 DB 에서 뭔가를 고치려고 할 때 특정 테이블에 컬럼이 매우 많아서 예를 들어 컬럼이 40개 내지 250개 정도 되는 테이블이라고 하면, 지금 프로그래밍을 배우는 사람이 아니라면 뭔 설계를 그 때위로 해 또는 그런 바보 같은 뭐 이런 소리가 나올 법한 일이나 실제 컬럼 40개 정도는 어디서나 쉽게 볼 수 있지 않나 그런 생각을 합니다. 실제로 이 곳 저곳 기웃 거리며 봤을 때도 컬럼 40개 정도는 흔한 일이었죠. 다만, 문제 삼고 싶은 부분은 그 컬럼 40개가 정말로 필요한 것들이냐는 점입니다. 정말 그 엔터티를 만족시키기 위한 것이냐는 점입니다. 정말 그 개별 속성을 일일이 가지고 필요한 정보들을 넣은 것이냐는 점입니다. 기웃거리며 본 짧은 지식으로는 비슷한 속성이지만 구분을 위해서 조회의 편의를 위해 기타 기존 컬럼의 성격을 잘 몰라서 컬럼을 늘인 경우가 대부분으로 판단되기 때문입니다. 더구나 허용 여부를 나타내는 char(1) 컬럼이 넘쳐나고 있다면 뭔가 새로운 고민을 해봐야 하고 선호도를 묻기 위해 늘여 놓은 체크박스 UI 를 보며 저 내용을 어떻게 DB data 화 할지에 대해 고민해봐야 한다고 생각합니다. 제 맘속에 답은 이미 정해져 있으니...^^


DB 를 이용하면서 알지 못하는 내용이 너무 많은 것 같습니다. 


	select 7 & 2, 7 & 3, 7 & 4, 7 & 5  -- from dual;
	select 8 & 2, 8 & 3, 8 & 4, 8 & 5  -- from dual;

위에 적힌 쿼리문이 쿼리문으로 보이시나요? "-- from dual;"  은 mysql 을 이용하시는 경우 주석 풀고...

어쨌거나 쿼리문으로 보이기는 하나요? "select 1 + 1" 과 같이 쿼리문에도 연산이 적용 가능합니다. 물론 그 연산을 조건문으로 포함하여 사용할 수도 있습니다. 


다시 이야기의 처음으로 돌아가서 체크박스 UI 를 고민해 봅시다. 여러가지 예시중에 원하는 것 또는 해당 사항을 n 개 이상 고르는 UI 에 대해 고민해 봅니다. 좀더 구체적인 예시로 좋아하는 빵의 종류를 선택하고 나중에 특정 빵을 선호하는 사람들을 조회한다고 가정해 보겠습니다. 빵의 종류는 단팥빵, 소보르빵, 바게뜨, 크로와상 네개로 시작해 보죠.

단팥빵
소보르빵
바게뜨
크로와상

특정 회원에 대한 선택값을 저장하는 경우이므로 데이타 저장 시 회원에 따른 1:n 형태의 서브 테이블을 구성할 수 있을 것입니다. 빵에 대한 코드 값과 이름을 회원 키별로 저장하는 서브테이블 말입니다. 

그런데, 빵 외의 속성을 가진 서브 테이블을 만드는 것이 의미가 있을까요? 여러 예외 사항이 있을 수 있지만, 지금은 단순히 회원의 선호도만 평가한다는 점에서는 서브테이블 생성은 크게 의미 없는 데이타 구성이라고 생각합니다. 그렇다면 서브테이블 말고 회원의 한 컬럼으로 담을 방법은 없을까? 위에서 설명한 연산을 이용하면 하나의 컬럼으로 담을 수 있습니다. 

    2 -->       10

    4 -->     100

    8 -->   1000

  16 --> 10000

단팥빵 : 2, 소보르빵 : 4, 바게뜨 : 8, 크로와상 : 16 으로 매칭된 코드표만 잘 관리한다면 여러개가 선택되어도 하나의 컬럼으로 관리할 수 있습니다. 좀더 구체화 해서 단팥빵과 소보르빵을 선택한 사람은 2, 4 의 합 6을 저장하면됩니다. 어떤 조합을 선택해도 더한 합은 31 이하의 숫자가 저장됩니다. 이진수를 이용한 연산을 하면 char(1) 컬럼을 늘인 것과 같은 효과를 볼 수 있습니다. 예시한 네개 외에 새로운 빵이 생성된다면 코드표에 32 를 적용하면됩니다. 그리고 최대 63까지 저장되는 커럼이 되면 됩니다. 보통 숫자 타입의 경우 int 를 사용하므로 최대 26개의 컬럼을 대체 할 수 있게 되는 것입니다. ^^ 

-- 소보르빵만 선택한 사람

select ... from [회원] where [빵선호도] = 4;

-- 소보르빵도 선택한 사람

select ... from [회원] where ([빵선호도] & 4) = 4;

-- 단팥빵과 소보르빵만 선택한 사람

select ... from [회원] where [빵선호도] = 6;

-- 단팥빵과 소보르빵도 선택한 사람

select ... from [회원] where ([빵선호도] & 6) = 6;

선호도와 같이 다중 선택에 대한 조사는 여러가지 경우가 있을 수 있다. 조건절에서의 활용 뿐 아니라 case 문을 활용한 처리도 가능하다.


설명을 위한 예시는 빵선호지만 제가 주로 사용하는 실예는 유저 그룹단위의 권한 처리 부분에서 많이 활용합니다. 쓰기 권한 컬럼, 읽기 권한 컬럼을 두고 그룹별(sysadmin, admin, manage, user) 코드표를 이용하여 권한 체크에 이용하는 것이 유용합니다. 나중에 유저 그룹에 뭐가 추가 될지 모르니 말입니다. ^^

저작자 표시
신고
크리에이티브 커먼즈 라이선스
Creative Commons License

'뭐하는데 > 디비 디비 딥' 카테고리의 다른 글

SQL 에서 비트연산을 사용하자.  (0) 2012.06.08
SQL Unplugged : 300  (0) 2011.06.03
SQL 에서 영업일 계산하기  (0) 2011.04.01
stored Procedure 검색  (0) 2010.04.09
SQL 서버명 변경  (0) 2009.07.31
OPEN XML 문서  (0) 2009.05.07
Trackback 1 Comment 0


티스토리 툴바