[postgreSql] WITH절로 복잡한 join 쿼리를 대신하기

특정 범위의 날짜데이터로 한컬럼을 만들고,  a테이블의 데이터로 한컬럼을 만들고,  b테이블의 데이터로 한컬럼을 만들어

조회 결과를 뽑아야한다고 가정했을때

 

join으로만 쿼리를 작성하면 아래와 같이 복잡한 쿼리문이 완성된다

SELECT
  TO_CHAR(generate_series(#{stDate}::DATE, #{edDate}::DATE, '1 month'::INTERVAL), 'yyyymm') AS date,
  COALESCE(SUM(CASE WHEN a.value IS NOT NULL THEN a.value ELSE 0 END) / 100, 0) AS goal,
  COALESCE(SUM(CASE WHEN b.value IS NOT NULL THEN b.value ELSE 0 END), 0) AS expect
FROM
  (
    SELECT
      TO_CHAR(a.date, 'yyyymm') AS date,
      SUM(a.value) AS value
    FROM
      table1 a
    WHERE
      a.areaCd1 = #{param1}
      AND a.areaCd2 = #{param2}
      AND a.date BETWEEN #{stDate}::DATE AND #{edDate}::DATE
    GROUP BY TO_CHAR(a.date, 'yyyymm')
  ) a
  FULL JOIN
  (
    SELECT
      TO_CHAR(a.date, 'yyyymm') AS date,
      SUM(a.value) AS value
    FROM
      table2 a
    WHERE
      a.areaCd1 = #{param1}
      AND a.areaCd2 = #{param2}
      AND a.date BETWEEN #{stDate}::DATE AND #{edDate}::DATE
    GROUP BY TO_CHAR(a.date, 'yyyymm')
  ) b
  ON a.date = b.date
GROUP BY TO_CHAR(generate_series(#{stDate}::DATE, #{edDate}::DATE, '1 month'::INTERVAL), 'yyyymm');

나도 못 알아볼 정도로 복잡하고 가독성이 좋지않다

이렇게 쿼리를 작성하면 join단에서 오류가 날 가능성이 급상승..

 

with 구문

쿼리문을 쪼개어 하나의 작은 테이블(view), 혹은 서브쿼리처럼 쿼리를 작성하는 방법을 말합니다.

 

위와 똑같은 기능의 쿼리를 with절로 작성해보았습니다.

with dateList as
     (select to_char(generate_series(#{stDate}::date , #{edDate}::date, '1 month'::interval),'yyyymm') as date),
valueList1 as
	 (select A.date,
             sum(A.usage) as goal
        from ( select to_char(a.date,'yyyymm') as date,
				      sum(a.value) as usage
				 from table1 tb1
				where a.areaCd1 = #{param1}
				and a.areaCd2  = #{param2}
				and a.date between #{stDate}::date and #{edDate}::date
				group by date, a.areaCd1, a.areaCd2
				order by date
              ) A
      group by A.date),
valueList2 as
	  (select to_char(a.date, 'yyyymm') as date,
              sum(a.value) as expect
        from
            table2 a
        where a.areaCd1 = #{param1}
          and a.areaCd2 = #{param2}
          and a.date between #{stDate}::date and #{edDate}::date
        group by date)
select
       a.date,
       (b.goal /100) as goal,
       c.expect as expect
  from dateList a
       left outer join valueList1 b
                    on a.date = b.date
       left outer join valueList2 c
                    on a.date = c.date

한 눈에 쏙 들어오지 않나요? 코드 가독성이 아주 좋아졌네요

특히 위의 예시처럼 임의의 더미데이터(ex:날짜 간격데이터, 숫자 간격데이터)를 함께 뿌려줘야 하는 경우에

아주 유용하게 쓰이는게 with절 입니다.

 

필요한 데이터 마다 쿼리를 쪼개어 작성하고, 쿼리가 묶인 괄호 앞에 별명을 붙여줍니다.

맨 마지막 단에서 최종 쿼리를 작성할 때 별명들을 join으로 묶어주고 사용하면 됩니다.

with 별명1
(쿼리1),
별명2
(쿼리2),
별명3
(쿼리3)
최종 쿼리[여기에서 사용할 쿼리의 별명을 join으로 묶어주면 됩니다]

 

감사합니다.