[ PostgreSQL ] mybatis에서 postgresql의 피벗 (crosstab) 과 동적쿼리를 함께 써보려는 노력 (결과 성공)

 

❓이 쿼리를 쓰게 된 이유는... 

https://careerly.co.kr/qnas/5171?utm_campaign=user-share

PostgreSQL 특정 테이블의 데이터에 따라서 동적 컬럼 설정 방법

여기에 적혀 있다. 질문할 때 힘든 것 중 하나는.. 회사 코드를 그대로 노출할 수 없기 때문에, 이런식으로 비유를 해서 설명해야한다는 것이다. 머리 한번 굴려줘야함 ㅠ_ㅠ 

 

답변이 큰 도움이 되었지만! 회사 사수분이 crosstab을 이용한 쿼리를 예시로 주셨는데, 꽤 할만한데..? 해서 킵고잉을 하게 되었다는 이야기~~! 하지만 길이 굉장히 험난했는데...🤔 그 여정을 대충 정리해보았다. 꼬박 하루가 걸렸기 때문에 시행착오가 많았지만, 모든 내용을 적어둘 수 없었다. 

 

💡피벗

자세히 설명하면 좋겠지만, 아래의 기록이 더 중요하기 때문에 일단은 생략을 하도록 한다. 

https://velog.io/@mstar228/PostgreSQL-CrosstabPivot

PostgreSQL Crosstab(Pivot)

이 블로그 참고해도 좋을듯!!

 

SELECT *
FROM   crosstab(
   'SELECT section, status, ct
    FROM   tbl
    ORDER  BY 1,2'  -- needs to be "ORDER BY 1,2" here
   ) AS ct ("Section" text, "Active" int, "Inactive" int);

위 블로그의 코드를 보며, 내가 단순하게 이해한걸 말하면! 

SELECT section, status, ct 이 부분에서 뒤에서 2번째 'status'로 묶이고 데이터에는 마지막 'ct'값이 들어간다. 

암튼, 여차저차에서 쿼리는 작성 완료 했다. DBeaber에서 잘 돌아갔다. 

 

하지만... mybatis에서 crosstab 안에 동적쿼리를 넣으면서 험난해졌다. 

 

🔐첫 번째 오류

Could not set parameters for mapping: ParameterMapping{property='__frch_item_0.cdId', mode=IN, javaType=class java.lang.Integer, jdbcType=null, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #1 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.

 

이 오류를 직면했다. parameter가 null로 들어간다는 것 같은데.. 

<if test="agentId != null and agentId != ''">
			  and pas.agent_id = #{agentId}
			  </if>

이런식으로 똑같이 동적쿼리를 넣었는데 왜 나오지 않는 것인가! 

 

 

<if test="agentId != null and agentId != ''">
			  and pas.agent_id = ${agentId}
			  </if>

그래서 이걸로 변경해봤다. 하지만 pas.agent_id는 char고, agentId는 자꾸 숫자로 넘어가더라...

 

<if test="agentId != null and agentId != ''">
			  and pas.agent_id = ''${agentId}''
			  </if>

이렇게 해주었더니 잘 되었다! ㅎㅎ crosstab 괄호 내부는 작은 따옴표로 씌워져 있기 때문에 문자열이 잘 들어가기 위해서는 작은 따옴표를 2개씩 써줘야했다. 

 

+ 참고로 동적쿼리가 너무 안 되어서, 조회 조건이 들어가는 select절을 with절로 빼버리고, crosstab에서는 걸러진 데이터들을 토대로 select하려고 했는데! with절의 가상 테이블을 crosstab에서 인지를 못했다. 

 

 

🔐두 번째 문제

쿼리 돌렸는데 자꾸 한줄 밖에 안 나오는 현상이 있었다.. 

https://stackoverflow.com/questions/48680978/postgres-crosstab-text-text-cascading-within-group

Postgres crosstab (text, text) cascading within group

스택오버플로우에서 같은 상황의 게시글이 있었고, 따라해보니 잘 나왔다!

select dense_rank() over (order by agent_id, job_id)::int as row_name

이런식으로 추가해주고, 

AS 뒤에 rowname integer 추가해주면 된다.