수리링의 데이터교실

프로그래머스에 비트 연산을 해야하는 MySQL문제가 종종 보이는데요. 정리해놓으면 좋을 것 같아서 문제 풀이를 한번 작성해 보겠습니다.

https://school.programmers.co.kr/learn/courses/30/lessons/301646

문제 : 2번 형질을 보유하지 않으면서 1번이나 3번 형질을 보유하고 있는 대장균 개체의 수(COUNT)를 출력하는 SQL 문을 작성해주세요. 1번과 3번 형질을 모두 보유하고 있는 경우도 1번이나 3번 형질을 보유하고 있는 경우에 포함합니다.

[1] 2진법으로 계산해 보기

먼저 각 열의 GENOTYPE의 수를 2진법으로 바꾸어 출력해 보겠습니다. 주어진 정수 데이터에 BIN() 함수를 사용하면 정수를 2진법 수로 변환한 결과를 출력합니다.

SELECT BIN(GENOTYPE) AS GENOTYPE_BIN
FROM ECOLI_DATA

그럼 이제 출력된 결과를 CAST함수를 이용해서 CHAR 데이터타입으로 바꾼 뒤, WHERE문을 사용해서 필터링을 해 보겠습니다.

SELECT COUNT(*) AS `COUNT` 
FROM (SELECT ID,
             CAST(BIN(GENOTYPE) AS CHAR) AS GENOTYPE_BIN
             FROM ECOLI_DATA) A
WHERE A.GENOTYPE_BIN LIKE '1' OR
      A.GENOTYPE_BIN LIKE '%10_' OR
      A.GENOTYPE_BIN LIKE '%01'

WHERE문의 각 줄은 다음과 같은 결과를 필터링합니다.

1. 서브쿼리문의 수가 1인 경우
2. 서브쿼리가 101 또는 100인 경우
3. 서브쿼리가 01로 끝나는 경우

마지막으로 SELECT COUNT(*) AS `COUNT`를 통해 필터링된 데이터 열의 갯수를 COUNT라는 컬럼명으로 출력하도록 해 주었습니다. 정답 통과입니다.

[2] 비트 연산으로 풀어보기

MySQL에는 비트(Bit) 단위로 논리 연산을 수행하는 연산자가 있습니다. 챗지피티한테 비트 연산자의 종류에 뭐가 있는지 테이블을 만들어 달라고 했는데요.

네. 이런게 있다네요. (^_^;;) 저는 여기서 &연산자와 NOT 연산자를 사용해서 위의 문제를 풀어보겠습니다.

SELECT * FROM ECOLI_DATA
WHERE
    GENOTYPE & 5
    AND NOT GENOTYPE & 2

GENOTYPE & 5

• 정수 5를 2진법 비트로 변환하면 101입니다. 따라서 &5는 값의 첫 번째, 세 번째 비트가 1인지 여부를 확인합니다.

NOT GENOTYPE & 2

• 비트 NOT 연산은 비트를 반전시키는 역할을 합니다. 여기서 2의 이진 표현은 10입니다. 따라서 이 비트 연산은 GENOTYPE 열의 값에서 2번째 비트를 확인하고, 그 값을 반전시키므로, 2번째 비트가 0인지 여부를 확인합니다.

SELECT COUNT(ID) AS `COUNT`
FROM ECOLI_DATA
WHERE
    GENOTYPE & 5
    AND NOT GENOTYPE & 2

마지막으로 카운트한 값을 출력해 주면 정답 통과입니다.

비트 연산자는 간단하게 풀 수 있지만 까먹기 쉽다는 단점이 있는 것 같습니다.

그래도 이런 게 있다는 걸 알아두고 필요할 때마다 열심히 꺼내 보면서 익숙해 져야겠습니다.

특정 열의 값에 대해 순위를(랭킹을) 매기기 위해서 다음과 같은 함수를 사용해볼 수 있습니다.

• RANK
• DENSE_RANK
• ROW_NUMBER
• NTILE

기본적인 함수 형식은 아래과 같습니다.

RANK() OVER ( [PARTITION BY colName1] ORDER BY colName2 [DESC] )
DENSE_RANK() OVER ( [PARTITION BY colName1] ORDER BY colName2 [DESC] )
ROW_NUMBER() OVER ( [PARTITION BY colName1] ORDER BY colName2 [DESC] )
NTILE() OVER ( [PARTITION BY colName1] ORDER BY colName2 [DESC] )

# [대괄호] 안의 값은 선택사항입니다

이렇게 보면 외계어같지만 실제로는 사용이 쉬운 함수들 입니다. (진짜루요)

제가 갖고 있는 데이터베이스에서 Employee의 사번, 이름, 젠더, 샐러리(연봉) 4개를 저장한 임시 테이블 TEMP를 가지고 차례차례 함수를 적용시켜 보겠습니다.

