공기업 전산직을 위한 도서관

SELECT 

문제풀이

A. 비트연산 사용

SELECT DISTINCT A.ID, A.EMAIL, A.FIRST_NAME, A.LAST_NAME FROM DEVELOPERS AS A
              LEFT JOIN SKILLCODES AS B ON (A.SKILL_CODE & B.CODE) =  B.CODE  
WHERE B.NAME IN ('Python', 'C#')
ORDER BY A.ID ASC

문제를 풀면서 처음 써본 비트연산. 

해당 코드는 JOIN 과정 중 조건에서 A.SKILL_CODE 와 B.CODE 의 값을 비트로 치환 후 비교한다

예를들어 SKILL_CODE에서 400(1_1001_0000)은  256(1_0000_0000) , 128(1000_0000), 16(1_0000)으로

이루어져 있어 B.CODE의 PYTHON, JAVASCRIPT, JAVA을 포함한다.

B.CODE와 비트연산시 저 3개의 비트(B.CODE)에서 TRUE이므로 저 3개를 가진 B.NAME을 반환한다...

이정도의 개념인데

1. (A.SKILL_CODE & B.CODE) =  B.CODE와

2. A.SKILL_CODE & B.CODE 결과값이 같게 나왔다

이 부분에 대해서 좀 더 찾아봤다 요약하자면

1번은 특정 기술이 정확히 포함된 경우

2번은 특정 기술을 하나라도 포함한 경우 였다.

문제에서 모든 B.CODE들은 겹치지가 않아 해당문제에서는 문제될 일이 없다고 생각이 드는데 만약에 문제에서

A = 10(2)  , B=11(3)   ,   JAVA =01(1),  C=10(2) , C++ = 11(3) 라고한다면

2번을 했을경우  A는  C,  B는 JAVA, C, C++을 가지고 있게되고

1번은 A는 C를 가지지만 B는 C++ 만을 가지고 있게된다 

1. (A.SKILL_CODE & B.CODE) =  B.CODE → A=C , B= C++

2. A.SKILL_CODE & B.CODE →                      A=C , B=  C++, JAVA, C

이 정도의 차이인데 문제 예시에서는 그런경우는 없으니 두 결과값이 같다고 나온다

그리고 비트연산을 했을경우 행이 중복으로 출력이 되는데

문제에서는 기술을 가진 개발자의 정보기 때문에 중복해서 결과를 뽑아낼 필요는 없기 때문에 DISTINCT를 사용하여 출력한다. (해당 문제의 예시는 DISTINCT 를 안써도 풀리지만 테스트케이스에서 실패라고 뜸...)

GROUP BY 

문제풀이

A. CONCAT, ROUND, 집계함수 사용

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 SUM(D_BETWEEN_DIST) DESC

문제 해석을 잘못해서 헤맸던 문제, 문제에서 D_CUMLATIVE(노선별 누계 거리) 첫행이 51.1이여서 갈렸는데 

D_BETWEEN_DIST 만 봐도 누계거리, 평균사이 거리를 알 수 있었다.. (실제로는 51.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 SUM(D_BETWEEN_DIST) DESC

→  틀린이유 : 자주 틀리는 오답 중에 하나 컬럼명설정, 대문자 K가 아닌 소문자 K였다. 그래서 오답

GROUP BY

문제풀이

A. 재귀함수(RECURSIVE) 활용

WITH RECURSIVE TIME AS (
    SELECT 0 AS HOUR
    UNION ALL
    SELECT HOUR + 1 
    FROM TIME 
    WHERE HOUR <23)     

SELECT TIME.HOUR AS HOUR, COUNT(A.ANIMAL_ID) AS COUNT FROM TIME 
LEFT JOIN ANIMAL_OUTS AS A ON TIME.HOUR = HOUR(A.DATETIME)
GROUP BY TIME.HOUR
ORDER BY TIME.HOUR

※ RECURSIVE : 자기자신을 반복적으로 호출(재귀)할 때 사용하는 함수

RECURSIVE 라는 함수를 이 문제 풀면서 처음 알게 되었다.. 사용방법은 아래와 같다

WITH RECURSIVE TIME AS (
    SELECT 0 AS HOUR   ........... 1) 초기값 설정
    UNION ALL                  ........... 3) 초기값 + 다음값
    SELECT HOUR + 1     ........... 2) 초기값에서 다시불러온 값 = 다음 값
    FROM TIME 
    WHERE HOUR <23)     

RECURSIVE는 1번, 2번, 3번을 계속해서 행을 계속결합한다

WHERE 을 사용하여 HOUR가 23보다 작다는 조건을 건다면 해당 조건까지 반복출력한다

그 외에는 두개의 테이블 결합, 다만 INNER 조인을 쓰게된다면 공통부분이 나오기때문에 X, LEFT, RIGHT JOIN도 두 테이블 위치를 잘 보고 써야함

