[MySQL] programmers SQL 고득점 Kit: GROUP BY

https://school.programmers.co.kr/learn/courses/30/parts/17044

Lv3. 즐겨찾기가 가장 많은 식당 정보 출력하기

• REST_INFO 식당 정보 테이블
  • REST_ID 식당 ID
  • REST_NAME 식당 이름
  • FOOD_TYPE 음식 종류, NULL
  • VIEWS 조회수, NULL
  • FAVORITES 즐겨찾기 수, NULL
  • PARKING_LOT 주차장 유무, NULL
  • ADDRESS 주소, NULL
  • TEL 전화번호, NULL
• 문제
  • 음식종류별로 즐겨찾기수가 가장 많은 식당

    GROUP BY FOOD_TYPE

  • 음식 종류, ID, 식당 이름, 즐겨찾기 수 조회

    SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES

  • 음식 종류 기준 내림차순 정렬

    ORDER BY FOOD_TYPE desc

• 정답

  SELECT FOOD_TYPE, REST_ID, REST_NAME, FAVORITES
  FROM REST_INFO
  WHERE (FOOD_TYPE, FAVORITES) in (SELECT FOOD_TYPE, MAX(FAVORITES)
                                  FROM REST_INFO
                                  GROUP BY FOOD_TYPE)
  ORDER BY FOOD_TYPE desc

  SELECT A.FOOD_TYPE, A.REST_ID, A.REST_NAME, A.FAVORITES
  FROM REST_INFO A
  WHERE A.FAVORITES = (SELECT MAX(B.FAVORITES)
  											FROM REST_INFO B
  											WHERE B.FOOD_TYPE = A.FOOD_TYPE)
  ORDER BY FOOD_TYPE desc

Lv3. 조건에 맞는 사용자와 총 거래금액 조회하기

• USED_GOODS_BOARD 중고 거래 게시판 정보 테이블
  • BOARD_ID 게시글 ID
  • WRITER_ID 작성자 ID
  • TITLE 게시글 제목
  • CONTENTS 게시글 내용
  • PRICE 가격
  • CREATED_DATE 작성일, DATE
  • STATUS 거래상태
  • VIEWS 조회수
• USED_GOODS_USER 중고 거래 게시판 사용자 정보 테이블
  • USER_ID 회원 ID
  • NICKNAME 닉네임
  • CITY 시
  • STREET_ADDRESS1 도로명 주소
  • STREET_ADDRESS2 상세 주소, NULL
  • TLNO 전화번호
• 문제
  • 완료된 중고 거래

    WHERE STATUS = 'DONE'

  • 총금액 70만원 이상

    WHERE (SELECT SUM(PRICE) ...) >= 700000

  • 회원ID, 닉네임, 총거래금액 출력

    SELECT USER_ID, NICKNAME, SUM(PRICE)

  • 총거래금액 기준 오름차순 정렬

    ORDER BY SUM(PRICE)

• 정답

  SELECT USER_ID, NICKNAME, SUM(PRICE) as TOTAL_SALES
  FROM USED_GOODS_BOARD A
  JOIN USED_GOODS_USER B on A.WRITER_ID = B.USER_ID
  WHERE STATUS = 'DONE'
  GROUP BY USER_ID
  HAVING TOTAL_SALES >= 700000
  ORDER BY TOTAL_SALES

Lv4. 저자 별 카테고리 별 매출액 집계하기

• BOOK : 어느 한 서점에서 판매중인 도서들의 도서 정보 테이블

  Column name	Type	Nullable	Description
  BOOK_ID	INTEGER	FALSE	도서 ID
  CATEGORY	VARCHAR(N)	FALSE	카테고리 (경제, 인문, 소설, 생활, 기술)
  AUTHOR_ID	INTEGER	FALSE	저자 ID
  PRICE	INTEGER	FALSE	판매가 (원)
  PUBLISHED_DATE	DATE	FALSE	출판일

• AUTHOR 도서의 저자의 정보 테이블

  Column name	Type	Nullable	Description
  AUTHOR_ID	INTEGER	FALSE	저자 ID
  AUTHOR_NAME	VARCHAR(N)	FALSE	저자명

