예제로 익히는 SQL - 3회차

Suhyeon Lee·2024년 10월 7일
라이브 세션
0

수업 목표

  1. SQL 집계함수: AVG, MIN, MAX, SUM, COUNT 숙지
  2. SQL 그룹화: GROUP BY, HAVING(+WHERE 과 차이점) 을 이용한 쿼리문 숙지
  3. SQL 내부 중첩: SUB QUERY를 이해하고 활용

2회차 과제 리뷰

문제2

  • ((etc_str2 ='레벨업 패키지') or (etc_str2 ='시즌패스')) 관련
    • (etc_str2 in ('레벨업 패키지', '시즌패스'))로도 가능
    • etc_str2 = '레벨업 패키지' or '시즌패스'로 적으면 안 되는 이유
      • 괄호 안 하면 컴퓨터가 이해를 못해요~

문제3

select 
  game_actor_id
  , level
  , case
    when level <=10 then '1~10Lv 이하'
	when level <=20 then '11~20Lv 이하'
	when level <=30 then '21~30Lv 이하'
	when level <=40 then '31~40Lv 이하'
	when level <=50 then '41~50Lv 이하'
	when level <=60 then '51~60Lv 이하'
	when level <=70 then '61~70Lv 이하'
	when level <=80 then '71~80Lv 이하'
	when level <=90 then '81~90Lv 이하'
	else '91~100Lv' 
  end as levelgroup
  , first_login_date
from
  basic.users 
order by
  first_login_date desc 
;
  • CASE WHEN 구문에서 두 번째 WHEN은 첫 번째 WHEN에서 빠져나온 것만 가지고 실행한다는 사실을 이해한다면 위와 같이 더 간단하게 적을 수 있음!

추가

  • DECODE()는 MySQL 아니고 Oracle 함수임
    • MySQL은 CASE WHEN 사용하세요.
  • WHERE 레벨 IS NULL과 WHERE 레벨 IS "NULL"은 다른 의미
    • 전자는 '레벨이라는 컬럼이 비어 있으면', 후자는 '레벨이라는 컬럼이 NULL이라는 텍스트이면'임

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

  • 데이터를 요약해서 보고 싶을 때
  • 데이터분석가는 데이터를 요약해서 살펴보며, 통계적 의미를 찾기도 함

SQL의 집계함수 → 여러 행 또는 열로부터 하나의 결과값을 반환

  • 전체 데이터를 대상으로 사용하거나, 특정 컬럼을 기준으로 사용할 수 있음
  • 테이블의 정보를 요약해서 보고 싶을 때 사용
    • 테이블: 행과 열로 이루어진 데이터 구조
  • SELECT 문에서 사용되며, 종류는 총 5가지
    • 동시에 사용 가능(여러 집계함수를 동시에 사용하여, 한눈에 데이터셋을 볼 수 있음)

특징

집계함수기능
COUNT테이블의 행 수 반환
SUM테이블의 열 합계 반환
AVG테이블의 열 평균 반환
MIN테이블의 열 최소값 반환
MAX테이블의 열 최대값 반환
select
  count(*)as cnt
  , avg(나이)as avg_age 
  , max(나이)as max_age
  , min(나이)as min_age
  , sum(나이)as sum_age
from
  basic.theglory 
;

SQL 그룹화: GROUP BY와 HAVING

  • 데이터를 특정 기준에 따라 요약해서 보고 싶을 때

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

  • 특정 컬럼을 기준으로 데이터를 요약해서 비교하고 싶을 때 주로 사용

SQL 작성 방법

  1. SELECT 뒤 기준 컬럼(나라, 성별, 레벨.. 등등) 작성
  2. 집계함수(COUNT, MAX, MIN, AVG, SUM) 작성
  3. WHERE 절 뒤 GROUP BY 기준 컬럼 작성 (WHERE 절은 생략 가능)

    ※ 주의할 점
    1, 2번 작성 후, 3번을 작성하지 않을 경우 에러가 발생됩니다.
    그 이유는, 데이터를 SELECT할 때, 2번 집계함수는 여러 데이터로부터 하나의 행을 반환하지만, 1번 기준컬럼은 N개의 값을 반환하기 때문에, 3번 GROUP BY 절을 사용하여 기준컬럼 당 값 1개를 반환 할 수 있도록 명시해야 합니다.
    ✨여기서 잠깐! SQL 의 작동순서를 기억하고 계신다면, 이해에 도움이 될 거예요!
    작동순서: FROM → ON → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY

# 집계함수와 GROUP BY 절을 함께 사용하는 SQL문
select 
  기준컬럼 -- 결과값 여러 개
  , 집계함수(조건컬럼을 기준으로 여러 개의 집계함수 동시 사용 가능) -- 결과값 1개  
from
  테이블명
where
  조건 #(생략가능)
group by
  기준컬럼 -- 기준컬럼을 그룹별로 묶어 한 개씩 가지고 오게 만듦
;

# 주의)기준컬럼과 집계함수를 작성하고 GROUP BY 절을 사용하지 않을 때 에러가 발생하는 예
select
  기준컬럼 -- 결과값 여러 개
  , 집계함수(조건컬럼을 기준으로 여러 개의 집계함수 동시사용 가능) -- 결과값 1개
from
  테이블명
where
  조건 #(생략가능)
;
/*출력되는 결과값 개수가 일치하지 않으므로 에러 발생*/

데이터의 그룹화 GROUP BY 실습

# 집계함수와 GROUP BY 절을 함께 사용하는 SQL문 - 실습

select 
  성별 # 기준 컬럼 🚩 
  , count(*)as cnt #집계함수 ✅
  , avg(나이)as avg_age #집계함수 ✅
  , max(나이)as max_age #집계함수 ✅
  , min(나이)as min_age #집계함수 ✅
  , sum(나이)as sum_age #집계함수 ✅
from
  basic.theglory 
group by
  성별 # 기준 컬럼 🚩
;

HAVING ≠ WHERE

  • Having절은 Group By에 의한 결과를 필터링 할 때 사용
  • SQL 구문에서는 Group By 절 뒤에 위치
  • 데이터 필터링
    • WHERE절은 GROUP BY 전 데이터를 기준으로 필터링, HAVING절은 GROUP BY 후 결과값을 기준으로 데이터를 필터링
# 집계함수, GROUP BY절, HAVING을 함께 사용하는 SQL문

select
  기준컬럼
  , 집계함수(조건컬럼을 기준으로 여러개의 집계함수 동시사용 가능)   
from
  테이블명
where
  조건 #(생략가능)
group by
  기준컬럼
having
  조건식
;

예시

  • 조건
  1. 나이 31세 이상
  2. 성별 기준으로 평균 나이 구하기
  3. 평균 나이가 41 초과인 경우

1 → 전체 데이터에 대한 필터링: WHERE 절을 사용
2 → 집계함수와 GROUP BY 를 사용해서 작성
3 → GROUP BY 된 결과값을 필터링 해야 하므로, HAVING 절을 사용

# 집계함수, GROUP BY절, HAVING을 함께 사용하는 SQL문 - 실습
select
  성별
  , avg(나이)as avg_age 2️⃣
from
  basic.theglory 
where
  나이>=311️⃣
group by
  성별 2️⃣
having
  AVG_AGE>41 3️⃣
;

SQL : SUB QEURY 구문 ★★★

  • 쿼리 속의 쿼리! 쿼리를 구조화하는 것!

SUB QUERY 를 통한 데이터 구조화

: 빅데이터를 다루다 보면 많은 컬럼이 들었이는 데이터 셋을 보게되는 경우가 있습니다. 컬럼들을 활용한 많은 연산(사칙, 집계, 윈도우 함수, 포맷팅)을 하는 경우, SUB QUERY 는 이를 순차적(구조적)으로 기록하는 역할을 수행합니다.

