[빅쿼리 #6] 여러 개의 값에 대한 조작(1)

- Today's Topic

• 작년 대비 올해 매출의 증가와 감소 표시하기
• 지난 4년간 최대/최소 매출 찾기
• 연합계 및 연평균 매출 계산하기 (집계함수 미사용)

- Used Function

• concat() 함수
• case when ~ then ~ else ~ end 구문
• greatest(), least() 함수
• order by절

---

오늘은 하나의 값이 아닌 여러 개의 값에 대해 조작하고, 결과를 출력하는 첫 번째 시간을 가져보도록 하겠습니다.

오늘 사용할 테이블은 다음과 같습니다. 가상으로 업체의 지점별 매출을 만든 테이블입니다.

code: 지점고유코드

place: 지점이 속한 지역

markname: 지점명

regdate: 지점등록일자

sales_년도: 연매출

ip: 각 지점의 사무실 ip (임의로 정한 값으로, 실제 존재하지 않을 수 있음.)

Latitude: 위도 

Longitude: 경도

 

1. 2021년 대비 2022년의 매출 증감 표시하기

위 테이블에서 2021년과 비교해 2022년의 매출이 얼마나 올랐는지 확인하고 싶다면 어떻게 해야 할까요?

먼저 테이블을 보며 천천히 생각해 봅시다.

두 값을 비교해야 하니 부등호가 들어갈 것이고, 증감표시를 해야하니까 +, -부호가 들어갈 것인데, 이것을 상황에 따라 나누어야 합니다. 이럴 때 사용하는 구문이 바로 case when ~ then ~ else ~ end 구문입니다.

 

구문 작성식은 다음과 같습니다.

select case when '조건' then '조건 만족시 출력할 데이터' else '조건에 만족하지 않을 시 출력할 데이터' end
from 테이블명;

쉽게 말하면 엑셀에서 if함수와 비슷하다고 생각되실 수 있습니다. 실제로 활용법도 상당히 비슷합니다.

 

그렇다면 위 테이블에서 2021년 대비 2022년의 매출 증감을 표시하고자 할 땐, 어떻게 하면 좋을까요?

2021년보다 2022년 매출이 크다면, '+'기호를,

2021년보다 2022년 매출이 작다면 '-'기호를,

아니면 공백으로 나두면 되지 않을까 싶습니다.

그러면 아래와 같이 적어주면 될 것 같습니다.

select code, place, markname
, case when sales_2022>sales_2021 then '+' when sales_2022<sales_2021 then '-' else '' end as judge
, sales_2022 - sales_2021 as diff_judge
from inlaid-lane-373607.sqlr202301.lec6_adv;

표시할 때 같이 표시하고자 하는 칼럼을 추가로 적어준 뒤, case구문을 적어주었습니다. 물론, case구문을 먼저 적고 같이 표시하고자 하는 칼럼을 적어주어도 됩니다.

추가로 얼마나 차이가 나는지 보고싶다면 2022년 매출에서 2021년 매출을 빼주면 그 차액이 나오겠지요.

이것을 diff_judge로 별칭을 주었습니다.

 

결과

---

2. 지난 4년간 최대/최소 매출 찾기

이번에는 위 테이블을 활용해 지난 4년간 최대/최소 매출을 찾아보겠습니다.

각 지점별로 4년간 최대/최소 매출을 구하려면 어떻게 하여야 할까요?

바로 greatest/least 함수를 사용해 주면 됩니다.

 

greatest() / least()함수의 특징

1. 함수에 적혀진 열 중, 최대값 혹은 최소값을 반환해 주는 함수이다.
2. 숫자형 뿐만 아니라, 문자형, 날짜형에서도 사용이 가능하다.
3. 비교 대상의 데이터 유형이 다를 경우 오류가 발생한다.
4. null이 존재할 경우 null이 반환된다.

 

함수 작성식은 다음과 같습니다.

--greatest()함수의 경우 열 인자값 중 최대값을 반환합니다.
greatest(열1, 열2, 열3...) 

--least()함수의 경우, 열 인자값 중 최소값을 반환합니다.
greatest(열1, 열2, 열3...)

그렇다면, 궁금점이 생길 수 있습니다.

기존에 알고있는 min/max함수도 있는데, 무엇이 다른지 말이지요.

min/max는 여러 행(row)에서 값을 비교하는 것이고,

least/greatest는 여러 열(column)에서 값을 비교하는 것이 가장 큰 차이점입니다.

 

그렇다면 다시 본론으로 돌아와서,

지난 4년간 최대/최소값을 찾는 방법을 앞서 보았던 greatest/least함수를 활용해 구해 보겠습니다.

select code, concat(place,markname) as market_name
, greatest(sales_2019, sales_2020, sales_2021, sales_2022) as greatest_sale
, least(sales_2019, sales_2020, sales_2021, sales_2022) as least_sale
from inlaid-lane-373607.sqlr202301.lec6_adv;

이렇게 쓴다면 2019년,2020년,2021년,2022년 매출 중 가장 큰 값, 작은 값을 구할 수 있겠네요.

여러 칼럼을 비교하는 것이므로 greatest()/least()를 쓰는 것도 맞고요.

 

참고. 텍스트를 결합하는 함수, concat()

첫 번째 줄에 사용된 concat()함수는 여러 텍스트를 하나로 합쳐주는 함수입니다.
위에서 place열(지역)과 markname(지점명)이 분리되어 있기 때문에 테이블상에서 분리되어 표현되었습니다. 
하지만 concat()함수를 사용하면 이것을 합쳐주기 때문에 하나의 열에 표시할 수 있게 됩니다.
예로, '서울시 | 영등포' 를 '서울시영등포'와 같은 형태로 합쳐주는 함수가 바로 concat입니다.

 

결과

각 지점의 4년 매출 중 가장 높은 매출과 가장 낮은 매출을 볼 수 있게 되었습니다.

이 둘을 뺀 값에 대한 칼럼을 추가하면 매출 폭이 가장 작은 지점은 어디인지도 확인할 수 있을 것 같습니다.

그때는 여러 행을 비교하는 것이므로, min()함수를 쓸 수 있겠네요. 

 

함수 설명이 많아서 좀 많이 길어졌네요. 바로 다음으로 넘어가겠습니다.

 

---

3. 연합계 / 연평균 매출 구하기 (집계함수 미사용)

이번에는 위 테이블을 활용해 집계함수를 사용하지 않고 연합계/연평균 매출을 구해보겠습니다.

해당 테이블에는 null값이 없기 때문에 빅쿼리 5강에서 했던 내용들을 조금 변형하면 됩니다.

select concat(place,markname) as market_name, sales_2019 + sales_2020 + sales_2021 + sales_2022 as total_sales
from inlaid-lane-373607.sqlr202301.lec6_adv
order by total_sales desc; --연합계 매출

select concat(place,markname) as market_name, (sales_2019 + sales_2020 + sales_2021 + sales_2022)/4 as avg_sales
from inlaid-lane-373607.sqlr202301.lec6_adv
order by avg_sales desc;  --연평균 매출

select concat(place,markname) as market_name, (sales_2021 + sales_2022)/2 as avg_sales_2years
from inlaid-lane-373607.sqlr202301.lec6_adv
order by avg_sales_2years desc;  --최근 2년 연평균 매출

2019년+2020년+2021년+2022년 매출을 모두 더하면 4년간의 합계 매출이 나오게 되고, 

그 값에다가 연수(4)로 나눠주면 연평균 매출이 나오게 됩니다.

만일, 최근 2년간의 매출을 보고 싶다면 2021년과 2022년을 더해 2로 나누면 될 것입니다.

이 부분은 추후 집계함수를 사용하면 더욱 활용도가 높아지고 쿼리도 간단해지므로, 현재는 이렇게 구한다는 정도만 확인해 보겠습니다.

 

만일, '정렬'을 하고자 한다면 order by절을 활용하여 정렬을 하면 됩니다.

기본값은 asc(오름차순)로, 정렬하고자 하는 칼럼 옆에 아무런 문구를 적지 않으면 기본값인 오름차순 순서대로 정렬됩니다.  

만일 내림차순으로 보고싶다면 desc를 적어주시면 됩니다.

 

결과

4년간 합계 매출액(좌), 4년간 평균 매출액(우) 을 내림차순으로 정렬한 결과이다.

감사합니다.

---

- 본 내용은 '데이터 분석을 위한 sql레시피'를 활용해 작성되었습니다.

단, 데이터셋의 내용이나 코드의 일부는 다를 수 있습니다.