오늘은 프로그래머스의 MySQL 2단계 문제들을 풀며, 정리해 두면 좋겠다고 생각이 들었던 내용들을 정리해봤다.
정석적인 답은 아니고, 그냥 나는 이렇게 풀었다... 라는 내용의 기록으로 봐주면 될 것 같다. 아래 내용은 모두 MySQL 기준이다.
1. 2️⃣ 비트 연산
MySQL에서는 비트 연산자를 사용 할 수 있다. 공식 문서에 따르면 다음의 6가지 연산자를 사용할 수 있다. (BIT_COUNT는 함수이므로 제외했다)
&: Bitwise AND로, 두 비트가 모두 1일 때만 1을 반환한다.|: Bitwise OR로, 두 비트 중 하나라도 1이면 1을 반환한다.^: Bitwise XOR로, 두 비트가 서로 다를 때만 1을 반환한다. 배타적 논리합이라고도 한다.~: Bitwise Inversion으로, 비트를 반전시킨다. 즉, 0이면 1이 되고 1이면 0이 된다.<<: 비트를 왼쪽으로 지정된 횟수만큼 이동시킨다.n << x는n * (2 ** x)와 같다.>>: 비트를 오른쪽으로 지정된 횟수만큼 이동시킨다.n >> x는n이 양수일 때n // (2 ** x)와 같다.
예시: 특정 형질을 가지는 대장균 찾기
문제 링크: 특정 형질을 가지는 대장균 찾기 | 프로그래머스 스쿨
이 문제는 1단계 문제이지만, 저번에 안 풀었던 문제이기도 하고, 비트 연산 예시로 좋을 것 같아서 들고 왔다.
이 문제에서 개체의 형질 GENOTYPE은 비트로 해석했을 때 의미를 가지게 된다. 비트를 오른쪽부터 왼쪽 방향으로 읽었을 때, 각 자리는 해당 형질의 보유 여부를 의미한다. 예를 들어, GENOTYPE이 8이라면, 8은 비트로 표현하면 1000이므로 4번 형질만을 보유하고 있는 것이다. GENOTYPE이 15라면, 15는 비트로 표현하면 1111이므로 1번 형질부터 4번 형질까지 모두 보유하고 있는 것이다.
이 문제에서 GENOTYPE의 조건은 다음과 같이 정리할 수 있다.
- 2번 형질(비트: 0010)과의
&연산 결과가 0이 된다. - 1번 형질(비트: 0001)과의
&연산 결과가 1 (비트: 0001)이 되거나, 3번 형질(비트: 0100)과의&연산 결과가 4(비트: 0100)가 된다. 또는 둘 다에 해당한다.
두 조건을 AND로 연결하기만 하면 된다.
SELECT COUNT(ID) AS COUNT
FROM ECOLI_DATA AS E
WHERE GENOTYPE & 2 = 0 AND (GENOTYPE & 4 = 4 OR GENOTYPE & 1 = 1);2. ➡️ 형 변환
MySQL에서 형 변환은 CAST 함수와 CONVERT 함수를 이용해서 할 수 있다. 두 함수는 syntax가 약간 다르다. 하지만 두 함수의 결과는 같다.
자동 형 변환
공식 문서에 따르면, 만약 어떤 연산자가 다른 타입의 피연산자들과 사용된다면, 피연산자의 타입 변환이 일어난다. 예를 들어, CONCAT는 문자열에 사용되는 함수인데, CONCAT의 인수에 문자열이 아닌 타입을 넣으면 그 인수의 타입이 자동으로 문자열로 변환된다.
SELECT CONCAT(3, "km")위 SQL문은 3이 문자열 타입으로 변환되어 km와 합쳐지므로 문자열 3km를 출력한다.
3. 📚 서브 쿼리
이제 레벨 2에 오니까 서브 쿼리를 이용해야 하는 문제들이 종종 보이기 시작한다. 서브 쿼리는 SQL문 안에 포함된 또다른 SQL문을 의미하며, 괄호 () 안에 쓴다. 서브 쿼리를 어디에 쓰냐에 따라 부르는 명칭이 있는데, SQLD에선 알아야 하지만 지금은 생략하는 걸로..
예시: 가격이 제일 비싼 식품의 정보 출력하기
문제 링크: 가격이 제일 비싼 식품의 정보 출력하기 | 프로그래머스 스쿨
문제 내용을 그대로 생각해보면, 가격이 제일 비싼 식품의 가격을 구하고, 그 가격에 해당하는 식품을 구하는 쿼리를 짜야 한다. 그런데 이 쿼리는 메인 쿼리만으론 짤 수 없다. 따라서, 가격이 제일 비싼 식품의 가격을 구하는 서브 쿼리를 짜고, 그 서브 쿼리 결과의 가격과 가격이 일치하는 식품의 정보를 구하는 식으로 짠다.
SELECT *
FROM FOOD_PRODUCT
WHERE PRICE = (SELECT MAX(PRICE)
FROM FOOD_PRODUCT);그런데, 사실 이 문제는 서브 쿼리를 이용하지 않고 풀 수 있다. 가격이 제일 비싼 식품의 정보는, 가격 기준으로 내림차순 정렬했을 때 첫 번째 행에 있을 것이다. 그래서 이 점을 활용해서, 가격 순으로 내림차순 정렬 후 첫 번째 행만을 출력하게 하면 된다.
SELECT *
FROM FOOD_PRODUCT
ORDER BY PRICE DESC
LIMIT 1;4. 🗄️ GROUP BY
특정 컬럼 또는 컬럼을 이용한 표현식에 따라 그룹으로 묶는다. 문법은 아래와 같다.
GROUP BY 그룹기준
[HAVING 집계값조건]GROUP BY로 그룹을 묶고,HAVING으로 집계 결과가 조건에 해당하는 그룹만을 출력한다.
거의 ~별로 라고 하면 GROUP BY를 생각하면 된다.
여러 기준으로 묶기
그룹 기준은 꼭 하나가 아니어도 된다. ,로 연결해서 여러 기준으로 묶을 수도 있다.
예시: 재구매가 일어난 상품과 회원 리스트 구하기
문제 링크: 재구매가 일어난 상품과 회원 리스트 구하기 | 프로그래머스 스쿨
이 문제에서 동일한 USER_ID에 대해 동일한 PRODUCT_ID가 2번 나타날 때, 재구매가 일어났다고 한다. 그런데, 만약 USER_ID를 기준으로 그룹으로 묶으면 PRODUCT_ID가 분류되지 않고, PRODUCT_ID를 기준으로 그룹으로 묶으면 USER_ID가 분류되지 않는다. 그렇다면 어떻게 해야 할까? 답은, 두 개를 모두 기준으로 묶는 것이다. USER_ID와 PRODUCT_ID 둘 다를 기준으로 삼아 묶게 되면 USER_ID도 분류되고, 그 USER_ID에 대한 PRODUCT_ID도 분류되어 아래의 표와 같은 형태가 된다.
| USER_ID | PRODUCT_ID |
|---|---|
| 1 | 3 |
| 1 | 4 |
| 2 | 4 |
| 3 | 5 |
이제 COUNT를 이용해 각 그룹에 대해 행이 2개 이상인 그룹들의 USER_ID와 PRODUCT_ID를 출력하면 끝이다. 이 문제의 테이블 구조를 보면 어떤 컬럼에서도 NULL이 허용되지 않으므로 안심하고 마음껏 COUNT(*)을 쓰자.
SELECT USER_ID, PRODUCT_ID
FROM ONLINE_SALE
GROUP BY USER_ID, PRODUCT_ID
HAVING COUNT(*) >= 2
ORDER BY USER_ID, PRODUCT_ID DESC;5. ❌ 중복 제거
앞서 설명한 GROUP BY를 중복 제거 용도로 이용할 수 있다. 그룹이라는 것이 어떤 그룹 기준에 해당하는 행들을 모은 것이기 때문에, 같은 그룹기준에 속하는 행들이 한 행으로 정리되기 때문이다.
예시: 중복 제거하기
문제 링크: 중복 제거하기 | 프로그래머스 스쿨
SELECT COUNT(S.NAME) AS count
FROM (SELECT NAME
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
GROUP BY NAME) AS S;이렇게 하면 같은 NAME들은 같은 NAME 그룹으로 속하기 때문에, 중복이 제거된다. 그런데, 사실 서브 쿼리를 쓰지 않고도 이 문제를 풀 수 있다.
바로 DISTINCT를 이용하는 것이다. 공식 문서에 따르면, 아래의 두 SQL문은 같은 의미를 갖는다고 한다.
SELECT DISTINCT c1, c2, c3 FROM t1
WHERE c1 > const;SELECT c1, c2, c3 FROM t1
WHERE c1 > const
GROUP BY c1, c2, c3;위 문제를 DISTINCT를 이용해 풀어보면 다음과 같다.
SELECT COUNT(DISTINCT NAME) AS count
FROM ANIMAL_INS;코드의 길이가 확 줄어든 것을 볼 수 있다.
6. ➗ DIV 연산자
내가 자주 쓰는 파이썬에서는// 연산자로 몫을 구할 수 있지만, MySQL에서 // 연산자라는 것은 없다. 대신 MySQL에서는 DIV 연산자를 사용한다.
예시: 가격대 별 상품의 개수 구하기
문제 링크: 가격대 별 상품의 개수 구하기 | 프로그래머스 스쿨
SELECT (PRICE DIV 10000 * 10000) AS PRICE_GROUP,
COUNT(PRODUCT_ID) AS PRODUCTS
FROM PRODUCT
GROUP BY (PRICE DIV 10000 * 10000)
ORDER BY PRICE DIV 10000 * 100000;- 이 문제의 풀이를 구경해보면, 가격대의 최소 가격을 하드 코딩하는 풀이도 있었는데.. 솔직히 그렇게 풀면 안된다고 생각한다..
- 그렇다면 하드 코딩 없이 가격대의 최소 가격을 구하려면 어떻게 해야 할까? 단위의 기준으로 나눈 몫을 구해서 거기에 단위를 곱하면 된다.
- 예를 들어, 이 문제에서는 가격 단위가 10000원이므로, 10000으로 나눈 몫을 구해서 10000을 곱한다. 어떤 상품이 13000원이라면
13000 DIV 10000 * 10000을 계산하여 10000이 가격대의 최소 가격인 가격대에 속하게 될 것이다. - 그 후, 이 가격대의 최소 가격을 그룹 기준으로 삼으면 된다.
7. 🔗 ANSI 테이블 조인
MySQL에서는 ANSI 테이블 조인을 사용해서 테이블을 조인할 수 있다. 그런데, 이 ANSI 테이블 조인이 매번 쓸 때마다 좀 헷갈린다. (특히, LEFT OUTER JOIN의 단어 순서라든지..) 그래서 테이블 조인 문법을 정리해보고자 한다.
기본 문법
SELECT 컬럼명
FROM 테이블1 조인타입 JOIN 테이블2
ON 조건ON 조건을 필수로 사용해야 하는 조인 타입이 있고, 오히려 쓰면 안되는 조인 타입도 있다.- 만약 두 테이블의 컬럼명이 같고 동등 비교라면
ON 조건을USING 컬럼명으로 바꿔 쓸 수 있다.
LEFT OUTER? OUTER LEFT?
다른 조인 타입의 경우 어느 쪽을 기준으로 한다는 개념이 없다 보니, 그냥 조인 타입만 쓰면 되는데, OUTER JOIN의 경우는 기준 방향을 같이 쓰기 때문에, 이게 은근 헷갈린다.
답은 아래와 같다. OUTER를 생략할 수도 있다.
SELECT 컬럼명
FROM 테이블1 방향 [OUTER] JOIN 테이블2
ON 조건8. 📌 CASE문
CASE문도 은근 헷갈리는 것 중 하나이다. WHEN을 까먹는다든가, END를 까먹는다든가... 그래서 CASE문 문법을 한 번 정리해두고자 한다.
SELECT CASE WHEN 비교조건1 THEN 값1
WHEN 비교조건2 THEN 값2
...
ELSE 값n
END AS 별칭기본적인 CASE문의 구조이다. 여기에서 동등 비교일 때에는 아래와 같이 쓸 수도 있다.
SELECT CASE 컬럼명(표현식) WHEN 비교값1 THEN 값1
WHEN 비교값2 THEN 값2
...
ELSE 값n
END AS 별칭컬럼명(표현식)을 CASE와 WHEN 사이에 쓰고, WHEN 뒤에는 동등 비교할 비교값만 쓰면 된다.
9. 📅 DATEDIFF와 단순 뺄셈
날짜끼리 빼기 위해서 - 연산을 하면 예상치 못한 값을 얻을 수 있다. 예시를 통해 살펴보자.
예시: 자동차 평균 대여 기간 구하기
문제 링크: 자동차 평균 대여 기간 구하기 | 프로그래머스 스쿨
이 문제에서, END_DATE - START_DATE + 1을 해서 대여 기간을 구하는 방법을 떠올릴 수 있다. 하지만, 이 방법은 잘못되었다.
SELECT END_DATE, START_DATE, END_DATE - START_DATE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY;이 쿼리를 실행해보면, 뭔가 좀 이상한 부분을 볼 수 있다. END_DATE가 2023-01-11이고 START_DATE가 2022-10-10인 행을 보자. END_DATE - START_DATE가 9101이다. 말이 안 된다.
어떻게 이런 일이 발생하는지를 알고 싶다면, END_DATE와 START_DATE를 정수(UNSIGNED, 부호 없는 BIGINT)로 캐스팅해보면 된다.
SELECT CAST(END_DATE AS UNSIGNED), CAST(START_DATE AS UNSIGNED)
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY;값을 살펴보면, 연도-월-일에서 중간의 -를 없앤 문자열을 수로 바꾼 느낌이 들 것이다. 2023-01-11이라면 20230111이라는 수로 바뀌는 것이다.
그렇다. 20230111 - 20221010 = 9101이다.
왜 이렇게 되는 걸까? 이것은 - 연산자는 수에 사용되는 연산자, 산술 연산자이기 때문이다. DATETIME이 수 형태로 자동 형 변환되어서, 산술 연산자 -에 해당하는 연산이 실행된 것이다.
따라서, 날짜끼리 빼기 위해서는 DATEDIFF 함수를 사용해야 한다. DATEDIFF 함수는 첫번째 인수에서 두번째 인수를 일수 기준으로 뺀 값을 돌려주는 함수이다.
위 문제의 올바른 풀이는 다음과 같다.
SELECT CAR_ID, ROUND(AVG(DATEDIFF(END_DATE, START_DATE) + 1), 1) AS AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
HAVING ROUND(AVG(DATEDIFF(END_DATE, START_DATE) + 1), 1) >= 7
ORDER BY ROUND(AVG(DATEDIFF(END_DATE, START_DATE) + 1), 1) DESC, CAR_ID DESC;- 저번에 언급했지만, 당일 대여하고 당일 반납하면 1일이기 때문에, 날짜끼리 뺸 결과(일수)에 1을 더해야 한다.
참고로 오라클의 경우는 날짜 간에 - 연산자를 사용할 수 있다. 연산 결과는 일수를 기준으로 한 NUMBER 타입이다. 오히려 DATEDIFF 함수가 없다.
SELECT CAR_ID, TO_CHAR(ROUND(AVG(END_DATE - START_DATE + 1), 1), 'FM999.0') AS AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
HAVING ROUND(AVG(END_DATE - START_DATE + 1), 1) >= 7
ORDER BY ROUND(AVG(END_DATE - START_DATE + 1), 1) DESC, CAR_ID DESC;오라클로 풀어본 버전인데, 좀 귀찮은 포인트가 하나 있다. 문제에 정수도 소숫점 한 자리까지 출력해야 하는 조건이 있다. 오라클의 경우 소수 부분이 0이면 소수 부분 출력을 안 해줘서 포맷을 지정해야 한다.. TO_CHAR 함수의 두 번째 인수 'FM999.0' 부분이 소수 부분 출력을 위해 포맷을 지정해주는 부분이다.
10. 🔠 문자열 비교와 숫자 비교의 차이
결론부터 얘기하자면 '12'와 '110'의 비교 결과와 12와 110의 비교 결과는 다르다는 것이다. 이번에도 예시를 통해 살펴보자.
예시: 노선별 평균 역 사이 거리 조회하기
문제 링크: 노선별 평균 역 사이 거리 조회하기 | 프로그래머스 스쿨
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;- 여기에서
ORDER BY TOTAL_DISTANCE를 하게 되면 예시로 주어진 테스트 케이스에서는 정답이 나오지만, 채점해보면 오답이 나오게 된다. 왜냐하면, 문자열 간의 비교가 되어서 특정 경우에 원하는 결과와 다른 결과가 나오기 때문이다. - 그 특정 케이스라 함은 위에서 결론이라고 설명한 케이스이다. 위 케이스에서
'1'과'1'을 비교하고,'2'와'1'을 비교해서'12'가 더 크다(사전상 뒤에 온다)는 결과를 얻게 된다. - 그래서, 여기에서는
TOTAL_DISTANCE가 아니라CONCAT의 첫 번째 인수 부분을 정렬 조건으로 두어야 한다.
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 ROUND(SUM(D_BETWEEN_DIST), 1) DESC;11. 📆 QUARTER 함수
예시 문제를 통해 설명하도록 하겠다.
예시: 분기별 분화된 대장균의 개체 수 구하기
문제 링크: 분기별 분화된 대장균의 개체 수 구하기 | 프로그래머스 스쿨
SELECT CONCAT(((MONTH(DIFFERENTIATION_DATE) - 1) DIV 3) + 1, "Q") AS QUARTER,
COUNT(ID) AS ECOLI_COUNT
FROM ECOLI_DATA
GROUP BY CONCAT(((MONTH(DIFFERENTIATION_DATE) - 1) DIV 3) + 1, "Q")
ORDER BY QUARTER;일단, QUARTER 함수 없이 풀어본 예시이다. 분기를 구하기 위해서는 월에서 1을 빼고 3으로 나눈 몫에 1을 더하면 된다. 예를 들어, 3월은 ((3 - 1) DIV 3) + 1이고, 이 값은 1이므로 1분기에 속한다.
하지만, QUARTER라는 함수를 이용해서 분기를 구할 수도 있다. 날짜를 인수로 주면, 분기가 나온다.
SELECT CONCAT(QUARTER(DIFFERENTIATION_DATE), "Q") AS QUARTER,
COUNT(ID) AS ECOLI_COUNT
FROM ECOLI_DATA
GROUP BY CONCAT(QUARTER(DIFFERENTIATION_DATE), "Q")
ORDER BY QUARTER;QUARTER 함수를 사용하니 간단하게 풀렸다.
12. 🔚 결론
이렇게 해서 오늘은 프로그래머스 레벨 2 문제를 풀면서 알게 된 점과, 헷갈렸던 점들을 정리하는 시간을 가져 보았다.
이제 앞으로 레벨 3, 레벨 4, 레벨 5 문제가 남았다. SQLD 합격 후기에 따르면 4단계까지 술술 풀 정도로 공부했다는 후기가 있어서, 나도 4단계까지를 목표로 잡고 풀이를 이어나가보고자 한다. 문제들을 풀어보면서 쿼리에 익숙해지고, 함수에 익숙해지고, 문법에 익숙해지면 SQLD 문제를 푸는 데에도 많은 도움이 되지 않을까?