• BOOK_SALES 각 도서의 날짜 별 판매량 정보 테이블

  Column name	Type	Nullable	Description
  BOOK_ID	INTEGER	FALSE	도서 ID
  SALES_DATE	DATE	FALSE	판매일
  SALES	INTEGER	FALSE	판매량

• 문제
  • 2022년 1월 도서 판매 데이터 기준

    SELECT BOOK_ID, count(SALES)
    FROM BOOK_SALES
    WHERE SALES_DATE like '2022-01%'
    GROUP BY BOOK_ID

  • 저자 별, 카테고리 별 매출액

    • TOTAL_SALES = 판매량 * 판매가

      SELECT PRICE * count(SALES) as TOTAL_SALES

  • 저자ID, 저자명, 카테고리, 매출액 리스트 출력

    SELECT AUTHOR_ID, AUTHOR_NAME, CATEGORY, TOTAL_SALES

  • 저자 ID 오름차순 정렬, 같다면 카테고리 내림차순 정렬

    ORDER BY AUTHOR_ID, CATEGORY desc

• 오답

  SELECT AUTHOR_ID, AUTHOR_NAME, CATEGORY, TOTAL_SALES
  FROM AUTHOR
  NATURAL JOIN (SELECT BOOK_ID, CATEGORY, AUTHOR_ID, CNTSALES * price as TOTAL_SALES
              FROM BOOK
              NATURAL JOIN (SELECT BOOK_ID, SUM(SALES) as CNTSALES
              FROM BOOK_SALES
              WHERE SALES_DATE like '2022-01%'
              GROUP BY BOOK_ID) as SUB) as TOTAL
  ORDER BY AUTHOR_ID, CATEGORY desc

  • JOIN을 한 번에 써보자. Sub query 만드는 방법보다는 !
  • NATURAL JOIN보다는 JOIN…ON~으로 명시적인 방법 사용해서 오류 줄이기!
• 정답

  SELECT AUTHOR_ID, AUTHOR_NAME, CATEGORY, SUM(SALES * PRICE) as TOTAL_SALES
  FROM BOOK_SALES as BS
  NATURAL JOIN BOOK as B
  NATURAL JOIN AUTHOR as A
  WHERE BS.SALES_DATE like '2022-01%'
  GROUP BY AUTHOR_ID, CATEGORY
  ORDER BY AUTHOR_ID, CATEGORY desc

  • 그래도 NATURAL JOIN이 편하다.. 공통 칼럼 하나만 있는게 명확하니까

Lv3. 카테고리 별 도서 판매량 집계하기

• 문제
  • 2022년 1월
  • 카테고리 별 도서 판매량 합산
  • 카테고리, 총 판매량 출력
  • 카테고리 오름차순 정렬
• 정답

  SELECT CATEGORY, SUM(SALES) as TOTAL_SALES
  FROM BOOK as B
  NATURAL JOIN BOOK_SALES as BS
  WHERE SALES_DATE like '2022-01%'
  GROUP BY CATEGORY
  ORDER BY CATEGORY

Lv3. 자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기

• CAR_RENTAL_COMPANY_RENTAL_HISTORY 자동차 대여 기록 정보 테이블
  • HISTORY_ID 자동차 대여 기록 ID
  • CAR_ID 자동차 ID
  • START_DATE 대여 시작일, DATE
  • END_DATE 대여 종료일, DATE
• 문제
  • 2022-10-16
    • BETWEEN A AND B : [A, B]
  • AVAILABILITY 추가
    • 대여 중이면 대여중 / 대여 가능
    • CASE WHEN .. THEN ~ ELSE - END
  • 자동차 ID 기준 내림차순 정렬
• 정답

  SELECT CAR_ID,
  (CASE WHEN CAR_ID IN (SELECT CAR_ID
                      FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                      WHERE '2022-10-16' BETWEEN DATE_FORMAT(START_DATE, '%Y-%m-%d') AND DATE_FORMAT(END_DATE, '%Y-%m-%d'))
  THEN '대여중'
  ELSE '대여 가능'
  END) as AVAILABILITY
  FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
  GROUP BY CAR_ID
  ORDER BY CAR_ID desc

