[프로그래머스_SQL_코딩테스트_풀이]_0402

DONGYOON KIM·2024년 4월 2일

SQL

목록 보기

10/14

SQL_프로그래머스 오답풀이

SUM(C.SALES A.PRICE) VS SUM(C.SALES) A.PRICE

첫 번째 쿼리


SELECT A.AUTHOR_ID, B.AUTHOR_NAME, A.CATEGORY, SUM(C.SALES * A.PRICE) AS TOTAL_SALES
FROM BOOK AS A
INNER JOIN AUTHOR AS B ON A.AUTHOR_ID = B.AUTHOR_ID
INNER JOIN BOOK_SALES AS C ON A.BOOK_ID = C.BOOK_ID
WHERE SALES_DATE BETWEEN '2022-01-01' AND '2022-01-31'
GROUP BY A.CATEGORY, A.AUTHOR_ID
ORDER BY A.AUTHOR_ID ASC, A.CATEGORY DESC

이 쿼리에서 SUM(C.SALES * A.PRICE)는 각 책의 판매량(C.SALES)과 그 책의 가격(A.PRICE)을 곱한 값들의 합계를 계산합니다. 즉, 각 책별로 판매된 수량에 대한 실제 판매 금액을 먼저 계산하고, 이들을 모두 더하여 총 판매액을 도출합니다. 이 방식은 각 책의 판매 수량과 가격을 정확히 반영하여 총 판매액을 계산하는 가장 정확한 방법입니다.

두 번째 쿼리


SELECT A.AUTHOR_ID, B.AUTHOR_NAME, A.CATEGORY, SUM(C.SALES) * A.PRICE AS TOTAL_SALES
FROM BOOK AS A
INNER JOIN AUTHOR AS B ON A.AUTHOR_ID = B.AUTHOR_ID
INNER JOIN BOOK_SALES AS C ON A.BOOK_ID = C.BOOK_ID
WHERE SALES_DATE BETWEEN '2022-01-01' AND '2022-01-31'
GROUP BY A.CATEGORY, A.AUTHOR_ID
ORDER BY A.AUTHOR_ID ASC, A.CATEGORY DESC

이 쿼리에서 SUM(C.SALES) * A.PRICE는 모든 판매된 책의 수량 합계를 먼저 계산한 후, 이를 해당 카테고리의 책 가격(A.PRICE)과 곱합니다. 이 방식은 그룹별(여기서는 저자와 카테고리별)로 모든 책의 총 판매 수량을 동일한 가격으로 계산하는 것을 의미합니다. 이는 특정 그룹 내에서 판매된 각 책의 가격이 서로 다를 경우 부정확한 결과를 초래할 수 있습니다.

차이점

첫 번째 쿼리는 각 책별로 실제 판매액을 계산하여 그 합계를 취하므로, 각 책의 가격 차이를 정확하게 반영합니다.
두 번째 쿼리는 모든 책의 판매 수량 합계에 단일 가격을 적용하므로, 가격 차이를 고려하지 않고 총 판매액을 추정합니다. 이 방식은 특정 조건(예: 모든 책의 가격이 동일한 경우)에서만 정확한 값을 제공합니다.

따라서, 정확한 총 판매액을 계산하기 위해서는 첫 번째 쿼리 방식을 사용하는 것이 좋습니다. 이는 각 책별 판매량과 가격을 모두 고려하기 때문에, 실제 판매액을 정확하게 반영할 수 있습니다.

비트연산자 활용 구문(SQL)

이 SQL 쿼리는 DEVELOPERS 테이블에서 특정 기준에 맞는 개발자 정보를 선택하여 반환합니다. 구체적으로, SKILLCODES 테이블에서 'Front End' 카테고리에 속하는 모든 기술 코드의 합을 계산하고, 이 합을 이용해 DEVELOPERS 테이블에서 해당 기술을 가진 개발자들을 필터링합니다. 쿼리를 단계별로 분해하여 해석해 보겠습니다.

1. 내부 쿼리 (서브쿼리)


SELECT SUM(B.CODE) FROM SKILLCODES B WHERE B.CATEGORY = 'Front End' GROUP BY B.CATEGORY

SKILLCODES 테이블에서 CATEGORY가 'Front End'인 모든 행을 찾습니다.
찾은 행들의 CODE 값을 합산합니다. CODE는 아마도 각 기술에 할당된 고유한 이진 코드(또는 다른 형태의 식별자)일 것입니다.
GROUP BY B.CATEGORY는 여기서는 한 카테고리('Front End')만을 대상으로 하므로, 실질적으로는 모든 'Front End' 기술 코드의 합을 구하는 데 사용됩니다.

2. 외부 쿼리


SELECT A.ID, A.EMAIL, A.FIRST_NAME, A.LAST_NAME
FROM DEVELOPERS A
WHERE A.SKILL_CODE & (서브쿼리 결과)
ORDER BY A.ID

