데이터베이스/SQL11

• 

  [SQL 뉴비 가이드북] 2.SQL 쿼리로 게임 업계 지표 추출하기 1탄.AU편 (DAU, WAU, MAU) 게임업계를 희망하는 데이터 분석가들을 위해, SQL로 게임 업계에서 정말 많이 쓰는 주요 지표들을 추출하는 방법에 대해 작성하려고 한다. 정말 많은 지표가 있는데, 그 중에서도 가장 많이 쓰인다고 생각하는 DAU, WAU, MAU에 다뤄보겠다. 쿼리는 빅쿼리 기준으로 작성하였다.  추후 PU , NPU, ARPPU 등을 다루는 PU 편도 작성할 예정!  0.AU Active User, 활성 사용자 수 : 기간 동안 게임/앱을 사용한 유저의 유니크 카운트 여기서 Active라는걸 어떻게 정의하냐에 따라 다르지만, 내가 일했던 모든 회사들은 로그인 기준으로 산정했기 때문에 해당 글 또한 모두 로그인 기준으로 작성합니당. 1.DAU Daily Active User , 일일 활성 사용자 수 : 기준일자에 접속한.. 데이터베이스/SQL 2024. 11. 26.

• 

  [SQL 뉴비 가이드북] 1.로그인 로그에서 SQL로 리텐션 뽑기 SQL 뉴비 가이드북의 첫 탄! 내가 첫 회사 입사 후 맡은 첫 업무였기도 하고, SELECT FROM WHERE JOIN만 알던 나에게 지옥을 선사해준 리텐션이다. 해보기 전엔 어떻게 하는지 감이 안오지만, 차근차근 따라오면 정말 쉽게 뽑을 수 있다. 그럼 함께 지표의 아주 아주 기본이 되는 리텐션을 로그인 로그를 바탕으로 뽑아보자. SQL은 모두 빅쿼리 기준!0.리텐션 고객 유지로, 고객이나 사용자가 특정 기간 동안 어떤 제품이나 서비스에 계속 관여하거나 이용하는 비율이다. 이걸 보면 이해하기가 쉬운데, 첫줄 기준으로 모든 기간에 유입한 유저들이 Day0에는 41만명이었으나, 하루가 지나면 그 중 16%인 6만명, 이틀이 지나면 첫 41만명의 11%인 약 5만명이 잔존해있다는걸 보기 쉽게 표기한 것이.. 데이터베이스/SQL 2024. 10. 2.

• 

  [SQL 뉴비 가이드북] 0.컨텐츠 아카이브 SQL을 공부하게 되면, SELECT , FROM , WHERE, JOIN 등으로 시작할 것이다. 하지만 이런 예쁜 문법만 쓰는 코딩테스트의 문제는 실제 현업과 좀 다르다.코테에서 리텐션을 구하는 문제를 본 적이 있는가..? 프로그램 코테는 최적화를 시키는데 SQL 코테는 왜 안그럴까? 이처럼 SQL 문법은 익혔는데, 업무에선 어떤 쿼리를 작성할지 잘 모르는  주니어들을 위한케이스 별 쿼리 작성법에 대한 가이드를 기록해보려고 한다.  나중에 SQL 어디서 배워? 라고 했을 때 이 블로그를 전달해주는 그날까지 차곡차곡 쌓아나가보자!     [SQL 뉴비 가이드북] 1.로그인 로그에서 SQL로 리텐션 뽑기SQL 뉴비 가이드북의 첫 탄! 내가 첫 회사 입사 후 맡은 첫 업무였기도 하고, SELECT FROM .. 데이터베이스/SQL 2024. 10. 2.

• 

  [MSSQL] MSSQL에서 MERGE 구문과 WITH절 같이 쓰기 Biguqery에서는 Merge 구문 안에 With절을 사용해도 정상적으로 작동한다.Bigquery ver MERGE INTO `` AS targetUSING (WITH signup_log AS (쿼리를 입력하세요)SELECT a, FROM signup_log) AS source ON target.date = source.dateWHEN MATCHED THEN UPDATE SET target.dau = source.dau,WHEN NOT MATCHED THEN INSERT (date) VALUES (source.date); 그러나, MSSQL에서 이렇게 사용해버리면 With절이 옳지 않다는 오류가 발생하는데, 순서를 바꿔주면 가볍게 해결가능하다  MSSQL에서 이렇게 사용하세요Merge 구문을 사용하기.. 데이터베이스/SQL 2024. 8. 7.

• 

  [Bigquery] 빅쿼리 SELECT * REPLACE 빅쿼리에서는 일부 컬럼만 바꿔주는 기능이 있다. 바로 이것이다. SELECT * REPLACE(원하는 데이터 AS 바꿀 데이터)-- A컬럼을 모두 A+1로 바꾸고 싶으면SELECT * REPLACE(A+1 AS A) -- 한번에 다중 컬럼을 변경하고 싶으면 SELECT * REPLACE(A+1 AS A, B+1 AS B)  아래 빅쿼리 공공 데이터를 통해 SELECT * 을 해보면 다음과 같은 결과가 출력된다. SELECT * FROM `bigquery-public-data`.utility_us.zipcode_area WHERE zipcode='87505' 내가 이 중  zipcode 컬럼에 속한 데이터를 1로 다 바꾸고 싶다면 이렇게 써주면 된다SELECT * REPLACE('1' AS zipcod.. 데이터베이스/SQL 2024. 6. 26.

• 

  [MSSQL] 해당 날짜 월의 1일에 해당하는 날짜 추출하기 / 월 추출 주 추출 👇 [MSSQL] 해당 날짜 주차의 월요일에 해당하는 날짜 추출하기빅쿼리에서는 DATE_TRUNC(date,week(monday))로 엄청 편하게 쓰는데 MsSQL은 조금 복잡하다.SET DATEFIRST 1;을 사용해주는게 제일 편리해서 나는 이 방법으로 사용하고 있다.  *SET DATEFIRST란? 주에서 첫번째sseozytank.tistory.com  앞선 포스팅과 마찬가지로, 빅쿼리에서는 DATE_TRUNC(month,date) 로 할 수 있는 것을 MSSQL에선 조금 복잡하게 해줘야 한다. 어렵진 않은데 모르면 만들어 내기는 힘든..  1번. 연과 월을 추출해서 포맷팅해주기SELECT DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1) AS .. 데이터베이스/SQL 2024. 6. 12.

