[예제로 익히는 SQL] - 3회차

01. SQL 집계함수: COUNT, MAX, MIN, SUM, AVG

RDBMS 최소 단위 테이블
전체 데이터 혹은 특정 컬럼을 기준으로 요약해서 확인할 수 있음
COUNT 테이블의 행 수를 반환
SUM 테이블의 열의 합계를 반환
AVG 테이블의 열 평균 반환
MIN 테이블의 열 최소값 반환
MAX 테이블의 열 최대값 반환
여러 개의 집계 함수를 동시에 사용 가능

02. SQL 그룹화: GROUP BY와 HAVING

• GROUP BY는 집계함수에 그룹(기준)이 더해진 개념

1. SELECT 뒤 기준 컬럼 작성(작성하지 않아도, 실행은 되지만 어떤 열을 기준으로 했는지 보이지 않음)
2. 집계함수 작성
3. WHERE절 뒤, GROUP BY 기준 컬럼 작성 (WHERE 절은 생략 가능)

• HAVING
  HAVING은 GROUP BY에 의한 결과를 필터링할 때 사용
  즉, WHERE는 GROUP BY 하기 전에, HAVING은 GROUP BY 후에!

03. SQL : SUB QEURY 구문 (🔥중요)

• 서브쿼리(SUB QUERY)
  작동 순서 : 안쪽에 위치한 쿼리에서 메인 쿼리순으로 실행
  주의사항 : SELECT, FROM은 반드시 명시해야하며 쿼리 마지막에 세미콜론 사용 불가, ORDER BY 사용 불가, 반드시 별칭 지정해야함
  서브쿼리를 사용하여 실행된 결과를 가지고 다시 메인 쿼리에서 SELECT를 함

• 서브쿼리의 종류 세가지

1️⃣중첩 서브쿼리 - WHERE 뒤에서 사용하여 조건처럼 사용하는 서브쿼리. 서브쿼리 결과에 따라 달라지는 조건절

2️⃣스칼라 서브쿼리 - SELECT 뒤에서 사용하여 하나의 컬럼처럼 사용되는 서브쿼리.

3️⃣인라인 뷰(가장 많이 사용) - FROM 뒤에서 사용하여 하나의 테이블처럼 사용된다. AS를 사용해서 별칭을 지정해줘야한다.

04. 숙제

지난번 DBeaver에 업로드 한 users.csv 파일을 기준으로, 아래 쿼리문을 작성해 주세요.

문제1 - 집계함수의 활용
조건1) 서버별, 월별 게임계정id 수를 중복값 없이 추출해주세요. 월은 첫 접속일자를 기준으로 계산해주세요. 월은 yyyy-mm의 형태로 추출해주세요.
힌트: 월을 추출하는 방법→날짜는 string(문자열) 형식으로 저장되어 있으므로, 문자열을 자르는 함수를 사용해주시면 좋겠죠? 😃

SELECT
	serverno,
	substr(first_login_date, 1, 7) AS login_month,
	count(DISTINCT game_account_id) AS total_account
FROM
	users
GROUP BY
	serverno,
	login_month;

문제2 - 집계함수와 조건절의 활용
조건1) group by 를 활용하여 first_login_date별 게임캐릭터수를 중복값 없이 구하고,
조건2) having 절을 사용하여 그 값이 10개를 초과하는 경우의 첫 접속일자 및 게임캐릭터id 개수를 추출해주세요.

SELECT
	first_login_date,
	count(DISTINCT game_actor_id) AS total_actor
FROM
	users
GROUP BY
	first_login_date
HAVING
	total_actor > 10;

문제3 - 집계함수와 조건절의 활용2
조건1) group by 절을 사용하여 서버별, 유저구분(기존/신규) 게임캐릭터id수를 구해주세요. 중복값을 허용하지 않는 고유한 갯수로 추출해주세요.
조건2) 기존/신규 기준→ 첫 접속일자가 2024-01-01 보다 작으면(미만) 기존유저, 그렇지 않은 경우 신규유저
조건3) 또한, 서버별 평균레벨을 함께 추출해주세요.

SELECT
	serverno,
	CASE
		WHEN str_to_date(
			first_login_date,
			'%Y-%m-%d'
		) < '2024-01-01' THEN '기존'
		ELSE '신규'
	END AS '유저구분',
	count(DISTINCT game_actor_id) AS total_actor,
	avg(`level`) AS avg_level
FROM
	users
GROUP BY
	serverno,
	2;

문제4 - SubQuery의 활용 2번 문제를 having 이 아닌 인라인 뷰 subquery 를 사용하여, 추출해주세요.
조건1) 문제2번을 having 이 아닌 인라인 뷰 서브쿼리를 사용하여 추출해주세요.
힌트: 인라인 뷰 서브쿼리는 from 절 뒤에 위치하여, 마치 하나의 테이블 같은 역할을 했었습니다!

SELECT
	first_login_date,
	sub.total_actor
FROM
	(
		SELECT
			first_login_date,
			count(DISTINCT game_actor_id) AS total_actor
		FROM
			users
		GROUP BY
			first_login_date
	) AS sub
WHERE
	sub.total_actor > 10;

문제5 - SubQuery의 응용
조건1) 레벨이 30 이상인 캐릭터를 기준으로, 게임계정 별 캐릭터 수를 중복값 없이 추출해주세요.
조건2) having 구문을 사용하여 캐릭터 수가 2 이상인 게임계정만 추출해주세요.
조건3) 인라인 뷰 서브쿼리를 활용하여 캐릭터 수 별 게임계정 개수를 중복값 없이 추출해주세요.

SELECT
	a.total_actor,
	count(DISTINCT game_account_id) AS total_account
FROM
	(
		SELECT
			game_account_id,
			count(DISTINCT game_actor_id) AS total_actor
		FROM
			users
		WHERE
			`level` >= 30
		GROUP BY
			game_account_id
		HAVING
			total_actor >= 2
	) AS a
GROUP BY
	a.total_actor;