[졸업 작품] 연령 별 제품 추천 SQL

구현내용

회원 로그인시, 구매자의 연령대를 구하고, 연령대별  잘 팔리는 제품 3가지 추천

 

구현 순서

1. 멤버별 연령대 구하기
2. 연령대별 거래수 구하기
3. 최종 연령대별 상위 3건 갖고오기

테이블 (요약)

member

id	식별자
created_date	등록날짜
modified_date	수정날짜
age	나이
birthday	생년월일
email	이메일
gender	성별
nick_name	닉네임
phone_number	전화번호

Product

id	bigint(20)	NO	PRI		auto_increment
created_date	datetime	YES
modified_date	datetime	YES
content	text	YES
flag_courier_fee	tinyint(1)	YES		0
hash_tag	varchar(255)	YES
hit	int(11)	NO
price	int(11)	NO
product_state	varchar(15)	YES		SELL
title	varchar(255)	YES
trade_place	varchar(255)	YES
category_id	bigint(20)	YES	MUL
owner_id	bigint(20)	YES	MUL

product_img

id	bigint(20)	NO	PRI		auto_increment
created_date	datetime	YES
modified_date	datetime	YES
original_img_src	varchar(255)	YES
original_name	varchar(255)	YES
thumb_img_src	varchar(255)	YES
product_id	bigint(20)	YES	MUL

Review

id	bigint(20)	NO	PRI		auto_increment
created_date	datetime	YES
modified_date	datetime	YES
content	text	YES
grade	double	NO
trade_state	varchar(255)	YES
owner_id	bigint(20)	YES	MUL
product_id	bigint(20)	YES	MUL
trade_completed_date	date	YES

 

---

구현

요청자의 연령대 구하기

먼저 요청자의 연령대를 구해보자.

SELECT 
        CASE
                WHEN m.age >= 10 AND m.age < 20 THEN '10대'
                WHEN m.age >= 20 AND m.age < 30 THEN '20대'
                WHEN m.age >= 30 AND m.age < 40 THEN '30대'
                WHEN m.age >= 40 AND m.age < 50 THEN '40대'
                WHEN m.age >= 60 AND m.age < 70 THEN '50대'
            END AS age_group
    FROM
        member m
    WHERE
        id = 10

편의를 위하여 id 값을 지정하였지만, 실제 java 코드에서는 동적으로 할당할 것이다.

위의 결과로는 아래와 같이 나올 것이다.

20대

 

연령대 별 거래 구하기

연령대 별 거래 같은 경우, 많이 잘못된 설계이지만 Review테이블에서 거래 상태를 확인할 수 있다.

1

2022-05-22 16:07:54

2022-09-06 08:56:04

맛있었다

2

COMPLETED

1

31

2022-09-06

위와 같이 COMPLETED 일시, 거래가 완료 된것이다. 
거래 완료 된 컬럼을 뽑아보면

SELECT * FROM review
WHERE trade_state='COMPLETED';

1	2022-05-22 16:07:54	2022-09-06 08:56:04	맛있었다	2	COMPLETED	1	31	2022-09-06
2	2022-05-22 16:47:11	2022-06-22 17:28:50		0	COMPLETED	1	30	2022-06-22
3	2022-05-24 06:13:48	2022-05-24 06:14:29	괜찮습니다	2.5	COMPLETED	1	36	2022-05-29

  같이 나오게 되고

형광펜이 되어있는 1(member.id)를 통해 구매자의 연령을 뽑아올 수 있다.

 

SELECT 
    r.*, m.age
FROM
    review r
        LEFT JOIN
    member m ON r.owner_id = m.id
WHERE
    trade_state = 'COMPLETED';

1	2022-05-22 16:07:54	2022-09-06 08:56:04	맛있었다	2	COMPLETED	1	31	2022-09-06	24
2	2022-05-22 16:47:11	2022-06-22 17:28:50		0	COMPLETED	1	30	2022-06-22	24
3	2022-05-24 06:13:48	2022-05-24 06:14:29	괜찮습니다	2.5	COMPLETED	1	36	2022-05-29	24

이제 저 나이를 통해 연령대를 구하고 그룹화 하게된다면, 연령별 제품 구매량이 나올 것이다.

SELECT 
    CASE
        WHEN m.age >= 10 AND m.age < 20 THEN '10대'
        WHEN m.age >= 20 AND m.age < 30 THEN '20대'
        WHEN m.age >= 30 AND m.age < 40 THEN '30대'
        WHEN m.age >= 40 AND m.age < 50 THEN '40대'
        WHEN m.age >= 60 AND m.age < 70 THEN '50대'
    END AS age_group,
    r.product_id,
    COUNT(*)
FROM
    review r
        LEFT JOIN
    member m ON r.owner_id = m.id
WHERE
    r.trade_state = 'COMPLETED'
group by age_group,r.product_id;

10대	25	1
10대	38	1
20대	1	1
20대	3	1
20대	7	1
20대	18	2
20대	30	1
20대	31	3
20대	33	1
20대	36	2
20대	37	1
20대	38	1
40대	7	1

 

요청자의 연령대를 검색하기

  여기 부분에서 고민이 좀 생겼다.

현재 이 코드에서는, age_group을 그룹화하여 연령대라는 컬럼을 만들었기 때문에
WHERE 을 통해 조건문으로 원하는 연령대를 갖고올 수 없었다. 그래서 사용한 것이 서브쿼리이다.

SELECT 
    CASE
        WHEN m.age >= 10 AND m.age < 20 THEN '10대'
        WHEN m.age >= 20 AND m.age < 30 THEN '20대'
        WHEN m.age >= 30 AND m.age < 40 THEN '30대'
        WHEN m.age >= 40 AND m.age < 50 THEN '40대'
        WHEN m.age >= 60 AND m.age < 70 THEN '50대'
    END AS age_group,
    r.product_id,
    p.title as product_title,
    img.thumb_img_src as img,
    COUNT(*) AS '거래량'
FROM
    review r
        JOIN
    member m ON r.owner_id = m.id
    LEFT JOIN
	product p ON r.product_id = p.id
     LEFT JOIN
	post_img img ON r.product_id = img.id
WHERE
    r.trade_state = 'COMPLETED'
GROUP BY age_group , r.product_id
HAVING age_group = (SELECT 
        CASE
                WHEN m.age >= 10 AND m.age < 20 THEN '10대'
                WHEN m.age >= 20 AND m.age < 30 THEN '20대'
                WHEN m.age >= 30 AND m.age < 40 THEN '30대'
                WHEN m.age >= 40 AND m.age < 50 THEN '40대'
                WHEN m.age >= 60 AND m.age < 70 THEN '50대'
            END AS age_group
    FROM
        member m
    WHERE
        id = 10)
ORDER BY COUNT(*) DESC
LIMIT 3;

 

후기

   마지막으로 도출해낸 SQL문을 보면,

연령대를 두번 구하는 부분이 있다. 해당 부분이 매우 비효율적으로 보여 리팩토링이 필요할 것 같다.

예상할 수 있는 방법은 연령을 먼저 구하는 쿼리를 작성하여, 결과값을 통해 호출하는 것이다.

좀 더 확인해보고 바꾸던가 해야겠다