SQL 연습

SQL

프로그래머스에서 문제를 풀면서 W3스쿨을 보면서 하고 있다
NULL처리하기
SELECT ANIMAL_TYPE, COALESCE(NAME,"No name"), SEX_UPON_INTAKE FROM ANIMAL_INS
-> NAME 출력할 때 NAME이 NULL이면 "No name"이 출력 아니면 NAME이 출력된다
MY SQL과 Oracle,MSSQL공통으로 사용되는 COALESCE를 사용해서 NULL값을 걸러낸다
COALESCE(EXPR1,EXPR2,EXPR3...)- 가장 앞에 있는 EXPR 값이 NULL이 아닌 것을 반환한다

나이정보 없는 회원수

SELECT COUNT(*) AS USERS FROM USER_INFO WHERE AGE IS NULL
COUNT는 개수를 나타내 주는데 *을 통해 모든 정보를 가져온 뒤 WHERE을 통해 AGE IS NULL로 AGE값이 NULL인 것만 골라서 카운트 개수를 나타낸다
AS는 이름을 칼럼 명을 다르게 바꿀 수 있다

그냥 갑자기 난이도가 확 뛰어버렸다
그룹별 조건에 맞는 식당 목록 출력하기

SELECT
    MP.MEMBER_NAME,
    RR.REVIEW_TEXT,
    DATE_FORMAT(RR.REVIEW_DATE, "%Y-%m-%d") AS FORMATTED_REVIEW_DATE
FROM
    MEMBER_PROFILE MP
INNER JOIN (
    SELECT
        T1.MEMBER_ID,
        T1.REVIEW_TEXT,
        T1.REVIEW_DATE
    FROM
        REST_REVIEW T1
    INNER JOIN (
        SELECT
            U1.MEMBER_ID
        FROM
            REST_REVIEW U1
        GROUP BY
            U1.MEMBER_ID
        HAVING
            COUNT(*) = (
                SELECT MAX(REVIEW_COUNT)
                FROM (
                    SELECT COUNT(*) AS REVIEW_COUNT
                    FROM REST_REVIEW
                    GROUP BY MEMBER_ID
                ) U2
            )
    ) T2 ON T1.MEMBER_ID = T2.MEMBER_ID
) RR ON MP.MEMBER_ID = RR.MEMBER_ID
ORDER BY
    RR.REVIEW_DATE, RR.REVIEW_TEXT;

일단 작성하는데 급해서 별칭이 조금 복잡하게 되었는데 하나하나 설명해보자면

• 먼저 두개의 테이블을 합쳐야 하는데 필요한건 가장 많은 리뷰를 작성한 멤버의 아이디와 해당 아이디로 작성한 리뷰글 작성 날짜를 가진 테이블 하나와 그 멤버 아이디의 멤버 이름을 가진 테이블을 JOIN해야한다
• 이때 리뷰 목록이 있는 테이블에서 가장 많이 작성한 멤버들만 그룹으로 추려야 하는데 여기서 많이 복잡해진다

SELECT
    MP.MEMBER_NAME,
    RR.REVIEW_TEXT,
    DATE_FORMAT(RR.REVIEW_DATE, "%Y-%m-%d") AS FORMATTED_REVIEW_DATE
FROM
    MEMBER_PROFILE MP
INNER JOIN

여기까진 뽑아낼 테이블 칼럼과 에서 멤버 이름을 가진 멤버 테이블 그리고 JOIN이다

• 이후 JOIN할 테이블을 만들어야 하는데 가장 많은 리뷰를 쓴 멤버 아이디와 리뷰 글, 작성날짜 칼럼이 필요하다

    SELECT
        T1.MEMBER_ID,
        T1.REVIEW_TEXT,
        T1.REVIEW_DATE
    FROM
        REST_REVIEW T1
    INNER JOIN

• 이렇게 작성되었는데 먼저 REST_REVIEW은 모든 리뷰글이 모인 테이블이다 이중에 필요한 칼럼이 아이디, 리뷰글, 날짜 칼럼이고 여기서 또 INNER JOIN을 통해 가장 많은 리뷰글을 쓴 아이디를 추려내서 교집합 하면 그 아이디의 리뷰글들만 모이게 된다

(
        SELECT
            U1.MEMBER_ID
        FROM
            REST_REVIEW U1
        GROUP BY
            U1.MEMBER_ID
        HAVING
            COUNT(*) = (
                SELECT MAX(REVIEW_COUNT)
                FROM (
                    SELECT COUNT(*) AS REVIEW_COUNT
                    FROM REST_REVIEW
                    GROUP BY MEMBER_ID
                ) U2
            )
    )

• INNER JOIN아래 작성될 테이블로 먼저 멤버 ID를 추릴건데 GROUP BY를 통해 각 멤버 아이디별로 그룹화 하고 HAVING으로 조건을 건다
• COUNT(*)가 멤버ID로 그룹화 되었으니 각 멤버 ID당 몇개씩 행을 가지고 있는지 확인할 수 있다
• 그래서 COUNT(*)즉 멤버 ID당 가지고 있는 행의 개수가 가장 큰 수인 애들만 모을 수 있게 아래 테이블에서 가장 큰 수를 가져온다

(
  SELECT MAX(REVIEW_COUNT)
  FROM (
   SELECT COUNT(*) AS REVIEW_COUNT
   FROM REST_REVIEW
   GROUP BY MEMBER_ID
      ) U2
)

• 같은 REST_REVIEW테이블에서 GROUP BY를 통해 똑같이 멤버 아이디로 묵고 COUNT(*)로 칼럼을 나타낸 즉 각 멤버당 가지고 있는 행의 개수만 나타낸 테이블에서 MAX를 통해 가장 큰 값만 가져온다
• 그래서 위에서 HAVING COUNT(*) = (MAX(....))가 개수가 가장 많은 행들 이라는 조건 절을 가지게 된다

이후 아래에

  ) T2 ON T1.MEMBER_ID = T2.MEMBER_ID
) RR ON MP.MEMBER_ID = RR.MEMBER_ID

각각의 ON은 2번의 INNER JOIN의 조건인데 멤버아이디가 같은 것만 추려낼 수 있도록 한다

• T1은 모든 리뷰글이 모인 테이블, T2는 가장 많은 개수의 리뷰를 가진 멤버 아이디
• ON T1.MEMBER_ID = T2.MEMBER_ID이걸 통해 가장 많이 리뷰를 작성한 멤버들의 리뷰글들만 추려진다
• MP는 멤버 이름을 가진 멤버 프로필 테이블, RR은 위의 T1,T2가 서로 조인된 테이블
• ON MP.MEMBER_ID = RR.MEMBER_ID을 통해 가장 많이 리뷰를 작성한 멤버들의 이름들이 추려진다

마지막으로 ORDER BY를 통해 나타내고자 하는 오름차순 내림차순으로 정리하면 원하는 데이터 값이 뽑히게 된다