![[SQL문제다시풀기] 상품을 구매한 회원 비율 구하기 - extract](https://image.inblog.dev?url=https%3A%2F%2Fsource.inblog.dev%2Ffeatured_image%2F2025-02-16T06%3A12%3A04.168Z-79897e3f-0a48-46db-aff9-7c897488e830&w=3840&q=75)
문제
MYSQL
내가 작성한 오답
: 같은 아이디가 여러번 구매하면 여러번 카운트 된다.
select year(sales_date) year, month(sales_date) month,
count(u.user_id) purchased_users,
round(count(s.user_id) / (select count(user_id) from user_info where year(joined) = '2021'), 1) PUCHASED_RATIO
from user_info u
join online_sale s on u.user_id = s.user_id
where year(joined) = '2021'
group by 1, 2
order by 1 asc, 2 asc내가 작성한 정답
: distinct 추가
select year(sales_date) year, month(sales_date) month,
count(distinct u.user_id) purchased_users,
round(count(distinct s.user_id) / (select count(user_id) from user_info where year(joined) = '2021'), 1) PUCHASED_RATIO
from user_info u
join online_sale s on u.user_id = s.user_id
where year(joined) = '2021'
group by 1, 2
order by 1 asc, 2 ascORACLE
내가 작성한 정답
select to_char(s.sales_date,'yyyy') year,
extract(month from s.sales_date) month,
count(distinct s.user_id) purchase_users,
round(count(distinct s.user_id) / (select count(user_id) from user_info where to_char(joined,'yyyy')=2021),1) purchased_ratio
from user_info u
join online_sale s on u.user_id = s.user_id
where to_char(joined,'yyyy') = 2021
group by to_char(s.sales_date,'yyyy'), extract(month from s.sales_date)
order by year asc, month ascEXTRACT(field FROM source) - 대부분의 db에서 지원
field: 추출하려는 날짜/시간의 구성 요소로 YEAR, MONTH, DAY, HOUR, MINUTE, SECOND 등을 사용
source: 날짜 또는 시간 데이터 타입의 값
Share article