スクリプトを使用した親子階層の関係表の手動作成

機械翻訳について

スクリプトを使用した親子階層の関係表の手動作成

このトピックで説明するスクリプトの例を使用して、親子階層の関係表を手動で作成します。スクリプトを実行すると、親子階層に必要な関係表が生成され、ロードされます。

ノート:

Oracleでは、関係表の生成機能を使用してスクリプトを作成することをお薦めします。「親子関係表を作成するためのスクリプトの生成」を参照してください。

親子階層で定義する必要のある主要な要素として、メンバーの識別列とメンバーの親の識別列があります。スクリプトを実行する前に、次のものをメンバー・キー列、親キー列および必要な関係表名に置き換える必要があります。

${SOURCE_TABLE_NAME} - 親子データを含むソース表。たとえば、従業員または従業員ディメンション表などです。
${SOURCE_MEMBER_KEY_COL_NAME} - ソース表のキー列。たとえば、Employee表のEmployee_Keyなどです。
${SOURCE_PARENT_KEY_COL_NAME} - ソース表の親キー列。たとえば、Employee表の Manager_Keyなどです。
${RELATIONSHIP_TABLE_NAME} - リレーショナル表に基づく親子階層の親子関係表。
${MEMBER_COLUMN_DATA_TYPE} - メンバー・キー列のデータ型
${PARENT_COLUMN_DATA_TYPE} - 親キー列のデータ型。メンバー・キー列と同じデータ型です。

Oracle Database

例20-1 関係表スクリプトの作成

CREATE TABLE ${RELATIONSHIP_TABLE_NAME} (MEMBER_KEY ${MEMBER_COLUMN_DATA_TYPE} , ANCESTOR_KEY ${PARENT_COLUMN_DATA_TYPE}, DISTANCE NUMBER(10,0), IS_LEAF NUMBER(10,0) );

例20-2 関係表スクリプトのロード


For details declare 

v_max_depth integer;

v_stmt varchar2(32000);

i integer;

begin

select max(level) into v_max_depth

from ${SOURCE_TABLE_NAME}

connect by prior ${SOURCE_MEMBER_KEY_COL_NAME}= ${SOURCE_PARENT_KEY_COL_NAME}

start with ${SOURCE_PARENT_KEY_COL_NAME} is null;

v_stmt := 'insert into ${PARENT_CHILD_TABLE_NAME} (MEMBER_KEY, ANCESTOR_KEY, DISTANCE, IS_LEAF)' || chr(10) 

|| 'select ${SOURCE_MEMBER_KEY_COL_NAME}as member_key, null, null, 0 from ${SOURCE_TABLE_NAME} where ${SOURCE_PARENT_KEY_COL_NAME} is null' || chr(10) 

|| 'union all' || chr(10) 

|| 'select' || chr(10) 

|| '  member_key,' || chr(10) 

|| '  replace(replace(ancestor_key, ''\p'', ''|''), ''\'', ''\'') as ancestor_key,' || chr(10) 

|| '  case when depth is null then 0' || chr(10) 

|| '  else max(depth) over (partition by member_key) - depth + 1' || chr(10) 

|| '  end as distance,' || chr(10) 

|| '  is_leaf' || chr(10) 

|| 'from' || chr(10) 

|| '(' || chr(10) 

|| '  select' || chr(10) 

|| '    member_key,' || chr(10) 

|| '    depth,' || chr(10) 

|| '    case' || chr(10) 

