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

SQL/Google Big Query

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

Delants 2023. 1. 27. 11:47

- Today's Topic

현재날짜를 기준으로, 날짜의 차이 계산하기
현재날짜를 이용하여, 연수 계산하기
ip주소를 정수형으로 변환하기
ip주소를 0으로 채우기

- Used Function

날짜함수 (current_date(), date(), timestamp(), date_diff() 등)
cast() 함수
split() 함수
lpad()함수

이번에는 날짜함수를 활용해 날짜의 차이를 계산해보고, 연수를 파악하는 방법,

그리고 텍스트분석에 사용되는 cast, split함수와 함께 lpad()함수를 사용해 ip주소를 정수형으로 변환하고, 0으로 채우는 방법에 대해 살펴보겠습니다.

사용할 테이블은 다음과 같습니다. 제 임의로 업체의 지점별 매출을 만든 테이블입니다.

code: 지점고유코드

place: 지점이 속한 지역

markname: 지점명

regdate: 지점등록일자

sales_년도: 연매출

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

Latitude: 위도

Longitude: 경도

1. 현재날짜와 지점등록 날짜의 차이 계산하기

위 표에서 현재 날짜와 지점이 등록된 날짜(regdate)의 차이를 계산하고자 합니다.

해당 날짜의 차이를 알고자 할 경우, 현재날짜를 구하는 current_date() 함수와 날짜의 차이를 계산해 주는 date_diff()함수를 이용할 수 있습니다.

물론 regdate가 date타입이 아닐 경우 date타입으로 변환하는 일련의 과정이 필요합니다.

사용할 함수 및 설명

current_date() : 현재 날짜를 반환합니다.

date_diff(시작일자, 종료일자, 형식) : 두 날짜 사이의 차이를 계산합니다. 
(형식에는 year,quarter,month,week,day,hour,minute,second 등 시간개념이 올 수 있습니다.)

쿼리

select concat(place,markname) as market_name
, current_date() as today
, date(timestamp(regdate)) as register_date
, date_diff(current_date(), date(timestamp(regdate)), day) as diff_days
from inlaid-lane-373607.sqlr202301.lec6_adv;

1행. concat()함수를 활용해 place(지역)와 markname(지점명)을 결합하여 하나의 칼럼에 표시되게 합니다.

별칭은 market_name으로 줍니다.

2행. 현재 날짜를 구하는 함수인 current_date()함수를 활용해 현재 날짜를 구합니다.

3행. timestamp()를 활용해 reg_date를 yyyy-mm-dd hh:mm:ss 형식으로 변환한 후, date()함수를 활용해 연-월-일 형태로 변환합니다.

4행. date_diff()를 활옹해 현재 날짜(current_date)와 3열에서 date포맷으로 변환시킨 날짜와의 차이를 day(일)별로 셉니다.

5행. 해당 쿼리를 실행할 테이블을 지정합니다.

결과

2. 등록날짜를 이용해 현재기준 등록년수 계산하기

다음으로는 등록날짜(regdate)를 이용해 현재날짜기준으로 등록년수를 계산하는 방법에 대해 살펴보겠습니다.

해당 쿼리문을 활용하면 2023년 기준으로 각 지점은 개점한지 몇 년이 되었는지 알 수 있을 것 같네요.

사실 앞선 쿼리문과 큰 차이는 없기 때문에 어렵지 않을 것 같습니다.

사용할 함수 및 설명

current_date() : 현재 날짜를 반환합니다.

date_diff(시작일자, 종료일자, 형식) : 두 날짜 사이의 차이를 계산합니다. 
(형식에는 year,quarter,month,week,day,hour,minute,second 등 시간개념이 올 수 있습니다.)

date_diff()함수 사용 시 앞서 현재날짜와 지점등록일자의 차이를 구할 때는 형식을 일수(day)로 지정해 주었지만

지금은 년수로 계산하는 것이므로 연수(year)로 지정해 주는 것이 차이입니다.

쿼리

select concat(place,markname) as market_name
, current_date as today
, date(regdate) as register_date
, date_diff(current_date(), date(regdate), year) as year
from inlaid-lane-373607.sqlr202301.lec6_adv;

1행. concat()함수를 활용해 place(지역)와 markname(지점명)을 결합하여 하나의 칼럼에 표시되게 합니다.

별칭은 market_name으로 줍니다.

