본문 바로가기
[빅쿼리-SQL]고객 마스터 데이터를 카테고리화(성별,연령대)하여 집계하기 서비스를 제공하는 측에서 사용자와 관련된 정보로 알고 싶은 것을 정리해보면 다음과 같은 두 가지로 분류할 수 있습니다. 사용자의 속성(나이,성별,주소지) 사용자의 행동(구매한 상품, 사용한 기능, 사용하는 빈도) 이 중 사용자의 속성을 정리한 데이터를 '고객 마스터 데이터'라고 합니다. '고객 마스터 데이터'를 통하여 분석가는 '어떤 속성의 사용자가 우리 서비스를 사용 중인가?'를 알아볼 수 있습니다. 이번 글에서는 예제 '고객 마스터 데이터'를 사용하여 성별, 연령별로 구분하고 이를 '카테고리화' 하는 방법을 알아보겠습니다. 샘플 데이터 의 예제 데이터 셋을 사용하였습니다. 테이블 명 : mst_users user_ iD : 고객 ID Sex : 고객의 성별 birth_date : 고객의 생년월일 re.. 2022. 6. 21.
SQL 코딩 테스트 연습 사이트 추천 및 후기 다른 언어는 몰라도 SQL 만큼은 자유자재로 사용하고 싶은 욕심이 있다. 하지만 혼자서 SQL을 연습하기에는 우선 연습에 적합한 테이블을 직접 만드는 것에 한계가 있고, 혼자 공부하다 보면 쓰던 쿼리문만 계속 쓰게 되는 단점이 있었다. 따라서 다양한 문제와 다양한 데이터가 정리되어 있는 SQL 코딩 테스트 연습 사이트 이용하면 좋은데, 이번 글에서는 SQL을 연습하기 좋은 사이트 추천과 그 중 '프로그래머스' 사이트의 풀이 후기를 남기겠다. 1. 프로그래머스 https://programmers.co.kr/learn/challenges?tab=all_challenges 코딩테스트 연습 기초부터 차근차근, 직접 코드를 작성해 보세요. programmers.co.kr 프로그래밍 언어를 MySQL 또는 Orac.. 2022. 5. 15.
[빅쿼리 - SQL] 샘플 매출 데이터로 Z 차트 구성하기 매출의 성장 또는 쇠퇴를 다양한 각도에서 살펴보기 위해 Z 차트를 활용하는 것은 매우 좋은 방법이다. 이번 글에서는 Z차트의 정의와 샘플 데이터 셋을 빅쿼리 SQL을 통해 구성하는 법을 알아보겠다. Z 차트란? '월차매출,'매출누계','이동년계'라는 3가지 지표로 구성되어, 계절변동의 영향을 배제하고 트렌드를 분석하는 방법이다. 이를 통해 데이터의 단기적 추이(매출누계)와 장기적 추이(이동년계)를 알 수 있다. 월차매출 : 월별 매출 합계 매출누계 : 해당 월의 매출에 이전 월까지의 매출 누계 (ex : 2018년 03월 기준 ---> 2018년 1월, 2월, 3월 매출의 합) 이동년계 : 해당 월의 매출에 과거 11개월의 매출을 합한 값 (ex : 2018년 3월 기준 ---> 2017년 2월 ~ 20.. 2022. 5. 12.
[빅쿼리-SQL] 매출 데이터 분석 '한방 정리' 쿼리 매출 데이터를 급하게 분석해 달라는 요구에 신속하게 대응하기 위해서는 기준점이 되는 매출 지표를 알고 있어야 한다. 이번 글에서는 기본적인 매출 지표엔 어떤 것이 있는지 모르는 학생들과, 신속하게 매출 분석을 해야 하는 분석가들을 위해 매출 분석의 기준이 되는 매출 지표를 알아보고, 그것을 SQL 문으로 작성하는 방법을 알아보겠다. 샘플 데이터 캐글-Superstore Sales Dataset을 정제한 데이터 셋입니다. 출처 : https://www.kaggle.com/datasets/rohitsahoo/sales-forecasting 데이터 세트 : ch4 테이블 명 : sale_data Date(date) : 주문 일자 Order_id(string) : 주문 ID Customer_id(string) .. 2022. 5. 9.
[빅쿼리 - SQL]OVER 절과 ORDER BY로 순서 정의하기(row_number,rank,dense_rank, lag,lead 함수 사용) ↓↓↓ 샘플데이터를 아래 결과값처럼 나타내기 위해선 어떤 함수를 어떻게 사용해야 할까? 결과값을 자세히 보면 크게 4 부분으로 나눌 수 있다. 이번 글에서는 위 4부분을 구현하는 방법을 알아보겠다. 1. 기존 테이블의 열들(설명x) 2. 순위를 메기는 열들 row_number 열 : 점수 순위로 유일한 순위를 붙임 ROW_NUMBER() OVER(ORDER BY score DESC) rank 열 : 같은 순위가 있을 때 같은 순위 다음 순위를 건너뛰고 순위를 붙임 RANK() OVER(ORDER BY score DESC) dense_rank열 : 같은 순위를 허용해서 순위를 붙임 dense_rank() OVER(ORDER BY score DESC) row_number, rank, dense_rank의 기.. 2022. 5. 1.
[빅쿼리 - SQL] OVER 절과 PARTITION BY 이번 글에서는 PARTITION BY에 대해서 알아보겠다. 그전에, 이것과 함께 항상 등장하는 OVER 절에 대해서도 알아보겠다. 샘플 데이터 1. OVER 절은 왜 사용하는가? 위와 같은 상황은 (CATEGORY : 단일 칼럼), (SUM(SCORE) : 집계 칼럼)을 함께 사용했기 때문에 발생하는 문제이다. 단일 칼럼은 집계 칼럼과 함께 사용될 수 없기 때문에, 이를 함께 쓰려면 서브 쿼리를 사용해야 한다. 하지만 서브쿼리 때문에 쿼리는 매우 지저분 해진다. 이를 깔끔하게 해결해 주는 것이 바로 over 절! 위와 같은 서브 쿼리를 over 을 사용하여 한방에 깔끔하게 할 수 있다. 그런데, 뭔가 이상하다. 합을 구하려면, category 별로 합을 구해야 어색하지 않은데, 위의 sum(score)o.. 2022. 4. 29.
[빅쿼리-SQL] 테이블 만들기! (create table) 빅쿼리로 데이터를 만들기 위해 일반적인 방법으로 테이블을 만들려다가는 실패를 맛볼 수 있다. 오늘은 빅쿼리로 태이블을 만들기 위한 가장 기본적인 방법을 빠르게 소개해 보겠다. 1. sql 입력 창에서 그냥 만들어본다 ---> 실패 경험 2. 프로젝트 이름 옆의 점 세개 클릭 - 데이터세트 만들기 클릭 빅쿼리는 '데이터 세트' 라는 개념이 있다. 데이터 세트란 테이블의 모아놓은 세트이다. 이때문에 데이터세트를 만들지 않고 테이블을 만들면 오류가 뜨는 것! 3. 데이터세트 id를 만들고 위치 만료설정은 건들이지 안아도 됨! 데이터 세트 ID는 데이터 세트의 이름 - 테이블 집합을 잘 나타낼 수 있는 단어로 적으면 좋다. 테이블 위치는 따로 설정하지 않을 경우 기본값 (US)으로 지정된다. 아마 이 글을 보는.. 2022. 4. 29.
[빅쿼리 - SQL]를 사용하여 IP 데이터 다루기(cast,pow,concat,lpad 함수 사용) 로그데이터는 IP 데이터가 필수적으로 포함되므로, ip 데이터를 자유롭게 핸들링 하는 능력은 매우 중요하다. 이번 시간에는 빅쿼리를 사용하여 ip 데이터를 핸들링하는 3가지 방법을 알아보겠다. 1.ip 주소에서 4개의 10진수 부분을 추출하기 첫번째 방법으로는 ip 주소를 정수 자료형으로 변환한 후 ip 주소에 있는 4개의 10진수 부분(점으로 구분된 각각의 값)을 정수 자료형으로 추출해보겠다. 새로운 함수는 cast 함수이다. cast 함수란 값의 유형을 변환시켜주는 함수이다. cast('기존값' as 변환하고싶은 유형) (위 예에서는 int64로 변환시킴) 2. 추출한 4개의 10진수를 정수 자료형으로 표기하는 법 추출한 정수 자료형을 2^24, 2^16, 2^8, 2^0을 곱한 후 모두 더해주면 정.. 2022. 4. 29.
[빅쿼리 - SQL]를 사용하여 매출 데이터 다루기(case문,sign함수,coalesce,round함수) 데이터를 SQL 을 이용하여 목적에 따라 정리하거나, 레코드의 다른 값을 조합해서 새로운 값을 집계하기 위해 우리는 SQL을 배운다. 그것이 바로 '지표 정의' 이다. 이번 시간에는 샘플 매출 데이터에서 '지표정의'를 하는 여러 방법에 대해 소개하겠다. 샘플 데이터 지표1 - 분기별 매출 증감 판정하기 새로 등장하는 형식인 case문 과 sign 함수. case는 일반적인 if 문이라고 생각하면 편한데, case 문 안에 'when 조건 식' 이 참이면 'then 결과값', 거짓이면 다음 코드로, 모든 조건식이 만족 안될때는 'else 결과값' 도출 end 로 마무리 되는 형식이다. 어렵지 않으니 이해하기는 쉬울 것이라고 생각된다. sign 함수는 값이 양수면 1, 0이면 0, 음수면 -1을 도출하는 함.. 2022. 4. 29.
[빅쿼리 - SQL]를 사용하여 날짜/타임스탬프 다루기(current_data, current_timestamp, substr 함수) 이번 글에서는 지난 시간에 다룬 샘플 로그 데이터 중 stamp 열을 사용해서 시간 데이터를 다루어 보겠다. 시간 데이터의 인스턴스는 'DATE' 와 'TIMESTAMP'로 나뉜다. 이 둘의 차이점을 알기 위해 '현재 날짜'와 '현재 타임스탬프'를 알아보는 함수를 사진으로 결과와 함께 보자 현재 날짜를 나타내는 함수 : CURRENT_DATA 현재 타임 스탬프를 나타내는 함수 : CURRENT_STAMP date 와 time stamp 의 차이를 한눈에 알 수 있었다. 단, 빅쿼리의 경우 타임 스탬프가 UTC 시간을 나타내므로, 한국은 UTC + 9를 해야한다. 그렇다면, 우리의 로우 데이터의 stamp 열은? 로우 데이터의 stamp는 내용물은 타임 스탬프 같았지만, 유형은 사실 string(문자)이었.. 2022. 4. 29.
[빅쿼리 - SQL]를 사용하여 URL에서 요소 추출하기(host, split 함수) 분석 형장에서는 서비스 런칭 때 로그 조건과 분석 요건을 제대로 검토하지 못하고, 일단 최소한의 요건으로 레퍼러와 페이지 URL을 저장해 두는 경우가 있다. 그리고 이후에 저장한 URL을 기반으로 요소들을 추출하는데, 이번 글에서 샘플 데이터를 활용하여 URL에서 요소들을 추출 하는 법을 알아보겠다. 샘플 데이터 1. 레퍼러로 어떤 웹 페이지를 거쳐왔는지 판별하기 어떤 웹페이지를 거쳐 넘어왔는지 판별 할때는 레퍼러를 집계한다. 하지만, 이를 위 샘플 데이터처럼 페이지 단위로 집계하면 밀도가 너무 작아 복잡해지기 쉽다. 따라서 '호스트'단위로 집계하는 것이 일반적인데, 구글 빅쿼리에는 URL에서 '호스트 이름의 패턴'을 추출하는 함수를 사용할 수 있다. 이처럼 net.host 함수를 사용하여 URL 에서 .. 2022. 4. 29.
구글 빅 쿼리(Google BigQuery) 시작하기 # 서론 '데이터 분석을 위한 'SQL 레시피' 도서를 기반하여, 데이터 분석을 위한 시스템으로 구글 빅쿼리를 시작하려는 사람들에게 그 사용법을 A-Z 알려주기 위해 이 글을 작성한다. # 구글 빅쿼리 시작하기 1. 로그인 - 무료로 시작하기 버튼 클릭 2. 개인 정보 및 결제정보 입력 결제 수단을 입력하는 것은 자동 가입을 방지하는 것 일 뿐, 무료 크레딧 사용 기간인 12개월이 지난 후에도 따로 유료 계정으로 변경하지 않으면 추가 결제되지 않습니다. TIP) 학교 아이디로 가입한다면 기존 300 크레딧에 + 100크레딧을 추가로 더 얻을 수 있음! 3. My First Projet 라는 프로젝트가 자동으로 생성되고, Google Cloud Platform(GCP)로 연결됨 ----> 사이드 바의 Bi.. 2022. 4. 18.

티스토리툴바