DEVELOPERS 테이블에서 개발자 정보(ID, EMAIL, FIRST_NAME, LAST_NAME)를 선택합니다.
WHERE 절에서 A.SKILL_CODE와 서브쿼리의 결과(즉, 'Front End' 카테고리에 속하는 모든 기술 코드의 합)를 비트 AND 연산합니다.
- 이 비트 AND 연산은 DEVELOPERS 테이블의 SKILL_CODE 필드가 비트마스크로 사용되고 있음을 가리킵니다. 각 비트는 특정 기술을 나타내며, 'Front End' 카테고리에 해당하는 기술들의 조합(합)과 AND 연산을 통해 해당 기술을 가진 개발자를 식별합니다.
결과는 ID 필드에 따라 오름차순으로 정렬됩니다.

종합해석

이 쿼리는 'Front End' 카테고리에 속하는 모든 기술을 가진 개발자들의 ID, EMAIL, FIRST_NAME, LAST_NAME을 DEVELOPERS 테이블에서 선택하여, 개발자 ID의 오름차순으로 정렬한 목록을 반환합니다. 비트 AND 연산을 사용한 조건은 개발자가 'Front End' 카테고리의 하나 이상의 기술을 가지고 있음을 의미합니다.

비트 연산(Bitwise Operations)은 정수를 이진수(비트의 연속)로 표현했을 때 각 비트별로 수행하는 연산입니다. 이러한 연산은 주로 낮은 수준의 프로그래밍(시스템 프로그래밍, 하드웨어 제어 등)에서 사용되며, 성능 최적화, 데이터 압축, 암호화 등 다양한 분야에서 활용됩니다. 비트 연산은 각 비트를 직접 조작하기 때문에, 연산이 매우 빠르고 메모리 사용이 효율적입니다.

다음은 가장 일반적인 비트 연산자들입니다:

1. AND (`&`)

두 비트 모두 1이면 1을 반환합니다. 그렇지 않으면 0을 반환합니다. 예를 들어, 1011 & 1101 = 1001입니다.

2. OR (`|`)

두 비트 중 하나라도 1이면 1을 반환합니다. 두 비트 모두 0이면 0을 반환합니다. 예를 들어, 1011 | 1101 = 1111입니다.

3. XOR (`^`)

두 비트가 서로 다르면 1을 반환하고, 같으면 0을 반환합니다. 예를 들어, 1011 ^ 1101 = 0110입니다.

4. NOT (`~`)

모든 비트를 반전시킵니다. 1은 0으로, 0은 1로 바뀝니다. 예를 들어, ~1011 = 0100 (단, 실제 연산에서는 부호 비트와 정수의 크기에 따라 결과가 달라질 수 있습니다).

5. 비트 시프트 연산자

왼쪽 시프트 (<<): 모든 비트를 왼쪽으로 지정된 수만큼 이동시킵니다. 오른쪽에는 0이 채워집니다. 예를 들어, 1011 << 2 = 101100.
오른쪽 시프트 (>>): 모든 비트를 오른쪽으로 지정된 수만큼 이동시킵니다. 왼쪽에는 부호 비트(음수인 경우) 또는 0(양수인 경우)이 채워집니다. 예를 들어, 1011 >> 2 = 0010.

비트 연산의 활용 예:

플래그와 비트 마스크: 여러 조건을 하나의 정수로 표현하고 검사할 때 사용합니다. 예를 들어, 파일 시스템의 권한 설정(읽기, 쓰기, 실행 권한 등)에 비트 마스크를 사용할 수 있습니다.
데이터 압축과 암호화: 데이터를 압축하거나 암호화할 때 비트 연산이 자주 사용됩니다.
하드웨어와의 인터페이스: 하드웨어 디바이스를 제어하거나 상태를 읽어올 때, 비트 연산을 통해 특정 비트만을 조작하는 것이 필요합니다.
성능 최적화: 특정 연산들은 곱셈이나 나눗셈보다 비트 연산으로 수행하는 것이 훨씬 빠릅니다. 예를 들어, 2로 나누는 연산은 오른쪽으로 1비트 시프트하는 것으로 대체할 수 있습니다.

비트 연산은 매우 강력하지만, 코드의 가독성을 저해할 수 있으므로, 사용 시 주의가 필요합니다. 연산의 의도가 명확하게 드러나도록 주석을 달아두는 것

WHERE 절에서 A.SKILL_CODE와 서브쿼리의 결과(즉, 'Front End' 카테고리에 속하는 모든 기술 코드의 합)를 비트 AND 연산합니다.
이 비트 AND 연산은 DEVELOPERS 테이블의 SKILL_CODE 필드가 비트마스크로 사용되고 있음을 가리킵니다. 각 비트는 특정 기술을 나타내며, 'Front End' 카테고리에 해당하는 기술들의 조합(합)과 AND 연산을 통해 해당 기술을 가진 개발자를 식별합니다.

