<프로그래머스 67> 년, 월, 성별 별 상품 구매 회원 수 구하기(Lv.4)

 

프로그래머스에서 제공하는 SQL문제 매일 풀기 챌린지

---

 

문제 설명

다음은 어느 의류 쇼핑몰에 가입한 회원 정보를 담은 USER_INFO 테이블과 온라인 상품 판매 정보를 담은 ONLINE_SALE 테이블 입니다.USER_INFO 테이블은 아래와 같은 구조로 되어있으며 USER_ID, GENDER, AGE, JOINED는 각각 회원 ID, 성별, 나이, 가입일을 나타냅니다.
GENDER 컬럼은 비어있거나 0 또는 1의 값을 가지며 0인 경우 남자를, 1인 경우는 여자를 나타냅니다

Column name	Type	Nullable
USER_ID	INTEGER	FALSE
GENDER	TINYINT(1)	TRUE
AGE	INTEGER	TRUE
JOINED	DATE	FALSE

ONLINE_SALE 테이블은 아래와 같은 구조로 되어있으며, ONLINE_SALE_ID, USER_ID, PRODUCT_ID, SALES_AMOUNT, SALES_DATE는 각각 온라인 상품 판매 ID, 회원 ID, 상품 ID, 판매량, 판매일을 나타냅니다.
동일한 날짜, 회원 ID, 상품 ID 조합에 대해서는 하나의 판매 데이터만 존재합니다.

Column name	Type	Nullable
ONLINE_SALE_ID	INTEGER	FALSE
USER_ID	INTEGER	FALSE
PRODUCT_ID	INTEGER	FALSE
SALES_AMOUNT	INTEGER	FALSE
SALES_DATE	DATE	FALSE

---

문제

USER_INFO 테이블과 ONLINE_SALE 테이블에서 년, 월, 성별 별로 상품을 구매한 회원수를 집계하는 SQL문을 작성해주세요. 결과는 년, 월, 성별을 기준으로 오름차순 정렬해주세요. 이때, 성별 정보가 없는 경우 결과에서 제외해주세요

---

예시

예를 들어 USER_INFO 테이블이 다음과 같고

[ USER_INFO ]

USER_ID	GENDER	AGE	JOINED
1	1	26	2021-06-01
2	NULL	NULL	2021-06-25
3	0	NULL	2021-06-30
4	0	31	2021-07-03
5	1	25	2021-07-09
6	1	33	2021-07-14

 

ONLINE_SALE 테이블이 다음과 같다면

 

[ ONLINE_SALE ]

ONLINE_SALE_ID	USER_ID	PRODUCT_ID	SALES_AMOUNT	SALES_DATE
1	1	54	1	2022-01-01
2	1	3	2	2022-01-25
3	4	34	1	2022-01-30
4	6	253	3	2022-02-03
5	2	31	2	2022-02-09
6	5	35	1	2022-02-14
7	5	57	1	2022-02-18

 

2022년 1월에 상품을 구매한 회원은 USER_ID 가 1(GENDER=1), 4(GENDER=0)인 회원들이고,
2022년 2월에 상품을 구매한 회원은 USER_ID 가 2(GENDER=NULL), 5(GENDER=1), 6(GENDER=1)인 회원들 이므로,

 

년, 월, 성별 별로 상품을 구매한 회원수를 집계하고, 년, 월, 성별을 기준으로 오름차순 정렬하면 다음과 같은 결과가 나와야 합니다.

YEAR	MONTH	GENDER	USERS
2022	1	0	1
2022	1	1	1
2022	2	1	2

 

---

 

♥ 오라클 정답

select extract(year from o.sales_date) year, extract(month from o.sales_date) month, u.gender, count(distinct o.user_id) users
    from online_sale o, user_info u
    where o.user_id = u.user_id
    and u.gender is not null
    group by extract(year from o.sales_date), extract(month from o.sales_date), u.gender
    order by year asc, month asc, gender asc;

 

♥   MySQL정답

select year(o.sales_date) year, month(o.sales_date) month, u.gender, count(distinct o.user_id) users
    from online_sale o, user_info u
    where o.user_id = u.user_id
    and u.gender is not null
    group by year(o.sales_date), month(o.sales_date), u.gender
    order by year asc, month asc, gender asc;

 

---

 

■ EXTRACT() 함수 - Oracle

