SQL Review(2nd. GROUP BY, HAVING )

일단 지난주 강의 문제 풀이로 시작

 

▼ SQL 강의 연습문제 풀이(4-1 ~ 4-7)

 

 

#2 회원 테이블에서 여성 유저의 평균연령 조회
select avg(age)
from `thelook_ecommerce.users`
where gender = 'F';

 

#3 회원(users) 테이블에서 국가별 가입자수를 조회하세요
select country,
count(id)
from `thelook_ecommerce.users`
group by country;

 

#4 회원(users) 테이블에서 남성 유저의 국가별 가입자 수를 조회하세요.
select country,
count(id)
from `thelook_ecommerce.users`
where gender ='M'
group by country;

 

#5 회원(users) 테이블에서 가입기간(created_at)이 2020년도 1월인 유저의 
#  국가별 가입자 수 (country_user_count)를 조회하세요.
select country,
count(id) as country_user_count
from `thelook_ecommerce.users`
where created_at between '2020-01-01' and '2020-01-31'
group by country;
# like 함수 사용하고 싶으면 string(created_at) like '2020-01%'로 해주면 된다
# 참고로 strin() 함수는 Big Query 밖에 없다!

 

select country,
gender,
count(id) as country_gender_user_count
from `thelook_ecommerce.users`
where created_at between '2020-01-01' and '2020-01-31'
group by country, gender;

 

select user_id,
num_of_item
from `thelook_ecommerce.orders`
where status = 'returned' and string(created_at) like('2022%')

 이거 지난주에도 틀렸었던 문제다

그냥 이렇게 해봤는데... 아래와 같은 오류가 뜬다

내용 보니까 그룹화하라는거 같아서 user_id 기준으로 그룹화해줬다.

 

#7 주문정보(orders) 테이블에서 2022년도의 주문 상태가 환불(Returned)인 
# 유저 아이디(user_id), 총 주문 아이템(num_of_item)의 합계를 조회하세요.
select user_id,
sum(num_of_item)
from `thelook_ecommerce.orders`
where status = 'returned' and string(created_at) like('2022%')
group by user_id

아마 sum,max,min,avg 이런 애들이 붙는 컬럼 앞에 오려면 그룹화가 되어있어야 하나보다 

 

---

 

HAVING 

 

그룹화된 데이터다 그룹화된...where이랑 좀 헷갈리는데 다른 조건 말고 그룹화되어있는 컬럼에 조건이 붙을 때를

잘 봐야할 것 같다 

 

HAVING 예시

#1 유저를 국가별로 그룹화하고 국가 별 유저수가 4000인 이상의 유저수 조회

select 
  country, 
  count(id) as user_count
from `thelook_ecommerce.users`
group by country #국가로 그룹화 하고
having count(id) >= 4000; #국가에 대한 조건(유저수가 4000)이므로 HAVING

 

#2 30대 유저를 나이별로 카운팅

select 
  age, 
  count(age) as thirites
from `thelook_ecommerce.users`
group by age #마찬가지로 나이로 그룹화하고
having age<40 and age>=30 # 나이에 대한 조건( 30대 : 30>=, 40< )
order by age;

 

ORDER BY

 

ASC (오름차순) 이 Default 값 

 

ORDER BY 예시

 

#1 나이순으로 정렬하여 유저정보 조회
select * 
from `thelook_ecommerce.users` 
order by age asc;

#2 나이 내림차순으로 정렬하여 유저정보 조회
select * 
from `thelook_ecommerce.users` 
order by age desc; #별도의 내림차순 없이 순이라고하면 보통 ASC : 오름차순

#일차정렬로 나이 내림차순, 이차정렬로 id 오름차순으로 조회
select * 
from `thelook_ecommerce.users` 
order by age desc, id asc; #일차정렬이라는건 처음 정렬조건

나이가 제일 어린 3명의 유저정보를 조회
select * 
from `thelook_ecommerce.users` 
order by age asc
limit 3;

 

함수 작성 순서

1. from
2. where
3. group by
4. having
5. select
6. order by
7. limit

 

HAVING , ORDER BY 문제

