Study (32) 썸네일형 리스트형 [빅쿼리(BigQuery) - SQL] 계산한 테이블에 이름 붙여 재사용하기(CTE - WITH 구문) 계산한 테이블에 이름 붙여 재사용하기(CTE - WITH 구문) 복잡한 처리를 하는 SQL문을 작성할 때는 서브 쿼리의 중첩이 많아진다. 비슷한 처리를 여러 번 하는 경우도 있는데 이렇게 되면 가독성이 굉장히 낮아진다. 이때 SQL99에서 도입된 공통 테이블 식 (CTE: Common Table Expression)을 사용하면 일시적인 테이블에 이름을 붙여 재사용할 수 있다. 그리고 이를 활용하면 코드의 가독성이 크게 높아진다. 이번에는 다음과 같은 카테고리별 상품 매출 정보를 사용해 카테고리별 순위를 가로로 전개하고, 'DVD', 'CD', 'BOOK' 카테고리의 상품 매출 순위를 한번에 볼 수 있는 형식으로 변환하는 방법을 알아본다. 카테고리별 순위를 윈도 함수로 계산하고, JOIN을 사용해 같은 순.. [빅쿼리(BigQuery) - SQL] 조건 플래그를 0과 1로 표현하기(CASE 식, SIGN 함수) 조건 플래그를 0과 1로 표현하기(CASE 식, SIGN 함수) 여러 개의 테이블을 가로로 정렬하는 방법을 응용해서 마스터 테이블에 다양한 데이터를 집약하고, 마스터 테이블의 속성 조건을 0또는 1이라는 플래그로 표현하는 방법을 소개하겠다. 예를 들어 다음 데이터 예처럼 신용카드 번호를 포함한 마스터 테이블(데이터 1)에 구매 로그 테이블(데이터 2)을 결합해서 사용자들의 '신용카드 번호 등록 여부', '구매 이력 여부'라는 두 가지 조건을 0과 1로 표현하는 방법을 살펴본다. 사용자 마스터에 구매 로그를 결합할 때 LEFT JOIN을 사용한 뒤 사용자 ID로 GROUP BY하면, 사용자 마스터의 레코드 수를 그대로 유지한 상태로 구매 로그 정보를 결합할 수 있다. 이렇게 얻은 테이블을 기반으로 조건 플.. [빅쿼리(BigQuery) - SQL] 여러 개의 테이블을 가로로 정렬하기(LEFT JOIN) 여러 개의 테이블을 가로로 정렬하기(LEFT JOIN) 여러 개의 테이블을 가로로 정렬하여 데이터를 비교하거나 값을 조합하고 싶은 경우도 있다. 예를 들어 [데이터 1]의 카테고리 별 마스터 테이블에 카테고리별 매출 [데이터 2] 또는 카테고리별 상품 매출 순위 [데이터 3]를 기반으로 카테고리 내부에서 가장 잘 팔리는 상품 ID를 모아 테이블로 한 번에 보고싶다고 한다. 여러 개의 테이블을 가로 정렬할 때 가장 일반적인 방법은 JOIN을 사용하는 것이다. 다만 마스터 테이블에 JOIN을 사용하면 결합하지 못한 데이터가 사라지거나, 반대로 중복된 데이터가 발생할 수 있다. 다음 코드 예의 출력 결과는 테이블을 카테고리 ID로 단순하게 결합한 결과이다. 그런데 카테고리 마스터에 존재하는 book 카테고리가.. [빅쿼리(BigQuery) - SQL] 여러 개의 테이블을 세로로 결합하기(UNION ALL 구문) 여러개의 테이블을 세로로 결합하기(UNION ALL 구문) 여러 테이블에 분산된 비슷한 형식의 데이터에 같은 처리를 해야하는 경우가 있다. 예를 들어 다음 데이터 예를 살펴본다. 애플리케이션 1과 애플리케이션 2가 있을 때 각각의 사용자를 저장한 테이블이다. 비슷한 구조를 가지는 테이블의 데이터를 일괄 처리하고 싶은 경우, 다음 코드처럼 UNION ALL 구문을 사용해 여러 개의 테이블을 세로로 결합하면 좋다. 결합할 때는 테이블의 컬럼이 완전히 일치해야 하므로, 한쪽 테이블에만 존재하는 컬럼은 phone 컬럼처럼 SELECT 구문으로 제외하거나 email 컬럼처럼 디폴트 값을 줘야한다. 추가로 결합후의 데이터가 어떤 테이블의 데이터였는지 식별할 수 있게 app_name이라는 열을 추가했다. UNION .. [빅쿼리(BigQuery) - SQL] 가로 기반 데이터를 세로 기반으로 변환하기(unnest 함수, CROSS JOIN) 가로 기반 데이터를 세로 기반으로 변환하기 (unnest 함수, CROSS JOIN) 레코드로 저장된 세로 기반 데이터를 가로 기반으로 변환하는 것은 비교적 간단하지만, 반대로 가로 기반 데이터를 세로 기반으로 변환하는 것은 간단한 일이 아니다. 하지만 이미 데이터가 쉼표로 구분된 열 기반의 형식으로 저장되어서, 이를 분석하기 위해 어쩔 수 없이 변환해야 하는 경우가 꽤 많다. 이번에는 세로 기반 데이터를 가로 기반으로 변환해서 가공이 쉬운 데이터 형식으로 만들어 본다. 열로 표현된 값을 행으로 변환하기 가로 기반 데이터의 예로 4분기 매출 테이블을 사용한다. 이 테이블은 매년 4분기 매출(q1부터 q4)을 하나의 레코드로 가진다. 이 테이블을 '연도', '4분기 레이블', '매출'이라는 3개의 컬럼을 .. [빅쿼리(BigQuery) - SQL] 세로 기반 데이터를 가로 기반으로 변환하기(MAX 구문, string_agg 함수) 세로 기반 데이터를 가로 기반으로 변환하기 (MAX 구문, string_agg 함수) SQL은 행(레코드) 기반으로 처리하는 것이 기본이다. 따라서 데이터를 저장할 때 최대한 데이터를 행으로 분할해서 저장하는 것이 좋다. 하지만 최종 출력에서는 데이터를 열로 전개해야 가독성이 높은 경우가 많다. 이번에는 행 단위로 저장된 '세로 기반'을, 열 또는 쉼표로 구분된 문자열 등의 '가로 기반'으로 변환하는 방법을 설명하겠다. 행을 열로 변환하기 일단 행을 열로 변환하는 방법을 생각해본다. SQL에서 열은 고정적이어야 한다. 따라서 열로 전개할 데이터의 종류 또는 수를 명확하게 미리 알고 있어야만 지금부터 설명하는 방법을 사용할 수 있다. 예를 들어 다음 데이터는 날짜별 KPI 데이터이다. 날짜별로 '노출(im.. [빅쿼리(BigQuery) - SQL] 그룹 내부의 순서(윈도 함수, OVER 구문) 그룹 내부의 순서(윈도 함수, OVER 구문) 윈도 함수를 활용해 데이터를 가공하는 법을 알아본다. SQL의 테이블은 기본적으로 순서라는 개념이 없다. 따라서 SQL로 순위를 작성하거나 시간 순서로 데이터를 다루려면 복잡한 방법을 사용해야 했다. 하지만 윈도 함수가 등장하면서 SQL로 순서를 다루는 것이 굉장히 쉬어졌다. 여기서는 인기 상품의 상품 ID, 카테고리, 스코어 정보를 가진 인기 상품 테이블을 샘플로 SQL를 사용한 순위 작성, 순서를 고려한 계산 실행 방법 등을 소개한다. ORDER BY 구문과 집약 함수 조합하기 ORDER_BY 구문과 SUM/AVG 등의 집약 함수를 조합하면, 집약 함수의 적용 범위를 유연하게 지정할 수 있다. 다음 코드 예는 ORDER BY 구문과 집약 함수를 조합해서 .. [빅쿼리(BigQuery) - SQL] 그룹의 특징 잡기(집약 함수, GROUP BY 구문, 윈도 함수, OVER 구문) 그룹의 특징 잡기 (집약 함수, GROUP BY 구문, 윈도 함수, OVER 구문) 집약 함수를 사용하는 방법을 알아본다. 집약 함수란 여러 레코드를 기반으로 하나의 값을 리턴하는 함수이다. 예를 들어 모든 레코드의 수를 리턴해주는 COUNT 함수, 값의 합계를 리턴해주는 SUM 함수 등이 있다. 다음 데이터 예는 상품 평가 테이블이다. 상품(product_id)에 대한 사용자 평가(score)가 저장된다. 여기서는 이 테이블을 사용해서 SUM 함수와 AVG 함수 등의 집약 함수를 사용하는 방법에 대해 알아본다. 테이블 전체의 특징량 계산하기 다음 코드 예는 자주 사용되는 집약 함수를 위 테이블에 적용하는 쿼리이다. COUNT 함수는 지정한 컬럼의 레코드를 리턴하는 함수이다. 컬럼 이름 앞에 DISTIN.. 이전 1 2 3 4 다음