EXTRACT함수는 날짜형 data type을 가진 특정 데이터로부터

원하는 날짜 정보만 추출하여, 새로운 컬럼의 형태로 추출해주는 함수이다.

select EXTRACT( [날짜요소] FROM [특정컬럼B] ) as [별칭]
    from [테이블명A];

 

특정 테이블 A에 있는 컬럼 중 날짜 변수가 담긴 특정컬럼B로부터 

원하는 날짜요소를 추출하여 새로운 컬럼으로 추출해 주는 쿼리 문이다.

 

- 날짜요소 : YEAR, MONTH, DAY, HOUR, MINUTE, SECOND

(자세한 예시와 실습은 추후 게시물로 업로드할 예정)

하지만 주의해야 할 점은, 
날짜형 데이터 타입 중 DATETIME 타입은
EXTRACT를 활용한 HOUR, MINUTE, SECOND 추출은 할 수 없다는 것이다.

Q. 그럼 어떻게 추출해야 하는가?
DATETIME의 형식을 TIMESTAMP로 일시적으로 변경해 주면 된다. 
그럴 때 사용할 수 있는 함수는 CAST() 함수이다.

 

관련문제는 아래 링크를 통해 풀어볼 수 있다.

<프로그래머스 37> 입양 시각 구하기1(Lv.2)

 

■ YEAR(), MONTH() - MySQL

 

YEAR( [컬럼명] ), MONTH( [컬럼명] )

날짜형식의 DATA TYPE인 컬럼에서 년, 월을 뽑을 때 사용하는 함수이다.

 

 

■ EQUI JOIN

조인은 여러 개의 테이블의 컬럼들을 활용하여 한 번에 원하는 것을 출력하고자 할 때 사용하는 문법이다.
 
그 중 EQUI JOIN 이란, 
조인하려는 테이블 사이에 공통된 컬럼이 있을 경우 (컬럼명은 달라도 되고, 데이터가 공통될 때)
EQUAL(=) 조인하는 조인 문법이다.
 
ORACLE과 MySQL의 차이점은
MySQL에서는 IN LINE VIEW에 별칭을 지정해줘야 오류가 발생하지 않는 것이다.
ORACLE은 별칭이 없어도 에러가 발생하지 않았다.

---

 

정답 해설

위의 내용을 기반으로 해설해보면,

 

# 1. SELECT절

년, 월, 성별 별 상품 구매 회원수를 출력해야하기 때문에

online_sale 테이블의 sales_date 컬럼에서 년, 월을 추출하여 컬럼을 생성해준다.

이때 오라클은 EXTRAT() 함수를 사용해서 추출해주고,

MySQL은 YEAR(), MONTH() 함수를 사용하여 YEAR, MONTH 컬럼을 생성한다.

 

회원수(USERS)컬럼은 ONLINE_SALE 테이블의 USER_ID를 사용하여

COUNT(USER_ID)를 할 경우 중복된 USER_ID가 포함되기 때문에

DISTNCT를 앞에 작성하여 중복된 데이터는 제거해준다. 

 

 

# 2. FROM절

조인해줄 테이블들을 입력해주고,

컬럼명 앞에 편하게 명시해주기 위해서 별칭을 넣어준다. 

 

 

# 3. WHERE절

공통 컬럼인 USER_ID를 사용하여 EQUI JOIN으로 연결조건을 생성하고,

성별의 데이터가 NULL인 값은 출력되지 않도록 AND로 연결하여 일반조건을 생성해준다.

NULL은 등호를 사용하지 않고 IS NOT NULL을 사용해야한다.

 

 

# 4. GROUP BY 절

년, 월, 성별 별 상품 구매 회원 수를 구해야하기 때문에

년, 월, 성별을 GROUP으로 묶어주어야 한다. 

따라서 아래와 같이 3개의 컬럼 모두 넣어준다.

 

group by year(o.sales_date), month(o.sales_date), u.gender

 

# 5. ORDER BY 절

년, 월, 성별을 기준으로 오름차순 정렬하라는 의미는

년을 기준으로 정렬하되, 년이 같다면 월을 기준으로 정렬하고

월도 같다면 성별을 기준으로 정렬하라는 의미이다. 

 

 

 

 

 

출처 : 프로그래머스 코딩 테스트 연습, https://school.programmers.co.kr/learn/challenges