# 국가별 20세 이하 유저수가 500명 이상인 유저수 국가 TOP 5를 조회
select country,
coiunt(id) as user_count
from `thelook_ecommerce.users`
group by country
having user_count >= 500 # select 구문에서 as로 지정해준 이름으로 조건 잡아줘도 됨
order by user_count desc
limit 5;

 

프로그래머스 문제(틀렸거나 잘 안풀렸던 문제만)

 

프로그래머스 문제 5-14 (level 2) (LEFT)

코딩테스트 연습 - 카테고리 별 상품 개수 구하기 | 프로그래머스 스쿨 (programmers.co.kr)

select LEFT(PRODUCT_CODE,2) AS CATEGORY,
count(PRODUCT_ID)
from PRODUCT
group by CATEGORY
order by CATEGORY

 

LEFT 라는 기능을 이용해야해서 좀 어려웠다. LEFT( 컬럼명, 데이터 마스킹 하고싶은 자릿수)

 

 

프로그래머스 문제 5-15 (level 2) (TRUNCATE,LEFT)

TRUNCATE(숫자,버릴 자릿수) - 숫자를 버릴 자릿수 아래로 버림

코딩테스트 연습 - 가격대 별 상품 개수 구하기 | 프로그래머스 스쿨 (programmers.co.kr)

select LEFT(PRICE/10000,1)*10000 as PRICE_GROUP,
count(PRODUCT_ID) as PRODUCTS
from PRODUCT
group by PRICE_GROUP
order by PRICE_GROUP

 

강의 같이 듣는 동기분들이 푸신 다른 방법도 참고

#1 PRICE - PRICE % 10000

SELECT
PRICE - PRICE % 10000 AS PRICE_GROUP, COUNT(*)
FROM PRODUCT
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP;

 

#2  TRUNCATE(PRICE, -4)

SELECT 
TRUNCATE(PRICE,-4) AS 'PRICE_GROUP', COUNT(*) AS 'PRODUCTS'
FROM PRODUCT
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP

 

프로그래머스 문제 5-16 (level 2)

코딩테스트 연습 - 가격대 별 상품 개수 구하기 | 프로그래머스 스쿨 (programmers.co.kr)

select NAME,
count(ANIMAL_ID) as COUNT
FROM ANIMAL_INS
GROUP BY NAME
HAVING count(NAME) >= 2
order by NAME asc

 

프로그래머스 문제 5-17 (level 2)

코딩테스트 연습 - 재구매가 일어난 상품과 회원 리스트 구하기 | 프로그래머스 스쿨 (programmers.co.kr)

select USER_ID,
PRODUCT_ID
from ONLINE_SALE
group by USER_ID, PRODUCT_ID
HAVING count(PRODUCT_ID) >= 2
order by USER_ID asc, #1차정렬
PRODUCT_ID desc

 

 + 동기 분들 자체 제작 문제

# events 테이블에서 로그인한 사용자의 도시 별(city), 브라우저 별(browser), 트래픽 소스(traffic_source)와 그 개수를 조회하는 SQL문을 작성해주세요. 도시와 브라우저는 이름 순, 트래픽 소스의 개수를 내림차순으로 정렬해주세요.

user_id is not null 을 넣을 생각은 못해봤다...대단

 

# products 테이블에서 deparment(Men, Women) 별로 평균 이익률이 높은 brand top 5를 조회하는 SQL문을 작성해주세요. # 이때 결과는 남성 브랜드가 먼저 나타나도록, 평균 이익률이 높은 순서대로 해주세요.

우선 여기까진 풀었는데..문제 내신분의 의도대로 하려면 나중 내용을 배워야 한다고 한다 그래서 우선 가져옴

---

+ REVIEW 하면서 얻은 TIP

 

COUNT

 

▶ COUNT(별표) : NULL값 포함 O

▶ COUNT(컬럼명) : NULL값 포함 X

 

GROUP BY

 

▶ SELECT 문에 있는 모든 열은 집계 함수가 되거나 GROUP BY 절에 나타나야 합니다.

▶ GROUP BY 절을 사용하는데 만약 SELECT 문에 집계 함수를 사용하지 않거나 GROUP BY 절에 언급되지 않은 열이 존재한다면 오류가 발생합니다.