Lv2. 진료과별 총 예약 횟수 출력하기

• APPOINTMENT 종합병원의 진료 예약정보 테이블
  • APNT_YMD 진료예약일시, TIMESTAMP
  • APNT_NO 진료예약번호
  • PT_NO 환자번호
  • MCDP_CD 진료과코드
  • MDDR_ID 의사ID,
  • APNT_CNCL_YN 예약취소여부, NULL
  • APNT_CNCL_YMD예약취소날짜, NULL, DATE
• 문제
  • 2022년 5월 예약
  • 진료과코드 별 환자수 출력
    • 진료과 코드, 5월예약건수 : 칼럼명 지정
  • 진료과 별 예약한 환자수 기준 오름차순 정렬, 진료과 코드 기준 오름차순
• 정답

  SELECT MCDP_CD, COUNT(*) as 5월예약건수
  FROM APPOINTMENT
  WHERE APNT_YMD like '2022-05%'
  GROUP BY MCDP_CD
  ORDER BY 5월예약건수, MCDP_CD

Lv2. 자동차 종류 별 특정 옵션이 포함된 자동차 수 구하기

• 문제
  • 통풍시트, 열선시트, 가죽시트 중 하나 이상의 옵션 포함
  • 자동차 종류 별로 몇 대인지(”CARS”) 출력
  • 자동차 종류 기준 오름차순 정렬
• 정답

  SELECT CAR_TYPE, COUNT(*) as CARS
  FROM CAR_RENTAL_COMPANY_CAR
  WHERE OPTIONS like '%통풍시트%' or OPTIONS like '%열선시트%' or OPTIONS like '%가죽시트%'
  GROUP BY CAR_TYPE
  ORDER BY CAR_TYPE

Lv3. 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기

• 문제
  • 대여시작일 기준 2022-08-01 ~ 2022-10-31
  • 총 대여횟수가 5회 이상
  • 해당 기간 동안의 월별 자동차 ID 별 총 대여횟수 : RECORDS 출력
  • 월 기준 오름차순, 자동차ID 기준 내림차순
  • 특정 월의 총 대여횟수가 0인 경우 제외
• 정답 → 어렵다 …

  SELECT MONTH(START_DATE) as MONTH, CAR_ID, COUNT(*) as RECORDS
  FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
  WHERE DATE_FORMAT(START_DATE, '%Y-%m') BETWEEN '2022-08' AND '2022-10'
  AND CAR_ID IN (SELECT CAR_ID
                FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                WHERE DATE_FORMAT(START_DATE, '%Y-%m') BETWEEN '2022-08' AND '2022-10'
                GROUP BY CAR_ID
                HAVING COUNT(*) >= 5)
  GROUP BY MONTH, CAR_ID
  HAVING RECORDS > 0
  ORDER BY MONTH, CAR_ID desc

Lv2. 성분으로 구분한 아이스크림 총 주문량

• FIRST_HALF 아이스크림 가게의 상반기 주문 정보 테이블
  • SHIPMENT_ID 출하 번호
  • FLAVOR 아이스크림 맛, pk
  • TOTAL_ORDER 총주문량
• ICECREAM_INFO 아이스크림 성분 정보 테이블
  • FLAVOR 아이스크림 맛, pk, 외래키
  • INGREDIENT_TYPE 아이스크림의 성분 타입 (sugar_based, fruit_based)
• 문제
  • 성분타입에 대한 아이스크림의 총주문량 = TOTAL_ORDER
  • 총주문량이 작은 순서대로
• 정답

  SELECT INGREDIENT_TYPE, SUM(TOTAL_ORDER) as TOTAL_ORDER
  FROM FIRST_HALF
  NATURAL JOIN ICECREAM_INFO
  GROUP BY INGREDIENT_TYPE
  ORDER BY TOTAL_ORDER

Lv4. 식품분류별 가장 비싼 식품의 정보 조회하기

• FOOD_PRODUCT 식품 정보 테이블

  Column name	Type	Nullable
  PRODUCT_ID	VARCHAR(10)	FALSE
  PRODUCT_NAME	VARCHAR(50)	FALSE
  PRODUCT_CD	VARCHAR(10)	TRUE
  CATEGORY	VARCHAR(10)	TRUE
  PRICE	NUMBER	TRUE