|| '      when depth is null then '''' || member_key' || chr(10) 

|| '      when instr(hier_path, ''|'', 1, depth + 1) = 0 then null' || chr(10) 

|| '      else substr(hier_path, instr(hier_path, ''|'', 1, depth) + 1, instr(hier_path, ''|'', 1, depth + 1) - instr(hier_path, ''|'', 1, depth) - 1)' || chr(10) 

|| '    end ancestor_key,' || chr(10) 

|| '    is_leaf' || chr(10) 

|| '  from' || chr(10) 

|| '    (' || chr(10) 

|| '      select ${SOURCE_MEMBER_KEY_COL_NAME} as member_key, ${SOURCE_PARENT_KEY_COL_NAME} as ancestor_key, sys_connect_by_path(replace(replace(${SOURCE_MEMBER_KEY_COL_NAME}, ''\'', ''\''), ''|'', ''\p''), ''|'') as hier_path,' || chr(10) 

|| '        case when ${SOURCE_MEMBER_KEY_COL_NAME} in (select ${SOURCE_PARENT_KEY_COL_NAME} from ${SOURCE_TABLE_NAME} ) then 0 else 1 end as IS_LEAF' || chr(10) 

|| '      from ${SOURCE_TABLE_NAME} ' || chr(10) 

|| '      connect by prior ${SOURCE_MEMBER_KEY_COL_NAME} = ${SOURCE_PARENT_KEY_COL_NAME} ' || chr(10) 

|| '      start with ${SOURCE_PARENT_KEY_COL_NAME} is null' || chr(10) 

|| '    ),' || chr(10) 

|| '    (' || chr(10) 

|| '      select null as depth from dual' || chr(10); 

for i in 1..v_max_depth - 1 loop 

v_stmt := v_stmt || '      union all select ' || i || ' from dual' || chr(10); 

end loop; 

v_stmt := v_stmt || '    )' || chr(10) 

|| ')' || chr(10) 

|| 'where ancestor_key is not null' || chr(10); 

execute immediate v_stmt; 

end; 

/

例20-3 従業員表の例

declare 

v_max_depth integer;

 v_stmt varchar2(32000);

 i integer;

 begin

 select max(level) into v_max_depth

 from SAMP_EMPL_D_VH

 connect by prior Employee_Key=Mgr_Id

 start with Mgr_Id is null;

 v_stmt := 'insert into 07 - ORE Sample.<<SQLSERVER_TABLE>> (MEMBER_KEY, ANCESTOR_KEY, DISTANCE, IS_LEAF)' || chr(10) 

 || 'select Employee_Key as member_key, null, null, 0 from SAMP_EMPL_D_VH where Mgr_Id is null' || chr(10) 

 || 'union all' || chr(10) 

 || 'select' || chr(10) 

 || '  member_key,' || chr(10) 

 || '  replace(replace(ancestor_key, ''\p'', ''|''), ''\'', ''\'') as ancestor_key,' || chr(10) 

 || '  case when depth is null then 0' || chr(10) 

 || '  else max(depth) over (partition by member_key) - depth + 1' || chr(10) 

 || '  end as distance,' || chr(10) 

 || '  is_leaf' || chr(10) 

 || 'from' || chr(10) 

 || '(' || chr(10) 

 || '  select' || chr(10) 

 || '    member_key,' || chr(10) 

 || '    depth,' || chr(10) 

 || '    case' || chr(10) 

 || '      when depth is null then '''' || member_key' || chr(10) 

 || '      when instr(hier_path, ''|'', 1, depth + 1) = 0 then null' || chr(10) 

 || '      else substr(hier_path, instr(hier_path, ''|'', 1, depth) + 1, instr(hier_path, ''|'', 1, depth + 1) - instr(hier_path, ''|'', 1, depth) - 1)' || chr(10) 

 || '    end ancestor_key,' || chr(10) 

 || '    is_leaf' || chr(10) 

 || '  from' || chr(10) 

 || '    (' || chr(10) 

 || '      select Employee_Key as member_key, Mgr_Id as ancestor_key, sys_connect_by_path(replace(replace(Employee_Key, ''\'', ''\''), ''|'', ''\p''), ''|'') as hier_path,' || chr(10) 

 || '        case when Employee_Key in (select Mgr_Id from SAMP_EMPL_D_VH ) then 0 else 1 end as IS_LEAF' || chr(10) 

 || '      from SAMP_EMPL_D_VH ' || chr(10) 

 || '      connect by prior Employee_Key = Mgr_Id ' || chr(10) 

 || '      start with Mgr_Id is null' || chr(10) 

 || '    ),' || chr(10) 

 || '    (' || chr(10) 

 || '      select null as depth from dual' || chr(10); 

 for i in 1..v_max_depth - 1 loop 

 v_stmt := v_stmt || '      union all select ' || i || ' from dual' || chr(10); 

 end loop; 

 v_stmt := v_stmt || '    )' || chr(10) 

 || ')' || chr(10) 

 || 'where ancestor_key is not null' || chr(10); 

 execute immediate v_stmt; 

 end; 

 /

