[Programmers] GROUP BY

* Alias

group by 절, having 절, order by 절에도 alias 사용 가능! (where 절은 인식 X)

" select 절에서 별칭 선언 후, group by 절 이후부터 별칭(alias) 인식 가능 "

 

- 가격대 별 상품 개수 구하기

 

SELECT (
    case
    when price < 10000 then 0
    else truncate(price, -4)
    end
    ) as PRICE_GROUP
    , count(product_id) as PRODUCTS
FROM product
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP;

 

* 핵심 : 가격대를 어떻게 처리할 것인지? truncate(컬럼, -4)

ex) price = 13000 -> 숫자 3부터 4자리 잘라내기 즉, 10000이 됨.

 

 

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

 

SELECT i.ingredient_type as ingredient_type
    , sum(total_order) as total_order
FROM first_half as f
    left join icecream_info as i
        on f.flavor = i.flavor
GROUP BY ingredient_type
ORDER BY total_order;

 

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

 

SELECT mcdp_cd as '진료과코드'
    , count(apnt_ymd) as '5월예약건수'
FROM appointment
WHERE date_format(apnt_ymd, '%Y-%m') = '2022-05'
GROUP BY mcdp_cd
ORDER BY 2, 1;

 

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

 

SELECT car_type, count(car_id) as cars
FROM car_rental_company_car
WHERE regexp_like(options, '통풍시트')
 or regexp_like(options, '열선시트')
 or regexp_like(options, '가죽시트')
GROUP BY car_type
ORDER BY car_type;

 

MySQL LIKE REGEXP(LIKE IN) 사용방법

 

 

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

 

* 핵심 : 같은 연도, 같은 월에 여러 user_id 존재한다는 것 파악 -> distinct 사용 필요

 

* 특정 정보 테이블과 이에 대한 판매(주문) 테이블을 조인 할 경우, 판매 테이블을 기준으로 조인하기 

그러면, 판매가 된 혹은 주문이 있는 데이터만 존재하게 됨. 

 

-- 온라인 주문 테이블 기준으로 조인 > 주문이 있는 회원목록 확인 가능
SELECT year(sales_date) as year
    , month(sales_date) as month
    , gender
    , count(distinct o.user_id) as users
FROM online_sale as o
    left join user_info as u
        on o.user_id = u.user_id
WHERE gender is not null
GROUP BY year, month, gender
ORDER BY year, month, gender;

-- 유저 정보 테이블 기준으로 조인 후, where 절에서 주문이 없는 날짜 제거
SELECT year(sales_date) as year
    , month(sales_date) as month
    , gender
    , count(distinct o.user_id) as users
FROM user_info as u
    left join online_sale as o
        on u.user_id = o.user_id
WHERE sales_date is not null and gender is not null
GROUP BY year, month, gender
ORDER BY year, month, gender;

 

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

 

* 주의 : date_format 함수에서 날짜 형식이 y가 아닌 Y라는 것 

 

SELECT category, sum(sales) as total_sales
FROM book_sales as sale -- 주문이 있는 책을 확인하기 위함
    left join book as info
        on sale.book_id = info.book_id
WHERE date_format(sales_date, '%Y-%m') = '2022-01'
GROUP BY category
ORDER BY category;

 

 

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

 

SELECT food_type, rest_id, rest_name, max(favorites) as favorites
FROM rest_info
GROUP BY food_type
ORDER BY food_type desc;

 

오답인 이유

: food_type으로 group by 했기에, food_type과 max(favorites)에 대한 값은 정확하게 나옴.

하지만 rest_id와 rest_name은 여러 개의 컬럼이 나올 수 있음. (그도 그럴것이 카테고리 별로 식당은 여러 개가 있을테니까.)

결과값이 정상적으로 나오는 건 해당 그룹의 첫 번째 행에 있는 값이 추출되었기 때문이다.

 

SELECT a.food_type, a.rest_id, a.rest_name, a.favorites
FROM rest_info as a join 
    (SELECT food_type, max(favorites) as favorites
    FROM rest_info
    GROUP BY food_type
    ) as b -- 각 카테고리별 최대 favorites 
on a.food_type = b.food_type and a.favorites = b.favorites -- 테이블 B에 해당하는 컬럼(최대 즐겨찾기수)만 남기기
ORDER BY food_type desc;

 

 

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

 

SELECT month(start_date) as month, car_id, count(*) as records
FROM car_rental_company_rental_history
WHERE start_date BETWEEN '2022-08-01' AND '2022-10-31'
and car_id in (
    SELECT car_id
    FROM car_rental_company_rental_history
    WHERE start_date BETWEEN '2022-08-01' AND '2022-10-31'
    GROUP BY car_id
    HAVING count(*) >= 5)