비트마스크(Bitmask)를 사용하는 방식은 각 비트를 독립된 플래그나 스위치로 취급하여, 단일 정수 값 내에서 여러 정보를 효율적으로 저장하고 조작하는 기법입니다. 이 방식에서는 각 비트의 위치가 특정 조건이나 속성을 나타내며, 비트의 값(0 또는 1)은 해당 조건이나 속성의 존재 여부를 표시합니다.

비트마스크의 원리

예를 들어, 8비트의 정수 하나를 사용해 8가지 서로 다른 기술을 나타낼 수 있습니다. 각 기술은 하나의 비트에 할당됩니다. 비트가 1이면 해당 기술을 가지고 있다는 것을 의미하고, 0이면 그렇지 않다는 것을 의미합니다.

비트:  7 6 5 4 3 2 1 0
기술:  H G F E D C B A

여기서 기술 A가 비트 0에, 기술 B가 비트 1에, 기술 H가 비트 7에 할당되었다고 가정해 보겠습니다. 만약 어떤 개발자가 기술 A, C, 그리고 G만을 가지고 있다면, 해당 개발자의 SKILL_CODE는 10000101 (2진수) 또는 133 (10진수)가 됩니다.

비트 AND 연산의 활용

SKILL_CODE 필드에 비트마스크를 사용하여 개발자의 기술을 표현할 때, 특정 기술 조합을 가진 개발자를 찾기 위해 비트 AND 연산을 사용할 수 있습니다. 비트 AND 연산은 두 정수의 각 비트 위치를 비교하여, 둘 다 1인 경우에만 결과의 해당 위치를 1로 설정합니다.

'Front End' 카테고리에 해당하는 기술들의 비트마스크 합(예: 기술 A, C, E에 대한 비트마스크가 1010100이라고 가정)을 DEVELOPERS 테이블의 SKILL_CODE와 비트 AND 연산으로 비교하면, 해당 카테고리의 기술을 하나 이상 가진 개발자를 식별할 수 있습니다.

MySQL WITH 구문

WITH 구문, 또한 Common Table Expression(CTE)라고 불리는 이 SQL 구문은 복잡한 쿼리를 단순화하고, 가독성을 높이며, 재귀적인 쿼리를 가능하게 합니다. WITH 구문을 사용하면, 쿼리의 시작 부분에서 하나 이상의 임시 결과 집합을 정의할 수 있으며, 이후의 SELECT, INSERT, UPDATE, 또는 DELETE 문에서 이 임시 결과 집합을 참조할 수 있습니다.

기본 구조


WITH CTE이름 AS (
    SELECT 문
)
SELECT * FROM CTE이름;

예시: 단순한 CTE 사용


WITH RegionalSales AS (
    SELECT Region, SUM(Sales) AS TotalSales
    FROM SalesRecords
    GROUP BY Region
)
SELECT Region, TotalSales
FROM RegionalSales
WHERE TotalSales > 1000000;

이 예시에서는 SalesRecords 테이블에서 각 지역별 총 판매액을 계산한 뒤, 총 판매액이 1,000,000 이상인 지역만 선택합니다.

프로그래머스 SQL 코테 오답

문제 설명

SKILLCODES 테이블은 개발자들이 사용하는 프로그래밍 언어에 대한 정보를 담은 테이블입니다. SKILLCODES 테이블의 구조는 다음과 같으며, NAME, CATEGORY, CODE는 각각 스킬의 이름, 스킬의 범주, 스킬의 코드를 의미합니다. 스킬의 코드는 2진수로 표현했을 때 각 bit로 구분될 수 있도록 2의 제곱수로 구성되어 있습니다.

NAME	TYPE	UNIQUE	NULLABLE
NAME	VARCHAR(N)	Y	N
CATEGORY	VARCHAR(N)	N	N
CODE	INTEGER	Y	N

DEVELOPERS 테이블은 개발자들의 프로그래밍 스킬 정보를 담은 테이블입니다. DEVELOPERS 테이블의 구조는 다음과 같으며, ID, FIRST_NAME, LAST_NAME, EMAIL, SKILL_CODE는 각각 개발자의 ID, 이름, 성, 이메일, 스킬 코드를 의미합니다. SKILL_CODE 컬럼은 INTEGER 타입이고, 2진수로 표현했을 때 각 bit는 SKILLCODES 테이블의 코드를 의미합니다.

NAME	TYPE	UNIQUE	NULLABLE
ID	VARCHAR(N)	Y	N
FIRST_NAME	VARCHAR(N)	N	Y
LAST_NAME	VARCHAR(N)	N	Y
EMAIL	VARCHAR(N)	Y	N
SKILL_CODE	INTEGER	N	N

