SQL 고득점 Kit (Oracle 풀이) GROUP BY 편

---

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

SELECT MCDP_CD AS "진료과코드", COUNT(*) AS "5월예약건수" 
FROM APPOINTMENT
WHERE TO_CHAR(APNT_YMD,'YYYY-MM') LIKE '2022-05' 
GROUP BY MCDP_CD 
ORDER BY COUNT(*), MCDP_CD 

쉬운 쿼리인데 뭔가 이상하게 헤맸다. ORDER BY 면에서 COUNT 를 직접적으로 사용할 수 있다는것을 배웠다.

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

SELECT I.ingredient_type, SUM(F.TOTAL_ORDER) AS "TOTAL_ORDER" 
FROM FIRST_HALF F LEFT JOIN ICECREAM_INFO I ON F.FLAVOR = I.FLAVOR
GROUP BY I.ingredient_type
ORDER BY SUM(F.TOTAL_ORDER) 

평범하게 그룹 바이를 해주는 문제다. 그런데 이번에는 테이블이 조인 되었을떄 그룹 바이를 넣어줬는데...그냥 일반적으로 GROUP BY 를 사용하듯이 쿼리를 작성하면 생전 처음 보는 에러 메세지가 나왔다. 조인한 테이블을 사용할때는 꼭 Alias를 붙혀서 GROUP BY를 넣는걸 잊지 말자!

입양 시각 구하기(1)

SELECT  hour , count(hour)
FROM
    (
        --SELECT to_number(to_char(datetime , 'hh24')) as hour
        select to_number(to_char(datetime, 'hh24')) as hour 
        FROM animal_outs
    )
where hour between 9 and 19 
GROUP BY hour
ORDER BY hour

이번에 배운거는 BETWEEN 이라는 함수와 to_number 이라는 함수다. 몇시부터 몇시까지의 결과를 만들어야하는데 to_char 함수로 시간을 가지고 왔지만 아쉽게도 09시로 표현이 되기때문에 to_number 로 9시로 바꿨다고 보면은 편하다.

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

-- 코드를 입력하세요
WITH filter as(
    
    SELECT CAR_ID, COUNT(CAR_ID) AS COUNTS
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
    WHERE TO_NUMBER(TO_CHAR(START_DATE,'MM')) BETWEEN 8 AND 10 
    GROUP BY CAR_ID
    ORDER BY CAR_ID DESC 
) 

SELECT TO_NUMBER(TO_CHAR(C.START_DATE,'MM')) AS MONTH
       ,C.CAR_ID
       ,COUNT(*) AS RECORDS 
       --F.COUNTS 
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY C INNER JOIN 
     filter F ON C.CAR_ID = F.CAR_ID 
WHERE F.COUNTS >= 5 
      AND TO_NUMBER(TO_CHAR(START_DATE,'MM')) BETWEEN 8 AND 10 
GROUP BY TO_NUMBER(TO_CHAR(C.START_DATE,'MM')), C.CAR_ID
--ORDER BY TO_NUMBER(TO_CHAR(C.START_DATE,'MM')), F.CAR_ID DESC 
ORDER BY MONTH, CAR_ID DESC 

좀 어려운 문제였던거 같다 .먼저 필터를 사용해서 8~10월 사이에 있는 CAR_ID 를 전부다 합산하고 SELECT 문을 사용하려고 했지만 결국에 MONTH 를 잡기가 힘들어서 JOIN 을 해주었다. 여기서 좀 이해가 안됐었던게 COUNT() 를 한번 더 해줘야 한다는 점이었다. 이미 Filter 테이블을 통해서 데이터를 뽑았는데 이걸 사용안하고 COUNT() 를 더 해준다고? 생각했는데 아래와 같은 문제가 있었다.

결국 INNER JOIN 한 상태의 테이블에서 F.COUNT를 얻어오려면 중복된 값이 계속해서 나온다. 그렇기 때문에 그냥 위에서 COUNT() 를 한번 더 써줘서 아래와 같이 고쳐줄 수 있었다.

다른 사람의 풀이를 보다가 신기한 점도 발견했다.

WITH FILTER AS ( -- 8~10월의 최소 1개 이상인 자동차 아이디만 가져오는 것
    SELECT CAR_ID
       , COUNT(*) AS FILTER
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE EXTRACT(MONTH FROM START_DATE) IN(8,9,10)
    GROUP BY CAR_ID
    ORDER BY CAR_ID DESC
)

SELECT EXTRACT(MONTH FROM START_DATE) AS MONTH
       , C.CAR_ID
       , COUNT(*) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY C INNER JOIN FILTER F
ON  C.CAR_ID = F.CAR_ID 
WHERE   F.FILTER>=5 
        AND EXTRACT(MONTH FROM START_DATE) IN (8,9,10)
GROUP BY EXTRACT(MONTH FROM START_DATE), C.CAR_ID
ORDER BY MONTH ASC, CAR_ID DESC

Month 를 뽑을때 나는 계속 전에 배웠던 풀이대로 TO_CHAR 방식으로 뽑았는데 이렇게 EXTRACT라는 새로운 함수로도 할 수 있었다. 참고하고 좀 사용해봐야겠다. 풀이

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

