[SQL] 데이터 관리를 위한 SQL 심화

지난 SQL 기초에 있어서 아주 조금 심화로 들어가보겠다

함수, 술어와 case

SQL에서도 함수를 이용해 다양한 연산을 할 수 있다

• 산술 함수
• 문자열 함수
• 날짜 함수

산술 함수

• abs - 절대값 계산

• mod - 나눗셈의 나머지 계산

• round - 반올림

문자열 함수

• concat - 문자열 연결

select str1, str2, concat(str1, str2) as str_concat
from SampleStr;

• lower - 소문자 변환

  • 반대는 upper

• replace - 문자 변경

  • 문자열 안에 있는 일부 문자를 다른 문자열로 변경하며
    replace(대상 문자열, 치환 전 문자열, 치환 후 문자열) 형태로 입력한다

날짜 함수

SQL에는 날짜를 다루는 함수가 있는데 DBMS 종류마다 그 형태가 조금씩 다르다

현재 날짜, 시간, 일시

select current_date, current_time, current_timestamp;

extract - 날짜 요소 추출

select
    current_timestamp,
    extract(year from current_timestamp) as year,
    extract(month from current_timestamp) as month,
    extract(day	from current_timestamp) as day,
    extract(hour from current_timestamp) as hour,
    extract(minute from current_timestamp) as minute,
    extract(second from current_timestamp) as second;

술어

술어란 반환 값이 진리값(TRUE, FALSE, UNKNOWN)인 함수를 가리킨다

like - 문자열 부분 일치

select *
from samplelike
where strcol like 'ddd%';

여기서 %는 0개 이상의 임의의 문자를 의미한다

그래서 위의 예시의 경우 ddd로 시작하는 모든 문자열을 의미한다

between - 범위 검색

select *
from goods
where sell_price between 100 and 1000;

is null/ is not null

• null인 데이터 선택

select *
from goods
where buy_price is null;

• null이 포함되지 않은 데이터 선택

select *
from goods
where buy_price is not null;

in - 복수의 값 지정

아래의 두 예시 쿼리문은 같은 의미이다

select *
from goods
where buy_price = 320 
	or buy_price = 500
	or buy_price = 5000;

select *
from goods
where buy_price in (320, 500, 5000);

누가 봐도 2번째가 더 간단하고 효율적으로 보이죠? 😎

case

case when <평가식 1> then <식 1>
    when <평가식 2> then <식 2>
    when <평가식 3> then <식 3>
        ⋮
    else <식 n>
end

• 예시

select goods_name, sell_price,
	case when sell_price >=  6000 then '고가'    
		 when sell_price >= 3000 and sell_price < 6000 then '중가'
         when sell_price < 3000 then '저가'
		 else null
end as price_classify
from goods;

테이블의 집합과 결합

테이블 더하기

union 을 사용하면 테이블을 위 아래로 더할 수 있다

select *
from goods
union
select *
from goods2;

만약 중복되는 행을 포함하여 테이블을 합치고자 할 경우에는 union all 을 사용하면 된다

이처럼 집합 연산을 사용할 때의 주의점은 다음과 같다

• 연산 대상이 되는 레코드의 열 개수가 같아야 한다
• 덧셈 대상이 되는 레코드의 열이 같은 데이터형이어야 한다
• order by 구는 마지막에 하나만 사용 가능

테이블 결합

앞서 살펴 본 union 은 행으로 테이블을 합치는 것이라면

결합(join)은 다른 테이블에서 열을 가지고 와서 열을 늘리는 작업을 한다

실무에서는 원하는 데이터가 여러 테이블에 분산되어 있는 경우가 많아 테이블을 결합하여 사용해야 한다

inner join - 내부 결합

가장 많이 사용되는 결합 방법으로, 두 테이블에 모두 존재하는 속성을 기준으로 테이블을 결합하는 방법이다

select store.store_id, store.store_name, store.goods_id,
	goods.goods_name, goods.sell_price
from StoreGoods as store 
inner join Goods as goods
	on store.goods_id = goods.goods_id;

outer join - 외부 결합

inner join은 두 테이블에 모두 존재하는 데이터를 합쳤지만
outer join은 한쪽 테이블에만 존재하는 데이터도 출력한다

select store.store_id, store.store_name, goods.goods_id,
	goods.goods_name, goods.sell_price
from StoreGoods as store 
right outer join Goods as goods
	on store.goods_id = goods.goods_id;

inner join은 양쪽 테이블에 모두 존재하는 정보만을 선택하지만

