본문 바로가기

데이터분석21

[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.
[SQL] 데이터 UN-PIVOT 하기 (presto, athena) 지난 포스팅에서는 데이터를 PIVOT 하는 내용에 대해서 알아보았었다. https://walkingfox.tistory.com/166 [SQL] 데이터 PIVOT 하기 (presto, athena) 위 그림에서 파란색의 화살표와 같이 속성(attributtes)과 값(values) 들이 쌓여있는 형태에서, 속성들을 열(컬럼)의 형태로 변형하는 것을 피봇(pivot) 한다고 얘기한다. 그리고 그 반대의 과정을 언피 walkingfox.tistory.com 언피봇(un-pivot) 이란, 위 그림에서처럼 속성(attributes)들이 컬럼으로 되어있는 형태에서 속성들을 행(row)으로 쌓여있는 형태로 만드는 것이다. 이번 포스팅에서는 presto (또는 athena) 에서 un-pivot 하는것에 대해서 설.. 2022. 6. 9.
[SQL] 데이터 PIVOT 하기 (presto, athena) 위 그림에서 파란색의 화살표와 같이 속성(attributtes)과 값(values) 들이 쌓여있는 형태에서, 속성들을 열(컬럼)의 형태로 변형하는 것을 피봇(pivot) 한다고 얘기한다. 그리고 그 반대의 과정을 언피봇(unpivot) 한다고 얘기한다. 이 포스팅에서는 presto (또는 athena) 에서 SQL로 피봇하는 것을 성명하려고 한다. (1) 아래와 같은 형태의 테이블이 있다. 실행을 위해서 sql 로 데이터를 아래와 같이 만들었다. with list (user_id, subject, score) as ( values (1, '국어', 90) , (1, '영어', 85) , (1, '수학', 60) , (2, '국어', 100) , (2, '영어', 50) , (2, '수학', 75) , (.. 2022. 6. 9.
[SQL] null 체크 (presto, athena) 어떤 DB (데이터베이스)던지, 표준 SQL 문법을 지원하기 때문에 특정 컬럼의 값이 null 일때의 처리는 동일한 구문으로 할 수 있다. 예를 들어 아래와 같이 테이블이 있다고 가정을 하면, create table tbl01 ( col1 varchar, col2 varchar ) col1 의 값을 출력하는데, col1의 값이 null 인경우 'none' 이라는 문자열로 출력하기를 원한다고 하면 아래와 같이 case when 구문으로 처리하면 어떤 db 에서든지 작동한다. select case when col1 is null then 'none' else col1 end as new_col1, col2 from tbl01 하지만, sql 문장이 길어져서 간결하게 만들려고 할 때에는 presto (athe.. 2022. 6. 3.
[SQL] 주단위 집계 꼭!! 주의해야할 점 (MySql) 쿼리(Query) 작업 시에 주 (Week) 단위로 집계를 해야한다면, 꼭 주의해야할 점이 하나 있다. 그것은 특정 일자가 어느 주에 속하는지에 따라서 다른년도의 주로 잘 못 계산되는 경우가 무척 많기 때문이다. 특히 연의 마지막주와 연의 첫번째 주에서 이 오류를 자주 범하게 된다. 주 번호 (week number) 를 계산하는 방식에는 여러 가지가 있는데, 예를 들어서 매주 월요일이 있는 날짜의 주를 해당 주의 기준으로 본다고 하면, 위 달력에서 22년도 1월 1일과 22년도 1월 2일은 21년도의 52번째 주에 포함이 된다. 즉, 22년 1월 3일부터 22년도 첫번째주 (01주)가 되는 것이다. 그리고 반대의 경우도 발생할 수 있다. 특정 연도의 12월 30일 또는 31일이 다음 년도의 첫번째 주에 .. 2022. 5. 16.
Mysql 샘플링 (Sampling) : 랜덤 추출 Mysql 에서 랜덤(무작위)으로 몇개의 데이터를 추출하는 경우에 사용할 수 있는 쿼리 문장이다. order by 절에 rand() 함수를 적용하면 데이터가 랜덤한 순서로 추출이되면 limit 절로 그 갯수를 정하는 방식이다.  Select *From tb_sampleOrder by rand()Limit 100위의 쿼리는 tb_sample 이라는 테이블에서 랜덤으로 100개의 raw 를 추출하는 쿼리문장이며, 필요에 따라서 Where 절을 추가할 수 있다. Where 절이 추가되면, 조건이 적용된 데이터중에서 랜덤하게 100개를 추출한다.      [ 추가 ]그룹별로 랜덤하게 sampling 하기https://walkingfox.tistory.com/231 그룹별로 랜덤 sampling 하기예전에 단순히.. 2022. 4. 6.
MySQL 날짜/시간 함수 mysql 날짜/시간 함수 정리 -- 현재시간 날짜 구하기 select now(), current_date, current_time -- 날짜 더하고 빼기 select now(), date_add(now(), interval 1 day) select now(), date_add(now(), interval 4 hour) select now(), date_sub(now(), interval 1 day) select now(), date_sub(now(), interval 4 hour) -- 두 시간의 차이 select timestampdiff(MONTH, now(), date_add(now(), interval 4 month)) 시간주기에 아래의 단위를 사용할 수 있다. SECOND : 초 MINUTE :.. 2022. 3. 29.
M+3 의 재 구매율, M+(1,2,3)의 재 구매율 M+3 의 '재구매율' 이란? 예를 들어 1월의 (신규) 구매자들 중에 4월달에도 재구매를 한 비율을 뜻한다. M+(1,2,3) 의 '재구매율' 이란? 예를 들어 1월의 (신규) 구매자들 중에 2월부터 4월까지 재구매를 한 비율을 뜻한다. 이를 SQL를 이용하여 계산해보도록 하자. 1. 아래와 같이 tbl_uesr, tbl_order 라는 테이블이 있다고 가정합니다. 2. 2019년1월 부터 2019년 12월까지 12개월을 대상으로 'M+3 재구매율' select base_mon.now_dt , count(distinct base_mon.user_id) as cnt_curmon , count(distinct future_mon.user_id) as cnt_nextmon , count(distinct fu.. 2020. 6. 25.
DBeaver 로 AWS Athena 접속하기 1. DBeaver의 데이터베이스 접속 화면에서 Athena 를 검색하여 선택합니다. 2. 선택을 하면 아래 그램과 같이 - Region - S3 location - Access Key - Scret Key 를 입력해줘야 합니다. 3. 위 창의 입력정보를 얻기 위해서는 웹상에서 AWS의 콘솔에 로그인을 합니다. 창의 오른쪽 상단을 보면 Region ('서울'이라고 나와있는)을 선택하는 화면이 있는게 여기서 Region 코드를 얻을 수 있습니다. 버지니아 북부라면 us-east-1 이 리젼이 됩니다. 4. 위의 지역 옆에 내 계정이 나와 있는 부분을 클릭하면 아래와 같이 '내 보안 자격증명'이라는 메뉴가 보입니다. 클릭하여 들어갑니다. 5. 아래와 같은 화면이 나타나는데 '엑세스 키 만들기' 버튼을 클릭하.. 2020. 6. 9.

티스토리툴바