SQL Server

例20-4 表の作成

CREATE TABLE ${PARENT_CHILD_TABLE_NAME} ( MEMBER_KEY ${MEMBER_TYPE_DEF} , ANCESTOR_KEY ${ANCESTOR_TYPE_DEF}, DISTANCE INTEGER, IS_LEAF INTEGER )

例20-5 内容を挿入

WITH Closure(${SOURCE_PARENT_KEY_COL_NAME}, ${SOURCE_MEMBER_KEY_COL_NAME}, Distance) as (

select directs.${SOURCE_PARENT_KEY_COL_NAME}, directs.${SOURCE_MEMBER_KEY_COL_NAME}, NULL from ${SOURCE_TABLE_NAME} directs where directs.${SOURCE_PARENT_KEY_COL_NAME} is NULL union all 

select directs.${SOURCE_MEMBER_KEY_COL_NAME} ,directs.${SOURCE_MEMBER_KEY_COL_NAME}, 0 from ${SOURCE_TABLE_NAME} directs union all 

select super.${SOURCE_PARENT_KEY_COL_NAME}, sub.${SOURCE_MEMBER_KEY_COL_NAME}, sub.Distance+1 from ${SOURCE_TABLE_NAME} super, Closure sub 

where super.${SOURCE_MEMBER_KEY_COL_NAME} = sub.${SOURCE_PARENT_KEY_COL_NAME} and super.${SOURCE_PARENT_KEY_COL_NAME} is not NULL)

Insert into ${PARENT_CHILD_TABLE_NAME} (MEMBER_KEY, ANCESTOR_KEY, DISTANCE, IS_LEAF)

select Closure.${SOURCE_MEMBER_KEY_COL_NAME} as MEMBER_KEY, Closure.${SOURCE_PARENT_KEY_COL_NAME} as ANCESTOR_KEY, Closure.Distance as DISTANCE, 

Case WHEN temp.${SOURCE_PARENT_KEY_COL_NAME} is null then 1 else 0 end as IS_LEAF

from Closure Left Outer Join (select distinct ${SOURCE_PARENT_KEY_COL_NAME} from ${SOURCE_TABLE_NAME} ) temp on Closure.${SOURCE_MEMBER_KEY_COL_NAME}= temp.${SOURCE_PARENT_KEY_COL_NAME} where Closure.${SOURCE_PARENT_KEY_COL_NAME} is NOT NULL or (Closure.${SOURCE_PARENT_KEY_COL_NAME} is NULL and Closure.Distance is NULL)

例20-6 元のファイル

WITH Closure(Mgr_Id, Employee_Key, Distance) as (

select directs.Mgr_Id, directs.Employee_Key, NULL from SAMP_EMPL_D_VH directs where directs.Mgr_Id is NULL union all 

select directs.Employee_Key ,directs.Employee_Key, 0 from SAMP_EMPL_D_VH directs union all 

 select super.Mgr_Id, sub.Employee_Key, sub.Distance+1 from SAMP_EMPL_D_VH super, Closure sub 

 where super.Employee_Key = sub.Mgr_Id and super.Mgr_Id is not NULL)

 Insert into 07 - ORE Sample.<<TABLE_NAME>> (MEMBER_KEY, ANCESTOR_KEY, DISTANCE, IS_LEAF)

  select Closure.Employee_Key as MEMBER_KEY, Closure.Mgr_Id as ANCESTOR_KEY, Closure.Distance as DISTANCE, 

 Case WHEN temp.Mgr_Id is null then 1 else 0 end as IS_LEAF