outer join은 한쪽 테이블에만 존재해 출력하며 정보가 없는 부분은 NULL로 표시한다

또한 어느 쪽 테이블을 마스터로 할 것인지 정해야 한다

즉, left/right를 지정해주어야 한다

위의 예시 쿼리는 마스터 테이블을 right로 지정했기 때문에 오른쪽에 해당하는 Goods 테이블의 내용이 모두 출력된다

✅ 참고 - join할 수 있는 테이블의 개수는 제한 없음

SQL 고급 처리

마지막으로 순위 계산, 누적합 계산, 소계를 구하는 등
고급 집계 처리를 하는 방법인 윈도우 함수에 대해 알아보겠다

윈도우 함수

윈도우 함수를 이용하면 랭킹, 순번 생성 등 일반적인 집약 함수로는 불가능한 고급처리를 할 수 있다

[윈도우 함수] over (partition by [열 리스트] order by [정렬용 열 리스트])

이 중에 partition by는 생략이 가능하다

윈도우 함수로 사용할 수 있는 함수는 크게 다음과 같다

• 윈도우 전용 함수: rank, dense_rank, row_number 등
• 집약함수: sum, avg, count, max, min 등

rank - 순위 계산

select goods_name, goods_classify, sell_price,
	rank() over (partition by goods_classify order by sell_price) as ranking
from Goods;

partition by는 순위를 정할 대상 범위를 설정하며
어떤 조건으로 그룹을 나눈다고 생각하면 이해가 쉽다

위의 예시는 상품 분류마다 순위를 구하고자 하므로 goods_classify를 입력한다

order by는 윈도우 함수를 어떤 열에 어떤 순서로 적용할지 정한다

예시의 경우 판매단가를 오름차순으로 순위를 구하고자 하므로 sell_price를 입력하였다

만일 내림차순으로 순위를 구하고자 할 경우 desc 를 입력하면 된다

이 중 partition by를 통해 구분된 레코드 집합을 '윈도우'라고 하며 이는 '범위'를 나타낸다

만일 partition by를 지정하지 않으면 전체 테이블이 윈도우가 된다


순위를 구하는 윈도우 함수는 rank 외에도 다양하게 존재하며 그 결과가 약간씩 다르다

• rank - 같은 순위인 행이 복수개 있으면 후순위를 건너뛴다
  ex) 1위가 3개인 경우 ➡️ 1위, 1위, 1위, 4위, …

• dense_rank - 같은 순위인 행이 복수가 있어도 후순위를 건너뛰지 않는다
  ex) 1위가 3개인 경우 ➡️ 1위, 1위, 1위, 2위, …

• row_number - 순위와 상관없이 연속 번호를 부여한다
  ex) 1위가 3개인 레코드인 경우 ➡️ 1위, 2위, 3위, 4위, …

윈도우 함수에서 집약 함수의 사용

sum이나 avg와 같은 집약 함수도 윈도우 함수로 사용할 수 있다

select goods_id, goods_name, sell_price,
	sum(sell_price) over() as current_sum
from Goods;

이번에는 누적합계를 구해보도록 하자

select goods_id, goods_name, sell_price,
	sum(sell_price) over(order by goods_id) as current_sum
from Goods;

order by에 열을 지정할 경우
goods_id를 기준으로 오름차순으로 정렬한 후 누적합계를 구한다

이동평균 계산

윈도우 함수에서는 그 범위를 정해 '프레임'을 만들 수도 있다

이는 over 내의 order by 구문 뒤에 범위 지정 키워드를 사용하면 된다

최근 3개 데이터만 이용해 평균을 구하는 이동평균을 계산하는 쿼리는 다음과 같다

select goods_id, goods_classify, goods_name, sell_price,
	avg(sell_price) over(order by goods_id rows 2 preceding) as moving_avg
from Goods;

rows n proceding을 입력할 경우 앞의 n 행까지만 프레임을 만들어 계산한다

위의 예제에서는 n=2를 입력했으므로 현재 행과 앞의 2개 행

즉, 3개 행으로만 이동평균을 계산한다

앞의 행이 아닌 뒤의 행을 이용해 계산하고 싶을 경우
preceding 대신 following을 입력!

• current row and 2 following
  ➡️ 현재 행과 뒤의 2개 행을 의미

• rows between n preceding and m following
  ➡️ 앞의 n행과 뒤의 m행 까지를 프레임으로 지정

출처

데이터 관리를 위한 SQL 심화 (MySQL) - Youtube[헨리의 금융MBA]