1. RANK()

RANK()함수는 내가 저장한 행(column)에 순위를 매겨서 정렬한 결과값을 보여주는 함수입니다.

SELECT emp_no, 
	CONCAT(first_name, ' ', last_name) AS full_name,
	salary,
	RANK() OVER(ORDER BY SALARY DESC) AS `rank`
FROM TEMP;

위의 쿼리에는 SALARY 행 내림차순을 기준으로 랭킹함수를 적용해서 rank라는 새로운 이름의 행(column)을 반환하도록 했습니다.

결과를 살펴봅시다. 잘 보시면 rank 행에 128등이 두 명입니다. 이유는 두 명의 salary 값이 중복되기 때문인데요. 이렇게 RANK() 함수에서는 tie : 중복값이 있는 데이터끼리는 같은 등수를 쉐어하게 됩니다. 그리고 그 등수를 쉐어하는 만큼 그 다음 등수는 밀려서 사라지게 되는데요. 128등이 두 명 오고 난 다음에 129등 없이 130등으로 시작하는 것을 보면 알 수 있습니다. 만약 128등이 3명이었다면 그 다음 등수는 131등으로 시작하겠죠.

2. DENSE_RANK()

DENSE_RANK() 함수는 RANK()와 거의 비슷하지만 약간 다릅니다.

SELECT emp_no, 
	CONCAT(first_name, ' ', last_name) AS full_name,
	salary,
	DENSE_RANK() OVER(ORDER BY SALARY DESC) AS `rank`
FROM TEMP;

DENSE_RANK()에서는 같은 값, 중복값의 존재나 갯수와 관계없이 무조건 1씩 차례로 증가합니다. 랭킹이 밀리지 않아요. 127등이 두 명 있다고 그 다음 등수가 129로 시작하지 않고, 128으로 시작하는 것을 보면 알 수 있어요.

예를 들어 올림픽에서 금메달을 2명이 공동 수상한다고 가정을 했을 때, 금메달이 2명이라고 은메달은 아무도 주지 않고 동메달을 주면 안 되잖아요? 금메달을 받은 사람의 수와 관계 없이 은메달도 반드시 준다, 라고 보면 됩니다. (금메달 공동 수상이 실제로 가능한지는.... 저도 모르지만요...)

그런데 보니까 아까 RANK()에서는 128등이 두 명이었는데 이번엔 127등이 두 명이네요, 왜 그럴까요? 데이터 이미지 중간에 생략된 부분에서 같은 값을 가지는 tie가 두 명 있었습니다. 그래서 RANK()는 숫자가 하나 밀려서 128등 두 명이 되었고, DENSE_RANK()는 숫자가 밀리지 않아서 127등 두 명이 되었어요.

3. ROW_NUMBER()

ROW_NUMBER() 함수는 기준에 따라 랭킹 정렬을 하되, 중복값과 관계 없이 무조건 1부터 차례대로 행 번호를 매겨 반환합니다.

SELECT emp_no, 
	CONCAT(first_name, ' ', last_name) AS full_name,
	salary,
	ROW_NUMBER() OVER(ORDER BY SALARY DESC) AS `rank`
FROM TEMP;

보시는 것처럼 같은 값이 있던 말던 무조건 1부터 시작해서 하나씩 줄번호를 매겨서 반환합니다.

직관적인 예시로 출석 번호나 키번호를 생각해볼 수 있을 것 같아요. 학급에 동명이인이 있다고 둘이 같은 출석번호를 쉐어하지는 않죠. 키가 같다고 키번호를 똑같이 쓰지도 않을 거구요. 이런 상황에서 ROW_NUMBER() 함수를 사용하면 되겠습니다.

4. NTILE()

NTILE 함수는 랭킹을 매기되, 내가 지정한 블럭 갯수만큼 구간을 나누어 등급 랭킹을 부여합니다.

SELECT emp_no, 
	CONCAT(first_name, ' ', last_name) AS full_name,
	salary,
	NTILE(5) OVER(ORDER BY SALARY DESC) AS `rank`
FROM TEMP;

위의 코드에서 NTILE(5)와 같이 NTILE 함수 안에 정수를 넣어 줬는데요. 이 말은 1, 2, 3, 4, 5등 구간으로 나누어 5개의 등급을 매겨 랭킹을 반환하라는 뜻이에요.

데이터 갯수가 많아 이렇게 초반부에는 1등급만 보이지만

샐러리를 내림차순으로 정렬한 다음 5개의 등급으로 나누어 랭킹이 1부터 5까지 차례대로 부여가 되었습니다.

5. PARTITION BY 추가 응용

그럼 PARTITION BY는 어떻게 활용할 수 있을까요?