from Closure Left Outer Join (select distinct Mgr_Id from SAMP_EMPL_D_VH ) temp on Closure.Employee_Key = temp.Mgr_Id where Closure.Mgr_Id is NOT NULL or (Closure.Mgr_Id is NULL and Closure.Distance is NULL)

IBM DB2

例20-7 表の作成

CREATE TABLE "${PARENT_CHILD_TABLE_NAME} " ( "MEMBER_KEY" ${MEMBER_TYPE_DEF} , "ANCESTOR_KEY" ${ANCESTOR_TYPE_DEF}, "DISTANCE" INTEGER, "IS_LEAF" INTEGER )

例20-8 内容を挿入

Insert into "${PARENT_CHILD_TABLE_NAME} " (MEMBER_KEY, ANCESTOR_KEY, DISTANCE, IS_LEAF)

WITH Closure(${SOURCE_PARENT_KEY_COL_NAME}, ${SOURCE_MEMBER_KEY_COL_NAME}, Distance) as (

select directs.${SOURCE_PARENT_KEY_COL_NAME}, directs.${SOURCE_MEMBER_KEY_COL_NAME}, NULL from ${SOURCE_TABLE_NAME} directs where directs.${SOURCE_PARENT_KEY_COL_NAME} is NULL union all 

select directs.${SOURCE_MEMBER_KEY_COL_NAME} ,directs.${SOURCE_MEMBER_KEY_COL_NAME}, 0 from ${SOURCE_TABLE_NAME} directs union all 

select super.${SOURCE_PARENT_KEY_COL_NAME}, sub.${SOURCE_MEMBER_KEY_COL_NAME}, sub.Distance+1 from ${SOURCE_TABLE_NAME} super, Closure sub 

where super.${SOURCE_MEMBER_KEY_COL_NAME} = sub.${SOURCE_PARENT_KEY_COL_NAME} and super.${SOURCE_PARENT_KEY_COL_NAME} is not NULL)

select Closure.${SOURCE_MEMBER_KEY_COL_NAME} as MEMBER_KEY, Closure.${SOURCE_PARENT_KEY_COL_NAME} as ANCESTOR_KEY, Closure.Distance as DISTANCE, 

Case WHEN temp.${SOURCE_PARENT_KEY_COL_NAME} is null then 1 else 0 end as IS_LEAF

from Closure Left Outer Join (select distinct ${SOURCE_PARENT_KEY_COL_NAME} from ${SOURCE_TABLE_NAME} ) temp on Closure.${SOURCE_MEMBER_KEY_COL_NAME} = temp.${SOURCE_PARENT_KEY_COL_NAME} where Closure.${SOURCE_PARENT_KEY_COL_NAME} is NOT NULL or (Closure.${SOURCE_PARENT_KEY_COL_NAME} is NULL and Closure.Distance is NULL)

例20-9 元のファイル

 Insert into "07 - ORE Sample"."<<CreateTable>>" (MEMBER_KEY, ANCESTOR_KEY, DISTANCE, IS_LEAF)

 WITH Closure(Mgr_Id, Employee_Key, Distance) as (

select directs.Mgr_Id, directs.Employee_Key, NULL from SAMP_EMPL_D_VH directs where directs.Mgr_Id is NULL union all 

select directs.Employee_Key ,directs.Employee_Key, 0 from SAMP_EMPL_D_VH directs union all 

 select super.Mgr_Id, sub.Employee_Key, sub.Distance+1 from SAMP_EMPL_D_VH super, Closure sub 

 where super.Employee_Key = sub.Mgr_Id and super.Mgr_Id is not NULL)

 select Closure.Employee_Key as MEMBER_KEY, Closure.Mgr_Id as ANCESTOR_KEY, Closure.Distance as DISTANCE, 

 Case WHEN temp.Mgr_Id is null then 1 else 0 end as IS_LEAF

from Closure Left Outer Join (select distinct Mgr_Id from SAMP_EMPL_D_VH ) temp on Closure.Employee_Key = temp.Mgr_Id where Closure.Mgr_Id is NOT NULL or (Closure.Mgr_Id is NULL and Closure.Distance is NULL)

