WM_CONCAT()이란?

행을 열로 열을 행으로 바꾸는 pivot 개념이 오늘의 주제.

 

 

이전 프로젝트 관련 글을 쓰면서 막혔던 부분이 있었다.

정확히는 막혔다고 하기에는 애매하고 불필요하게 긴 쿼리문이 문제였다.

분명히 비슷한 역할을 하는 짧은 간략한 쿼리문이 있을 텐데...

 

오늘은 간략한 쿼리문을 한 번 알아보려고 한다.

 

[ substr()을 이용한 지난 시간 쿼리문 ]

select * from
(select ROW_NUMBER() OVER(ORDER BY p.product_seq) RNUM, p.* ,c.product_color, c.product_size as product_size2 from product p, 
(select product_seq, substr(xmlagg(xmlelement(col,',',product_color)).extract('//text()').getstringval(),2)product_color, substr(xmlagg(xmlelement(col,',',product_size)).extract('//text()').getstringval(),2)product_size from optiontest group by product_seq) c 
where p.product_seq = c.product_seq) where rnum between ((1-1)*10)+1 and (1*10) and product_category = '치마' and product_color like '%yellow%' and product_size2 like '%95%';

지난 시간에 작성했던 쿼리문이다.

ROW_NUMBER() 함수가 어떤 함수 인지도 모른 채 사용했고, substr을 사용하면서 괜히 쿼리문이 길어졌다. 더군다나 내가 원하는 결과값이 나오지 않는 상황이 발생했었다.

 

원하는 것은 내가 원하는 조건을 걸었을 때 (ex product_category = '치마') 많은 데이터 중에서 치마인 것들만 골라서 rownum을 설정해주고 1~10에 출력되길 원했지만 정작 rownum을 먼저 선언한 뒤에 조건을 걸어뒀기 때문에 1페이지에 1개, 2페이지에 1개 이런 식으로 기존 rownum을 참조해서 출력됐었다.

 

아래 간략하고, 해결된 코드를 보자

 

 

[ wm_concat() 함수를 이용한 쿼리문 ]

select * from (
select rownum rnum, p.* ,o.product_color, o.product_size from product p, 
(select product_option_seq, wm_concat(product_color) product_color, wm_concat(product_size) product_size from product_option group by product_option_seq) o 
where p.product_seq = o.product_option_seq and p.product_gender='W' and p.product_category = '치마' and c.product_color like '%black%' and c.product_size like '%XL%') 
where rnum between ((1-1)*12)+1 and (1*12);

위의 쿼리문과 비교하면 차이점이 뚜렷하다.

 

• substr을 대신해 wm_concat을 사용했다.
• row_number를 대신해 rownum을 사용했다.
• rownum을 설정해준 뒤 조건을 걸었던 것을 rownum을 설정하는 select문에 조건을 설정했다.

 

하나하나 살펴볼 필요가 있다.

 

먼저, substr을 대신해 wm_concat을 사용한 이유는 간단하다. 코드의 길이가 짧아지기 때문이다.

 직관성도 높아지고 짧은 코드는 나중에 보더라도 알아보기 쉽기 때문이다. 다만 wm_concat을 사용하게 되면 중간에 세미콜론을 찍을지 -를 찍을지 정해줄 수 없는 것으로 보인다. substr은 구분자로 넣을 문자를 내가 정해줄 수 있었다.

 

 

두 번째, row_number함수를 대신해 db에 있는 기존 rownum을 사용하게 됐다.

 결과적으로는 같은 출력 값을 받아낼 수 있었다. row_number을 사용하면서 over을 통해서 다양한 조건을 걸 수는 있지만 모든 리스트를 불러올 예정인 나한테는 불필요한 함수의 사용이었기 때문에 rownum으로 변경했다.

 

 

세 번째, select 조건을 rownum을 설정해준 뒤에서 rownum을 설정하기 전으로 변경했다.

 기존에 문제점을 한 번에 해결할 수 있었다. 지금까지 where 절은 사실상 맨 마지막에만 사용해왔기 때문에 생각지도 못한 곳에서 해결했다. 그 실마리는 강사님께서 강의를 하시면서 rownum은 'select 한 뒤에 적용된다.'를 말씀해주셨을 때 번뜩 떠올랐다. 왜 지금까지 rownum을 적용시킨 뒤에 나는 조건을 걸었을까? 그리고 조건에 맞는 결과값이 나왔을 때 그제야 page처리를 하기 위해서 rownum을 잘라주는 조건문을 걸었다.

 

 

[ mybatis에서 사용한 쿼리문 ]

    <!-- Product 리스트 출력 -->
    <select id="productCategory" parameterType="com.example.demo.vo.ProductVO" resultType="com.example.demo.vo.ProductVO">
        select * from (
        select rownum rnum, p.* ,o.product_color, o.product_size from product p,
        (select product_option_seq, wm_concat(product_color) product_color, wm_concat(product_size) product_size from product_option group by product_option_seq) o

        <where>
                p.product_seq = o.product_option_seq
            <if test="product_gender != 'null'">
                and p.product_gender = #{product_gender}
            </if>
            <if test="product_category != 'null'">
                and p.product_category = #{product_category}
            </if>
            <if test="select_color != 'null'">
                and o.product_color like '%'||#{select_color}||'%'
            </if>
            <if test="select_size != 'null'">
                and o.product_size like '%'||#{select_size}||'%'
            </if>
        </where>
        ) where rnum between ((#{product_pageNum}-1)*12)+1 and (#{product_pageNum}*12)
    </select>

위에서 말했던 쿼리문들은 oracle에서 바로 실행할 수 있는 쿼리문이었다. 하지만 스프링 부트에서 mybatis에서 사용하는 쿼리문은 조건에 들어가는 값들이 null인지 확인하고 해당 값이 null이 아니라면 조건을 걸어줘야 했다.

 

그래서 최종 쿼리문은 위와 같이 나왔다. if에 있는 test의 값이 true라면 조건문을 걸고 그렇지 않으면 조건문이 생기지 않도록 했다.