복잡한 연산을 단계적으로 진행하게 되는 것이죠!

  • 사용 이유
    • N 번의 쿼리문 실행을 1번의 쿼리문으로 실행하기 위해 쿼리의 결과값을 가지고 추가 연산을 하기 위해 사용
    • JOIN, UNION 동작 수행을 대체할 수 있는 방법을 제공
      : SELECT를 하고 결과값을 확인하고, 그 결과값을 기억했다가 또 SELECT 를 하고, 그 결과값을 기억했다가 또 SELECT 를 하고.. 이러한 경우, N 번의 쿼리문을 수행해야 하는 건 너무 힘듦
  • 실행순서
  • 서브쿼리 실행(안쪽에 위치한 쿼리) → 메인쿼리(바깥쪽에 위치한 쿼리) 실행
  • 쿼리의 가장 안쪽부터, 바깥쪽 쿼리를 실행하며 최종 결과값을 반환
  • 특징
    • () 안에 SELECT, FROM 을 반드시 명시해 주어야 함
    • 쿼리 마지막에 ; 기호를 사용할 수 없음
    • ORDER BY절을 사용할 수 없음
  • 동작 순서
    • 보라색 괄호로 싸여져 있는 부분을 가장 먼저 처리하고 → 그 다음 노란색 괄호를 처리
    • 즉, 안쪽에서부터 바깥쪽으로 그 범위를 넓혀 가며 데이터 추출이 이뤄진다

종류

  1. 중첩(일반) 서브쿼리
  • WHERE절에서 사용
  • 서브쿼리의 결과에 따라 달라지는 조건절 역할
# 중첩 서브쿼리 실습
# 문동은의 나이보다 나이가 많은 모든 데이터 반환하기
select
  *
from
  basic.theglory 
where
  나이 > (select 나이 from basic.theglory where 이름='문동은')
;
  1. 스칼라 서브쿼리
  • SELECT절에서 사용
  • 하나의 컬럼처럼 사용
  • 스칼라 서브쿼리 이용을 위해서는, 서로 다른 테이블이 필요
# 스칼라 서브쿼리 실습
# theglory 의 이름과 theglory2 테이블의 이름이 일치하는 경우를 count 하여 
# same_name_cnt 컬럼으로 반환
# theglory 의 이름과 theglory 테이블의 이름이 일치하는 경우의 결제금액을 sum 하여 
# same_name_sumamount 컬럼으로 반환
select
  이름
  , 나이
  , (
    select 
      count(*)
    from
      theglory2 
    where theglory2.이름=theglory.이름
  ) as same_name_cnt
  , (
    select
      sum(결제금액)
    from
      theglory2
    where
      theglory2.이름=theglory.이름
  ) as same_name_sumamount
from
  basic.theglory
;
  1. 인라인 뷰(가장 많이 사용) 서브쿼리 ★★★
  • FROM 절에서 사용
  • 하나의 테이블처럼 사용
  • AS 구문을 사용하여 명칭을 반드시 기재해야 함
  • 간단해 보이지만, 향후 JOIN 및 UNION 시 가장 유용하게 사용
  • 괄호 안 연산 → 값 일단 저장 → 해당 값 가지고 괄호 밖 연산
# 인라인 뷰 서브쿼리 실습
# 나이가 33세 이상인 모든 데이터 중 나이와 직업 컬럼 반환하기 
select
  x.나이
  , x.직업
from (
  select
    *
  from
    basic.theglory
  where
    나이>=33
  ) as x 
;

-- 위와 동일한 의미
SELECT
  x.나이
  , x.직업
FROM
  x
;

숙제

1. 집계함수의 활용

조건1) 서버별, 월별 게임계정id 수를 중복값 없이 추출해주세요. 월은 첫 접속일자를 기준으로 계산해주세요. 월은 yyyy-mm의 형태로 추출해주세요.

힌트: 월을 추출하는 방법→날짜는 string(문자열) 형식으로 저장되어 있으므로, 문자열을 자르는 함수를 사용해주시면 좋겠죠? 😃

2. 집계함수와 조건절의 활용

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

3. 집계함수와 조건절의 활용

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

4. SubQuery의 활용

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

힌트: 인라인 뷰 서브쿼리는 from 절 뒤에 위치하여, 마치 하나의 테이블 같은 역할을 했었습니다!

5. SubQuery의 응용

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

profile
Suhyeon Lee
2 B R 0 2 B
이전 포스트

2024-10-06

다음 포스트

2주차

0개의 댓글