DATOR


Oracle의 순환전개를 활용한 Decision Tree 구현사례. 3 SQL


 앞에서 데이터 모델과 인스턴스 차트를 보고 어떤 엔티티와 속성이 어떤 정보를 관리하는지 파악 하였습니다.

5Kxn_dz-HSktfqHhXJzpkOQRC4QK_TKjernjrEIvWSY3CCUyRPzBv-chwJflnD06cDCVUph--u668fL_8Uh9BMgTc45r4Ui49KCfQqKpwPhtc79cdN0
 그림 1. 의사결정나무
 SQL을 보기전에 다시 한번 의사결정나무를 보겠습니다. 만약에 사람이 직접 수행한다면 최상위에서부터 하나씩 탐색해 나가면서 분류할 것입니다. 하지만 SQL로 처리하는 방식은 모든 값을 대입하여 비교한 후에 최상위에서부터 최하위까지의 경로를 연결이 가능한 하나의 선을 찾는 것이라고 볼 수 있습니다.

 그렇다면 SQL의 기본형태를 보고 설명을 드리겠습니다. 
(※ SQL을 작성할때 속성명을 그대로 사용하는 이유는 이해가 편리하도록 하기 위함입니다.)


SELECT "데이터셋 보유자 번호",  "결과"
FROM (SELECT A."의사결정나무 번호", 
             A."의사결정나무 마디 번호",
     A."분류 의사결정나무 마디 번호",
     A."분류 의사결정나무 가지 번호", 
     B."최종여부",
     B."결과"
     C."데이터셋 보유자 번호", 
     C."데이터셋 구성 항목 번호",
     C."항목값"
      FROM  "Decision Tree Node" A,
            "Decision Tree Branch" B,
            "Data Set" C
      WHERE A."의사결정나무 번호"       = B."의사결정나무 번호"     
        AND A."의사결정나무 마디 번호"  = B."의사결정나무 마디 번호"
        AND A."마디 번호"              = C."데이터셋 구성 항목 번호"
        AND C."항목값" BETWEEN B."최소값" AND B."최대값"
        AND B."의사결정나무 번호"       = :V_TREE_NO
        AND C."데이터셋 보유자 번호"    = :V_OWNER_ID) D
WHERE            "최종여부"  = 'O'
  AND            CONNECT_BY_ISLEAF = 1
START WITH       "분류 의사결정나무 가지 번호" is Null
              AND       "분류 의사결정나무 마디 번호" is Null
CONNECT BY PRIOR "의사결정나무 마디 번호" = "분류 의사결정나무 마디 번호"
               AND PRIOR "의사결정나무 가지 번호" = "분류 의사결정나무 가지 번호"


SQL에 대해서 설명 드릴것은 아래와 같습니다.
 - 인라인뷰안에서 데이터가 연결된 후의 상태
 - 의사결정나무에 명시적으로 정의된 최종점과 순환전개를 하면서 발생된 최종점의 차이와 이를 활용하는 방법.
 - 순환전개를 활용하는 메인쿼리에 대한 데이터의 상태와 순환전개가 진행되는 과정


1) 인라인뷰안에서 데이터가 연결된 후의 상태

인라인 뷰안의 SQL 입니다.

SELECT A."의사결정나무 번호", 
             A."의사결정나무 마디 번호",
     A."분류 의사결정나무 마디 번호",
     A."분류 의사결정나무 가지 번호", 
     B."최종여부",
     B."결과"
     C."데이터셋 보유자 번호", 
     C."데이터셋 구성 항목 번호",
     C."항목값"
FROM  "Decision Tree Node" A,
      "Decision Tree Branch" B,
      "Data Set" C
WHERE A."의사결정나무 번호"       = B."의사결정나무 번호"     
  AND A."의사결정나무 마디 번호"  = B."의사결정나무 마디 번호"
  AND A."마디 번호"              = C."데이터셋 구성 항목 번호"
  AND C."항목값" BETWEEN B."최소값" AND B."최대값"
  AND B."의사결정나무 번호"       = :V_TREE_NO
  AND C."데이터셋 보유자 번호"    = :V_OWNER_ID


 "Decision Tree Node"와 "Decision Tree Branch" 간의 데이터 연결은 아래의 조인으로 수행됩니다. 

      A."의사결정나무 번호" = B."의사결정나무 번호" 
AND A."의사결정나무 마디 번호"  = B."의사결정나무 마디 번호" 

의사결정나무의 마디와 마디에서 분류하는 가지를 연결하는 것입니다. 그래서 해당 마디가 어떤 분류의 정보를 가지는지를 연결하는 것입니다.
 "Decision Tree Branch"와 "Data Set"간의 데이터 연결은 아래의 조인으로 수행됩니다.

      A.”마디 번호” = C.데이터셋 구성 항목 번호”
AND C."항목값" BETWEEN B."최소값" AND B."최대값"

"Data Set"의 정보가 의사결정나무의 마디에 있는 가지에 따라 분류해 보는 것입니다. 이부분은 아래 두개의 그림을 참고하겠습니다.

kIV5Gjg_cuaLtJRDTe_t6BwxlqiT5gmgNFGThLEPwhCILfBtXYffkRqOezwR5OYPWTZQoHIc1SruWjdIg5j9Cnv4jaXQ-4U2brbMw8oc4UGkfV4IGEU
그림 2. 의사결정나무 가지에 대입해보는 그림.

 위의 그림은 의사결정나무 가지에 값을 대입해보는 과정을 표현하는 것입니다. 이 글 앞에서 설명드렸듯이, 이 처리방식의 특징인 순차적으로 값을 비교해 나가는 것이 아니라 집합적인 처리 방식을 위해서 모든 가지에 값을 모두 대입하는 것입니다.
