[빅쿼리 #5] 하나의 값 조작하기

SQL/Google Big Query

[빅쿼리 #5] 하나의 값 조작하기

Delants 2023. 1. 25. 11:56

- Today's Topic

연월일을 column별로 추출
새로운 행 삽입하기 (데이터 추가하기)
총합계 구하기 (집계함수 미사용)

- Used Function

extract()
insert into ~ values
coalesce()

오늘은 간단한 값을 조작하는 방법에 대해 살펴보겠습니다.

오늘 사용할 테이블은 다음과 같으며, 해당 코드를 big query에 입력해 줍니다.

CREATE TABLE inlaid-lane-373607.sqlr202301.lec5_adv (
customer_id string(255)
, reg_date date
, num_of_purchase int64
, recent_purchase_price1 int64
, recent_purchase_price2 int64
, recent_purchase_price3 int64);

INSERT INTO inlaid-lane-373607.sqlr202301.lec5_adv
VALUES
('a001', '2022-04-24', 5, 16200, 55000, 34600)
, ('a002', '2020-11-20', 34, 7450, 3300, 76500)
, ('a003', '2021-08-19', 12, 175600, 1050, 6000)
, ('a004', '2023-01-19', NULL, NULL, NULL, NULL)  ;

select * from inlaid-lane-373607.sqlr202301.lec5_adv

customer_id : 고객 이름

reg_date: 가입일자

num_of_purchase: 가입 이후 누적 구매 횟수

recent_purchase_price1~3: 최근 구매한 상품의 가격.

1. 연월일을 column별로 추출

평소 쿼리를 작성하다보면, 연, 월, 일에 대한 데이터를 따로 추출해야할 경우가 생길 수 있습니다.

이런 경우에는 어떻게 사용할 수 있는지 살펴보겠습니다.

위의 데이터셋에서는 회원들의 가입일자 (reg_date)를 추출해볼 수 있을 것입니다.

select customer_id
, extract(year from reg_date) as year -- reg_date칼럼에서 year(연도)정보를 추출
, extract(month from reg_date) as month -- reg_date칼럼에서 month(월)정보를 추출
, extract(day from reg_date) as day -- reg_date칼럼에서 day(일자)정보를 추출
from inlaid-lane-373607.sqlr202301.lec5_adv;

위 코드에서 사용된 extract함수는 날짜 타입의 데이터에서 날짜/시간 등 표현식을 추출하여 반환하는 함수입니다.

사용할 수 있는 구문은 extract(추출하고자하는 시간개념 from 추출하고자하는 칼럼명) as 별칭이름 입니다.

별칭은 선택사항이나, 테이블 출력 시 코드가 길어지므로 적어주는 것을 권장합니다.

위 코드를 타이핑하면 다음과 같은 결과를 얻을 수 있습니다.

어떤 회원이 언제 가입을 했는지에 대한 정보를 칼럼별로 나눠서 추출할 수 있게 되니, 연 정보를 이용해 사용자의 평균 가입 연도에 대한 데이터를 분석해보고자 할 경우 등등 다양한 상황에서 활용할 수 있을 것 같습니다.

2. 기본적인 새로운 행 삽입하기 (데이터 추가하기)

위의 테이블에서 id가 'a005'인 새로운 회원이 가입했다면 그에 대한 정보도 추가해 주어야 할 것입니다.

이럴 경우 어떻게 추가할 수 있는지 코드를 보겠습니다.

insert into inlaid-lane-373607.sqlr202301.lec5_adv values ('a005', '2022-10-06', 2, 56500, 372000, null);
select*from inlaid-lane-373607.sqlr202301.lec5_adv;

위 코드에서 사용된 구문이 하나 보이실 것입니다. 바로 insert into ~ values 구문입니다.

insert 구문은 영어 뜻 그대로 테이블에 어떠한 테이터를 추가할 경우 사용됩니다.

insert into 프로젝트명.데이터셋명.테이블명 (넣을 칼럼이름) values ('첫번째 칼럼에 들어갈 데이터', '두번째 칼럼에 들어갈 데이터' ....) 와 같이 사용합니다.

oracle기반 sql 등을 사용하시고 계실 경우, insert into 다음에 테이블명만 적어주셔도 됩니다.

그러나 이러신 분들도 계실 것입니다.

"특정 칼럼에만 넣고 싶은데, 어떻게 넣을 수 있을까요?"

위 코드에서는 (넣을 칼럼 이름)에 대한 정보를 생략하여 전체에 대한 정보를 삽입했습니다.

하지만 칼럼의 수가 막 20개 이렇게 되고, 전부 넣을 필요가 없는 테이블일 경우 매우 힘들 것입니다.

그래서 insert into 테이블명 옆에 (칼럼1, 칼럼2...) values ('칼럼1에 추가할 데이터' ....)를 추가하는 것입니다.

제가 customer_id칼럼에 a006데이터를 넣고, reg_date칼럼에 가입일자만 임의로 넣어 보겠습니다.

작성일 기준, 어제 날짜인 23년 1월 24일로 넣어보겠습니다.

아직 그 회원은 구매를 하지 않았다고 가정하고, 나머지 값은 빈칸으로 두고자 할 경우 유용합니다.

insert into inlaid-lane-373607.sqlr202301.lec5_adv (customer_id, reg_date) 
values ('a006', '2023-01-24');
select*from inlaid-lane-373607.sqlr202301.lec5_adv;

a006이라는 데이터가 추가된 것을 확인할 수 있을 것입니다.

3. 총합계 구하기 (집계함수사용하지 않고 구하기)

위의 테이블에서 최근 구매액의 총 합계를 구하고자 합니다.

물론 집계함수를 쓴다면 편하게 구할 수 있지만, 오늘은 일반적인 덧셈으로 구하는 방법을 살펴보겠습니다.

집계함수 사용하지 않고 단순히 덧셈만 해서 일반적으로 구하는 것 처럼 하면 어떤 결과가 나올지 보겠습니다.

select customer_id, recent_purchase_price1 + recent_purchase_price2 + recent_purchase_price3 as total_price
from inlaid-lane-373607.sqlr202301.lec5_adv;

a001, a003, a002회원은 전부 잘 나왔지만, a004, a005회원은 null로 출력되었습니다.

a004회원이야 아직 구매한 횟수가 없어 null로 나올 수 있겠지만 문제는 a005회원입니다.

2회 구매한 이력이 있지만 3회 모두 충족시키지 않아 총 구매액 전체가 null로 되어버린 것입니다.

여기서 하나 알고가야할 것이,

null과 null을 더하면 null이 되는 것은 알고 계실 것입니다.

하지만 어떤 정수와 null을 더하면(빼면), 그 결과값도 null이 되어 버립니다.

이것이 이른바 null의 공포인데, 데이터가 큰 상황에서 일어난다면 매우 말도 안되겠죠.

그래서 null을 치환하는 함수를 사용하여 이 null값을 다른 값으로 변환해 주어야 합니다.

null을 치환할 경우, nvl함수도 사용하기는 하지만 오늘은 coalesce()를 사용해 치환해 보겠습니다.

참고.
coalesce() 함수: null이 아닌 첫번째 식의 값을 반환한다.

coalesce(열1, 열2.....열n)과 같은 형태로 사용할 수 있다.

ID price coalesce(ID, price)

a001 null a001

null 1000 1000

coalesce(ID, reg_date)에서
null이 아닌 첫 번째 값을 반환하므로, id열에 해당하는 값이 null이면 다음 열의 값인 price열에서 값을 가져오며,
id가 null이 아니면 바로 id열의 값을 가져온다.

select customer_id, coalesce(recent_purchase_price1,0) + coalesce(recent_purchase_price2,0) + coalesce(recent_purchase_price3,0)  
from inlaid-lane-373607.sqlr202301.lec5_adv;

recent_purchase_price1열에 값이 null이 아니면 첫 번째 값인 그 열의 값을 그대로 출력하게 되며,

null이 나오면 0으로 치환하게 만들었다고 보시면 됩니다.

이렇게 coalesce()를 사용했더니 null로 나왔던 부분이 0원이 되게 깔끔해 졌으며, id가 a005인 회원도 합계금액 누락없이 잘 출력된 것을 확인할 수 있습니다.

감사합니다.

- 본 내용은 '데이터 분석을 위한 sql레시피' 교재를 활용한 것입니다.