SELECT CAR_ID
       ,MAX(CASE 
            WHEN '20221016' BETWEEN TO_CHAR(START_DATE,'YYYYMMDD') AND TO_CHAR(END_DATE,'YYYYMMDD')
            THEN '대여중'
            ELSE '대여 가능' 
            END) AS AVAILABILITY 
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
GROUP BY CAR_ID 
ORDER BY CAR_ID DESC

흠..확실히 뭔가 좀 어렵다고 느끼긴 한다. 내 실력이 부족한걸 인정하게 되는 문제였다. 분명히 문제를 읽으면서도 푸는 방법이 머리속에서는 그려졌는데 막상 적으려고 하니 하얗다. 이 문제는 2022-10-16일이 START_DATE 그리고 END_DATE 사이에 있는지를 확인하고 만약 맞다면은 대여중, 아니면은 대여가능이라고 적어야한다. 그리고 MAX는 가장 최신 날짜를 나타내기 위해서 사용되고 있다.

여기서 배운 점은 CASE WHEN (조건) THEN ELSE END 같다. 그리고 날짜는 특정한 범위 사이에서 사용된다고 생각하면은 확실히 BETWEEN 을 적극적으로 사용해주자 풀이 블로그

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

분명히 개쉬운 문제인데 이상하게 뻘짓을 많이 한거같은 기분이다...문제를 읽었을때만 해도 많이 어지러웠는데 다시 풀이를 보니깐 굉장히 쉬워서 당황스러울 정도다. 먼저 INNER JOIN으로 테이블을 합쳐준 다음에 COUNT를 년도, 달, 그리고 GENDER와 같이 묶어줘서 쉽게 풀 수 있었다. 여기서 중요한거는 COUNT를 해줄때 DISTINCT를 걸어준건데, 데이터가 INNER JOIN으로 뻥튀기 되서 중복되기 때문에 이렇게 해주었다. 다음번에는 이런 문제 너무 복잡하게 생각하지 말아보자.

-- 코드를 입력하세요

-- SELECT TO_NUMBER(TO_CHAR(SALES_DATE,'YYYY')) AS YEAR, TO_NUMBER(TO_CHAR(SALES_DATE,'MM')) AS MONTH, GENDER, COUNT(*) AS USERS 
-- FROM(
-- SELECT GENDER, sales_date, COUNT(USER_ID)
-- FROM(
-- SELECT B.USER_ID, A.GENDER, B.SALES_DATE 
-- FROM USER_INFO A INNER JOIN ONLINE_SALE B ON A.USER_ID = B.USER_ID 
-- WHERE A.GENDER IS NOT NULL
-- )
-- GROUP BY GENDER, sales_date)
-- GROUP BY TO_NUMBER(TO_CHAR(SALES_DATE,'YYYY')), TO_NUMBER(TO_CHAR(SALES_DATE,'MM')), GENDER 
-- ORDER BY TO_NUMBER(TO_CHAR(SALES_DATE,'YYYY')) ASC, TO_NUMBER(TO_CHAR(SALES_DATE,'MM')) ASC


select TO_NUMBER(TO_CHAR(B.SALES_DATE,'YYYY')) AS YEAR, TO_NUMBER(TO_CHAR(B.SALES_DATE,'MM')) AS MONTH, A.GENDER, COUNT(DISTINCT B.USER_ID) AS USERS 
from user_info A INNER JOIN online_sale B ON A.USER_ID = B.USER_ID 
where A.gender IS NOT NULL 
GROUP BY TO_NUMBER(TO_CHAR(B.SALES_DATE,'YYYY')), TO_NUMBER(TO_CHAR(B.SALES_DATE,'MM')), A.GENDER 
ORDER BY YEAR ASC, MONTH ASC, A.GENDER ASC

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

이제부터 문제 접근 자체를 조금은 더 간단하게 하기로 했다. 괜히 섭쿼리를 복잡하게 생각하기 보다는 최대하 한 테이블 안에서 얻을 수 있는 정보가 얼마나 많은지를 알아보고 그 결과를 기준으로 답을 찾는것이다. 이 문제에서는 3개의 테이블이 주어졌고 특정 컬럼에서 나온 데이터와 다른 테이블의 데이터를 곱해줘야 했다.

컬럼을 곱하는게 어색해서 뭐지 하고 찾아보니 직접적으로 SUM() 과 같은 함수에다가 컬럼 정보를 넣어서 곱해줄 수 있었다! 이번에 새로 배운건데 JOIN 함수는 두개의 테이블 뿐만 아니라 N개의 테이블에도 함께 적용할 수 있으면서 훨씬 간단한 코드가 나왔다.

-- 코드를 입력하세요
SELECT A.AUTHOR_ID, AUTHOR_NAME, CATEGORY, SUM(SALES * PRICE) AS TOTAL_SALES 
FROM BOOK A INNER JOIN BOOK_SALES B ON A.BOOK_ID = B.BOOK_ID 
INNER JOIN AUTHOR C ON A.AUTHOR_ID = C.AUTHOR_ID
WHERE TO_CHAR(B.SALES_DATE,'YYYY-MM') = '2022-01' 
GROUP BY A.AUTHOR_ID, AUTHOR_NAME, CATEGORY 
ORDER BY A.AUTHOR_ID, CATEGORY DESC