집계함수와 조건절의 활용
SQL에서는 데이터를 집계하고 조건을 설정할 수 있는 강력한 기능을 제공합니다. 이번 세션에서는 두 가지 주요 문제를 다루었는데, 첫 번째 문제는 집계함수와 조건절을 활용하여 특정 조건을 만족하는 데이터를 추출하는 것이었습니다.
문제2 분석
조건1: GROUP BY 활용
첫 번째 조건은 first_login_date별로 게임 캐릭터 수를 중복값 없이 구하는 것이었습니다. 이때 GROUP BY 절을 사용하여 각 날짜별로 그룹화한 후, 그에 해당하는 캐릭터 수를 세어야 합니다.
sql
SELECT first_login_date, COUNT(DISTINCT character_id) AS character_count
FROM game_characters
GROUP BY first_login_date;
위 쿼리는 first_login_date 열에 따라 그룹화한 후, character_id의 고유한 수를 세어 character_count라는 별칭으로 결과를 반환합니다.
조건2: HAVING 절 활용
두 번째 조건은 HAVING 절을 통해 캐릭터 수가 10개를 초과하는 경우만 필터링하여 첫 접속일자와 게임 캐릭터 ID 개수를 추출하는 것이었습니다.
sql
SELECT first_login_date, COUNT(DISTINCT character_id) AS character_count
FROM game_characters
GROUP BY first_login_date
HAVING COUNT(DISTINCT character_id) > 10;
이 쿼리는 앞서 작성한 쿼리와 유사하지만, HAVING 절을 추가하여 캐릭터 수가 10개를 초과하는 경우만 결과로 반환합니다. 이를 통해 특정 날짜에 많은 캐릭터가 생성된 경우를 분석할 수 있습니다.
| first_login_date | character_count |
|---|---|
| 2024-01-01 | 12 |
| 2024-01-02 | 15 |
위의 표는 예시로 first_login_date와 해당 날짜에 생성된 캐릭터 수를 보여줍니다. 이러한 데이터는 게임 분석이나 마케팅 전략 수립에 유용하게 사용될 수 있습니다.
집계함수와 조건절의 활용 2
다음 문제는 서버별 및 유저 구분(기존/신규) 게임 캐릭터 ID 수를 구하는 것이었습니다. 이 문제는 데이터 분석에서 기준에 따라 그룹화하고 평균을 계산하는 방법을 이해하는 데 중요한 점을 제공합니다.
문제3 분석
조건1: GROUP BY 절 사용
첫 번째 조건은 서버별로, 기존 사용자와 신규 사용자를 기준으로 게임 캐릭터 ID 수를 구하는 것입니다. 이때 중복값을 허용하지 않고 고유한 개수를 추출해야 합니다.
sql
SELECT server,
CASE
WHEN first_login_date < '2024-01-01' THEN '기존유저'
ELSE '신규유저'
END AS user_type,
COUNT(DISTINCT character_id) AS character_count
FROM game_characters
GROUP BY server, user_type;
위 쿼리는 server와 user_type를 기준으로 그룹화하여, 각 그룹의 고유 캐릭터 수를 세어 반환합니다. CASE 문을 통해 첫 접속일자가 2024년 1월 1일 이전이면 '기존유저', 그렇지 않으면 '신규유저'로 분류합니다.
조건2: 서버별 평균 레벨 추출
이번에는 서버별 평균 레벨도 함께 추출해야 합니다. 이를 위해 AVG 함수를 사용하여 각 서버의 평균 레벨을 계산합니다.
sql
SELECT server,
CASE
WHEN first_login_date < '2024-01-01' THEN '기존유저'
ELSE '신규유저'
END AS user_type,
COUNT(DISTINCT character_id) AS character_count,
AVG(level) AS average_level
FROM game_characters
GROUP BY server, user_type;
이 쿼리는 서버와 유저 유형별 캐릭터 수와 평균 레벨을 함께 반환합니다.
| server | user_type | character_count | average_level |
|---|---|---|---|
| A서버 | 기존유저 | 25 | 35.4 |
| A서버 | 신규유저 | 10 | 20.1 |
| B서버 | 기존유저 | 30 | 38.7 |
| B서버 | 신규유저 | 15 | 22.5 |
위의 표는 서버별로 기존 사용자와 신규 사용자의 캐릭터 수와 평균 레벨을 나타냅니다. 이러한 데이터는 서버 운영 및 유저 관리에 있어 매우 중요한 통찰을 제공합니다.
SubQuery의 활용
이번에는 서브쿼리를 활용하여 문제를 해결하는 방법에 대해 다루겠습니다. 서브쿼리는 복잡한 쿼리를 단순화하고, 여러 단계의 데이터 처리를 가능하게 합니다.
문제4 분석
문제4는 인라인 뷰를 사용하여 2번 문제를 해결하는 것이었습니다. 즉, HAVING 절을 사용하지 않고 서브쿼리를 활용하여 동일한 결과를 얻는 방법입니다.
sql
SELECT first_login_date, character_count
FROM (
SELECT first_login_date, COUNT(DISTINCT character_id) AS character_count
FROM game_characters
GROUP BY first_login_date
) AS subquery
WHERE character_count > 10;
위 쿼리에서 서브쿼리를 사용하여 먼저 first_login_date별 캐릭터 수를 계산한 후, 외부 쿼리에서 character_count가 10을 초과하는 경우만 필터링하여 결과를 얻습니다. 이는 데이터의 가독성을 높이고 쿼리를 효율적으로 구성하는 데 도움을 줍니다.
| first_login_date | character_count |
|---|---|
| 2024-01-01 | 12 |
| 2024-01-02 | 15 |
위의 표는 서브쿼리를 사용하여 얻은 결과입니다. 이 접근 방식은 복잡한 데이터 처리 상황에서 유용하게 활용될 수 있습니다.
SubQuery의 응용
마지막 문제는 서브쿼리를 더 응용하여 레벨이 30 이상인 캐릭터를 기준으로 게임 계정 별 캐릭터 수를 중복값 없이 추출하는 것이었습니다. 또한, 캐릭터 수가 2 이상인 게임 계정만 필터링해야 했습니다.
문제5 분석
조건1: 레벨 30 이상인 캐릭터 기준
먼저, 레벨이 30 이상인 캐릭터를 기준으로 캐릭터 수를 구하는 서브쿼리를 작성합니다.
sql
SELECT game_account_id, COUNT(DISTINCT character_id) AS character_count
FROM game_characters
WHERE level >= 30
GROUP BY game_account_id;
이 쿼리는 레벨이 30 이상인 캐릭터의 수를 각 게임 계정별로 세어 반환합니다.
조건2: Having 구문을 사용하여 필터링
다음으로, HAVING 절을 사용하여 캐릭터 수가 2 이상인 게임 계정만 추출합니다.
sql
SELECT game_account_id, COUNT(DISTINCT character_id) AS character_count
FROM game_characters
WHERE level >= 30
GROUP BY game_account_id
HAVING COUNT(DISTINCT character_id) >= 2;
이 쿼리는 두 번째 조건에 따라 캐릭터 수가 2개 이상인 게임 계정만 결과로 반환합니다.
조건3: 인라인 뷰 서브쿼리를 활용하여 게임 계정 개수 추출
마지막으로, 인라인 뷰를 활용하여 캐릭터 수 별 게임 계정 개수를 중복값 없이 추출합니다.
sql
SELECT character_count, COUNT(DISTINCT game_account_id) AS account_count
FROM (
SELECT game_account_id, COUNT(DISTINCT character_id) AS character_count
FROM game_characters
WHERE level >= 30
GROUP BY game_account_id
HAVING COUNT(DISTINCT character_id) >= 2
) AS subquery
GROUP BY character_count;
위 쿼리는 먼저 서브쿼리에서 레벨이 30 이상인 캐릭터 수가 2 이상인 게임 계정의 수를 계산한 후, 그 결과를 바탕으로 캐릭터 수별 게임 계정 개수를 세어 반환합니다.
| character_count | account_count |
|---|---|
| 2 | 5 |
| 3 | 3 |
| 4 | 1 |
위의 표는 각 캐릭터 수에 대해 해당하는 게임 계정의 수를 나타냅니다. 이러한 데이터는 게임의 유저 활동을 분석하거나 마케팅 전략을 수립하는 데 활용될 수 있습니다.
결론
SQL은 데이터베이스에서 데이터를 처리하고 분석하는 데 필수적인 도구입니다. 이번 3회차 세션에서는 집계함수와 조건절, 서브쿼리 활용에 대한 다양한 문제를 다루며 SQL의 응용력을 높이는 기회를 가졌습니다.
각 문제를 통해 SQL의 다양한 기능과 활용 방법을 배우고, 데이터 분석의 깊이를 더할 수 있었습니다. 이러한 경험은 앞으로의 데이터 분석 작업에서 큰 도움이 될 것입니다.
