Happiness can be found even in the dark Happiness can be found even in the dark

현재 프로젝트의 쿼리를 직접 만들면서, 쿼리와 DB에 관해 좀 더 깊이 공부해보고 싶은 생각이 생겼다. 기존 쿼리 속도를 높이는 튜닝도 차차 진행하려고 한다. 그 전에 sql에서도 함수가 존재한다는 사실을 최근에 알게되었고, 이 부분을 공부하여 기록하고자 한다. ※주의해야 할 점 -> DBeaver 같은 데이터베이스 관리도구에서는 function을 만들 수 없다(mariadb 같은 경우) 필자의 경우, HeidiSQL에 접속 -> 데이터베이스 -> 새로생성 -> 저장함수를 순서대로 클릭한다. 클릭을 하면 아래와 같은 화면이 나온다. HeidiSQL은 친절하게도, 옵션, 매개변수, create 코드로 분리되어 있어서, 처음이라도 쉽게 만들 수 있었다. 이름 : 함수 이름 기입 유형 : 함수(결과 반환) 으..

필자는 이 전에 다루었던 union all의 원리를 이용해서, with절 여러개를 만들어 하나의 결과로 만드는 쿼리를 만들었다. 그 전에 다루었던 공통 테이블식을 사용하였고, 테이블은 다르지만 지정된 구역의 값의 개수를 count하여 집계하여 보는 것이 나의 목표였다. 1. 현재 날짜, 시간 기준이다. 2. 이상치 혹은 결측치에 대한 값을 집계한다. 3. 만약 이 구역에서 집계된 부분이 0 이면 0으로 결과값이 나타나도록 한다. 위를 만족하는 쿼리를 만들기 위해 5번 정도(?) 수정이 필요했지만, 결국에는 완성되었다. 완성된 쿼리는 아래를 참고하자. WITH all_combinations_alarm AS ( SELECT DISTINCT region, null as area, con_type FROM te..

필자는 간단하게, 각각의 테이블에서 집계를 한 결과를 한번의 결과로 보여주는 쿼리가 필요하게 되었다. 보통 join이 가능하면, Left join을 많이사용하지만, key값이 없는 관계로 union all을 사용했다. 여기서! union all vs union의 차이를 말하자면 간단하다. - union all : 중복되는 결과 값이 있더라도 다 보여주기 - union : 중복되는 결과 값을 제거하고 보여주기 이제, 아래 쿼리를 보자 SELECT * FROM ( SELECT * FROM test.al_batch_table WHERE substr(save_dt, 1, 10) BETWEEN '2024-01-30' AND '2024-01-30' AND region = 'a' AND area = '1' ) as ..

필자는 화면에서 현재 시간을 기준으로 -5시간 전의 각 한시간별 집계를 구하는 쿼리를 만들었다. [화면에서 보여주고 싶은 모습은 아래의 그림과 같다] 여기서 조금 힘들었던 점은, 만약 -2시간전 집계 값이 정말로 0일때 -> -2시간에 값은 "0" 이라는 결과 값을 쿼리 결과로 만드는 부분이었다. 하지만, 정말 이것 저것 붙여서 만든 결과 성공하였다. 지금부터 이 쿼리에 대한 리뷰를 하려 한다. 1. 현재 시간 기준 -5시간 전까지 1시간 단위로 이상치 건수 집계 2. region, area 지역에 따라 -> 경영, 교육, 임대 등의 카테고리 묶음 3. 결측치와 이상치를 각각 집계 및 전체 값 만들기 4. 결측치 및 이상치 집계가 0일 경우, 그 시간대를 'No data'로 쿼리 결과값이 나오도록 할 것..

필자는 새로운 프로젝트를 진행하며, 처음으로 백엔드 전체를 혼자 다 맡게되었다. 어떤 to do list를 했는지에 관해서는 다른 포스팅에서 깊게 하도록 하고 처음으로 끙끙대면서 혼자 만든 쿼리를 기록하고자 한다. 기존 설계되어 있던 테이블 + 새로 만든 테이블을 조합하여 이상치를 필터링 하는 로직을 적용했다. 1. ten_minute_batch 라는 테이블은 10분 단위로 값들을 배치하는 테이블 2. sensor_check_test 라는 테이블은 오로지 이상치를 설정하고, 이것의 히스토리를 가지고 있는 테이블 내가 필터링 하고자 하는 로직 = 2번 테이블(이상치 설정)을 기점으로 1번 테이블의 각 컬럼 값이 min, max 사이에 존재하는가? 그리고, 컬럼에 대한 값이 min 보다 작을 경우 -> un..

필자는 실거래가 이루어진 데이터를 open api를 통해 bigquery에 적재하였다. 조건을 넣어, 2023년 서울 실거래되었고, 지어진 지 2010년 이상 된 전세, 아파트의 평균 거래 값과 등수를 나열해보는 쿼리를 만들어 보았다. SELECT ACC_YEAR ,SGG_NM ,round(avg(CAST(RENT_GTN as INT64)),1) as avgofrent FROM `sy-gcp-project.analytics.payment_2023` WHERE HOUSE_GBN_NM ='아파트' AND RENT_GBN = '전세' AND BUILD_YEAR like '201%' GROUP BY 1,2 ORDER BY 3 DESC 여기서 사용했던 함수는 3가지가 있다. 1. round 함수는 반올림을 첫째 ..

필자는 다른 테이블과 조인을 하기 위해서 날짜 컬럼(=숫자열) 에서 년도만 가져 오려 한다. 이때, 숫자로 된 컬럼을 1차로 문자열로 바꿔주는 함수가 빅쿼리에서는 CAST이다. 사용 방법은 아래와 같이 간단하다. CAST( 컬럼 as string) 이런 식으로 사용하면 된다. 이후, format_datetime을 사용하려 했으나, 날짜 컬럼이 되어 있어야 함으로 pass SQL에서 공통적으로 사용되는 substr을 이용하였다. SUBSTR(CAST(use_date as string), 1,4) as year로 변경해주었다. 이는 substr에서 1에서 4번째 까지의 문자열을 가져 온다는 뜻이다. SELECT SUBSTR(CAST(use_date as string),1,4) as year ,line_nam..

필자는 공공데이터에서 받은 서울시 지하철 데이터를 이용하여, 조건문이 들어간 데이터를 뽑으려고 한다. 궁금했던 점은 2023년 상반기 동안 집과 가까운 2호선 홍대입구역에 얼마나 많은 사람들이 타고 내리는지 중점을 두었다. 그리고, 탄 사람보다 내린 사람이 더 많은 날을 집계하는 쿼리를 만들어 보았다. SELECT use_date,line_num, line_name, sum(total_num) as total_num, sum(take_off_num) as tot_take_off FROM `{gcp 프로젝트이름}.{데이터세트 ID}.card_subway_month_total` WHERE total_num