사소한 리서치

lag, lead라는 영어 단어의 뜻 그대로 이전 또는 이후 행의 데이터를 현재의 행과 함께 조회할 수 있는 기능을 제공하는 함수이다. 대부분 sql db에서 지원하는 기능이면 본문에서는 Postgresql을 기준으로 문법을 쓰고 설명하도록 하겠다. 함수로 해결할 수 있는 문제 (order by) 아주 자주 쓰이는 함수는 아니긴 하지만 그래도 lag, lead 함수를 쓰면 복잡하게 만들어야 하는 쿼리를 한줄로 간단하게 끝낼수 있다. 특히 이전 행의 값과 현재 값을 비교하거나 계산해야할 때 쓰면 유용하다. 예를들어 아래와 같은 테이블이 존재한다고 가정해보자. 김정호라는 사람의 5일 전 소비 값과 현재 소비값을 비교해서 값이 오른 행만 추출하고 싶다면 어떻게 해야할까? 머리속이 복잡해지지만, lag함수를 사..

특정 셀의 값이 바뀌면 동적으로 다른 계산값이나 데이터가 바뀌는 구글시트를 구현할 때 유용한 것이 드롭다운 메뉴이다. 셀의 값을 직접 타이핑 해서 입력할 수도 있지만, 디폴트로 정해져있는 값이 있고 그것들 안에서 선택하도록 만들면 좀더 사용자 친화적인 스프레드 시트를 구현할 수 있다. 1. 드롭다운 목록을 넣으려는 셀 선택 드롭다운 메뉴를 만들면 한 셀 안에서 내가 원하는 값들을 선택할 수 있게 된다. 그림처럼 목록들이 표시될 셀을 하나 선택하자. (본 예시에서는 드롭다운 메뉴를 통해 구글파이낸스로 주식 데이터를 불러올 종목을 선택하도록 구현해봤다) 2. 셀에 커서를 가져다 두고 목록에서 데이터 > 데이터확인 클릭 데이터 확인을 클릭하면 아래 그림처럼 목록에 들어갈 값들을 지정하는 창이 나온다. 본 예시..

