[구글 스프레드시트] 날짜에 해당하는 마지막 값 구하기

어쩌다 생활비를 관리하고 있었다.

우선 출금 계좌에 체크 카드를 두 개 발급 받아서 지출을 알아 볼 수 있게 하였다.

은행 사이트에서 거래 내역을 다운로드를 받을 수 있기 때문에 한달 정도의 주기로 구글 스프레드 시트에 붙여서 지출에 대한 정리를 한다.

대략 아래와 같은 표가 달마다 있다.

영역을 설명하기 위해 영역을 구분해보면,

1) 빨간색 왼쪽 부분이 은행에서 내려박기한 양식의 날짜별로(오래된 날짜가 위로가게) 있는 사용 내역이다.

2) 왼쪽 영역을 기준으로 날짜별로 그날의 마지막 잔고를 옮긴다.

3) 2번을 바탕으로 생활비 감소 추세를 볼 수 있는 그래프가 오른쪽 파란색 박스이다.

2번으로 가는 작업을 매번 수작업으로 하려니 시간도 걸리고 자동화를 할 수 있는 방법이 없을까 고민을 했다.

즉, 자동화를 하는 것에 대한 고민이다.

1) 문제 도메인을 줄여본다.

 - 기존 생활비 시트로 시도를 해볼 수는 있었지만 좀더 문제를 간단히하기 위해 문제 해결 영역(도메인)을 의도적으로 줄였다.

 - 왼쪽의 빨간색이 '생활비' 시트를 추상화한 새로운 테이블이다. (기존 생활비에는 날자와 금액 말고, 내역, 입/출금액, 상세 내역등 문제 해결과 관련이 없는 정보까지 들어있다. 이런 불필요한 정보를 제거하게 되면 해결하려고 하는 문제가 명확해진다.)

 - 오른쪽의 녹색(연두색)부분이 원하는 목표이다. 단순한 문제라서 머릿속에 쉽게 생각할 수 있지만, 이렇게 적어놓고 눈으로 비교해 보면 결과에 대해 명확하게 비교가 가능하고 달성했는지 확인이 쉬워진다.

 - 가운데 노란색 영역이 문제를 해결하기 위해 테스트를 해볼 수 있는 영역(개발자 세계에서는 DOJO- 일본어 道場에서 비슷된-라는 용어도 가끔 보인다.)이다.

내가 할일은 위와 같이 세팅해놓고 가운데 노란색 영역을 이것저것 비교해서 오른쪽 연두색 영역처럼 만들면 된다.

구글 스프레드 시트의 QUERY라는 함수를 이용했는데 생각보다 쉽지 않았다. (SQL 문법과 다르고 제약 사항도 많았고 구문 자체가 생소했다.)0

그래서 결국 포기할 뻔했다가...

좀 더 익숙한 도메인 모델인 SQL로 단순화 해보면 어떨까 생각을 하게 되었다.

그래서 macOS에 기본으로 사용할 수 있는 SQLite3를 이용해서 대체 해보았다.

여기까지가 데이터를 만들어서 잘 들어 갔는지 확인하는 방법이었다.

$ sqlite3

SQLite version 3.19.3 2017-06-27 16:48:08

Enter ".help" for usage hints.

Connected to a transient in-memory database.

Use ".open FILENAME" to reopen on a persistent database.

sqlite> create table dojo (dt, val);

sqlite> insert into dojo (dt, val) values ('2019-01-01 05:11', 100);

sqlite> insert into dojo (dt, val) values ('2019-01-01 03:31', 90);

sqlite> insert into dojo (dt, val) values ('2019-01-01 14:31', 80);

sqlite> insert into dojo (dt, val) values ('2019-01-02 14:31', 70);

sqlite> insert into dojo (dt, val) values ('2019-01-03 14:31', 60);

sqlite> .mode column

sqlite> .header on

sqlite> select * from dojo;

그 다음에는 쿼리를 만드는 것이다.

확실히 SQL은 익숙해서 문제 해결이 쉬웠다. 더구나 인터넷에서 Solution을 구하기도 쉽다. (구글 스프레드 시트의 유스케이스는 SQL의 유스케이스보다 적다.)

http://www.sqler.com/bSQLQA/758787 의 질문에서 처리짱님의 답변을 많이 참고했다.

우선 날짜에 해당하는 최대 컬럼을 구해서 임시테이블로 지정(alias)하고 원래 테이블과 임시테이블의 최대 컬럼의 날짜와 동등한지 조건으로 inner join을 한 것이다.

이렇게 하고 나니 포기할 뻔한 기존 스프레드 시트 문제를 풀 수 있게 되었다.
구글 스프레드 시트에서 JOIN 문장은 따로 없다. 그래서 임시로 결과를 만들고 JOIN과 유사한 기능을 수행하면 되는데,
이것은 VLOOKUP이라는 함수를 사용하면 가능하다.

그래서 나온 결과가 위에서 봤던 아래 이미지이다.

사실 문제 풀이 과정에 대한 접근 방법에 대한 것을 기록하기 위함이 포인트(물고기를 잡는 방법)이지만,
혹시 검색을 통해 여기까지 왔는데 해결은 각자의 몫이다라고 끝내서 허탈할 수 있을 것 같아 위의 단순화한 스프레드시트를 공유한다.

시간이 난다면 직접 문제 해결을 해보는 것도 도움이 될 것 같다.

공유: Google Spread Sheet: query + vlookup