Pori_IT

프로그래머스 SQL 문제도 다 풀었고 hackerrank로 넘어갑니다.

문제

https://www.hackerrank.com/challenges/the-pads/problem

풀이

우선 두 가지 방식으로 출력이 진행되어야 한다.

1. Name(A)와 같은 (직업 이니셜)의 형태가 포함된 출력 (ex. Ashely(P), Christeen(P))

주의할 점은 직업의 수가 4개만 존재한다는 것이다. [Doctor Professor Singer Actor]

SELECT concat(name,'(',substr(Occupation,1,1),')')  as name
FROM OCCUPATIONS
ORDER BY name;

간단하게 substr과 concat을 사용해서 만들어주면 된다.

2. There are a total of [occupation_count] [occupation] s. 의 형태로 직업의 count를 출력할 것.

여기서 주의할 점은 occupations가 기존에는 Doctor와 같이 첫 글자가 대문자이나 출력값에서는
모두 소문자로 출력해야 하는 점이다. (이 부분이 가장 어려웠다..)

WITH CTE2 AS (
    SELECT  count(*) as c, occupation
    FROM OCCUPATIONS
    GROUP BY Occupation
    ORDER BY 1 
)
, CTE3 AS (
    SELECT CONCAT('There are a total of ',c,' ',lower(occupation),'s.') as name
    FROM CTE2
)
SELECT name
FROM CTE3;

GROUP BY를 사용해서 각 직업별 count를 계산하고 이를 concat을 활용해서 문장의 형태로 만들어주었다.

문제

https://leetcode.com/problems/nth-highest-salary/description/

풀이

SQL로 함수를 처음 구성해봐서 로직이 어떤식으로 돌아가는지 이해에 초점을 맞추고 진행했습니다.

우선 전체 코드를 보여드리고 코드를 나누어가면서 설명하고자 합니다.

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET N = N-1;
  RETURN (
      # Write your MySQL query statement below.
      SELECT DISTINCT(salary) from Employee order by salary DESC
      LIMIT 1 OFFSET N
  );
END

기본적인 함수의 구성

MySQL에서 기본적인 함수의 형태는 다음과 같습니다.

CREATE FUNCTION function_name (parameter_name parameter_type, ...)
RETURNS return_data_type
BEGIN
    -- 함수 본문
    DECLARE variable_name variable_type;
    -- SQL 문장
    RETURN return_value;
END;

추가 예시로 덧셈 함수를 들어보겠습니다.

CREATE FUNCTION add_numbers (a INT, b INT) RETURNS INT
BEGIN
    RETURN a + b;
END;

코드 분석

- 함수 선언

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT

: 정수형 N을 인자로 받고 정수값을 반환합니다.

- N값 조정

SET N = N-1;

: OFFSET 구문이 0부터 시작하기 때문에 첫번째로 높은 급여를 가져오려면 OFFSET이 0이되도록 만들어야하기 때문입니다.

- SQL 쿼리

RETURN (
    # Write your MySQL query statement below.
    SELECT DISTINCT(salary) FROM Employee ORDER BY salary DESC
    LIMIT 1 OFFSET N
);

: 문제의 조건에 따라서 Employee의 salary를 내림차순으로 나열하고 OFFSET을 이용해 상위 N개의 급여를 선택합니다.

LIMIT을 사용해서 N번째로 높은 급여 단하나를 반환합니다.

문제

https://school.programmers.co.kr/learn/courses/30/lessons/276035

[문제 포인트 짚기]

스킬의 코드는 2진수로 표현했을 때 각 bit로 구분될 수 있도록 2의 제곱수로 구성되어 있습니다.
예를 들어 어떤 개발자의 SKILL_CODE가 400 (=b'110010000')이라면,
이는 SKILLCODES 테이블에서 CODE가 256 (=b'100000000'), 128 (=b'10000000'),
16 (=b'10000')에 해당하는 스킬을 가졌다는 것을 의미합니다.

Front End 스킬을 가진 개발자의 정보를 조회 => AND 연산을 수행해야 한다!

풀이

AND 연산 수행하기

왜 AND연산인가? 둘 모두 참인 경우에만 결과도 참으로 나오기 때문이다.

이를 활용하여 위의 예를 다시 살펴보면 400 코드에 256이 어떻게 들어있는지 판별이 가능하다.

400 & 256을 수행해서 256이 나온다면 256은 400안에 들어있다는 의미가 된다.

이를 활용해 코드에 적용하면 된다.

SELECT DISTINCT(d.ID), d.EMAIL, d.FIRST_NAME, d.LAST_NAME
FROM DEVELOPERS d LEFT JOIN SKILLCODES s
    ON (d.SKILL_CODE & s.CODE <>0)
WHERE s.CATEGORY = 'Front End'
ORDER BY 1

카테고리가 Front End인 값들만 가져와서 JOIN을 이용해 값이 들어있는지 비교한 코드이다.

문제

https://school.programmers.co.kr/learn/courses/30/lessons/131124

[문제 포인트 짚기]

MEMBER_PROFILE와 REST_REVIEW 테이블에서 리뷰를 가장 많이 작성한 회원의

리뷰들을 조회하는 SQL문을 작성해 주세요.

tip) 리뷰를 가장 많이 작성한 회원이 한 명이 아닌 경우에 모두 출력해야 한다.

(ex. A:3개, B:3개 => A, B 모두 출력)

풀이

1. count 칼럼 추가하기

식별자로 MEMBER_ID를 사용할 것이므로! REST_REVIEW테이블에서 각 MEMBER_ID별 리뷰의 수를 count 해서 칼럼으로 추가해 주었다.

여기서 추가로 생각한 점은 나중에 리뷰를 가져와야 하기 때문에 count열에만 group을 적용하는 방법을 사용했다.

WITH CTE AS (
    SELECT 
        MEMBER_ID,
        REVIEW_TEXT, 
        REVIEW_DATE,
        COUNT(*) OVER (PARTITION BY MEMBER_ID) as c
    FROM REST_REVIEW
)

2. rank 적용하기

가장 많은 리뷰수를 갖는 id를 찾아야 하므로 count에 rank를 적용해 주고 rank=1인 경우만을 가져오고자 했다.

, CTE2 AS (
    SELECT *,RANK() OVER (ORDER BY c DESC) as rk
    FROM CTE
)

3. 출력양식에 맞추기

마지막은 출력양식에 맞추어 결과를 나타내면 된다. 생각할 점은 DATE_FORMAT, 정렬 순서 정도가 있겠다.

WITH CTE AS (
    SELECT 
        MEMBER_ID,
        REVIEW_TEXT, 
        REVIEW_DATE,
        COUNT(*) OVER (PARTITION BY MEMBER_ID) as c
    FROM REST_REVIEW
)
, CTE2 AS (
    SELECT *,RANK() OVER (ORDER BY c DESC) as rk
    FROM CTE
)
SELECT m.MEMBER_NAME, c.REVIEW_TEXT, DATE_FORMAT(c.REVIEW_DATE, '%Y-%m-%d') as REVIEW_DATE
FROM CTE2 c JOIN MEMBER_PROFILE m
    ON c.MEMBER_ID = m.MEMBER_ID
WHERE rk = 1
ORDER BY 3,2