USER_INFO 테이블과 ONLINE_SALE 테이블에서 2021년에 가입한 전체 회원들 중 상품을 구매한 회원수와 상품을 구매한 회원의 비율(=2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수)을 년, 월 별로 출력하는 SQL문을 작성해주세요. 상품을 구매한 회원의 비율은 소수점 두번째자리에서 반올림하고, 전체 결과는 년을 기준으로 오름차순 정렬해주시고 년이 같다면 월을 기준으로 오름차순 정렬해주세요.
📌 테이블 :
다음은 어느 의류 쇼핑몰에 가입한 회원 정보를 담은USER_INFO테이블과 온라인 상품 판매 정보를 담은ONLINE_SALE테이블 입니다.USER_INFO테이블은 아래와 같은 구조로 되어있으며USER_ID,GENDER,AGE,JOINED는 각각 회원 ID, 성별, 나이, 가입일을 나타냅니다.
USER_ID
INTEGER
FALSE
GENDER
TINYINT(1)
TRUE
AGE
INTEGER
TRUE
JOINED
DATE
FALSE
GENDER컬럼은 비어있거나 0 또는 1의 값을 가지며 0인 경우 남자를, 1인 경우는 여자를 나타냅니다.
ONLINE_SALE테이블은 아래와 같은 구조로 되어있으며ONLINE_SALE_ID,USER_ID,PRODUCT_ID,SALES_AMOUNT,SALES_DATE는 각각 온라인 상품 판매 ID, 회원 ID, 상품 ID, 판매량, 판매일을 나타냅니다.
ONLINE_SALE_ID
INTEGER
FALSE
USER_ID
INTEGER
FALSE
PRODUCT_ID
INTEGER
FALSE
SALES_AMOUNT
INTEGER
FALSE
SALES_DATE
DATE
FALSE
동일한 날짜, 회원 ID, 상품 ID 조합에 대해서는 하나의 판매 데이터만 존재합니다.
풀이
추측)
2021년에 가입한 사람수를 변수로 먼저 대입.
그리고 2021년에 가입한 사람들 리스트를 대상으로 온라인 주문을 join해서 일치하는 것을 조회.
년, 월 기준으로 그룹화한 다음에 같은 달에 똑같은 사람이 주문했을 경우를 생각해서 중복제거로 DISTINCT 적용하고
count로 주문회원수, 2021가입자 수 변수에서 count 비율을 구해 출력하면 될 듯!
쿼리)
* 1차) 실패
SET @CNT = (SELECT COUNT(*) FROM USER_INFO WHERE YEAR(JOINED) = '2021');
SELECT DISTINCT
YEAR(O.SALES_DATE) AS YEAR,
MONTH(O.SALES_DATE) AS MONTH,
COUNT(*) AS PUCHASED_USERS,
ROUND(COUNT(*) / @CNT, 1) AS PUCHASED_RATIO
FROM ONLINE_SALE AS O
INNER JOIN (SELECT *
FROM USER_INFO
WHERE YEAR(JOINED) = '2021') AS U
ON O.USER_ID = U.USER_ID
GROUP BY YEAR, MONTH
ORDER BY YEAR ASC, MONTH ASC
* 2차) 성공
SET @CNT = (SELECT COUNT(*) FROM USER_INFO WHERE YEAR(JOINED) = '2021');
SELECT
YEAR,
MONTH,
COUNT(*) AS PUCHASED_USERS,
ROUND(COUNT(*)/@CNT, 1) AS PUCHASED_RATIO
FROM (SELECT DISTINCT
YEAR(SALES_DATE) AS YEAR,
MONTH(SALES_DATE) AS MONTH,
USER_ID
FROM ONLINE_SALE) AS O # 년,달마다 구매한 회원 리스트
INNER JOIN (SELECT USER_ID
FROM USER_INFO
WHERE YEAR(JOINED) = '2021') AS U # 2021년에 가입한 회원 리스트
ON O.USER_ID = U.USER_ID
GROUP BY YEAR, MONTH
ORDER BY YEAR ASC, MONTH ASC
리뷰)
1차에서 중복제거랑 그룹화를 같이하면서 의도치 않은 결과가 나왔다.
DISTINCT가 실행되는 기준을 생각해보면
1차에서는 중복이 제거되지 않은 상태로 먼저 count과 비율이 다 계산되고난 이후에 중복을 제거하는 거라,
같은 달에 또 구매한 구매자를 거르지 못했다.
이 중복을 먼저 제거하기 위해
2차에서는 년과 달 기준으로 구매한 회원을 중복없이 리스트화 시킬 수 있게 질의해서 먼저 정리하고
# start : 시작, up : 생성 및 실행 , d 옵션 : detach모드 즉, 백그라운드 실행
docker compose up [-d]
# 지금 위치한 디렉토리 대상 도커컴포즈 파일 기준으로 프로세스 목록 조회
docker compose ps
# 개수 설정
docker compose scale mysql=2
# stop : 중지, down : =rm, 중지 및 삭제
docker compose down
** wsl2 환경에서는 별도로 bridge 기능을 활성화 하지 않으면 docker0이 보이지 않는다고 한다.
2️⃣ 컨테이너 포트를 외부로 노출할 수 있어요?
Port Forwarding 을 통해 포트를 외부로 노출시킬 수 있다.
docker run [-p 호스트포트:컨테이너포트] <이미지명:태그>
iptables rule을 통해 포트 노출이 가능한데 포트포워딩을 하면 방화벽 룰이 만들어져 연결 가능
아래 명령어로 포트포워딩 된거 확인 가능
iptables -t nat -L -n -v
* 80포트를 열고있는 컨테이너들이 여러개인 것은 가능 하나 호스트의 eth0 에 80포트로 연결될 수 있는 건 하나. 컨테이너 끼리는 ip가 달라서 포트가 80포트로 같아도 각자의 포트라 상관없지만 연결되는 호스트의 포트 80은 단 하나만 존재하기 때문이다. 그러므로 nginx를 여러개 연결해주고 싶다면, 80포트가 아닌 다른 포트로 연결시키면 된다.
* 포트끼리는 꼭 같은 포트로 일치시킬 필요 없다.
* 호스트 포트 중 사용하지 않고 있는 포트 중에 랜덤으로 부여하고 싶다면
# 호스트포트는 랜덤으로 지정
docker run [-p 컨테이너포트] <이미지명:태그>
만약 대문자 P만 한다면 dockerfile에서 expose로 정의하고 있는 포트에 맞춰 랜덤 포트를 설정하게 된다.