예를 들어 어떤 개발자의 SKILL_CODE가 400 (=b'110010000')이라면, 이는 SKILLCODES 테이블에서 CODE가 256 (=b'100000000'), 128 (=b'10000000'), 16 (=b'10000') 에 해당하는 스킬을 가졌다는 것을 의미합니다.

문제

DEVELOPERS 테이블에서 GRADE별 개발자의 정보를 조회하려 합니다. GRADE는 다음과 같이 정해집니다.

A : Front End 스킬과 Python 스킬을 함께 가지고 있는 개발자
B : C# 스킬을 가진 개발자
C : 그 외의 Front End 개발자

GRADE가 존재하는 개발자의 GRADE, ID, EMAIL을 조회하는 SQL 문을 작성해 주세요.

결과는 GRADE와 ID를 기준으로 오름차순 정렬해 주세요.


WITH FE AS (
    SELECT SUM(CODE) AS TotalCode FROM SKILLCODES WHERE CATEGORY = 'Front End'
)
SELECT
    Sub.ID,
    Sub.EMAIL,
    Sub.GRADE
FROM (
    SELECT
        D.ID,
        D.EMAIL,
        CASE
            WHEN D.SKILL_CODE & (SELECT TotalCode FROM FE) > 0
                 AND D.SKILL_CODE & (SELECT CODE FROM SKILLCODES WHERE NAME = 'Python') > 0
                THEN 'A'
            WHEN D.SKILL_CODE & (SELECT CODE FROM SKILLCODES WHERE NAME = 'C#') > 0
                THEN 'B'
            WHEN D.SKILL_CODE & (SELECT TotalCode FROM FE) > 0
                THEN 'C'
        END AS GRADE
    FROM DEVELOPERS D
) AS Sub
WHERE Sub.GRADE IS NOT NULL
ORDER BY Sub.GRADE, Sub.ID;

CTE 사용: FE라는 CTE를 정의하여 'Front End' 카테고리에 속하는 스킬 코드의 총합(TotalCode)을 계산합니다.
서브쿼리 계산: DEVELOPERS 테이블에서 개발자의 ID, 이메일, 그리고 GRADE를 계산하는 서브쿼리를 정의합니다. 이 서브쿼리는 GRADE가 계산되는 내부 쿼리 역할을 합니다.
외부 쿼리 필터링: 서브쿼리의 결과를 Sub라는 별칭으로 사용하고, 이를 외부 쿼리에서 WHERE Sub.GRADE IS NOT NULL 조건으로 필터링합니다. 이렇게 함으로써, GRADE 값이 NULL이 아닌 행만 최종 결과로 포함시킵니다.
정렬: 최종 결과를 GRADE와 ID 순으로 정렬합니다.

DONGYOON KIM

이전 포스트

[zerobase_데이터취업스쿨] SQL~CH17

다음 포스트

[프로그래머스_SQL_코딩테스트_풀이]_0402

SQL

SQL_프로그래머스 오답풀이

SUM(C.SALES A.PRICE) VS SUM(C.SALES) A.PRICE

첫 번째 쿼리

두 번째 쿼리

차이점

비트연산자 활용 구문(SQL)

1. 내부 쿼리 (서브쿼리)

2. 외부 쿼리

종합해석

1. AND (`&`)

2. OR (`|`)

3. XOR (`^`)

4. NOT (`~`)

5. 비트 시프트 연산자

비트마스크의 원리

비트 AND 연산의 활용

MySQL WITH 구문

기본 구조

예시: 단순한 CTE 사용

프로그래머스 SQL 코테 오답

문제 설명

문제

[zerobase_데이터취업스쿨] SQL~CH17

[프로그래머스_SQL_코테문풀]_0403

0개의 댓글

[프로그래머스_SQL_코딩테스트_풀이]_0402

SQL

SQL_프로그래머스 오답풀이

SUM(C.SALES A.PRICE) VS SUM(C.SALES) A.PRICE

첫 번째 쿼리

두 번째 쿼리

차이점

비트연산자 활용 구문(SQL)

1. 내부 쿼리 (서브쿼리)

2. 외부 쿼리

종합해석

1. AND (&)

2. OR (|)

3. XOR (^)

4. NOT (~)

5. 비트 시프트 연산자

비트마스크의 원리

비트 AND 연산의 활용

MySQL WITH 구문

기본 구조

예시: 단순한 CTE 사용

프로그래머스 SQL 코테 오답

문제 설명

문제

[zerobase_데이터취업스쿨] SQL~CH17

[프로그래머스_SQL_코테문풀]_0403

0개의 댓글

1. AND (`&`)

2. OR (`|`)

3. XOR (`^`)

4. NOT (`~`)