With 절 2개 사용하여, 필터링 쿼리 만들기

필자는 새로운 프로젝트를 진행하며, 처음으로 백엔드 전체를 혼자 다 맡게되었다.

어떤 to do list를 했는지에 관해서는 다른 포스팅에서 깊게 하도록 하고 처음으로 끙끙대면서 혼자 만든 쿼리를 기록하고자 한다.

 

기존 설계되어 있던 테이블 + 새로 만든 테이블을 조합하여 이상치를 필터링 하는 로직을 적용했다.

 

1. ten_minute_batch 라는 테이블은 10분 단위로 값들을 배치하는 테이블

2. sensor_check_test 라는 테이블은 오로지 이상치를 설정하고, 이것의 히스토리를 가지고 있는 테이블

 

내가 필터링 하고자 하는 로직 = 2번 테이블(이상치 설정)을 기점으로 1번 테이블의 각 컬럼 값이 min, max 사이에 존재하는가?

그리고, 컬럼에 대한 값이 min 보다 작을 경우 -> under로 표기, max 보다 클 경우 over로 표기될 수 있도록 쿼리 값이 나오게 하는 것이다.

 

※ 여기서 주의해야 할 것은, 원래 테이블에 PK key 혹은 Key값이 따로 없고, 연결 가능한 것은 지역&구역이 다였기 때문에 -> 향후 새로운 테이블에 이상치를  Insert 하는 부분에서 많은 쿼리 호출이 필요했다. -> 기본 테이블의 중요성에 대해 깨달음

 

본론으로 와서 다른 프로젝트를 하며, With 절에 대해 접한 적이 있었고, 기억을 떠올려 연관 없는 테이블 두개를 붙여, 필터링 하기로 결심

 

-- 현재, 10분 단위의 값
with now_data as 
(select *
from (select save_dt, region, area, {element}
    from smartfarm.ten_minute_batchtest2 
    where substr(save_dt,1,10) = '2023-11-07' -- CURDATE()
    and substr(save_dt,12,2) = '15') as data -- DATE_FORMAT(now(),'%H')) data
where substr(data.save_dt ,15,2) = '20'
and region = 'test' and area = '1'  -- where option 으로 변경 
group by  save_dt, region, area, {element}
)

-- min, max 값 포함
, recent as (select col_name, min_range, max_range,save_dt
from sensor_check_test
where (col_name ,save_dt) 
in (select col_name, max(save_dt) as max_date
    from sensor_check_test
    group by col_name)
)
-- 필터링 부분 
select b.col_name, a.region, a.area, a.{element} as value, b.min_range, b.max_range,
        CASE WHEN a.{element} < b.min_range THEN 'under'
            WHEN a.{element} > b.max_range THEN 'over'
            ELSE 'n' END AS alarm
from now_data a, recent b 
where a.region = 'test' and a.area ='1'  -- where option 으로 변경 
and b.col_name = '{element}'

 

< with 절 사용법 >

1. with 절을 사용할 때는 with 선언후, 별칭을 붙여 뒤에 서브 쿼리 처럼 사용하면 -> 임시 테이블처럼 사용할 수 있다.

2. with 절을 2개 사용하고 싶을 때는 with a as () , b as () 이런 식으로  " , " (콤마) 추가 한 후 생성하고자 하는 서브쿼리를 만들면 된다.

 

 <쿼리 설명>

1. now_data - 현재, 10 분 단위의 test의 1 구역에서 하나의 element의 값을 가져온다

2. recent - 이상치를 설정한 테이블에서, test의 1 구역에서 element의 가장 최근 이상치 값을 가져온다.

3. now_data와 recent에서 필요한 값을 뽑아와서, element의 범위가 min, max 사이에 있는지 판별( 이때, case ~ when 문을 사용하여, 결과 값을 under, over로 표기 할 수 있도록)

 

이렇게 한줄씩 나오는 값을 region, area 그리고 element를 적용하여(for문) -> 이상치 테이블에 Insert 까지 파이프 라인을 만들었다.

 파이프 라인 부분은 다음 포스팅에서 계속하겠다!