TIL(24.04.24.)

codataffee·2024년 4월 23일

TIL

목록 보기

16/135

#INTRO

/ / / 비가 추적추적 / / /

오히려 좋아.

#코드카타 (09:00 ~ 11:00)

KATA #14

#데이터와 친해지는 SQL (세션 2회차) (14:00 ~ 15:00)

2회차 리뷰 中
1. COUNT 함수 사용 시 (*), (1) 는 NULL값을 포함한 모든 행의 갯수 조회
2. SELECT COUNT(DISTINCT 칼럼명) > 칼럼 내 중복된 값을 제외한 행의 갯수 조회

UNION 함수: 여러 개의 SELECT 문의 결과를 하나의 테이블로 연결해 조회하고 싶을 때 사용
예로, 1월달 데이터와 2월달 데이터가 다른 테이블에 있을 때.

# union/union all 기본구조
select name, 
       goods_nm, 
       pay_date    --- #컬럼 순서가 같고, 그 형식이 같아야 함 
from 테이블명1

union (all) #수직결합 명시

select 
       name, 
       goods_nm, 
       pay_date
from 테이블명 2

UNION , UNION ALL
1. 공통점: 두 쿼리문을 하나로 수직결합
2. 차이점: UNION 은 중복된 행을 하나로 표기 (중복 제거 후 표기),
  -------- UNION ALL은 모두 표기 (중복 제거없이 표기)
3. 주의점: 열의 갯수와 순서가 모든 쿼리에서 동일해야 하며, 데이터의 형식이 일치해야 함.

JOIN: 여러 개의 SELECT 문의 결과를 단일 결과 세트로 연결, 두 개의 테이블을 결합하는 역할.
두 개의 데이터 셋을 조인하는 방법.
1. 공통 컬럼 찾기: 두 테이블에서 공통으로 존재하는 컬럼
2. 공통 컬럼 관계 찾기: PK 와 FK 찾기
  공통 컬럼 기준, 어떤 테이블이 기준이 되고, 어떤 테이블이 종속되어있는지 파악해야 함.
  테이블의 구조는 1:1, 1:N, N:1, N:N 와 같은 관계로 표현 가능
  - PK : 기본키, NULL일 수 없고, 유일한 값을 가짐.
    -모든 데이터를 식별하는 기준이 되는 컬럼
  - FK : 외래키, 다른 테이블의 PK와 연결되어 테이블 간 관계를 나타내주는 컬럼
    -기준이 되는 컬럼(PK)를 확인하기 위한 연결컬럼(FK)
3. 적절한 조인 방식 찾기

UNION과 JOIN의 활용
1. 테이블들의 열을 맞춰 UNION 을 하고, 결합된 데이터들을 가지고 공통컬럼 기반 JOIN 가능.
2. 공통컬럼 기준으로 JOIN을 하고, 결합된 데이터들을 가지고 열을 맞추는 UNION 가능.
UNION과 JOIN 비교 표

#세션 문제풀이 (15:00 ~ 18:00)

✔️ 문제 #1: CASE WHEN 구문과 JOIN 함수 사용하여 데이터 값 추출

✔️ 작성 쿼리

select a.gb,
       count(*) as usercnt
from
		(
		select case when p.game_account_id is null then '결제안함'
        		    else '결제함' end as gb
		from users u left join payment p 
  		   on u.game_account_id = p.game_account_id 
		group by u.game_account_id
		) a
group by a.gb
order by 2 desc
;

✔️ 실행 결과

✔️ 문제 #2: 조건부 테이블 JOIN 후 기준에 따른 데이터 추출

✔️ 작성 쿼리

select *
from
		(
		select u.game_account_id,
			   count(distinct u.game_actor_id) as actorcnt,
			   sum(distinct p.pay_amount) as sumamount
		from users u inner join payment p 
		     on u.game_account_id = p.game_account_id
		where u.serverno > 1 and p.pay_type = 'CARD'
		group by u.game_account_id
		) a
where a.actorcnt >= 2
;

# 매출에 해당하는 pay_amount의 중복값을 제거하는 함수를 
  사용하는 것을 피하기 위해 작성한 다른 쿼리

select * 
from
		(
	 	 select u.game_account_id,
				count(u.game_actor_id) actorcnt,
				a.sumamount
	 	 from users u inner join
			(
			 select game_account_id,
	 		 sum(pay_amount) sumamount
			 from payment
		 	 where pay_type='CARD'
			 group by game_account_id
			 ) a
		 on u.game_account_id = a.game_account_id
		 where u.serverno >= 2
		 group by u.game_account_id
 		 ) b
where b.actorcnt >= 2
order by b.game_account_id
;

✔️ 실행 결과

✔️ 문제 #3: 조건부 테이블 JOIN 후 기준에 따른 데이터 추출

✔️ 작성 쿼리

select c.serverno,
       round(avg(c.diffdate)) avgdiffdate
from 
	(
	select a.serverno,
	       datediff(b.recentpay, a.`date`) diffdate 
	from
			(
			select game_account_id,
			       `date`,
			       serverno
			from users u
			) a join
			(
			select game_account_id,
			       date_format(max(approved_at),'%Y-%m-%d') recentpay
			from payment p 
			group by game_account_id
			) b 
			on a.game_account_id = b.game_account_id
	) c
where diffdate >= 10
group by c.serverno
order by c.serverno desc
;

✔️ 실행 결과