• 문제
  • 식품분류별로 가격이 제일 비싼 식품
  • 분류, 가격, 이름 출력
  • 분류가 ‘과자’’국’’김치’식용유’인 경우만 출력
  • 식품가격 기준 내림차순 정렬
• 정답

  SELECT CATEGORY, PRICE as MAX_PRICE, PRODUCT_NAME
  FROM FOOD_PRODUCT
  WHERE (CATEGORY, PRICE) IN (SELECT CATEGORY, MAX(PRICE)
                              FROM FOOD_PRODUCT
                              WHERE CATEGORY IN ('과자', '국', '김치', '식용유')
                              GROUP BY CATEGORY)
  ORDER BY MAX_PRICE desc

Lv2. 고양이와 개는 몇 마리 있을까

• 문제
  • 동물 보호소에 들어온 동물 중 고양이와 개가 각각 몇마리인지 출력
  • 고양이보다 개를 먼저

    ORDER BY ANIMAL_TYPE
    // 'C'at > 'D'og

• 정답

  SELECT ANIMAL_TYPE, COUNT(*) as count
  FROM ANIMAL_INS
  GROUP BY ANIMAL_TYPE
  HAVING ANIMAL_TYPE = 'Cat' or ANIMAL_TYPE = 'Dog'
  ORDER BY ANIMAL_TYPE = 'Cat' desc

Lv2. 동명 동물 수 찾기

• 문제
  • 두 번 이상 쓰인 이름
  • 해당 이름이 쓰인 횟수 출력
  • 이름 순 출력
• 정답

  SELECT A.NAME, COUNT(*) as COUNT
  FROM ANIMAL_INS A
  WHERE A.NAME IN (SELECT B.NAME
                FROM ANIMAL_INS B
                WHERE A.ANIMAL_ID != B.ANIMAL_ID)
  GROUP BY NAME
  ORDER BY NAME

  SELECT NAME, COUNT(NAME) as COUNT
  //COUNT(*) 하면 오답
  FROM ANIMAL_INS
  GROUP BY NAME
  HAVING COUNT > 1
  ORDER BY NAME

  • COUNT(*) : NAME이 NUL인 행도 집계에 포함

Lv4. 년, 월, 성별 별 상품 구매 회원 수 구하기

• USER_INFO 의류 쇼핑몰에 가입한 회원 정보 테이블
  • USER_ID 회원 ID
  • GENDER 성별, NULL, 0남자 1여자
  • AGE 나이, NULL
  • JOINED 가입일, DATE
• ONLINE_SALE 테이블
  • ONLINE_SALE_ID 온라인 상품 판매 ID
  • USER_ID 회원 ID
  • PRODUCT_ID 상품 ID
  • SALES_AMOUNT 판매량
  • SALES_DATE 판매일
  • pk : {날짜, 회원ID, 상품ID}
• 문제
  • 년, 월, 성별 별로 상품을 구매한 회원수 집계
  • 년, 월, 성별 기준 오름차순
  • 성별 정보 없는 경우 제외
• 오답

  SELECT YEAR(SALES_DATE) as YEAR, MONTH(SALES_DATE) as MONTH, GENDER, COUNT(USER_ID) as USERS
  FROM USER_INFO
  NATURAL JOIN ONLINE_SALE
  GROUP BY YEAR, MONTH, GENDER
  HAVING GENDER is not null
  ORDER BY YEAR, MONTH, GENDER

  • 구매한 회원수 집계 → distinct !!
• 정답

  SELECT YEAR(SALES_DATE) as YEAR, MONTH(SALES_DATE) as MONTH, GENDER, COUNT(distinct USER_ID) as USERS
  FROM USER_INFO
  NATURAL JOIN ONLINE_SALE
  GROUP BY YEAR, MONTH, GENDER
  HAVING GENDER is not null   //where절이어도 된다!
  ORDER BY YEAR, MONTH, GENDER

Lv2. 입양 시각 구하기(1)

