[자바/SQL] 동적 트리 만드는 방법에 대해서

다음과 같은 차트를 만들고 싶었다.

시도별 사고유형별 사망자수의 정보가 담긴 treemap

계층적인 구조를 만들기 위해 다른 시각화 툴을 분석해보았는데, 어김없이 트리의 구조를 쓰고 있어, 나 또한 트리 구조로 만들어보고자 했다.

트리를 만드는 내부로직은 내가 찾아볼 수 없기 때문에 직접 구현해야 했다.

데이터의 경우 다음과 같이 온다.

SELECT "시도", "사고유형대분류" , SUM("사망자수") 
FROM "DO48"
GROUP BY ROLLUP("시도", "사고유형대분류")
 ORDER BY "시도" DESC, "사고유형대분류" DESC

SQL를 자세히 보면 각 컬럼별로 DESC 된 것을 볼 수 있다. 이유는 나중에 동적 트리를 만들면서 설명하겠다.

아무튼 계층적인 구조를 만들기 위해 SQL ROLLUP를 사용했다. 

맨처음 구현했을때에는 ROLLUP를 쓰지 않고 각 계층별 별도의 쿼리를 만들어서 통합하는 방식을 사용하고 있었다.

다시 말해보자면

시도, 사고유형대분류의 사망자수를 얻기 위해 

시도별 쿼리, 시도_사고유형대분류 쿼리, 2번을 통해 값을 통합했다. 이러한 이유는 값에 들어있는 aggregation function 때문인데,

바로 시도_사고유형대분류 퀴리를 통해서 AVG()를 판별할 방법이 없다. SUM인 경우 하위 계층으로 상위계층 값을 계산하는 것이 가능하지만 평균의 경우는 하위계층으로 상위계층을 판별할 방법이 없다. 그래서 각 계층별로 계산하는 방식을 채택했었다.

하지만 좀더 생각을 해보니 계층적 구조를 표현할 수 있는 멋진 함수가 있었다.

나는 SQL를 공부했을때 SQLD 자격증을 취득했었고 거기서 ROLLUP과 CUBE에 대해 배울 기회가 있었다. 그래서 여러 번 쿼리를 날라는 기존의 방식을 개선시킬 수 있다고 판단하고 ROLLUP를 써서 해당 로직을 구현하고 동적 트리를 생성하고자 하였다.

동적 트리를 만드는 코드는 다음과 같다.

ResultSet rs = null;
while (rs.next()) {
	if(rs.getString(1) == null ) {
		/* Make Root Node */
		root.addProperty("name", "root");
		root.addProperty("value", rs.getString(distinctColumnSize+1));
		root.add("children", new JsonArray());
	} 
	else {
		/* Make child Node */
		JsonElement targetElement = null;
		JsonElement parentElement = root;
		Loop1 :
		for(int i =1; i<= distinctColumnSize; i++){
		if(rs.getString(i) == null ) {
			JsonObject child = new JsonObject();
			child.addProperty("name", rs.getString(i-1));
			child.addProperty("value", rs.getString(distinctColumnSize+1));
			child.add("children", new JsonArray());
			parentElement.getAsJsonArray().add(child);
			break Loop1;
		}
		else {
			if(i == distinctColumnSize  ){
				JsonObject child = new JsonObject();
				child.addProperty("name", rs.getString(i));
				child.addProperty("value", rs.getString(distinctColumnSize+1));
				child.add("children", new JsonArray());
				if (parentElement.isJsonObject()){
					parentElement.getAsJsonObject().get("children").getAsJsonArray().add(child);
				} else{
					parentElement.getAsJsonArray().get(parentElement.getAsJsonArray().size()-1).getAsJsonObject().getAsJsonArray("children").add(child);
				}
				break Loop1;
			}
		}
		/* Tree traversal를 위한 한 계층 들어가기 */
		if (parentElement.isJsonObject()){
			targetElement = parentElement.getAsJsonObject().getAsJsonArray("children");
			parentElement = targetElement;
		} else{
			targetElement = parentElement.getAsJsonArray().get(parentElement.getAsJsonArray().size()-1).getAsJsonObject().getAsJsonArray("children");
			parentElement = targetElement;
		}
	}
	}
}

코드를 보면 각 rs마다 값을 판별한다.

첫 번째 행의 경우 시도, 사고유형 대분류가 모두 NULL 값이다. 즉 가장 최상단 Node인 Root가 된다.

	if(rs.getString(1) == null ) {
		/* Make Root Node */
		root.addProperty("name", "root");
		root.addProperty("value", rs.getString(distinctColumnSize+1));
		root.add("children", new JsonArray());
	} 

