SQL 문제 풀이 노트

집계 함수의 활용

월별 서버별 게임 유저 수를 중복 값 없이 추출할때

(월은 first_login_date에서 yyyy-mm 형태로 추출할 것)

 

정답

SELECT
    date_format(first_login_date, '%Y-%m') AS m,
    serverno,
    COUNT(distinct game_account_id) AS usercnt
FROM
    basic.users
GROUP BY
    date_format(first_login_date, '%Y-%m'), serverno;

 

내가 작성한 답변

select m, serverno,
count(distinct game_account_id) as usercnt
from
	(select
	date_format(first_login_date, '%Y-%m') as m,
	serverno,
	game_account_id
	from basic.users)
	as a
group by m, serverno;

 

 

답변과 비교할 때 추가 개념 이해가 필요한 내용:

1. date_format(칼럼명, '변경하고자 하는 형식')

2. count(distinct 칼럼명) : 집계함수 안에 칼럼명의 중복값을 제거하기 위해 distinct를 사용할 수 있음

3. group by 

- select보다 먼저 계산되는데, 집계함수 제외 거의 모든 형태의 함수식이 올 수 있음

칼럼 전체를 변형시키는 것이 아니라, 그룹화의 기준 자체를 변형된 값으로 정의하는 함수식은 모두 가능

case when.. then도 가능

- group by 로 기준 칼럼을 설정하면 해당 칼럼의 중복 행값은 자동으로 제거됨(select distinct 사용할 필요없음) 

 

data_format 함수

data_format(칼럼명, '작성 형식')

Year	%Y (2025) %y (25)
Month	%a (Mon) %W (Monday) %w (0 to 6)
Week	%M (January) %b (Jan to Dec) %m (00 to 12) %c (0 to 12)
Day	%d (01 to 31) %e (0 to 31) %D (st, n, rd or th) %j (001 to 366)

 

 

 

집계함수와 조건절의 활용

group by를 활용하여 첫 접속일자별 게임캐릭터 수를 중복값없이 구하고,

having절을 사용하여 그 값이 10개를 초과하는 경우만 추출하기

 

내가 작성한 답변(답안)

select
	first_login_date,
	count(distinct game_actor_id) as actorcnt
from basic.users
group by first_login_date
having count(distinct game_actor_id) > 10;

 

having 조건절

: where 조건절과 같이, 필터링 역할을 함

: where 조건절과의 차이

1. group by 보다 먼저 계산되면 where절, 이후 계산되면 having절

2. group by, where절과 다르게 distinct와 집계함수가 모두 올 수 있음

 

group by 절을 사용하여 서버별, 유저구분(기존, 신규)별 게임 캐릭터 id 수(중복 제외, 고유 개수) 및

평균 레벨 추출

단, 기존, 신규는 첫 접속일자가 2024-01-01보다 작으면 기존유저, 그렇지 않으면 신규유저

 

select serverno,
	case when first_login_date < '2024-01-01' then '기존유저' else '신규유저' end as gb,
	count(distinct game_actor_id) as actorcnt,
	avg(level) as avg_level
from basic.users
group by serverno, 
	case when first_login_date < '2024-01-01' then '기존유저'
	else '신규유저' end;

 

group by:

case when으로 조건에 맞는 값 도출을 같이 사용 가능

단, select 문에서도 case when을 동일하게 입력해주어야 하고, select 문에서는 별칭을 부여하지만,

group by에서는 부여하지 않음

 

 

Subquery 활용

group by를 활용하여 첫 접속일자별 게임캐릭터 수를 중복값없이 구하고,

having절을 사용하여 그 값이 10개를 초과하는 경우만 추출하기

-> 이 문제를 having을 사용하지 않고 subquery로 추출하기 

 

select first_login_date, actorcnt
from
	(select first_login_date, count(distinct game_actor_id) as actorcnt
	from basic.users
	group by first_login_date)
	as a
where actorcnt > 10;

 

 

Subquery 응용

레벨이 30 이상 캐릭터 기준, 게임계정별 캐릭터 수를 중복없이 추출한 후, having 구문을 사용해

계정별 캐릭터 수가 2개 이상인 경우만 추출하고 이를 subquery를 활용하여 캐릭터수별 게임계정 개수를

중복 없이 추출하기

 

select first_login_date, actorcnt
from
	(select first_login_date, count(distinct game_actor_id) as actorcnt
	from basic.users
	group by first_login_date)
	as a
where actorcnt > 10;