[oracle] GROUP BY - 년, 월, 성별 별 상품 구매 회원 수 구하기

 

 

 

 

SELECT EXTRACT(YEAR FROM A.SALES_DATE) AS YEAR,
       EXTRACT(MONTH FROM A.SALES_DATE) AS MONTH,
       B.GENDER, 
       COUNT(DISTINCT A.USER_ID) AS USERS
FROM ONLINE_SALE A
JOIN USER_INFO B
ON A.USER_ID = B.USER_ID
WHERE B.GENDER IS NOT NULL
GROUP BY EXTRACT(YEAR FROM A.SALES_DATE), 
         EXTRACT(MONTH FROM A.SALES_DATE), 
         B.GENDER
ORDER BY EXTRACT(YEAR FROM A.SALES_DATE), 
         EXTRACT(MONTH FROM A.SALES_DATE), 
         B.GENDER;

 

 

 

상품을 구매한 회원수를 계산할 때  distinct 를 사용하고,

 

년도 추출

EXTRACT(YEAR FROM A.SALES_DATE)