[SQL 쿼리테스트] 프로그래머스 Level1 복습

Level1 31문제 한 번에 다 풀기

 

문제별 복기사항

*우선순위: JOIN 복습

• 여러 기준으로 정렬하기: 기준 하나 빠트림, 정렬 기준 꼼꼼히 보기
• 상위 N개 레코드: 날짜가 가장 오래된 것은 ASC + LIMIT 1 또는 MIN (DESC + LIMIT 1 아님)
• 경기도에 위치한 식품창고 목록 출력하기: CASE-WHEN-THEN 구문 사용법 → (CASE WHEN 칼럼명 + 조건 THEN 값 ELSE 값 END) AS "칼럼명"
• 가장 비싼 상품 구하기: ORDER BY + LIMIT은 부하 가능성(느림), 집계함수 서브쿼리는 빠름 
• 흉부외과 또는 일반외과 의사 목록 출력하기: DATE_FORMAT(칼럼명, "%Y-%m-%d"), 칼럼에 여러 항목 리스트로 조건 걸 때 칼럼명 IN (항목1, 항목2) (IS IN 아님)
• 평균 일일 대여 요금 구하기: 평균 집계함수 AVG (AVERAGE 아님), 반올림 ROUND(칼럼명, 소수점 N자리까지)
• 자동차 대여 기록에서 장기/단기 대여 구분하기: 일수 차이 계산 DATEDIFF (DATE_DIFF 아님)
• 특정 옵션이 포함된 자동차 리스트 구하기: MySQL에서 문자열 비교시에는 LIKE "%문자%" 가 CONTAINS의 역할을 함
• 조건에 부합하는 중고거래 댓글 조회하기: 예시에 따라 RIGHT JOIN 해야 함 JOIN시 예시 확인하기
• 잡은 물고기의 평균 길이 구하기: NULL값 대체시 성능은 IFNULL(칼럼, 대체할 값)이 좋고 이식성은 FROM 서브쿼리에서 CASE-WHEN-THEN 구문이 좋음 (서브쿼리 사용시 CASE-WHEN-THEN 뒤에 AS로 칼럼명 재지정해야 함)
• 특정 형질을 가지는 대장균 찾기: 이진수 변환(BIN, CONV) + LIKE 또는 비트연산(&)으로 풀 수 있음

[SQL 비트연산 정리]

SQL 에서 비트연산은 사용자에게 권한을 부여하고 특정 사용자의 권한 확인시에 자주 사용됨 

ex) 읽기(1₁₀, 001₂), 쓰기(2₁₀, 010₂ ), 삭제(4₁₀ , 100₂) 권한 중 읽기, 쓰기 권한만 가지면 3₁₀(011₂)으로 입력

 

해당 사용자(3₁₀, 011₂) 에게 쓰기(2₁₀, 010₂) 권한이 있는지 확인할 때는 

쓰기에 해당하는 2₁₀를 플래그(Flag)로 사용자의 권한인 3₁₀과 AND 비트 연산(&)을 수행함

 

3 & 2 = 011₂ & 010₂ = 010₂ = 2 > 0 이므로 이 사용자는 쓰기 권한이 있다 라고 판단함

 

'권한이 있다'를 판단할 때 비트연산 = 플래그 대신 비트연산 > 0 을 사용하는 이유는 범용성(일반성) 때문임

여러 권한(플래그) 중 하나라도 가지고 있는가? 를 판단할 때는 비트연산 = 플래그(검증하려는 여러 플래그의 합계 값) 수식을 사용하면 특정 권한을 가지고 있더라도 가지고 있지 않다고 판단하기 때문에 비트연산 > 0 을 사용해야 함

→ 상세 설명은 Gemini 발췌

 

 

 

→ 결론: [특정 형질을 가지는 대장균 찾기] 의 쿼리는 2가지 버전으로 작성할 수 있음

① 형질을 이진수로 변환(CONV, BIN) 후 LIKE 로 문자열 비교

# 이진수 변환 (BIN, CONV)
SELECT COUNT(*) AS "COUNT"
FROM ECOLI_DATA
WHERE BIN(GENOTYPE) = "1" OR BIN(GENOTYPE) LIKE "%001" OR BIN(GENOTYPE) LIKE "%100" OR BIN(GENOTYPE) LIKE "%101";

 

② AND 비트 연산 

# 비트연산
SELECT COUNT(*) AS "COUNT"
FROM ECOLI_DATA
WHERE (GENOTYPE&2=0) AND (GENOTYPE&5>0);