zEYINRVJ0YoqOGsBEVbxrglyXnEQ-4XXFEW3p2I3Cpb_8ZIt4bwE70EAsRtbL_CaDyj3jnEtyj9WlYVvG_wR_TQHc7VPxeXN8s1e4Z_uOM4fRrawWy0
그림 3. Between Join 방식을 대입해보는 그림.

 위의 그림은 데이터셋을 의사결정나무에 대입하는 과정입니다. Between 조인에 대해서 이해하고 있다면 쉽게 이해가 될 것입니다. 의사결정나무의 마디에 사용하는 항목과 데이터셋의 항목은 같은 내용입니다. 위의 사례를 예로 들면 ‘Wage increase first year(입사 첫해 주급 상승률)’이 ‘01’값이 데이터셋과 의사결정나무마디에 같이 사용되는 것입니다. 
 마디를 구성하는 가지는 서로 베타적(Exclusive)인 범위를 가지고 있기 때문에 Between 조인을 하더라도 마디당 하나의 가지에만 분류되게 됩니다. 예를 들어 마디 최상위 마디인 ‘Wage increase first year’의 범위는 ‘0  x < 2.5’, ‘2.5  x < 99’ 이 값이 존재하는 전체의 범위이고, 이  두 개중에 데이터셋에 존재하는 값은 ‘2.3’ 이므로 왼쪽 첫번째 가지에만 조인을 성공하는 것입니다.


2)  ‘명시적으로 정의된 최종점’과 ‘순환전개상에 발생된 최종점’의 차이와 이를 활용하는 방법.

 오라클에서는 ‘Connect By’절을 이용하여 서로 다른 행과 행이 서로 연결할 수 있습니다. 일반적인 삼각형 형태의 트리 모양을 데이터를 구성하면 상위는 1개, 하위는 n개의 구조를 가지고 있는 것이 일반적입니다. 우리가 1:M 관계의 두개의 엔티티가 물리적인 테이블에서는 ‘M’쪽의 테이블이 ‘1’쪽의 PK를 상속받아 가지고 있게 됩니다. 이것을 하나의 테이블에 표현이 된다면 트리의 하위가 ‘M’쪽에 해당이 되기 때문에 ‘1’쪽에 해당하는 상위의 값을 가지고 있는 것입니다. 그래서 최상위에서 ‘M’쪽에 Connect by절에 명시된 데이터 연결고리를 찾아 전개해 나가다보면 더이상 전개할 데이터가 없을때 멈추게 됩니다. 이를 ‘순환전개상에 발생된 최종점’이고 이를 순환전개시에 최종인지, 아니면 하위가 더 존재하는지 여부를 표현하는 함수인 ‘CONNECT_BY_ISLEAF’의 ‘1’과 ‘0’값을 통해 표현할 수 있습니다.
 ‘명시적으로 정의된 최종점’은 순환전개처럼 전개해 나가면서 유동적으로 최종인지를 표현하는 것이 아니라 사용자가 미리 컬럼을 정의하고 해당 컬럼에 최종인지 아닌지를 표현하는 것입니다.
 이 두 가지를 동시에 사용하는 것은 데이터를 분류하는 가지에 모두 대입을 하고 최상위에서부터 연결할수 있는 다양한 경로 중에 ‘명시적으로 정의된 최종점’을 가지고 있는 하나의 경로를 찾기 위함입니다.


3) 순환전개를 활용하는 메인쿼리에 대한 데이터의 상태와 순환전개가 진행되는 과정

 그림 1과 그림 2를 해서 보면 인라인뷰안에서 총 5개의 로우가 조인에 성공했다는 것을 알 수 있습니다. 이는 의사결정나무의 마디안에 분류하는 가지가 서로 베타적이라는 전제조건이 있고, 이 가지가 가지고 있는 범위의 합은 존재할 수 있는 값의 전체이기도 합니다. 그렇기 때문에 조인에 성공한 로우의 갯수는 의사결정나무안의 마디의 총 개수와 같습니다. 

Hi415HlOWBrIIw94cJc_X1mNTzjwEi0T72JMNrLPe0Cu7CcjxqIz1kWk-5DDOio8MgtC1aGA932XsvSbms1Swc0tqPXDIKU9undv4sDJb4t50jiMjpk
그림 3. Between 조인이 된 이후의 데이터 인스턴스 차트


gAzmyo7t1ai8Uz8T6woEY73jEvX2f1If3_Vbs1UUaBRpIptY5I7y7Y4BuPZvQO-B6Jcap33rzEaoXwYncWm2-dKwbFnzy2RyuT9208Y_Nzaw0UsjVw4

그림 4. Connect by 를 이용하여 작성된 SQL

 이제는 서로 다른 행(row)간에 연결을 수행해나가면서 의사결정나무상의 최종점에 도달하는 선을 찾아주면 됩니다. 총 5개의 행중에 순환전개에 참여하게 되는 행은 3개의 행입니다. 그림 3 과 그림 4는 색상으로 인스턴스 차트와 SQL과 연개하여 보면 됩니다. 순환전개가 완료된 인스턴스 중에 최종여부가 = ‘O’ 이고 전개가 된 마지막 인스턴스의 결과는 “good” 으로 나오게 됩니다.
 
 어느정도 데이터 모델과 데이터 인스턴스 차트, 이를 풀어나갈수 있는 SQL이 작성이 되었다고 생각이 됩니다. 다음편은 다양한 상황별로 케이스를 작성을 하여 발생할 수 있는 성능상의 문제점에 대해 설명하고, 가장 중요한 이 모델이 가지고 있는 많은 제약사항에 대해서 설명하겠습니다.

TAG

Leave Comments


profile우리는 하나님을 믿는다. 다른 모든 것들은 데이터로 검증해야 한다. 

Recent Trackback