[데이터분석 부트캠프] SQL session #3. 집계함수, 그룹화, 서브쿼리

* 집계함수 = COUNT, SUM, AVG, MAX, MIN 

⭐️ 집계함수 - GROUP BY 셋뚜셋뚜! ⭐️

집계함수가 있다면 GROUP BY 필수 기입해야하고,

집계함수를 뺀 나머지 기준컬럼들을 GROUP BY에 다 적으면 됨!

- WHY?

  : 집계함수는 하나의 행을 반환, but 기준컬럼은 여러 값을 반환 > GROUP BY를 사용하여 기준컬럼 당 1개 값을 반환하도록 해야 함!

ex. SELECT ID,

                     NAME,

                     MAX(AGE) as MAX_AGE

     FROM User

     GROUP BY ID, NAME

 

* 그룹화 = GROUP BY, HAVING

- SQL 의 작동순서 : FROM → ON → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY

 

- HAVING: GROUP BY의 결과를 필터링 (!= WHERE)

ex. 나이가 20세 이상이고, 성별 기준으로 평균 나이를 구하고, 평균 나이가 30 초과인 경우

      SELECT gender,

                     AVG(age) AS avg_age,

                     COUNT(ID) AS cnt

      FROM User

      WHERE age >= 20

      GROUP BY gender

      HAVING AVG(age) > 30

 

* 서브쿼리

안쪽에서 바깥쪽으로 데이터 추출이 이루어짐!

ORDER BY 사용 불가!

 

- 중첩(일반) 서브쿼리

  : WHRER절에서 사용

  ex. SELECT *

        FROM User

        WHERE age >= (SELECT age FROM User WHERE gender = 'F')

 

- 스칼라 서브쿼리 

  : SELECT절에서 사용 - 컬럼 역할
  : 서로 다른 테이블 필요

  ex. SELECT gender,

                       name,

                      (SELECT COUNT(ID) FROM Order o WHERE u.name = o.name) AS user_cnt

                       (SELECT SUM(price) FROM Order o WHERE u.name = o.name) AS sum_price

        FROM User u

 

⭐️ 인라인뷰 중요!!

    : FROM절에서 사용 

    : Alias 필수 기입!

    ex. SELECT a.gender,

                         a.name,

                         a.age

          FROM (    SELECT *

                             FROM User

                             WHERE age >= 20

                         ) AS a

 

* 숙제 

1. 아래 조건을 만족시켜 월별, 서버별 게임계정id 수를 중복값 없이 추출해주세요.
   월은 첫 접속일자 컬럼을 기준으로 추출해주세요.

   조건1) 월은 yyyy-mm의 형태로 추출

SELECT SUBSTR(first_login_date,1,7) AS m,
	   serverno, 
	   COUNT(DISTINCT game_account_id) AS usercnt
FROM basic.users
GROUP BY SUBSTR(first_login_date,1,7), serverno
ORDER BY serverno,SUBSTR(first_login_date,1,7)
;

DATE FORMAT(first_login_date,"%Y-%m") 

참고) DATE FORMAT 

 

2. group by 를 활용하여 첫 접속일자별 게임캐릭터수를 중복값 없이 구하고, having 절을 사용하여 그 값이 10개를 초과하는 경우만 추출해주세요. 

SELECT first_login_date,
	   COUNT(distinct game_actor_id) as actor_cnt
FROM basic.users
GROUP BY first_login_date
HAVING COUNT(distinct game_actor_id) > 10
;

3. group by 절을 사용하여 서버별, 유저구분(기존/신규)별 게임캐릭터id수 및 평균레벨을 추출해주세요.

    게임캐릭터id수는 중복값을 허용하지 않는 고유한 갯수로 추출해주세요.

    조건1) 기존/신규 기준→ 첫 접속일자가 2024-01-01 보다 작으면(미만) 기존유저, 그렇지 않은 경우 신규유저

SELECT serverno,
	   CASE WHEN first_login_date < '2024-01-01' THEN "기존유저"
            ELSE "신규유저"
	   END as gb,
	   COUNT(DISTINCT game_actor_id) as actor_cnt,
	   AVG(level) as avg_level
FROM basic.users
GROUP BY serverno, 
         CASE WHEN first_login_date < '2024-01-01' THEN "기존유저"
			  ELSE "신규유저" 
         END 
;

4. 문제2번을 having 이 아닌 인라인 뷰 서브쿼리를 사용하여 추출해주세요.

SELECT *
FROM (   SELECT first_login_date,
	            COUNT(distinct game_actor_id) as actor_cnt
	     FROM basic.users
	     GROUP BY first_login_date 
	 ) as a
WHERE a.actor_cnt > 10
;

5. 레벨이 30 이상인 캐릭터를 기준으로, 게임계정 별 캐릭터 수를 중복값 없이 추출해주세요.
    그 다음, having 구문을 사용하여 계정별 캐릭터 수가 2개 이상인 경우만 추출해주세요.
    마지막으로 인라인 뷰 서브쿼리를 활용하여 캐릭터 수 별 게임계정 개수를 중복값 없이 추출해주세요.

SELECT a.actor_cnt,
 	   COUNT(DISTINCT game_account_id) as accnt
FROM (   SELECT game_account_id, 
 		      	COUNT(DISTINCT game_actor_id) as actor_cnt
         FROM basic.users
         WHERE level > 30
         GROUP BY game_account_id 
         HAVING COUNT(DISTINCT game_actor_id) >= 2
     ) as a
GROUP BY a.actor_cnt
;