GROUP BY month, car_id
ORDER BY month, car_id desc;

 

익숙해지기 - 날짜컬럼 between 날짜1 and 날짜 2

 

[MySQL] 중복값 갯수와 각각의 값 구하기 - LYNMP

 

Select 절에서 서브쿼리로 count값 구하기(MS-SQL)

[SQL] 집계와 서브쿼리 - COUNT , DISTINCT, SUM, AVG, MIN, MAX, GROUP BY, HAVING, 서브쿼리

 

* Group by

- group by 를 지정해 그룹화하면 distinct와 같이 중복을 제거하는 효과가 있다.

- group by 를 지정한 경우, 집계함수와 함께 사용하지 않으면 별 의미가 없다.

 

* SQL 내부 처리 순서

from -> where -> group by -> having -> select -> order by 

위와 같이 where 절은 group by 절보다 먼저 실행되므로, 그룹화가 필요한 집계함수는 where 절에서 사용할 수 없다. 

(select 글에서는 결과 위주로 정리해서 과정까지 다시 한번 정리)

 

따라서 집계함수를 사용한 결과에서 조건에 맞는 값을 걸러내기 위해서 having 절을 사용한다.

where 절로 검색 > group by 로 그룹화 > having 절로 조건식 반영

 

 

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

 

-- 오답 : product_name은 group by 절에 없고 집계함수도 없이 select 절에 사용됨.
SELECT category, max(price) as max_price, product_name
FROM food_product
WHERE category in ('과자', '국', '김치', '식용유')
GROUP BY category
ORDER BY price desc;

 

위 쿼리 작동 시 출력 결과

 

in 조건만 건 채 테이블 조회를 해보면 정답으로 인정되지 않은 이유가 보인다.

category가 국인 것 중 max(price)인 제품명은 '맛있는김치찌개'인데, 위에 작성한 쿼리 결과에선 '맛있는미역국'가 나왔다.

카테고리 별 최고 금액은 잘 출력되었지만 이에 상응하는 제품명이 올바르지 않다. (제품명은 이에 영향을 받지 않아 변화 없이 첫 행 값이 그대로 출력된 것)

 

 

group by 문제들을 풀면서 느낀 건, group by 절과 함께 서브쿼리가 자주 사용된다는 것이다.

해당 문제에서도 각 카테고리별로 max price를 가져오는 서브쿼리를 작성해야 제품명 또한 올바르게 출력된다.

SELECT category, price as max_price, product_name
FROM food_product
WHERE category in ('과자', '국', '김치', '식용유') -- 카테고리 조건 만족
and price in ( -- 각 카테고리별 max(price) 조건 만족
    select max(price)
    from food_product
    group by category)
ORDER BY max_price desc;

 

 

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

 

SELECT a.author_id, a.author_name, b.category
    , sum(b.price * s.sales) as total_sales
FROM book as b -- 저자, 매출 정보가 모두 있는 book 테이블을 기준으로 조인
    left join author as a
        on b.author_id = a.author_id
    left join book_sales as s
        on b.book_id = s.book_id
WHERE date_format(sales_date, '%Y-%m') = '2022-01' -- like '2022-01%'
GROUP BY a.author_id, b.category
ORDER BY a.author_id, b.category desc;

 

(식품분류별 ~ 문제와는 다르게) author_name은 author_id, category 별로 하나의 이름만 존재하기 때문에 별 문제 없는 듯하다.

 

 

 - 자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기 (그룹별 최신값을 불러와야 하는 문제)

 

SELECT CAR_ID
    , MAX(IF("2022-10-16" BETWEEN start_date AND end_date, "대여중", "대여 가능")) AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY 1
ORDER BY car_id desc;

 

 

car_id = 29인 경우, 가장 최신 날짜에 해당하는 네 번째 컬럼(history_id = 569)이 결과로서 추출되어야 한다.

'MAX(IF("2022-10-16" BETWEEN start_date AND end_date, "대여중", "대여 가능")' 이 부분을 적용시켜보면,

위에서 세 개의 컬럼은 '대여 가능'에 해당하고, 네번째 컬럼은 '대여중'에 해당한다.

따라서, car_id = 29는 '대여중'이라는 값이 출력되어야 한다는 것이다.

 

MAX 함수의 특징

- null을 최대/최소값에 포함시키지 않는다

- MAX 함수는 최신값을 구할 때 사용된다. (MIN 함수는 반대로)

 

b1ix | [MYSQL] group by로 뽑아온 값중에 가장큰 값(max)의 상태값을 가져오기 2012-07-21