group by 익숙해지기

sql에 익숙해졌다 싶었는데 막상 코테를 해보니 중간중간 검점?도 볼 수 없으니 당황하고 안절부절 그대로 몰락하고 말았다
안일했던 연습탓이다..

자체적인 응용 문제를 만들어 보기로 했다.

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

WITH F AS (
    SELECT CATEGORY, MAX(PRICE) as PRICE    
    FROM FOOD_PRODUCT
    WHERE CATEGORY IN ('과자', '국', '김치', '식용유')
    group by category
)

SELECT  P.CATEGORY	
        , F.PRICE AS MAX_PRICE	
        , P.PRODUCT_NAME
FROM    FOOD_PRODUCT AS P
JOIN    F
ON      F.CATEGORY = P.CATEGORY
        && F.PRICE = P.PRICE
ORDER BY 2 DESC;

• natural join을 써서 on으로 명시하지 않아도 되긴 함.

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

SELECT  B.AUTHOR_ID,	A.AUTHOR_NAME,	B.CATEGORY	
        , SUM (B.PRICE * S.SALES) AS TOTAL_SALES
FROM    BOOK AS B
NATURAL JOIN    AUTHOR AS A
NATURAL JOIN    BOOK_SALES AS S
WHERE   S.SALES_DATE LIKE '2022-01%'
GROUP BY 1, 3
ORDER BY 1, 3 DESC;

AUTHOR_ID	AUTHOR_NAME	CATEGORY	TOTAL_SALES
	1			홍길동		인문		561000
	1			홍길동		경제		1107000
	2			김영호		소설		450000
	3			김수진		생활		817000
	3			김수진		기술		1523000

2022년 월별 인기 카테고리

STEP1. 월별 카테고리별 카운트

SELECT  MONTH(S.SALES_DATE) AS MONTH
        , CATEGORY 
        ,COUNT(BOOK_ID) AS cnt
FROM    BOOK AS B
NATURAL JOIN    AUTHOR AS A
NATURAL JOIN    BOOK_SALES AS S
WHERE   S.SALES_DATE LIKE '2022%'
GROUP BY 1,2
;

MONTH	CATEGORY	cnt
  1		경제		22
  1		기술		26
  1		생활		12
  1		소설		9
  1		인문		10
  2		경제		21
  2		기술		19
  2		생활		12
  2		소설		13
  2		인문		11
  3		경제		26
  3		기술		28
  3		생활		13
  3		소설		10
  3		인문		14

step2: 월별 최고 매출 수량 찾기

select month, max(cnt)
from A
group by 1

month	category	max(cnt)
	1		경제		26
    2		경제		21
    3		경제		28        

• ‼️ 월별 최고 매출 수량은 잘 찾았지만 그 수량이 기록되어 있는 row의 정보를 가져오지는 못하였다 따라서..

final: join해주기

WITH A AS (
SELECT  MONTH(S.SALES_DATE) AS MONTH
        , CATEGORY 
        , COUNT(BOOK_ID) AS cnt
FROM    BOOK AS B
NATURAL JOIN    AUTHOR AS A
NATURAL JOIN    BOOK_SALES AS S
WHERE   S.SALES_DATE LIKE '2022%'
GROUP BY 1, 2
ORDER BY 1, 2)

SELECT  *
FROM    A
JOIN (
    select month, max(CNT) AS CNT
    from A
    group by month
) AS B USING (MONTH, CNT)
;

MONTH	cnt	CATEGORY
    1	26	 기술
    2	21	 경제
    3	28	 기술

!완성!

최소 판매량의 카테고리는?

• MAX 대신 min 으로 바꿔 주면 된다

  MONTH	cnt	CATEGORY
    1		9	소설
    2		11	인문
    3		10	소설

최대, 최소를 같은 표에 보여주려면?

WITH 

A AS (SELECT  MONTH(S.SALES_DATE) AS MONTH
        , CATEGORY 
        , COUNT(BOOK_ID) AS cnt
FROM    BOOK AS B
NATURAL JOIN    AUTHOR AS A
NATURAL JOIN    BOOK_SALES AS S
WHERE   S.SALES_DATE LIKE '2022%'
GROUP BY 1, 2
ORDER BY 1, 2),


XAM AS (
    SELECT  *
    FROM    A
    JOIN (
        SELECT MONTH, MAX(CNT) AS CNT
        from A
        group by 1
    ) AS B USING (MONTH,CNT)
),

NIM AS (
    SELECT  *
    FROM    A
    JOIN (
        SELECT MONTH, MIN(CNT) AS CNT
        from A
        group by 1
    ) AS B USING (MONTH,CNT)
)

SELECT  *
FROM    XAM
JOIN    NIM
USING   (MONTH)
;

MONTH	cnt	CATEGORY	cnt	CATEGORY
  1		26	기술			9	소설
  2		21	경제			11	인문
  3		28	기술			10	소설