2행. 현재 날짜를 구하는 함수인 current_date()함수를 활용해 현재 날짜를 구합니다.

3행. date()함수를 활용해 연-월-일 형태로 변환합니다.

(연도 확인하는 것이므로 시간까지 나타내주는 timestamp까지 갈 필요 없습니다.)

4행. date_diff()를 활옹해 현재 날짜(current_date)와 3열에서 date포맷으로 변환시킨 날짜와의 차이를 year(연도)별로 셉니다.

5행. 해당 쿼리를 실행할 테이블을 지정합니다.

결과

강릉주문진점은 개점한지 15년이 된 점포이고, 춘천소양강점은 개점한지 4년이 된 점포라서 비교적 최근에 개점한 것을 확인할 수 있습니다.

나중에 분석할 때 개점한 지 10년 이상된 점포에 대한 분석을 진행해 보는 것도 괜찮을 것 같습니다.

3. IP주소를 정수형으로 변환하기

다음으로는 IP주소를 정수형으로 변환하는 방법에 대해 살펴보겠습니다.

IP주소를 정수형으로 변환하는 이유는 IP주소간 대소비교, 범위판정 등을 하기 위해서입니다.

하지만 ip주소는 중간에 '.'이라는 기호가 있어서 이것을 한번에 변환할 수는 없습니다.

또한 ip주소는 .을 기준으로 8비트씩 끊어서 10진수로 나타내기때문에 조금 복잡하기도 합니다.

그래서 텍스트 전처리에 사용하는 함수들을 활용하여 변환해야 합니다.

사용할 함수 및 설명

split(칼럼명, '구분자') : 문자열을 특정 문자로 구분해서 배열형태로 출력해 주는 함수입니다.
예) 칼럼명이 abc인 칼럼 안에 "안녕.하세요"라는 데이터가 있다고 하면, 
split(abc, '.') => '안녕' '하세요'로 하나의 문장이 '.'을 기준으로 구분되어 출력됩니다.
칼럼명 대신 일반 문자가 와도 됩니다.

cast(바꿀 데이터 as 변경할 데이터타입(길이)) : 특정 데이터 타입을 다른 데이터 타입으로 변경하는 함수입니다.
예) cast('2023-01-26' as date) => '2023-01-26'이라는 문자열을 date타입(날짜형)으로 변경하였습니다.

pow(a,b) : a의 b제곱을 계산할 수 있는 함수입니다.
예) pow(2,3) => 2의 3제곱이므로 8이 출력됨.

쿼리

1 select concat(place,markname) as market_name, ip
2 , cast(split(ip, '.')[safe_ordinal(1)] as int64) * pow(2,24) 
3 + cast(split(ip, '.')[safe_ordinal(2)] as int64) * pow(2,16)
4 + cast(split(ip, '.')[safe_ordinal(3)] as int64) * pow(2,8)
5 + cast(split(ip, '.')[safe_ordinal(4)] as int64) * pow(2,0) as ip_integer
6 from inlaid-lane-373607.sqlr202301.lec6_adv;

1행. concat()함수를 활용해 place(지역)와 markname(지점명)을 결합하여 하나의 칼럼에 표시되게 합니다. 추가로 원래 ip에 대한 정보를 보여줍니다.

2행. split함수를 사용해, ip칼럼의 문자열을 '.'을 기준으로 나누고, cast함수를 사용해 '.'의 첫 번째에 해당하는 부분을 int64데이터타입으로 변환합니다. 이후 pow함수를 사용해 앞의 숫자와 2^24만큼 곱합니다.

3행. split함수를 사용해, ip칼럼의 문자열을 '.'을 기준으로 나누고, cast함수를 사용해 '.'의 두 번째에 해당하는 부분을 int64데이터타입으로 변환합니다. 이후 pow함수를 사용해 앞의 숫자와 2^16만큼 곱합니다. 그런 다음 2열의 결과와 3열의 결과를 더해줍니다.

4행. split함수를 사용해, ip칼럼의 문자열을 '.'을 기준으로 나누고, cast함수를 사용해 '.'의 세 번째에 해당하는 부분을 int64데이터타입으로 변환합니다. 이후 pow함수를 사용해 앞의 숫자와 2^8만큼 곱합니다. 그런 다음 3열의 결과와 4열의 결과를 더해줍니다.