• ANIMAL_OUTS 동물 보호소에서 입양 보낸 동물의 정보 테이블
  • ANIMAL_ID 동물의 아이디
  • ANIMAL_TYPE 생물 종
  • DATETIME 입양일
  • NAME 이름, NULL
  • SEX_UPON_OUTCOME 성별 및 중성화 여부
• 문제
  • 09:00 - 19:59
  • 각 시간대별로 입양이 몇 건 발생하는지 출력
  • 시간대 순으로 정렬
• DATE_FORMAT
  • %Y : 2023년, %y : 23년
  • %m : 09월
  • %d : 21일, 09일
  • %H : 24시간 형식 시간 (00~23), %h : 12시간 형식 시간 (00~12)
  • %i : 분 (00~59)
  • %s : 초 (00~59)
  • %M : 월 첫3글자 (Jan, Feb, …)
  • %W : 요일 (Sunday, Monday, ..)
  • %p : AM, PM
• 정답

  SELECT HOUR(DATETIME) as HOUR, COUNT(*) COUNT
  FROM ANIMAL_OUTS
  WHERE DATE_FORMAT(DATETIME, '%H:%i') BETWEEN '09:00' AND '19:59'
  GROUP BY HOUR
  ORDER BY HOUR

• 배울 점

  //같은 기능
  WHERE HOUR(DATETIME) BETWEEN 0 AND 23
  WHERE DATE_FORMAT(DATETIME, '%H') BETWEEN '0' AND '23'

Lv4. 입양 시각 구하기(2)

• 문제
  • 0시 ~ 23시
  • 각 시간대별로 입양이 몇 건 발생했는지 조회
  • 시간대 순으로 정렬
• 오답

  SELECT HOUR(DATETIME) as HOUR, count(*)
  FROM ANIMAL_OUTS
  WHERE HOUR(DATETIME) BETWEEN 0 AND 23
  GROUP BY HOUR
  ORDER BY HOUR

• 정답

  SET @hour := -1;
  SELECT (@hour := @hour + 1) as HOUR, (SELECT COUNT(*)
                                       FROM ANIMAL_OUTS
                                       WHERE HOUR(DATETIME) = @hour) as COUNT
  FROM ANIMAL_OUTS
  WHERE @hour < 23

  • 쿼리문에서 로컬 변수 활용
  • SET : 프로시저 종료 후에도 유지되는 변수 선언 → 값 누적 가능
    
  • := 대입 연산자 / = 비교 연산자
• 배울 점
  • GROUP BY 절을 사용할 때, COUNT(*)의 결과가 0이 되는 경우는 없다.
  • SET @variable :=

Lv2. 가격대 별 상품 개수 구하기

• PRODUCT 의류 쇼핑몰에서 판매중인 상품들의 정보 테이블
  • PRODUCT_ID 상품 ID
  • PRODUCT_CODE 상품코드, pk, 8자리 (카테고리코드 2자리 + 6자리)
  • PRICE 판매가
• 문제
  • 만원 단위의 가격대 별로
  • 상품 개수 출력
  • PRICE_GROUP, PRODUCTS로 지정
  • 가격대 정보 : 각 구간의 최소금액으로 표시
  • 가격대 기준 오름차순 정렬
• 오답

  SELECT FORMAT(PRICE / 10000, '%F') PRICE_GROUP
  FROM PRODUCT
  ORDER BY PRICE_GROUP

  • 이런 게 되네 → 근데 이건 반올림됨..ㅜㅜ
  • 소수점 버리는 함수 : FLOOR()
  • 소수점 이하 반올림 : ROUND(123.456, 0) → 0
  • 주어진 숫자보다 크거가 같은 가장 작은 정수 반환 : CEIL()
  • 지정된 소수점 자리에서 잘라내기 : TRUNCATE(123.456, 0) → 0
• 정답

  SELECT PRICE_GROUP, COUNT(*) PRODUCTS
  FROM (SELECT *, FLOOR(PRICE/10000)*10000 PRICE_GROUP
      FROM PRODUCT) as P2
  GROUP BY PRICE_GROUP
  ORDER BY PRICE

  • 서브쿼리에 별칭 지정 까먹지 말기 !!