[MY SQL] 언어별 개발자 분류하기_비트 연산자 활용

출처: [프로그래머스 스쿨 - 코딩테스트 연습 - MY SQL 문제] : 언어별 개발자 분류하기

링크: https://school.programmers.co.kr/learn/courses/30/lessons/276036

문제(일부): 2개의 테이블 정보를 활용하여 보유한 스킬별로 등급부여

 

0.BIT_OR 집계함수 이해하기

여러 사용자의 권한을 그룹별로 합쳐서 최대 권한을 계산할 수 있음

• 부서 ID가 10인 경우, 권한 1, 2, 4가 있는데, 이를 OR 연산하면 1 | 2 | 4 = 7이 됩니다.
  즉, 읽기, 쓰기, 실행 권한 모두 있습니다.
• 부서 ID가 20인 경우, 권한 3과 6을 OR 연산하면 3 | 6 = 7이 되므로
  같은 권한을 가지게 됩니다.

CREATE TABLE UserPermissions (
    UserID int,
    DepartmentID int,
    Permissions int
);

-- 각 비트는 다른 권한을 나타냅니다 (예: 1 = 읽기, 2 = 쓰기, 4 = 실행).
INSERT INTO UserPermissions VALUES (1, 10, 1);
INSERT INTO UserPermissions VALUES (2, 10, 2);
INSERT INTO UserPermissions VALUES (3, 10, 4);
INSERT INTO UserPermissions VALUES (4, 20, 3);
INSERT INTO UserPermissions VALUES (5, 20, 6);

-- 부서별로 모든 사용자의 권한을 OR 연산하여 합친 권한을 계산합니다.
SELECT DepartmentID, BIT_OR(Permissions) AS CombinedPermissions
FROM UserPermissions
GROUP BY DepartmentID;

 

1.BIT_OR 집계함수 사용 풀이법

1) 2개의 테이블을 JOIN하고 조건으로 &비트 연산자 사용, 해당 스킬을 보유한 개발자 데이터와 JOIN

2) BIT_OR연산자를 사용하여 ID별로 해당 스킬을 가지고 있는지 확인(GROUP BY ID이므로)

3) CASE WHEN구문으로 등급부여  

* 원본 풀이: https://school.programmers.co.kr/questions/75369

SELECT
    CASE
        WHEN
            BIT_OR(category = "Front End") AND BIT_OR(S.name = "Python")
            THEN "A"
        WHEN
            BIT_OR(S.name = "C#")
            THEN "B"
        WHEN
            BIT_OR(category = "Front End")
            THEN "C"
        ELSE NULL
    END AS grade,
    id,
    email
FROM developers AS D
JOIN skillcodes AS S
    ON (D.skill_code & S.code) = S.code
GROUP BY id, email
HAVING grade IS NOT NULL
ORDER BY grade, id

 

2. WITH절 활용하기

1) 각 스킬별 WITH절을 생성

2) 1번의 WITH절과 개발자 테이블을 조인
     &연산자로 보유한 스킬 확인
    CASE WHEN구문으로 등급부여한 WITH절 생성

3)  메인쿼리에서 where조건절로 등급이 없는 레코드 제외

* 원본 풀이: https://school.programmers.co.kr/questions/75586

WITH FECODE AS (
SELECT SUM(CODE) AS FECODE
FROM SKILLCODES 
WHERE CATEGORY = 'Front End'
) , 

PYCODE AS (
SELECT CODE AS PYCODE 
FROM SKILLCODES WHERE NAME = 'Python'
), 

CSCODE AS (
SELECT CODE AS CSCODE
FROM SKILLCODES 
WHERE NAME = 'C#'
),

GRDEVS AS(
SELECT CASE
    WHEN (SKILL_CODE & FECODE) AND (SKILL_CODE & PYCODE) THEN 'A'
    WHEN SKILL_CODE & CSCODE THEN 'B'
    WHEN SKILL_CODE & FECODE THEN 'C'
    END AS GRADE
    , ID
    , EMAIL
FROM DEVELOPERS, FECODE, PYCODE, CSCODE
)

SELECT * 
FROM GRDEVS
WHERE GRADE IS NOT NULL
ORDER BY GRADE ASC, ID ASC