본문 바로가기

빅쿼리9

[BigQuery] 최신의 데이터를 가져오는 3 가지 방법 먼저, 아래와 같은 데이터가 있다고 가정을 합니다. A 파트의 데이터가 우리가 갖고 있는 user_id 기준으로 중복이 있는 tbl_user 라는 테이블입니다.B 파트는 우리가 사용하고자 하는 user_id 기준으로 최신의 정보만 있는 "만들려고 하는 테이블" 입니다. 방법 1첫번째 방법은 사용자(user_id)별로 updated_at 기준으로 번호를 생성하고, 최신의 데이터를 가져오는 방법입니다.withuser_info as ( select user_id , name , phone , address , hobby , row_number() over(partition by user_id order by updated_at desc) as.. 2024. 12. 9.
[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.
[BigQuery] datetime 과 timestamp 의 차이점 ? Google 빅쿼리를 사용하면서 컬럼의 날짜 타입을 보면 date, time 등은 직관적으로 쉽게 이해할 수 있다. 그리고 datetime 이라는 타입은 역시 날짜와 시간의 정보가 함께 있는 타입이구나 라고 어렵지 않게 이해할 수 있다. 그런데 timestamp 라는 타입이 또 존재한다. 이 역시 날짜와 시간의 정보가 함께 있는 타입이다. 그렇다면 (1) 이 두 타입 datetime 과 timestamp 의 차이는 무엇일까? 결론 먼저 이야기하자면, datetime : 로컬의 시간을 저장하는 타입 timestamp : UTC 즉, 세계 표준시를 저장하는 타입 이라고 말할 수 있다. 그래서 bigquery 에서 아래의 그림처럼 조회를 해보면 timestamp 옆에는 UTC라는 표시가 되어있다. 그리고 위에.. 2023. 8. 25.
[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.
[BigQuery] 다양한 쿼리 예제 지난 포스팅에서는 빅쿼리에서의 array 그리고 struct 에 대해서 살펴보았었다. https://walkingfox.tistory.com/169 [BigQuery] Array, Struct 그리고 unnest 빅쿼리에는 Array (배열) 와 Struct (구조체) 라는 개념이 있다. 이 Array 와 Struct 가 생소하게 느껴질 수 있다. 일반적으로 쿼리문의 결과는 아래 그림과 같은 행(row)과 열(column)로 이루어진 테이블(tabl walkingfox.tistory.com 이번 포스팅에서는 여러 케이스의 쿼리 샘플을 살펴보려고 한다. (1) unnest 를 이용한 array 를 펼치기 with 문을 이용하여 위와 같은 테이블을 먼저 구성하였다. id, some_numbers 라는 두개의.. 2022. 6. 26.
[BigQuery] Array, Struct 그리고 unnest 빅쿼리에는 Array (배열) 와 Struct (구조체) 라는 개념이 있다. 이 Array 와 Struct 가 생소하게 느껴질 수 있다. 일반적으로 쿼리문의 결과는 아래 그림과 같은 행(row)과 열(column)로 이루어진 테이블(table) 형태의 결과를 갖는다. GA4와 연결된 BigQuery 창에서 간단하게 쿼리를 해보면 쉽게 array와 struct 를 볼 수 있는데, 아래 그림과 같다. Array 는 위 그림에서 key에 해당되는데 하나의 셀(특정 행,열 위치)에 여러개의 값이 다시 row 처럼 배열로 들어가 있는 형태이다. Struct 는 key 옆에 value 라는 컬럼인데 value.string_value, value.int_value, value.float_value, value.dou.. 2022. 6. 26.
[BigQuery] unixtime 을 timestamp 로 변환 웹사이트 분석에 없어서는 안될 분석 도구인 Google Analytics 가 기존 Universal Analytics (GA3) 에서 GA4 로 강제 전환이 된다. 2023년 7월부터는 GA3를 이용할 수 없게된다. 그리고 GA4로 전환이 되면서 여러 가지 장점이 있는데 그 중에 하나가 BigQuery를 무료로 이용할 수 있는 것이다. 그래서 BigQuery에 대해서 여러 회차에 나누어 글을 써보고자 한다. 이번에 다룰 내용은 숫자(int64 타입) 형태로 되어있는 시간 컬럼(event_timestamp)을 timestamp (날짜 형식 yyyy-mm-dd ) 으로 변환하는 것을 얘기하려 한다. GA4 를 Bigquery 와 연결하고 데이터를 살펴보면 다음과 같다. 이벤트의 발생 시간인 event_tim.. 2022. 6. 25.

티스토리툴바