5행. split함수를 사용해, ip칼럼의 문자열을 '.'을 기준으로 나누고, cast함수를 사용해 '.'의 네 번째에 해당하는 부분을 int64데이터타입으로 변환합니다. 이후 pow함수를 사용해 앞의 숫자와 2^0만큼 곱합니다. 그런 다음 4열의 결과와 5열의 결과를 더해줍니다. 별칭은 ip_integer로 주겠습니다.

6행. 해당 쿼리를 실행할 테이블을 지정합니다.

결과

위 테이블을 보시면 ip주소가 정수형으로 변환된 것을 확인할 수 있습니다.

이렇게 된다면 '.'을 기준으로 각기 다른 길이를 가지고 있던 형태였지만, 지금은 모두 정수형으로 통일시켜 놓았으므로, ip주소간 대소비교가 가능해집니다.

4. IP주소를 0으로 채우기

마지막으로 ip주소를 0으로 채우는 방법에 대해 살펴보겠습니다.

0으로 채우는 이유는 고정 길이 문자열을 만들기 위해서입니다.

현재 ip주소 칼럼을 보시면 자리수가 전부 상이하므로 문자 수가 고정되어 있지 않습니다.

이것을 문자 수를 같게끔 고정길이 문자열로 만들게 되면, 문자열 상태로도 대소비교 등을 할 수 있게 됩니다.

그러면 지금부터 각 10진수 부분을 3자리 숫자가 되게 앞 부분을 0으로 채우는 작업을 진행해 보겠습니다.

사용할 함수 및 설명

split(칼럼명, '구분자') : 문자열을 특정 문자로 구분해서 배열형태로 출력해 주는 함수입니다.
칼럼명 대신 일반 문자가 와도 됩니다.

lpad(문자열1, n, 문자열2) : 문자열2를 n자리만큼 왼쪽부터 채워넣어 문자열1을 리턴하는 함수입니다.
예) lpad(abc,5,0) => 00abc

쿼리

select concat(place,markname) as market_name, ip
, concat(
  lpad(split(ip, '.')[safe_ordinal(1)], 3, '0') 
, lpad(split(ip, '.')[safe_ordinal(2)], 3, '0')
, lpad(split(ip, '.')[safe_ordinal(3)], 3, '0')
, lpad(split(ip, '.')[safe_ordinal(4)], 3, '0')) as ip_padding

1행. concat()함수를 활용해 place(지역)와 markname(지점명)을 결합하여 하나의 칼럼에 표시되게 합니다. 추가로 원래 ip에 대한 정보를 보여줍니다.

2행~3행. split함수를 사용해 ip칼럼의 문자열을 '.'기준으로 나누고(배열분해), lpad함수를 사용해 첫번째 '.'까지에 해당하는 주소를 왼쪽부터 3자리의 0으로 매웁니다. (예를 들어 12.345.67 이라는 숫자가 있다면, 012.345.67과 같이 변환해 주는 작업입니다.)

4행. split함수를 사용해 ip칼럼의 문자열을 '.'기준으로 나누고(배열분해), lpad함수를 사용해 첫번째 '.' 다음부터 두번째 '.'까지에 해당하는 주소를 왼쪽부터 3자리의 0으로 매웁니다.

5행. split함수를 사용해 ip칼럼의 문자열을 '.'기준으로 나누고(배열분해), lpad함수를 사용해 두번째 '.' 다음부터 세번째 '.'까지에 해당하는 주소를 왼쪽부터 3자리의 0으로 매웁니다.

6행. split함수를 사용해 ip칼럼의 문자열을 '.'기준으로 나누고(배열분해), lpad함수를 사용해 세번째 '.' 다음부터 네번째 '.'까지에 해당하는 주소를 왼쪽부터 3자리의 0으로 매웁니다.

7행. 해당 쿼리를 실행할 테이블을 지정합니다.

결과

위 테이블의 ip칼럼과 ip_padding칼럼을 비교해 보시면 어떤 차이가 있는지 확인할 수 있을 것입니다.

split함수를 사용해 .을 기준으로 구분한 뒤,

lpad함수로 3자리의 고정 길이 문자열을 맞추기 위해 왼쪽부터 0을 채워 넣었습니다.

만일, 오른쪽부터 채워넣고 싶다면 rpad함수를 쓰면 됩니다.

이상으로 여러 개의 값에 대한 조작에 대한 글을 마치겠습니다.

감사합니다.

본 글은 "데이터 분석을 위한 sql레시피" 교재를 활용하여 작성되었습니다.