Teradata

例20-10 表の作成

CREATE MULTISET TABLE "${PARENT_CHILD_TABLE_NAME} " ( "MEMBER_KEY" ${MEMBER_TYPE_DEF} , "ANCESTOR_KEY" ${ANCESTOR_TYPE_DEF}, "DISTANCE" INTEGER, "IS_LEAF" INTEGER )

例20-11 内容を挿入

Insert into "${PARENT_CHILD_TABLE_NAME} " (MEMBER_KEY, ANCESTOR_KEY, DISTANCE, IS_LEAF)

WITH RECURSIVE Closure(${SOURCE_PARENT_KEY_COL_NAME}, ${SOURCE_MEMBER_KEY_COL_NAME}, Distance) as (

select directs.${SOURCE_PARENT_KEY_COL_NAME}, directs.${SOURCE_MEMBER_KEY_COL_NAME}, NULL from ${SOURCE_TABLE_NAME} directs where directs.${SOURCE_PARENT_KEY_COL_NAME} is NULL union all 

select directs.${SOURCE_MEMBER_KEY_COL_NAME} ,directs.${SOURCE_MEMBER_KEY_COL_NAME}, 0 from ${SOURCE_TABLE_NAME} directs union all 

select super.${SOURCE_PARENT_KEY_COL_NAME}, sub.${SOURCE_MEMBER_KEY_COL_NAME}, sub.Distance+1 from ${SOURCE_TABLE_NAME} super, Closure sub 

where super.${SOURCE_MEMBER_KEY_COL_NAME} = sub.${SOURCE_PARENT_KEY_COL_NAME} and super.${SOURCE_PARENT_KEY_COL_NAME} is not NULL)

select Closure.${SOURCE_MEMBER_KEY_COL_NAME} as MEMBER_KEY, Closure.${SOURCE_PARENT_KEY_COL_NAME}  as ANCESTOR_KEY, Closure.Distance as DISTANCE, 

Case WHEN temp.${SOURCE_PARENT_KEY_COL_NAME} is null then 1 else 0 end as IS_LEAF

from Closure Left Outer Join (select distinct ${SOURCE_PARENT_KEY_COL_NAME} from ${SOURCE_TABLE_NAME} ) temp on Closure.${SOURCE_MEMBER_KEY_COL_NAME} = temp.${SOURCE_PARENT_KEY_COL_NAME} where Closure.${SOURCE_PARENT_KEY_COL_NAME} is NOT NULL or (Closure.${SOURCE_PARENT_KEY_COL_NAME} is NULL and Closure.Distance is NULL)

例20-12 元のファイル

 Insert into "07 - ORE Sample"."<<CREATE_TABLE>>" (MEMBER_KEY, ANCESTOR_KEY, DISTANCE, IS_LEAF)

 WITH RECURSIVE Closure(Mgr_Id, Employee_Key, Distance) as (

select directs.Mgr_Id, directs.Employee_Key, NULL from SAMP_EMPL_D_VH directs where directs.Mgr_Id is NULL union all 

select directs.Employee_Key ,directs.Employee_Key, 0 from SAMP_EMPL_D_VH directs union all 

 select super.Mgr_Id, sub.Employee_Key, sub.Distance+1 from SAMP_EMPL_D_VH super, Closure sub 

 where super.Employee_Key = sub.Mgr_Id and super.Mgr_Id is not NULL)

 select Closure.Employee_Key as MEMBER_KEY, Closure.Mgr_Id as ANCESTOR_KEY, Closure.Distance as DISTANCE, 

 Case WHEN temp.Mgr_Id is null then 1 else 0 end as IS_LEAF

from Closure Left Outer Join (select distinct Mgr_Id from SAMP_EMPL_D_VH ) temp on Closure.Employee_Key = temp.Mgr_Id where Closure.Mgr_Id is NOT NULL or (Closure.Mgr_Id is NULL and Closure.Distance is NULL)