• 

  [MSSQL] 해당 날짜 주차의 월요일에 해당하는 날짜 추출하기 빅쿼리에서는 DATE_TRUNC(date,week(monday))로 엄청 편하게 쓰는데 MsSQL은 조금 복잡하다.SET DATEFIRST 1;을 사용해주는게 제일 편리해서 나는 이 방법으로 사용하고 있다.  *SET DATEFIRST란? 주에서 첫번째 시작일을 지정해주는 것 월화수목금토일1234567  오늘이 2024-06-12라고 했을 때, MsSQL에서 해당 날짜 주차의 월요일에 해당되는 2024-06-10을 뽑는 쿼리SET DATEFIRST 1;SELECT CONVERT(DATE, DATEADD(DAY, 1 - DATEPART(WEEKDAY, GETDATE()), GETDATE())) 따라서 사용할 때는 GETDATE() 부분을 내가 사용할 날짜 컬럼으로 바꾸어주면 된다! 데이터베이스/SQL 2024. 6. 12.

• 

  [MSSQL] SQL Server에서 SAFE_DIVIDE 사용하기 Divide by zero error encountered 이 오류는 분모가 0이 들어갈 때 나누기를 시도할 때 발생하는 에러임!  빅쿼리 같은 경우 SAFE_DIVIDE라는 함수가 있어서 아주 편하게 사용할 수 있는데 ... 😥SELECT IFNULL(SAFE_DIVIDE(a,b),0) AS safe_exampleFROM sample_table MsSQL에서 사용할려하니 당연히 없는 함수라 뜬다. 그럼 어떻게 사용하면 될까 ? 1.case when 으로 0일 때 null 처리 해주기. SELECT CASE WHEN b=0 THEN null ELSE a / b END AS sample FROM sample_table 분모가 0이 될 때, case when으로 null로.. 데이터베이스/SQL 2024. 6. 10.

• 

  [Bigquery] Merge into 사용 가이드 1.Merge란? Bigquery에는 Upsert가 없고, Merge라는 것이 존재한다.이름하고 사용법만 조금 다르지, 개념은 완전 똑같다. *Upsert : Update + Insert의 개념으로 , 대상 테이블에 기존 데이터가 있는 경우 해당 데이터를 업데이트 하고, 없는 경우에는 새 데이터를 삽입 *Merge : 조건에 따라 대상 테이블의 레코드를 업데이트 하거나 삽입 2.Bigquery Merge 기본 구문 및 실제 예시 MERGE INTO target_table AS target -- Merge를 수행할 대상 테이블 USING source_table AS source -- Merge에 사용할 소스 데이터 ON target.key_column = source.key_column -- 대상 테이블과.. 데이터베이스/SQL 2023. 9. 1.

• 

  [Bigquery] Bigquery SQL로 ML 모델링 A to Z Bigquery로 간단한 ML 모델링이 되는지 처음 알았다. 쿼리를 통해 모델링, 평가, 적용까지 모두 가능한 작업인데 Docs에 나온대로 샘플로 진행을 해보고, 회사 데이터를 가지고 가입 후 3일간 유저 행동을 보고 이 유저가 이후 이탈할 유저인지 아닌지 예측하는 Random Forest 이진 분류 모델까지 만들어보았다. 만들면서 느꼈던 점은, [장점] - python에 비해 훨씬 간단하다. - SQL 기반이라 프로그래밍 언어를 모르는 사람도 쉽게 접근 가능 - 데이터를 이동할 필요가 없다. - Bigquery기 때문에 아무래도 확장성이 유연하고, 대용량 데이터에서 효율적이다. (Python으로도 할 수는 있겠지만, 매일 들어오는 데이터에 대해 예약된 쿼리로 ML을 돌려 Precdiction 테이블 인.. 데이터베이스/SQL 2023. 8. 23.

• 

  [BigQuery] 중첩된 필드 조회 (event_params.key.value) 맨날 postgresql이나 MySQL만 쓰다가 빅쿼리를 처음 써보니 생소한 데이터 형식에 쿼리를 치자마자 오류를 만날 수 있었다. 난 단지 이걸 불러오고 싶어서 단순히 저걸 셀렉트해왔지.. 하지만 GA의 데이터 구조는 약간 달라서 쿼리가 실행되지 않았다 ㅜㅜ SELECT event_params.value.string_value FROM `dataset.tablename.events_20230118` LIMIT 1000 GA는 다음과 같이 계층형 구조로 이루어져있어서,위처럼 쿼리를 날리면 위같은 에러가 뜬다 . record 타입를 가진 필드가 repeated 모드(array) 까지 있어서 hits의 자식인 hits.page 필드는 select 쿼리가 안된다. struct 타입 + array 타입 (인용 .. 데이터베이스/SQL 2023. 1. 19.