[GROUP BY/LEVEL4] 년, 월, 성별 별 상품 구매 회원 수 구하기(MYSQL/Oracle)

MYSQL

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

 

---

Oracle

SELECT 
  TO_CHAR(SALES_DATE, 'YYYY') AS YEAR
, TO_NUMBER(TO_CHAR(SALES_DATE, 'MM')) AS MONTH
, A.GENDER                 AS GENDER
, COUNT(DISTINCT(B.USER_ID))           AS USERS
FROM USER_INFO A, ONLINE_SALE B
WHERE A.USER_ID = B.USER_ID
GROUP BY TO_CHAR(SALES_DATE, 'YYYY')
, TO_NUMBER(TO_CHAR(SALES_DATE, 'MM'))
, A.GENDER
HAVING GENDER IS NOT NULL
ORDER BY YEAR, MONTH, GENDER
;