SELECT TIME.HOUR AS HOUR, COUNT(A.ANIMAL_ID) AS COUNT FROM TIME 
LEFT JOIN ANIMAL_OUTS AS A ON TIME.HOUR = HOUR(A.DATETIME)
GROUP BY TIME.HOUR
ORDER BY TIME.HOUR

SELECT 

문제풀이

A. JOIN 활용

SELECT A.ITEM_ID, A.ITEM_NAME, A.RARITY FROM ITEM_INFO AS A
     LEFT JOIN ITEM_TREE AS B ON A.ITEM_ID = B.ITEM_ID
WHERE B.PARENT_ITEM_ID IN (SELECT ITEM_ID FROM ITEM_INFO WHERE RARITY = 'RARE')
ORDER BY A.ITEM_ID DESC

세대, 업그레이드, 상속 등등 묶여있는 테이블은 일단 JOIN ON을 어떻게 써야할지 고민해야한다

ON 조건에 따라 1)과 2)으로 분리가 된다.

1)

SELECT * FROM ITEM_INFO AS A
LEFT JOIN ITEM_TREE AS B ON A.ITEM_ID = B.ITEM_ID

2)

SELECT * FROM ITEM_INFO AS A
LEFT JOIN ITEM_TREE AS B ON A.ITEM_ID = B.PARENT_ITEM_ID 

문제는 아이템 희귀가 'RARE' 레벨인 부모의 자식들의 정보를 구하는 것. 

자식의 정보를 구하는 것이기에 구하기 쉬운 1번을 선택하여 진행했다

부모 조건을 RARE 로 해야하기 때문에 ITEM_INFO에서 RARE 인 행만 뽑아내고,

 →  SELECT ITEM_ID FROM ITEM_INFO WHERE RARITY = 'RARE'  

이 레어 레벨의 행들이 부모가 되어야하므로  B.PARENT_ITEM_ID을 IN으로 조건을 건다

 →  B.PARENT_ITEM_ID  IN ( SELECT ITEM_ID FROM ITEM_INFO WHERE RARITY = 'RARE'  )

그러면 RARE 레벨인 부모의 테이블이 완성된다

SELECT * FROM ITEM_INFO AS A
     LEFT JOIN ITEM_TREE AS B ON A.ITEM_ID = B.ITEM_ID
WHERE B.PARENT_ITEM_ID IN (SELECT ITEM_ID FROM ITEM_INFO WHERE RARITY = 'RARE')

여기서 자식들의 필요한 정보, 정렬순서를 추가해주면 정답이 나온다

String, Date

문제풀이

A1. UNION ALL 사용

SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE,
PRODUCT_ID, USER_ID, SALES_AMOUNT 
FROM (SELECT SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT 
             FROM ONLINE_SALE 
             UNION ALL
             SELECT SALES_DATE, PRODUCT_ID, NULL AS USER_ID, SALES_AMOUNT 
             FROM OFFLINE_SALE
             ) AS A
WHERE SALES_DATE LIKE '2022-03%'
ORDER BY SALES_DATE, PRODUCT_ID, USER_ID

※ UNION : 중복제거 + 테이블 세로로 결합(위+ 아래)
※ UNION ALL : 중복제거X + 테이블 세로로 결합(위+ 아래)

문제에 있는거 그대로 따라갔다. ONLINE_SALE에서 필요한거 그대로 가져오고, OFFLINE_SALE테이블에서 필요한거 그대로 가져오고 결합 (UNION은 위아래로 단순하게 붙이는 것이기 때문에 컬럼만 맞으면 나중에 지장은 없다)

OFFLINE_SALE 에서 USER_ID는 NULL로 하라고했기에 NULL을 중간에 넣어줬다

단순하게 붙이면 이런 결과가 나오고 이 테이블을 가지고 다시 조건출력하면 끝

A2. WITH 사용

중간에 결합된 테이블을 바로가져왔지만 WITH 함수를 이용하여 테이블 지정후 작성해도 풀 수 있다

String, Date

문제풀이

A1. QUARTER 함수 사용

SELECT CONCAT(QUARTER(DIFFERENTIATION_DATE), 'Q') AS QUARTER, COUNT(ID) AS ECOLI_COUNT

FROM ECOLI_DATA 
GROUP BY QUARTER
ORDER BY QUARTER

※ QUARTER(컬럼명) : 컬럼명에 대한 분기 출력 함수 

A2. case 구문 사용

SELECT

CASE WHEN MONTH(DIFFERENTIATION_DATE) <4 THEN '1Q'
           WHEN MONTH(DIFFERENTIATION_DATE) <7 THEN '2Q'

           WHEN MONTH(DIFFERENTIATION_DATE) <10 THEN '3Q'
           ELSE '4Q' END AS QUARTER, COUNT(ID) AS ECOLI_COUNT
FROM ECOLI_DATA 
GROUP BY QUARTER
ORDER BY QUARTER

→ case 구문 및 날짜데이터 함수를 이용하여 월 구간 분류 후 출력