4개의 with절과 union all을 사용해서 집계 결과 만들기

필자는 이 전에 다루었던 union all의 원리를 이용해서, with절 여러개를 만들어 하나의 결과로 만드는 쿼리를 만들었다.

 

그 전에 다루었던 공통 테이블식을 사용하였고, 테이블은 다르지만 지정된 구역의 값의 개수를 count하여 집계하여 보는 것이 나의 목표였다.

 

<필요한 조건>

1.  현재 날짜, 시간 기준이다.

2.  이상치 혹은 결측치에 대한 값을 집계한다.

3.  만약 이 구역에서 집계된 부분이 0 이면 0으로 결과값이 나타나도록 한다. 

 

위를 만족하는 쿼리를 만들기 위해 5번 정도(?) 수정이 필요했지만, 결국에는 완성되었다.

완성된 쿼리는 아래를 참고하자.

 

WITH all_combinations_alarm AS (
    SELECT DISTINCT region, null as area, con_type
    FROM test.hogen_batch_table
    WHERE region = 'edu1_5'
),
total_alarm AS (
    SELECT
        ac.region,
        null as area,
        COALESCE(subquery.save_dt, '') as save_dt,
        ac.con_type,
        COALESCE(SUM(subquery.cnt), 0) as cnt,
        'hogen' as data_type
    FROM all_combinations_alarm ac
    LEFT JOIN (
        SELECT
            save_dt,
            region,
            null as area,
            con_type,
            (COUNT(CASE WHEN alarm = 'null_value' THEN 1 ELSE NULL END) + COUNT(CASE WHEN alarm IN ('under', 'over') THEN 1 ELSE NULL END)) as cnt
        FROM test.hogen_batch_table 
        WHERE substr(save_dt, 1, 10) = CURRENT_DATE()
        AND substr(save_dt, 12, 2) = DATE_FORMAT(now(), '%H')
        AND (con_type = 'hogen_inside' OR con_type = 'hogen_control')
        AND substr(save_dt, 15, 1) = "1" -- DATE_FORMAT(now(), '%m')"
        AND region = 'edu1_5' 
        GROUP BY 1, 2, 3
    ) subquery ON ac.region = subquery.region AND ac.con_type = subquery.con_type
    GROUP BY 1, 2, 3
),
all_combinations_conditioner AS (
    SELECT DISTINCT region, area
    FROM test.conditioner_batch_table
    WHERE region = 'mylease1' AND area = '2'
),
total_conditioner AS (
    SELECT
        ac.region,
        ac.area,
        COALESCE(subquery.save_dt, '') as save_dt,
        COALESCE(subquery.con_type, 'total') as con_type,
        COALESCE(SUM(subquery.cnt), 0) as cnt,
        'conditioner' as data_type
    FROM all_combinations_conditioner ac
    LEFT JOIN (
        SELECT
            save_dt,
            region,
            area,
            con_type,
            (COUNT(CASE WHEN alarm = 'null_value' THEN 1 ELSE NULL END) + COUNT(CASE WHEN alarm IN ('under', 'over') THEN 1 ELSE NULL END)) as cnt
        FROM test.conditioner_batch_table 
        WHERE substr(save_dt, 1, 10) = CURRENT_DATE()
        AND substr(save_dt, 12, 2) = DATE_FORMAT(now(), '%H')
        AND substr(save_dt, 15, 1) = "2" -- DATE_FORMAT(now(), '%m')"
        AND region = 'mylease1' AND area = '2'
        GROUP BY 1, 2, 3, 4
    ) subquery ON ac.region = subquery.region AND ac.area = subquery.area
    GROUP BY 1, 2, 3, 4
)

SELECT * FROM total_alarm
UNION ALL
SELECT * FROM total_conditioner;

 

<간략한 설명을 하자면>

 

1. all 로 시작하는 with 절의 경우, 각각 테이블(아래)에 지역, 구역별 필터링을 적용하기 위해서 넣었다.

그리고 union을 하는 경우 꼭 컬럼의 개수를 맞춰줘야 하기 때문에 area 가 없는 테이블의 경우 null로 채워준다.

 

2. total로 시작하는 with절의 경우

- 서브쿼리에서 날짜, 시간, 필요한 region, area 구역에 따라 count 집계 값을 나타낸다

-  서브쿼리를 바탕으로 COALESCE  함수를 사용하여 집계가 0 이어도, 날짜, 구역의 값이 나오도록 해준다.

 

3. 위 4개의 with 절을 사용하여, union all 을 하면 아래와 같이 결과가 나온다.

 

cnt 값이 0 이어도 모든 region, area가 나올 수 있도록 완성하였다.

 

필자의 경우, 날짜를 API에서 다시 처리하기 때문에 비워두었지만, row 데이터가 필요하다면, 가공하지 않아도 괜찮다.