Wiki/SQL (8) 썸네일형 리스트형 Spark SQL에서 두 날짜의 차이 구하기(DATETIME, TIMESTAMP) Spark SQL에서 두 날짜의 차이를 구하는 방법에 관한 글입니다. 배경 Spark SQL 함수에는 DATE 끼리의 차이를 구하는 함수는 있습니다(DATE_SUB). 하지만, TIMESTAMP(DATETIME) 끼리의 차이를 구하는 함수는 없습니다. 따라서 다른 방법으로 우회해서 구하는 수밖에 없습니다. 참고로 DATE와 TIMESTAMP의 차이는 아래와 같습니다. DATE : 2022-09-01 TIMESTAMP(DATETIME) : 2022-09-01 00:00:00 UNIX_TIMESTAMP로 두 날짜의 차이 구하기 결론부터 말하자면, 두 DATETIME을 UNIX 시간으로 변환해준 후 차이를 구해야 합니다. SELECT UNIX_TIMESTAMP(시간1) - UNIX_TIMESTAMP(시간2) .. Spark SQL에서 배열을 풀고 요소의 인덱스 구하기 Spark SQL에서 배열을 풀고 요소의 인덱스를 구하는 방법에 관한 글입니다. EXPLODE, POSEXPLODE 함수를 활용합니다. 문제 필요에 따라 테이블에 데이터를 배열 형태로 저장할 때가 있습니다. 하지만, 배열로 된 데이터를 바로 분석에 활용하는 것은 어려움이 많습니다. 분석을 쉽게 하기 위해 배열을 푸는 방법을 알고 활용하는 것이 중요합니다. 해결 방법 1. EXPLODE 배열의 요소를 풀어서 Long Data로 만들어주는 함수입니다. 단, 하나의 서브쿼리 안에서 EXPLODE 함수는 한번만 사용 가능합니다. 아래 예제와 같이 skill 컬럼을 EXPLODE 함수로 풀어주면, 요소의 개수만큼 행이 생기게 됩니다. 2. POSEXPLODE EXPLODE와 동작하는 방식은 동일합니다. 하지만, .. BigQuery의 FORMAT_DATETIME 함수 사용 방법 빅쿼리의 FORMAT_DATETIME 함수에 관한 글입니다. FORMAT_DATETIME 함수는 빅쿼리에서 가장 많이 사용되는 함수 중 하나입니다. FORMAT_DATETIME은 어떤 함수인가요? DATETIME 데이터의 형식을 변환하는 함수입니다. 이 함수를 이용하여 초, 분, 시간, 일자, 주차 등 원하는 형태로 데이터를 가공할 수 있습니다. 입력값의 데이터 형태는 DATETIME이어야 하고, 반환값은 문자열 형태로 반환됩니다. Input / Output 입력값의 데이터 형태는 DATETIME이어야 하고, 반환값은 문자열 형태로 반환됩니다. Input : DATETIME Output : STRING 어떻게 사용해야 하나요? 아래의 방식으로 사용할 수 있습니다. FORMAT_DATETIME(format.. 빅쿼리 내 모든 테이블 및 컬럼 리스트 추출하기 빅쿼리에서 특정 프로젝트, 데이터셋에서 모든 테이블들과 컬럼들의 리스트를 추출하는 방법에 관한 글입니다. 1. 문제 우리 팀이 빅쿼리에서 관리하는 모든 테이블들의 스키마를 작성해야 하는 문제가 있었다. 일일이 타이핑하자니 시간이 너무 오래 걸릴 것 같았다. 찾아보니 많은 DBMS에서 테이블과 컬럼을 추출하는 기능을 지원하고 있었다. 2. 해결 방법 2.1 테이블명 추출하기 특정 데이터셋의 테이블들을 모두 추출하고 싶다면, 아래와 같은 방식으로 할 수 있다. #standardsql SELECT * FROM `[project-name].[dataset]`.INFORMATION_SCHEMA.TABLES ORDER BY table_name; 쿼리 실행 시 결과물 table_catalog : 프로젝트 이름 tab.. 구글 빅쿼리에서 정규표현식 함수 REGEXP_CONTAINS를 사용하여 문자열 필터링하기 빅쿼리 정규표현식 함수인 REGEXP_CONTAINS를 사용하여 값을 필터링하는 방법에 대한 글입니다. 소개 문자열을 다루다보면, LIKE만으로는 필터링하기 어려운 문자열들이 있습니다. 그럴 때 빅쿼리에서 사용할 수 있는 것이 REGEXP_CONTAINS 함수입니다. 사용법 아래 코드블럭과 같이 사용할 수 있습니다. 정규표현식은 정규표현식 라이브러리인 re2를 지원합니다. 따라서 re2에서 지원하지 않는 정규표현식 문법들은 이 함수에서도 쓸 수 없습니다. 이 함수는 SELECT문과 WHERE문에서 쓸 수 있습니다. SELECT문에서 쓰면, TRUE 또는 FALSE의 형태로 반환되고, WHERE문에서 쓰면, 쿼리 결과에서 정규표현식을 만족시키는 값들만 반환합니다. REGEXP_CONTAINS(문자열, 정규.. [빅쿼리] 지원하지 않는 GROUP_CONCAT 함수 우회적으로 사용하기(STRING_AGG, PARTITION) 문제 빅쿼리에서는 GROUP_CONCAT 함수를 지원하지 않는다. 하지만 난 GROUP_CONCAT 같은 함수가 필요했다. 해결 방법 GROUP_CONCAT과 비슷한 기능을 하는 함수는 찾을 수 없었다. 결국 다른 함수들을 조합해서 이를 해결했다. SELECT STRING_AGG(emp_name), ', ') OVER(PARTITION BY salary) 해석하자면, 연봉(salary)이 같은 그룹별로 이름을 가져와서 콤마(',')를 구분자로 하여 문자열을 결합하는 방식이다. [빅쿼리] 지난달의 데이터만 추출하기(EXTRACT, CURRENT_DATE) 문제 추출 시점을 기준으로 지난 달의 데이터만 추출하고 싶은 경우다. 해결 방법 SELECT date FROM table WHERE EXTRACT(MONTH FROM date) = (EXTRACT(MONTH FROM CURRENT\_DATE('Asia/Seoul')) - 1) 해석하자면, 아래와 같다. 먼저 EXTRACT 함수를 이용해 date 컬럼에서 월을 추출한다. 그 다음 CURRENT_DATE와 EXTRACT 함수를 이용해 현재 일자의 월을 가져와서 1을 뺀다. 각 로우별로 2번에 해당하는 값만 가져오도록 설정하면, 지난 달의 데이터만 가져오게 된다. SELECT절에서 조건에 맞는 유니크한 값 구하기(COUNT, IF, DISTINCT) 문제 SELECT절에서 조건에 맞는 유니크한 값을 구하려고 한다. 즉, COUNT와 IF와 DISTINCT를 같이 쓰고 싶다. 예를 들어 가격(price)이 100을 초과하는 유니크한 item_id들을 가져오려고 한다. 시도한 방법 처음 시도한 방법은 아래와 같다. SELECT COUNT(IF(price > 100, DISTINCT item_id, NULL)) 하지만 오류가 발생했고, 아래와 같이 해결했다. 해결 방법 생각해보면, 조건에 맞는 값만 가져와서 중복 여부를 판단하는 것이니 아래 방법이 맞기도 하고 더 직관적이기도 하다. SELECT COUNT(DISTINCT IF(price > 100, item_id, NULL)) 참고 문서 스택 오버 플로우 문서