따라서 해당 코드에서 분기처리한다.

코드를 보게되면 첫 번째 칼럼의 값이 NULL 이면 Root이다. 이건 ROLLUP이 주는 이 점 중에 하나인데, 오라클 SQL의 경우 NULL 정렬순서가 가장 아래로 되어버린다. 

그냥 해도 상관없지만, 이왕 tree를 만들어가는거 차곡차곡 Root부터 만들어가는 게 옳은 방식인 것 같아 SQL상에서 내림차순 정렬로 바꾸게 되었다.

그 결과 NULL 뿐만아니라 다른 값도 내림차순 정렬이 되었는데 이 문제에 대해선 프런트단에서 잘? 조회하면 간단히 되는 문제라 그냥 편하게 만들었다.

각 노드에 대해 간략히 설명하자면 name, value, children를 가지고 있다. 

{
  name : string,
  value : number,
  children : []
}

따라서 Root 노드의 경우 아래와 같은 값을 가지게 된다. 아직 children은 없는 상태이다.

{
  name : 'root',
  value : 4292,
  children : []
}

 

두 번째 값의 경우 충북, NULL 이 온다. 이제 Root 노드에 처음으로 ChildNode = 충북이 생겼다.

따라서 해당 값의 경우 첫 번째 값이 null이 아니므로 root노드 만드는 분기를 타지 않고 다음으로 넘어간다.

근데 다음 분기들 조건들을 모두 거치지 않고 마지막으로 넘어가게 된다.

if(rs.getString(i) == null ){
	...
}
else {
	if(i == distinctColumnSize){
    	...
    }
}

아무런 로직을 타지않으면 다음코드는 무조건 들어가게 되어있다.

/* Tree traversal를 위한 한 계층 들어가기 */
if (parentElement.isJsonObject()){
	targetElement = parentElement.getAsJsonObject().getAsJsonArray("children");
	parentElement = targetElement;
} else{
	targetElement = parentElement.getAsJsonArray().get(parentElement.getAsJsonArray().size()-1).getAsJsonObject().getAsJsonArray("children");
	parentElement = targetElement;
}

해당 코드의 경우 targetElement를 parent에서 child로 바꾸어 들어간다.

즉 하위값이 있으면 하위 노드로 파고들어 child를 target으로 하고 다음 값을 계산한다.

만약 그다음 값이 없다면 위에 if문 안에서 처리되어 바로 while문 조건은 rs.next를 수행하고 다음값으로 계산을 진행한다.

최종 결과는 아래와 같은 JSON 객체를 만들게된다. 시도 -> 사고유형대분류로 잘 들어간 것을 확인할 수 있다.

{
  "name": "root",
  "value": "24",
  "children": [
    {
      "name": "충북",
      "value": "15",
      "children": [
        {
          "name": "차량단독",
          "value": "12",
          "children": []
        },
        {
          "name": "차대차",
          "value": "12",
          "children": []
        },
        {
          "name": "차대사람",
          "value": "15",
          "children": []
        }
      ]
    },
    {
      "name": "충남",
      "value": "16",
      "children": [
        {
          "name": "차량단독",
          "value": "8",
          "children": []
        },
        {
          "name": "차대차",
          "value": "10",
          "children": []
        },
        {
          "name": "차대사람",
          "value": "16",
          "children": []
        }
      ]
    },
  	/* 중략 */
    {
      "name": "경기",
      "value": "22",
      "children": [
        {
          "name": "철길건널목",
          "value": "0",
          "children": []
        },
        {
          "name": "차량단독",
          "value": "5",
          "children": []
        },
        {
          "name": "차대차",
          "value": "13",
          "children": []
        },
        {
          "name": "차대사람",
          "value": "22",
          "children": []
        }
      ]
    },
    {
      "name": "강원",
      "value": "9",
      "children": [
        {
          "name": "차량단독",
          "value": "5",
          "children": []
        },
        {
          "name": "차대차",
          "value": "7",
          "children": []
        },
        {
          "name": "차대사람",
          "value": "9",
          "children": []
        }
      ]
    }
  ]
}

 이제 이걸 잘 파싱 하면 아름다운 계층적 차트를 만들 수 있게 된다. 

완성!!

참고로 차트는 D3로 만든 Plotly.js 라이브러리를 사용한 것이다. 각각 treemap, sunburst 차트입니다.

계층적인 데이터를 표현하는 데 사용합니다. 차트가 끊기는 이유는 gif가 10 fps라 그래요. 원래는 부드럽게 잘 됩니다.