250930_SQL_실무에 적용할 수 있는 SQL 기술

1. 사용할 수 없는 데이터가 들어있거나, 데이터 값이 존재하지 않을 때 (NULL)

만약 우리가 사용할 데이터에 잘못된 값이 들어 있거나, (별점을 매겼는데 Not Given이라는 데이터가 있다던가)

NULL, 즉 데이터가 아예 없는 경우 (LEFT JOIN을 했을 때 자주 발생하는 일) 

우리는 이러한 데이터들을 따로 처리한 뒤 계산을 해주어야 하는데, 사용할 수 있는 방법에는 2가지가 있다.

1) 없는 값을 제외하기

만약 함수가 계산할 수 없는 값이 column에 존재한다면, 함수는 이를 0으로 간주하고 계산에 포함한다.

product_name	quantity
Galaxy	6
iPhone	2
Pixel	8
Xiaomi	Not given

이 table에 대해 평균값을 구하고 싶을 때, 다음의 두 계산은 완전히 다른 값을 반환한다.

AVG(quantity)
AVG(if(quantity<>'Not given', quantity, null))
...
GROUP BY product_name

첫 번째 계산은 quantity column에 있는 Not given을 0으로 간주하고, 총 행 수인 4로 나누어 평균을 계산한다.

반환값은 (6+2+8+0) / 4 = 4가 될 것이다.

두 번째 계산은, Not given이 존재한다면 그 값을 NULL로 바꾼 뒤 평균을 계산한다. NULL은 총 개수에 포함되지 않는다.

반환값은 (6+2+8) / 3 = 5.333...이 될 것이다.

이처럼 없는 값을 제외함으로써 있는 값으로만 계산할 수 있다.

(물론 where절을 통해 값이 있는 경우만 필터링해서 계산할 수도 있다.)

2) 다른 값을 대신 사용하기

사용할 수 없는 값 대신 다른 값을 쓸 수도 있다.

예를 들면, 1)에서 본 table에서 if문을 이용해 Not given을 다른 임의의 숫자로 대신할 수도 있다.

그리고 만약 빈 칸, 즉 NULL인 경우는 다른 함수를 이용해 대체값을 채워넣을 수도 있다.

COALESCE(quantity, 2)

COALESCE는 해당 column에 존재하는 NULL 값에 대한 대체값을 넣을 수 있는 함수이다.

위의 query를 실행하면, quantity column에 존재하는 NULL 값이 모두 2로 대체된다.

 

2. SQL로 Pivot table 만들기

Pivot table은 엑셀에도 있는 기능이다. 그렇지만 SQL에서 한 번에 데이터를 정리하고 pivot table로 만들 수 있다면 시간을 줄일 수 있을 것이다.

Pivot table은 2개 이상의 집계 기준으로 데이터를 집계할 때, 보기 쉽게 배열하여 보여주는 table을 의미한다.

예를 들어, '시도별 시간당 강수량'이라는 내용의 pivot table을 만들 때, 집계 기준은 '시/도', 구분 column은 '시간', 그리고 그 안에 들어갈 데이터는 '강수량'이 될 것이다.

SQL에서 pivot table을 만들기 위해선 데이터를 집계 기준과 구분 column에 맞게 grouping하여 계산하는 것이 필요하다. 데이터 가공이 끝나면, 가공한 query를 subquery로 두고 main query에서 pivot table을 만드는 코드를 작성한다.

'시도별 시간당 강수량'을 계산하기 위해 다음과 같이 데이터를 가공했다고 하자.

SELECT sido, substring(time, 1, 2) as time, rain
FROM weathercast

sido	time	rain
서울	14	6
부산	14	5
경기	14	1
부산	15	3
서울	15	9
경기	15	6

그러면 이를 바탕으로 query를 설계하고, pivot table을 만들 수 있다.

SELECT
sido,
MAX(if(time=14, rain, 0)) "14",
MAX(if(time=15, rain, 0)) "15"
FROM
(
SELECT sido, substring(time, 1, 2) as time, rain
FROM weathercast
) Subquery
GROUP BY 1
ORDER BY 2 desc

sido	14	15
서울	6	9
부산	5	3
경기	1	6

첫 column (여기서는 sido)은 집계 기준이 되고,

두 번째 column부터 구분하는 column의 값이 된다. if절 안에는 column 조건을 설정하고, 추가해야 할 데이터를 넣는다.

앞에 있는 MAX는 pivot table이 잘 동작하도록 하기 위해 붙인다. 또한 grouping을 위해 마지막에 집계 기준 column을 기준으로 group by를 해줘야 한다. (order by는 꼭 필요한 것은 아니다)

 

3. Window function - RANK, SUM

Window function은 행과 행 간의 관계를 정의하기 위해 제공되는 함수이다.

행 간의 값을 비교해 순위를 매길 때, 특정 값의 전체 합에 대한 비율을 알고 싶은 상황 등에 사용할 수 있으며, subquery나 group by 등을 이용한 복잡한 연산 없이, 행 삭제 없이 계산할 수 있다는 장점이 있다.

Window function의 기본 구조는 다음과 같다.

WINDOW_FUNCTION(argument) OVER (partition by column1 order by column2)

WINDOW_FUNCTION : 기능 이름 (rank, sum, avg 등)

argument : 기능을 수행하고자 하는 값 또는 column. 함수에 따라 생략될 수 있다.

OVER : 기능 이름과 한 쌍이며, window function을 사용할 때는 항상 작성해 준다.

partition by : 그룹을 나누기 위한 기준 column.

order by : 계산값을 정렬할 기준 column.

partition by와 order by는 상황에 따라 둘 중 하나는 생략될 수 있다.

1) RANK

Rank 함수는 특정 기준으로 순위를 매겨주는 기능을 가진 함수이다.

기본 구조는 다음과 같다.

RANK() OVER (partition by column1 order by column2)

RANK는 어떤 column을 계산하여 값을 반환하는 것은 아니므로, argument는 생략할 수 있다.

column1의 값들을 기준으로 rank를 계산할 part를 분리하고, column2의 값을 기준으로 정렬하여 rank, 즉 순위값을 반환한다. 이때 큰 수의 rank를 높은 쪽으로 하고 싶다면 desc를 사용하면 된다.

2) SUM

SUM은 집계함수로, 이전에 본 함수와 사용 방법은 동일하지만 window function으로 사용되면 카테고리별 합계를 구하거나, 누적합을 구하는 데 사용할 수 있다.

기본 구조는 동일하며, 아래는 각각 카테고리별 전체 합을 구하는 함수 / 누적합을 구하는 함수이다.

SUM(column1) OVER (partition by column2) -- 1
SUM(column1) OVER (partition by column2 order by column1, column3) -- 2

1의 경우, 계산값을 정렬할 기준을 설정하지 않았기 때문에, column1의 값을 column2 값을 기준으로 모두 더하는 계산을 수행한다. (카테고리별 전체 합)

2의 경우, column1의 합을 구하는 데 column1을 기준으로 정렬하고, 추가적으로 행에 순서를 부여할 수 있는, column2보다 세부적인 카테고리인 column3를 통해 column1의 값 간의 순서를 부과한다. (column3가 없으면, column1에 동일한 값이 있는 경우 순서가 없어 모든 동일한 값들이 한꺼번에 누적합으로 처리된다.)

이 외에도 다양한 window function이 존재한다. 그러나 window function의 작동 원리에 대해 명확히 알고, 적용했을 때 더 코드가 간결해지고 작동이 원활해지는 경우에만 사용하는 것이 좋다.