[250812] 스파르타코딩 본캠프 7일차 - group by, having, subquery
junecho2025. 8. 12. 21:34
🟡SQL 3차 강의 🟡
SQL 그룹화 : GROUP BY와 HAVING
⇒ 데이터를 특정 기준에 따라 요약해서 보고 싶다면
✅ GROUP BY
집계 함수에서 그룹이 더해진 개념
특정 컬럼 기준으로 데이터를 요약해서 비교하고 싶을 때 사용
group by ⭕ : 전체 데이터를 하나의 그룹으로 간주 -> 전체 합계, 전체 평균 등 전체 집계
group by ❌ : 특정 컬럼(나이 등)을 기준으로 나눠서 각 그룹의 집계 결과를 비교하고 싶을 때
select 기준컬럼, 집계함수1(조건컬럼) as 별칭1, 집계함수2(조건컬럼) as 별칭2 ...
from 테이블명
where 조건 --- 선택적으로 사용 가능
group by 기준컬럼
작동 순서
FROM → ON → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY
이건 왜 에러가 날까?
select *, sum(나이) as sum_age
from basic.theglory;
### => 집계함수와 * 를 함께 사용할 경우, 무엇을 기준으로 합계를 낼지 알 수 없기 때문
✅ having ≠ where
having 절은 group by에 의한 결과를 필터링 할 때 사용
데이터 필터링 기능
where 절 : group by 전 데이터를 기준으로 필터링
having : group by 후 결과값을 기준으로 데이터를 필터링
select 기준컬럼, 집계함수1(조건컬럼) as 별칭1, 집계함수2(조건컬럼) as 별칭2 ... -- 그룹기준
from 테이블명
where 조건 -- (선택 사항)
group by 기준컬럼 -- 그룹 기준은 select와 동일해야 함
having 집계함수를 이용한 조건식 -- 그룹화된 결과에 대한 조건
예시
[조건]
1. 나이가 31세 이상이고,
2. 성별을 기준으로 평균 나이를 구하고,
3. 평균 나이가 41 초과인 경우
select 성별, avg(나이) as avg_age
from theglory
where 나이 >= 31 -- 원본 데이터에서 31세 이상만 필터링
group by 성별 -- 성별 기준으로 그룹화
having avg(나이) > 41 -- 그룹화된 결과 중 평균 나이가 41 초과인 그룹만
⭐⭐⭐SQL : SUB QUERY 구문 ⭐⭐⭐
✅ SUBQUERY
여러 번 select 를 반복하지 않고, 한 번의 쿼리로 연산을 수행할 수 있음
select 결과를 중간 결과처럼 활용해 추가 연산을 이어갈 수 있음
때로는 join 이나 union 을 사용하는 복잡한 로직을 더 단순한 구조로 대체할 수 있음
복잡한 조건 필터링, 집계 결과 비교 등에서 유용하게 사용
✅ SUBQUERY 종류
🔰 중첩(일반) 서브쿼리
서브쿼리 결과에 따라 달라지는 where 조건절
언제 사용?
-- where 로 직접 조건을 정함
select 이름
from theglory
where 나이 = 50;
-- 서브쿼리 활용
-- 가장 나이가 많은 사람의 이름 찾기
select 이름
from basic.theglory
where 나이 = (
select max(나이)
from theglory
);
🔰 스칼라 서브쿼리
select 절에서 사용
서브쿼리 결과가 단 하나의 값(1행, 1열)을 반환해야 함
일반적으로 다른 테이블과 함께 사용할 때 의미있음
언제 사용?
평균, 중앙값, 최대값 등 전체 기준 값과 비교 (ex. 평균 구매금액 이상 구매한 고객 필터링)
원본 데이터에 없던 값을 계산해서 새로 만들어 붙이는 파생 컬럼 추가시 (ex. 고객 테이블에 ‘최근 구매일’ 컬럼 붙이기)
다른 테이블에서 해당 행에 관련된 단일 값 가져오기
--- 비교 기준이 하나의 값일 때
select name, age, (select avg(age) from users) as avg_age
from users;
-- 각 사람의 가장 최근 결제일 표시
select name,
(select max(goods_pay_date) from s2 where s2.name = s1.name) as latest_payment
from s1;
🔰 인라인 뷰
from 절에서 사용되며, 하나의 테이블처럼 사용
(select … ) as 별칭 반드시 지정
가장 자주 사용
복잡한 join, union, 집계, 필터링을 깔끔하게 구성할 때 유용
활용 예시
고객별 최신 결제 내역만 붙이기
select a.name, a.age, b.goods_nm, b.goods_pay_date
from s1 as a inner join
(
select name, goods_nm, goods_pay_date
from s2
where (name, goods_pay_date) in
(
select name, max(goods_pay_date)
from s2
group by name
)
) as b
on a.name = b.name;
2. 월 매출 10만 이상인 고객만 조회
select
from customers as c inner join
(
select customer_id, sum(amount) as total_amount
from payments
where payment_date >= '2025-07-01' and payment_date < '2025-08-01'
group by customer_id
having sum(amount) >= 100000
) as m
on c.customer_id = m.customer_id;
select serverno, substr(first_login_date, 1, 7) as month, count(distinct game_account_id) as cnt_id
from users
group by serverno, month
order by serverno, month
2)
GROUP BY를 활용하여 first_login_date 별 게임 캐릭터 수를 중복값 없이 구하고,
HAVING 절을 사용하여 그 값(중복값 없이 구한 게임 캐릭터 수)이 10개를 초과하는 경우의 첫 접속일자 및 게임 캐릭터 ID 개수를 추출해주세요.
select first_login_date, count(distinct game_actor_id) as cnt_cha
from users
group by first_login_date
having cnt_cha > 10
3)
조건1)
GROUP BY 절을 사용하여 서버별, 유저구분(기존/신규) 게임 캐릭터 ID 수를 구해주세요.
중복값을 허용하지 않는 고유한 갯수로 추출해주세요.
조건2)
기존/신규 기준 → 첫 접속일자가 2024-01-01 보다 작으면(미만) 기존유저, 그렇지 않은 경우 신규유저
조건3)
또한, 서버 별 평균레벨을 함께 추출해주세요.
select serverno,
if(first_login_date < '2024-01-01', '기존유저', '신규유저') as user_type,
count(distinct game_actor_id) as cnt_actor,
avg(level) as avg_level
from users
group by serverno, user_type
4)
문제 2를 having 이 아닌 인라인 뷰 서브쿼리를 사용하여 추출해주세요.
select a.first_login_date, a.cnt_cha
from
(
select first_login_date,count(distinct game_actor_id) as cnt_cha
from users
group by first_login_date
) a
where a.cnt_cha > 10
order by a.first_login_date
5)
조건1)
레벨이 30 이상인 캐릭터를 기준으로, 게임 계정 별 캐릭터 수를 중복값 없이 추출해주세요.
조건2)
HAVING 구문을 사용하여 캐릭터 수가 2 이상인 게임 계정만 추출해주세요.
조건3)
인라인 뷰 서브쿼리를 활용하여 캐릭터 수 별 게임 계정 개수를 중복값 없이 추출해주세요.
select a.cnt_cha, count(1) as cnt_character
from
(
select game_account_id, count(distinct game_actor_id) as cnt_cha
from users
where level >= 30
group by game_account_id
having cnt_cha >= 2
) a
group by a.cnt_cha
order by a.cnt_cha
데이터의 품질 중에서도 '신뢰성'이 왜 중요한지 설명하고, 데이터를 믿을 수 있는지 판단하는 기준을 제시
각각 데이터 신뢰성에 어떤 영향을 주는가? - 데이터 오류, 결측 데이터, 데이터 가공
[주요 포인트]
데이터 오류
잘못된 값(ex. 나이 9999세로 표기)은 분석 결과를 왜곡 분석 초기 하나하나 데이터를 꼼꼼히 살펴보고, 수집 방법 파악을 반드시 선행해야 함
결측 데이터누락된 값이 많거나 특정 특성을 가진 데이터에서 집중적으로 발생하면 편향이 생길 수 있음. 삭제, 평균 또는 특정 값 대체 등의 방법은 있으나, 이상적인 해결책은 아님
결측 데이터란 데이터 수집, 적재 과정에서 누락된 데이터를 의미
데이터 가공 정도가공 데이터는 활용성이 높지만, 예측치나 추정치가 포함될 수 있어 신뢰성이 떨어질 수 있음
원천 데이터란 데이터 수집 직후 아무런 가공도 하지 않은 데이터. 가공이 적어 오류 가능성이 낮지만 이해하기 어려움
오류 허용 수준과 가공 수준의 균형 설정
무조건 가공이 적다고 좋은 것은 아니며, 상황과 목적에 맞는 적절한 균형이 필요함
[핵심 개념]
데이터 신뢰성 : 데이터가 얼마나 실제 정보를 똑바로 담고 있는가?
[인사이트]
데이터의 오류를 분석 초기에 하나하나 데이터를 살펴봐야 한다는데, 그렇다면 수 많은 원천, 원시 데이터들을 눈이 빠지도록 일일이 봐야 하는 방법밖에 없는 것인가? 내 생각에는 아니다. 이 또한 자동화를 할 수 있다고 생각한다. 예를 들면 게임 유저 연령대의 데이터의 오류를 발견해야 한다고 가정한다. 상식적인 나이의 선에서 7~80 으로 지정하면 되는 일 아닌가? 고의적이거나 실수로 틀리게 적은 데이터는 가려낼 수 없지만 말 그대로 '데이터 오류' 인 값은 얼마든지 자동화를 통해 걸러낼 수 있다고 생각하는데, 이 글에선 되게 힘든 일인 것마냥 적어두어서 고개를 갸웃하게된다.
