본문 바로가기

데이터분석20

Recursive Query (재귀쿼리) 아래의 쿼리는 mysql 8.x 버젼대에서 실행한 내용입니다. (recursive 쿼리를 지원하는) 1. 테이블 생성 HTML 삽입 미리보기할 수 없는 소스 위 코드를 실행하면 sample 데이터를 생성할 수 있으며, 그 데이터는 아래의 구조를 나타낸 데이터입니다. 2. 하향탐색 재귀쿼리 (기준 node 에서 아래 방향으로 탐색) HTML 삽입 미리보기할 수 없는 소스 -- start 부분에서는 하향검색을 시작할 노드를 설정하는 부분이다. -- repeat 부분에서는 위에서 설정한 초기 값이 재쿼리의 타이틀인 find_division 에 들어가고 그 값을 기준으로 다시 division 을 조인한다. division 테이블이 하위 코드를 탐색하게 되는 테이블이라 d.upper_division_code 를.. 2024. 3. 12.
[SQL] 월별로 잔존타입별 고객수 계산 아래와 같은 테이블이 있다고 가정하고, -- 테이블 명세 : tbl_order customer_id : 고객 아이디 (int) order_date : 주문일자 (date) order_id : 주문번호 (int) order_price : 주문금액 (int) 계산하려고 하는 것은 아래의 고객 타입별 고객 수입니다. -- 이번달 구매한 고객 신규고객 : 이번달 첫구매한 고객 잔존고객 : 전월과 이번달 구매한 고객 귀환고객 : 신규고객이 아니면서, 전월 구매하지 않은 고객이 이번달 구매한 고객 -- 이번달 구매하지 않은 고객 이탈고객 : 전월 구매한 고객이 이번달 구매하지 않은 고객 HTML 삽입 미리보기할 수 없는 소스 2024. 1. 25.
[BigQuery] 연속된 일자 생성하기 빅쿼리에서는 generate_date_array 라는 함수를 사용하면, 일련의 날짜 데이터에 대한 배열을 결과 값으로 얻을 수 있다. 이를 응용하면, 특정 기간안에 주별 데이터(주별 시작일, 연도, 주넘버)를 구하여 여러 쿼리에 활용할 수 있다. declare from_date, to_date date; set from_date = date_sub('2023-01-01', interval 375 day); set to_date = '2023-12-31'; select date_trunc(date(base_dt), week(sunday)) as current_dd , format_date('%Y', base_dt) as week_year , format_date('%U', base_dt) as week_n.. 2023. 10. 10.
A/B 테스트의 과정 (Process) A/B 테스트를 하려면 우선은 적당한 모수의 수를 계산하는 과정이 필요하다. 이 부분은 따로 이야기를 하기로 하고 실제 A/B 테스트를 진행하고 실험 종료 이후 검정 (결과 해석)을 하는 과정에 대해서 생각해보았다. 1. 고려한 부분은 다음과 같다. 회원가입 전환율(singup 시작페이지 -> signup 완료)과 같이 1번 퍼널(funnel)에서 2번 퍼널로 전환이 끝인 경우는 전환율에 대해서 검정하면 된다. 구매 전환율(제품 상세페이지 -> 구매 CTA)와 같이 전환율도 보지만, 전환 후 구매금액도 함께 봐야하는 경우, 전환율이외에 평균 구매금액의 차이에 대해서 비교의 대상을 구매자만 볼 것인지, 테스트의 대상을 모두 볼 것인지 고민스러운 부분이 있다. 이에 대해서 다음과 같이 생각을 정리해보았다. .. 2023. 8. 9.
[BigQuery] 빅쿼리에서 values 절을 구현해보자. MySQL, PostgresSQL 등에서는 Values 절을 이용하여 아래와 같이 외부 text를 테이블 처럼 사용할 수 있다. with list (id, name) as ( values (1004007, '홍길동'), (1002147, '장동건'), (1004493, '현빈'), (1007978, '이서진'), (1005218, '원빈'), (1005507, '이병헌') ) select id , name from list MySQL 에서는 구문이 살짝 다르긴 하다. 요렇게 `ROW` 라는 키워드를 붙여줘야 한다. values ROW (1004007, '홍길동'), 하지만 BigQuery에서는 Values 를 위처럼 사용할 수는 없다. 그래서 아래와 같이 사용해야 한다. (1) 컬럼이 하나인 경우 sel.. 2023. 2. 20.
[BigQuery] 빅쿼리에서 min_by, max_by 쿼리 문장에서 많이 사용하는 그룹 함수 중에 하나가 min(), max() 함수입니다. 아래와 같은 학생별 시험 성적 테이블이 있다고 가정을 합니다. 학생별로 가장 높은 점수와 가장 낮은 점수를 구하는 쿼리를 작성해볼까요. 아래와 같이 계산할 수 있습니다. select user_id , min(score) as min_score , max(score) as max_socre from tbl_score group by user_id 이번에는 학생별로 점수가 가장 낮은 과목은 무었인지 구하는 쿼리를 작성해볼까요. 이럴때 사용할 수 있는 함수가 min_by 입니다. 아시겠지만, min(subject) 라고 쓸 수 없는 이유는 이 함수는 알파벳의 사전순으로 가장 작은 값을 가져오는 내용이기 때문입니다. sele.. 2023. 1. 30.
[BigQuery] 빅쿼리에서 여러 날짜별 테이블을 한 쿼리로 아래 사진에서 보는 바와 같이 GA4를 BigQuery 에 연결하면, 일자별로 테이블이 나뉘어서 데이터들이 저장된다. 그래서 여러 날짜에 걸쳐서 데이터를 분석하고자 하면, 아래와 같이 할 수도 있지만 너무 비효율적이다. (두어개의 테이블만 사용한다면 괜찮지만, 몇달치를 쿼리한다고 하면 ??) SELECT * FROM `coherent-fx-234207.analytics_308890446.events_20220816` union SELECT FROM `coherent-fx-234207.analytics_308890446.events_20220815` union SELECT FROM `coherent-fx-234207.analytics_308890446.events_20220814` 그래서 빅쿼리(BigQu.. 2022. 8. 17.
[MySQL] date_trunc 함수를 구현하자. date_trunc 라는 함수는 특정일자의 시작이 되는 unit 의 첫날을 가져오는 함수이다. 예를 들면 date_trunc('week', '2022-08-05') 라고 하면 2022년 8월 5일이 있는 주(unit) 의 첫날인 '2022-08-01' 보여준다. date_trunc('month', '2022-04-25') 라고 하면 2022년 4월 25일이 있는 월(unit) 의 첫날인 '2022-04-01'을 출력한다. 그런데 MySQL 에서는 안타깝게도 date_trunc 함수를 지원하지 않는다. 월의 첫날은 date_trunc 함수를 사용하지 않아도 구하는 것이 어렵지는 않지만, 주의 첫날을 구하는 것은 까다로울 수 있다. 그래서 아래와 같이 응용을 하여 구해볼 수 있다. select STR_TO_.. 2022. 8. 17.
곱의 평균 (기하평균) 위의 차트는 어느 신문기사에서 가져온 경제성장률에 대한 내용이다. 2009년부터 2020년까지 12년동안 경제 성장률이 변화무쌍하다. 그럼 위 12년동안 평균 경제 성장률은 어떻지? 라는 물음에 답을 물어보면 음.. 평균을 구하자 라고 생각이 든다. 이때 위의 성장률을 모두 더한 후 12로 나누어 구하는 것이 우리가 아는 평균이다. 정확히는 산술평균이라고 하고, 합의 평균이라고 말할 수 있다. 그래서 엑셀로 계산해 보았다. (1) 실제 경제력 계산 기본 경제력을 (즉, 2008년의 경제력) 100 으로 놓고 년도별 성장률을 곱해보았다. 2020년도에는 경제력이 142.5705 정도가 되었다. (2) 산술 평균에 의한 경제력 성잘률의 평균을 구해보니 1.030083333 이 나왔다. 그래서 이 평균 성장률.. 2022. 7. 22.

티스토리툴바