구글 시트의 장점은 웹으로 접근이 가능하다는 점이며, 구글 스프레드시트 내부에서 다른 구글 스프레드시트로의 접근 또한 가능하다. 다른 시트의 데이터를 가져오기 위해 사용하는 함수가 바로 IMPORTRANGE 라는 함수이다. 함수 이름처럼 다른 시트의 특정 범위를 IMPORT 해주는 기능을 제공한다. 가령 내가 특정 '거래처 상세정보'를 구글시트로 만들어서 관리하려 한다고 생각해보자 이때 거래처의 전화번호는 '거래처 전화번호부' 라는 다른 시트에 있는 데이터를 그대로 가져와서 쓰고싶다. 이럴 때 IMPORTRANGE 함수로 두 구글시트를 연결해준 후 데이터를 가져오면 된다. IMPORTRANGE 함수 기본구성 함수의 인자로 3가지를 전달해야 한다. IMORTRANGE(구글시트 URL주소 / 시트이름 / 데..

구글 스프레드 시트를 사용하다보면 선택하고 싶은 데이터에 자유롭게 조건을 걸어서 선택하고 싶은데, if함수는 다중 컬럼에 조건을 거는것이 쉽지 않기 때문에 sql에서 하는 것 처럼 쿼리로 데이터를 추출해서 시트에 뿌려주고 싶은 생각이 들때가 많습니다. 구글 스프레드시트는 다행히도 쿼리 함수 기능을 지원하는데요, 문법도 sql에서 쓰는 select from where의 구조랑 크게 다르지 않아서 편하게 사용할 수 있습니다. 제가 arrayformula와 함께 구글시트에서 가장 유용하게 사용하는 기능 중 하나입니다. 구글시트 쿼리함수 기본 문법 쿼리함수의 인자로 범위, 쿼리, 헤더포함여부 3가지가 들어가며, 컬럼의 선택은 열 기호인 A,B,C,... 를 사용하면 됩니다. 아래 예제를 보면 C2:H29는 내가..

sumif 함수는 내가 원하는 조건들에 해당하는 셀만 골라서 더해줄 수 있는 기능이다. 그리고 아래 인자들이 함수의 input으로 들어가게 된다. 조건 범위 조건 합계범위 sumif 함수 예제 예를들어 아래와 같은 표가 있다고 생각해보자, 여러가지 브랜드의 제품들을 구매했는데, 여기서 우리는 아디다스 브랜드의 가격만 더해서 보고싶다. 이럴 경우 sumif 함수를 쓰면 되는데 수식은 다음과 같이 쓸수 있다. 조건을 걸 범위는 C3:C9이고 이 범위에서 값이 "아디다스"일 겨우 가격 열에 있는 해당되는 값을 모두 더해준다. =SUMIF(C3:C9,"아디다스",D3:D9) 그럼 아디다스에 해당되는 셀인 D4, D8, D9의 값 3000, 4500, 3000을 모두 더하는 것과 같은 결과를 셀에 계산해주게 된다..

구글 스프레드시트에 차트를 삽입하려다보면 생각처럼 깔끔하게 들어가지지 않는 경우가 많다. 그리고 어떤 싸이즈에 맞춰야 할지도 애매한 경우가 많은데, 이럴때 스파크라인(sparkline)을 이용해서 셀 안에 그래프를 그려넣으면 위치랑 사이즈가 다 깔끔하게 들어가서 아주 복잡한 차트가 아니면 스파크라인을 활용하는게 장표를 깔끔하게 만들 수 있다. 스파크라인을 사용하는 이유는 가독성을 위함임을 기억하자. 스파크라인 함수 기본 스파크라인은 여타 함수들처럼 함수식을 입력한다, 다른 함수들과 다른점은 output으로 셀 안에 숫자가 아닌 그래프가 나온다는 점이다. 스파크라인 함수로 만들수 있는 차트의 종류는 4가지가 있는데 아래와 같다. line - 선 모양의 함수 bar - 막대그래프 column - 열 차트 w..

아마존 Redshift에서 쿼리를 짜다보면 그 달의 마지막 날을 가져와서 조인하거나 그룹바이 해야하는 경우가 종종 생긴다. 레드시프트는 postgresql 기반으로 만들었지만 포스트그레에서는 달의 마지막날을 가져오려면 날코딩으로 구현할 경우 생각보다 코드 짜기가 까다롭니다. 이런 기능을 하는 날짜 함수를 제공하지 않기 때문이다. 놀랍게도 레드시프트에서는 last_day라는 함수로 자기가 원하는 달의 마지막 날을 계산해올 수 있다. 그리고 이를 이용해서 날짜 더하기 빼기등을 하면 특별한 코딩 없이도 굉장히 파워풀하게 날짜 계산을 할 수 있다. 월의 마지막 날짜를 계산하는 last_day 함수 사용법은 매우 간단하다. 아마존에서 제공하는 레드시프트 공식 설명문서 에서도 확인할 수 있는데, 아래와 같은 식으로..

구글 스프레드시트를 사용하다보면 한 셀이 아닌 여러개의 셀에 수식이나 함수를 반복 적용해야 하는 상황이 종종 발생한다. 이런경우 종종 1. 노가다를 통해 반복하면 되는 경우, 2. 수식 계산에 반복 로직이 꼭 필요해서 노가다로 해결이 불가능한 경우 이렇게 상황이 나뉘는 것 같다. 어쨋던 arrayformula 함수는 두가지 경우에서 모두 유용하게 사용된다. 공식 설명을 보면 ARRAYFORMULA함수는 배열수식에서 여러 셀에 반환된 값을 표시하고 배열이 아닌 함수에 배열을 사용할 수 있다고 나와있다. (공식설명 참조) 여기에서 하나 더 얻어갈 수 있는 꿀팁은 수식에 ctrl shift enter를 누르면 수식에 ARRAYFORMULA함수를 자동으로 추가할 수 있다는 점! IF문과 함께 사용하는 경우 필자..

통계 지식을 이용해서 확률분포의 파라미터(parameter)를 추정했으면 시뮬레이션을 통해 그 결과가 우리가 추정한 분포를 따르는지 확인해봐야 하는 상황이 발생한다. 이럴때 파이썬의 Random모듈 라이브러리를 사용하면 쉽게 시뮬레이션에 필요한 난수 생성이 가능하다. 랜덤으로 요소를 선택해주는 파이썬 모듈 랜덤으로 리스트의 요소를 선택할 수 있는 대표적인 모듈로는 random.choice(), random.sample(), random.shuffle 등이 있다. 각각이 어떻게 다른지는 아래 설명으로 정리한다. random.choice() 무작위로 리스트의 요소를 반환한다. 중복선택이 가능하기 때문에 sampling with replacement 상황에서 사용한다. 아래 코드는 파란공 2개, 빨간공 3개가..

서브쿼리(sub query)는 말 그대로 쿼리 안에 쿼리가 들어가있는 형태를 말한다. 서브쿼리는 SELECT, FROM, WHERE, HAVING 등 쿼리문 어디에도 들어갈 수 있으며 서브쿼리를 잘 활용하면 아주 꽤 많은 문제를 쉽게 해결할 수 있다. 서브쿼리와 메인쿼리(main query)의 관계에 따라서 Non-correlated 서브쿼리와 correlated 서브쿼리로 나눠서 생각할 수도 있는데 이는 서브쿼리를 더 잘 활용하기 위한 개념상의 구분 정도로 생각하면 된다. SELECT 절 서브쿼리 사용 다음과 같은 2개의 예제 테이블이 있다. 하나는 vendor에 대한 정보가 담긴 테이블이고 다른 하나의 테이블은 invoice에 대한 정보가 담긴 테이블이다. 이 두 테이블은 vendor_id로 연결되어..