SELECT emp_no, 
	CONCAT(first_name, ' ', last_name) AS full_name,
	salary,
	RANK() OVER(PARTITION BY emp_no ORDER BY SALARY DESC) AS `rank`
FROM TEMP;

제가 가지고 있던 TEMP 테이블에는 같은 사람의 샐러리가 매년 업데이트 되며 누적되어 있어서, 이렇게 이름에 따라서 샐러리 값을 여러 열(row)이 저장 되어 있었어요. OVER() 내부의 시작 부분에 PARTITION BY emp_no 를 추가해 주면서 직원 번호에 따라 파티션을 나누고 그 파티션 내부에서 랭킹을 매긴 값을 반환받았습니다. 이를 통해 직원별로 샐러리가(연봉이) 얼마나 상승했는지를 한 눈에 알아볼 수 있게 되었습니다.

<문제 설명>
어느 한 게임에서 사용되는 아이템들은 업그레이드가 가능합니다.
'ITEM_A'->'ITEM_B'와 같이 업그레이드가 가능할 때
'ITEM_A'를 'ITEM_B' 의 PARENT 아이템,
PARENT 아이템이 없는 아이템을 ROOT 아이템이라고 합니다.

(중략)....

<문제>
아이템의 희귀도가 'RARE'인 아이템들의 모든 다음 업그레이드 아이템의 아이템 ID(ITEM_ID), 아이템 명(ITEM_NAME), 아이템의 희귀도(RARITY)를 출력하는 SQL 문을 작성해 주세요. 이때 결과는 아이템 ID를 기준으로 내림차순 정렬해 주세요.

처음에 대충 생각하고 접근했더니 원하는 정답을 얻어내기 어려웠던 문제입니다!

다시 정신을 집중하고, RIGHT 조인과 서브쿼리를 이용해서 문제를 바로 해결했어요.

제가 풀이한 방법을 설명해 보겠습니다.

https://school.programmers.co.kr/learn/courses/30/lessons/273711

먼저 아이템의 희귀도가 'RARE'인 아이템들을 뽑아내서 시각화해 봅니다.

SELECT * FROM ITEM_INFO WHERE RARITY = 'RARE'

우리는 이 4개의 아이템들의 다음 업그레이드 아이템을 찾아야 합니다. 이를 위해서는 ITEM_TREE 테이블을 확인해 보아야 해요.

전 ITEM_TREE 테이블을 보고 직관적으로 이해하기가 어려웠어요. 딱 봤을 때 구조가 한눈에 들어오지 않았습니다. (쉽게 보이시는 분이 계시다면.... 멋지십니다! 따봉!)

그래서 이렇게 간단히 그래프를 그려 봤더니 쉽게 이해가 되었습니다. 우리가 구한 RARE 아이템은 0, 1, 3, 4였어요. 이 아이템들의 다음 업그레이드 아이템은 (0으로부터) 1, 2번 아이템 + (2로부터) 3, 4번 아이템 => 총 1, 2, 3, 4번 아이템이 해당됩니다. 이해가 되셨을까요?

그렇다면 일반화된 쿼리문으로 작성하려면 어떻게 해볼 수 있을까요? 우선 우리가 구한 RARE 아이템 0, 1, 3, 4가 부모가 되는 아이템, 즉 0, 1, 3, 4의 자식 아이템을 찾아야 해요. 이를 위해서 ITEM_TREE 테이블의 PARENT_ITEM_ID와, ITEM_INFO 테이블의 ITEM_ID가 동일한 부분을 연결해 주면 됩니다.

SELECT * FROM ITEM_INFO I
	RIGHT JOIN ITEM_TREE T
	ON I.ITEM_ID = T.PARENT_ITEM_ID
WHERE RARITY = 'RARE'

1) ITEM_INFO 테이블에서 RARITY가 'RARE'에 해당하는 아이템 중에서,

2) 그 아이템이 ITEM_TREE 테이블에 있는 어떤 놈의 PARENT_ITEM_ID에 해당한다면,

3) 그 놈들을 모두 뽑아내봐요.

라고 명령한 것과 같습니다. 그럼 위와 같이 결과를 시각화할 수 있는데요. 여기서 우리에게 필요한 것은 무엇일까요?

우리한테 필요한 것은 저기 저 1, 2, 3, 4번 아이템 번호밖에 없어요. T 테이블(ITEM_TREE)의 ITEM_ID만 뽑아내 주면 됩니다. SELECT 뒤에만 간단하게 바꾸어 줘 봅시다.

SELECT T.ITEM_ID FROM ITEM_INFO I
        RIGHT JOIN ITEM_TREE T
        ON I.ITEM_ID = T.PARENT_ITEM_ID
WHERE RARITY = 'RARE'

