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 ..

필자는 앞서, 개념적 설계를 바탕으로 논리적 설계를 해보았다. 즉, 구체화된 업무(=컬럼, 필요한 부분) 중심의 데이터 모델을 만드는 것 = '논리적 설계' 라고 할 수 있다. 이때, 우리가 흔히 알고있는 PK(Primary Key)와 외래키, 속성, 관계 등을 정의 하며, 정규화를 함께 한다. (다들 이론은 알지만, 정규화 부분을 실제로 접목하는 부분은 쉽지 않은 것 같다.) 여기서, 필요한 컬럼을 구체적인 네이밍과, 데이터 타입을 명시한다. 이를 바탕으로 테이블의 Key 도 표기해준다. 필자는 아래와 같이 간단하게 1대 다를 표현해보았다. 전부를 표기한 건 아니지만 대략적으로 기존 테이블에서 다른 테이블에 영향을 미친다. 즉 관계를 알 수 있으며, 구체적인 데이터 타입 까지 명시하였다. 위와 같이 설..

이전에, 요구사항 분석을 했다면 이를 바탕으로 개념적 설계가 필요하다. 크게, 나의 기준에서는 2가지 순서대로 진행하였다. 1. 핵심 Entity 도출 2. ERD 작성 [엔터티 설계 방법] 기획안을 보며 모든 키워드를 뽑아낸다 뽑아낸 키워드를 행위와 데이터로 나눈다. 행위와 데이터는 각각 행위 엔터티, 실체 엔터티로 매핑된다. 다만 모든 행위나 데이터가 DB에 담겨야 하는 것은 아니다. 서버에서 ENUM으로 관리하는 데이터도 있을 수 있다. 2-a. 에서 설계한 엔터티에 관계를 매핑한다. → 관계는 속성! 속성이 필요한 이유는 엔터티 간의 조인을 하기 위해서! 위를 기반으로, 이번 프로젝트에 적용했을 때 이상치 & 결측치 필터링 테이블 (region, area 기준) 1분 데이터 (API 호출 조회) ..

필자는 이번에 백엔드 전체를 맡게 되면서, DB설계도 해보았다. (물론, 테이블의 개수가 엄청 많지는 않지만, 배치 파이프 라인을 통해 진행 했기 때문에 덕분에 DB에 대한 두려움을 덜게 되었던 것 같다.) 먼저, 어떻게 하면 좋을지 막막하여, 요구 사항 수집 분석 부터 시작해보았다. 여기서, 가장 중요한 것 2가지를 추려보았다. 1. 어떤 시스템을 구축할 것 인가? 2. 요구 사항을 충족하기 위해서 어떤 데이터가 필요하며, 어떤 기능들이 추가되어야 하는가? -> 이것을 파악하기 위해서는 만들고자 하는 서비스의 목적과 정보에 대한 분석이 중요하다는 것을 깨닫게 되었다! [ 진행하려는 프로젝트의 큰 핵심은 무엇인가?] -> 스마트팜의 경우, 실시간 적재되는 데이터(온도,습도, 풍향, 등등) 의 모니터링과 ..

필자는 화면에서 현재 시간을 기준으로 -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..