이제 우리가 원하는 아이디를 뽑아냈으니, 이 테이블을 서브쿼리로 기존의 ITEM_INFO 테이블과 조인해 주면 끝입니다!

SELECT X.ITEM_ID, I.ITEM_NAME, I.RARITY
FROM (SELECT T.ITEM_ID
      FROM ITEM_INFO I
      RIGHT JOIN ITEM_TREE T
      ON I.ITEM_ID = T.PARENT_ITEM_ID
      WHERE RARITY = 'RARE') X
JOIN ITEM_INFO I ON X.ITEM_ID = I.ITEM_ID
ORDER BY ITEM_ID DESC

서브쿼리는 X로 이름을 지정해 주었습니다. X의 아이템 아이디와 ITEM_INFO 테이블(I)의 아이템 아이디가 같다면 아이디/이름/레어리티를 셀렉하여 프린트하고, 아이템 아이디를 기준으로 내림차순 하여 정렬하도록 지정했습니다.

코드 통과, 정답입니다 :)

이해가 안 되는 부분이 있으시다면 댓글 달아주세요!

서브쿼리와 RIGHT JOIN 연습을 할 수 있었던 좋은 문제였습니다.

프로그래머스의 MySQL 코딩테스트 연습문제 '노선별 평균 역 사이 거리 조회하기' 를 풀었습니다. 서브쿼리와 ORDER BY에 대해 정리해두기 좋은 문제인 것 같아 블로그 포스팅을 해 보도록 하겠습니다.

https://school.programmers.co.kr/learn/courses/30/lessons/284531

먼저, 1차로 제출한 정답코드입니다. (오답)

SELECT ROUTE, 
        CONCAT(ROUND(SUM(D_BETWEEN_DIST), 1), "km") AS TOTAL_DISTANCE,
        CONCAT(ROUND(AVG(D_BETWEEN_DIST), 2), "km") AS AVERAGE_DISTANCE
FROM SUBWAY_DISTANCE
GROUP BY ROUTE
ORDER BY TOTAL_DISTANCE DESC

테스트 케이스는 통과했으나 정답으로 제출하면 결과가 틀리다고 나와 저를 당황스럽게 하였습니다. 무엇이 문제였을까요? 문제 조건을 다시 한 번 살펴보겠습니다.

1) 총 누계 거리와 평균 역 사이 거리의 컬럼명은 각각 TOTAL_DISTANCE, AVERAGE_DISTANCE 
2) 총 누계거리는 소수 둘째자리에서, 평균 역 사이 거리는 소수 셋째 자리에서 반올림 한 뒤 단위(km)를 함께 출력
3) 결과는 총 누계 거리를 기준으로 내림차순 정렬

왠지 CONCAT으로 스트링으로 만들어버린 값을 정렬하면서 3번에서 문제가 생기지 않을까 싶었어요. 그래서 1차로 제출했던 코드에서 CONCAT 함수를 제외한 전체를 서브쿼리로 사용해 보기로 했습니다. 

(SELECT ROUTE, 
        ROUND(SUM(D_BETWEEN_DIST), 1) AS TOTAL_DISTANCE,
        ROUND(AVG(D_BETWEEN_DIST), 2) AS AVERAGE_DISTANCE
FROM SUBWAY_DISTANCE
GROUP BY ROUTE
ORDER BY TOTAL_DISTANCE DESC) BF

먼저 CONCAT을 빼버린 부분을 전체 ( ) 괄호로 묶어서 임시 테이블 BF로 만들어 자기참조합니다.

그럼 2차로 제출한 정답코드를 보여드리겠습니다.

SELECT BF.ROUTE,
       CONCAT(BF.TOTAL_DISTANCE, "km") AS TOTAL_DISTANCE,
       CONCAT(BF.AVERAGE_DISTANCE, "km") AS AVERAGE_DISTANCE
       
FROM (SELECT ROUTE, 
        ROUND(SUM(D_BETWEEN_DIST), 1) AS TOTAL_DISTANCE,
        ROUND(AVG(D_BETWEEN_DIST), 2) AS AVERAGE_DISTANCE
FROM SUBWAY_DISTANCE
GROUP BY ROUTE
ORDER BY TOTAL_DISTANCE DESC) BF

합계: 100.0 / 100.0 (정답)

FROM 뒤에 임시 테이블(이너 테이블) BF를 넣어 줍니다. TOTAL_DISTANCE를 기준으로 내림차순 정렬은 이미 임시 테이블 BF에서 숫자형태로 완료가 된 상태입니다. 이렇게 정렬이 완료된 임시 테이블에서 CONCAT을 이용해 "km"을 붙이고 스트링화 해주었더니 정답으로 인정이 되었습니다.

서브쿼리와 정렬에 대해 생각해볼 수 있는 좋은 문제였습니다.