Context Navigation

8.6節から8.8節

8.6 グラフの操作

8.6.1 テーブルの準備

PostgreSQL

CREATE LANGUAGE plpgsql;

Oracle

SET AUTOCOMMIT ON;
SET TIMING ON;
SET SERVEROUTPUT ON;

DB2: データベース・ロギングの構成ウィザードで、各ログ・ファイルのサイズを1024から4096に変更

テーブル:nodes

MySQL, PostgreSQL, DB2, SQL Server

CREATE TABLE nodes(
  id INT NOT NULL,
  name VARCHAR(20) DEFAULT '' NOT NULL,
  CONSTRAINT nodes_pri PRIMARY KEY (id)
);
CREATE INDEX name_idx ON nodes (name);

Oracle

CREATE TABLE nodes (
  id INT NOT NULL,
  name VARCHAR (20),
  CONSTRAINT nodes_pri PRIMARY KEY (id)
);
CREATE INDEX name_idx ON nodes (name);

テーブル:edges

共通

CREATE TABLE edges(
  head INT NOT NULL,
  tail INT NOT NULL,
  CONSTRAINT edges_pri PRIMARY KEY (head,tail)
);

P.100でインデックスの効果を見る際に、以下を実行する。

CREATE INDEX head_idx ON edges (head);
CREATE INDEX tail_idx ON edges (tail);

サンプルデータ

MySQL, PostgreSQL, DB2

INSERT INTO nodes (id,name) VALUES
(1,'A1'),(2,'A2'),(3,'A3'),(4,'A4'),(5,'A5'),
(6,'A6'),(7,'A7'),(8,'A8'),(9,'A9');

INSERT INTO edges (head,tail) VALUES
(1,2),(1,3),(1,4),(2,5),(2,6),
(3,6),(3,7),(6,8),(6,9);

Oracle, SQL Server

INSERT INTO nodes (id,name) VALUES (1,'A1');
INSERT INTO nodes (id,name) VALUES (2,'A2');
INSERT INTO nodes (id,name) VALUES (3,'A3');
INSERT INTO nodes (id,name) VALUES (4,'A4');
INSERT INTO nodes (id,name) VALUES (5,'A5');
INSERT INTO nodes (id,name) VALUES (6,'A6');
INSERT INTO nodes (id,name) VALUES (7,'A7');
INSERT INTO nodes (id,name) VALUES (8,'A8');
INSERT INTO nodes (id,name) VALUES (9,'A9');

INSERT INTO edges (head,tail) VALUES (1,2);
INSERT INTO edges (head,tail) VALUES (1,3);
INSERT INTO edges (head,tail) VALUES (1,4);
INSERT INTO edges (head,tail) VALUES (2,5);
INSERT INTO edges (head,tail) VALUES (2,6);
INSERT INTO edges (head,tail) VALUES (3,6);
INSERT INTO edges (head,tail) VALUES (3,7);
INSERT INTO edges (head,tail) VALUES (6,8);
INSERT INTO edges (head,tail) VALUES (6,9);

ノードあたりの平均エッジ数

共通

SELECT AVG(c) FROM (
  SELECT id,SUM(c) AS c FROM (
    (SELECT head AS id,COUNT(tail) AS c FROM edges GROUP BY head)
    UNION ALL
    (SELECT tail AS id,COUNT(head) AS c FROM edges GROUP by tail)
  ) tmp
  GROUP BY id
) tmp2;

大きなグラフの作成（下のmakeNumSeqを作ってから）

MySQL

CALL makeNumSeq(500);
TRUNCATE TABLE nodes;

INSERT INTO nodes (id,name) SELECT n AS id,'' AS name FROM numSequence;

TRUNCATE TABLE edges;

INSERT INTO edges (head,tail)
  SELECT a.id AS head,b.id AS tail
  FROM nodes a,nodes b
  WHERE a.id!=b.id
  ORDER BY RAND()
  LIMIT 1250; -- 5*500/2

PostgreSQL

SELECT makeNumSeq(500);
TRUNCATE TABLE nodes;

INSERT INTO nodes (id,name) SELECT n AS id,'' AS name FROM numSequence;

TRUNCATE TABLE edges;

INSERT INTO edges (head,tail)
  SELECT a.id AS head,b.id AS tail
  FROM nodes a,nodes b
  WHERE a.id!=b.id
  ORDER BY RANDOM()
  LIMIT FLOOR(5*500/2);

Oracle

CALL makeNumSeq(500);
TRUNCATE TABLE nodes;

INSERT INTO nodes (id,name) SELECT n AS id,'' AS name FROM numSequence;

TRUNCATE TABLE edges;

INSERT INTO edges (head,tail)
  SELECT * FROM (
    SELECT a.id AS head,b.id AS tail
    FROM nodes a,nodes b
    WHERE a.id!=b.id
    ORDER BY DBMS_RANDOM.VALUE()
  ) tmp
  WHERE ROWNUM<=FLOOR(5*500/2);

DB2

CALL makeNumSeq(500);
DELETE FROM nodes;

INSERT INTO nodes (id,name) SELECT n AS id,'' AS name FROM numSequence;

DELETE FROM edges;

INSERT INTO edges (head,tail)
  SELECT a.id head,b.id tail
  FROM nodes a,nodes b
  WHERE a.id!=b.id
  ORDER BY RAND()
  FETCH FIRST 1250 ROWS ONLY;
-- 5*500/2

（DB2ではステートメント終了文字を「@」にしてください。）

SQL Server

EXECUTE makeNumSeq 500
GO
TRUNCATE TABLE nodes
GO

INSERT INTO nodes (id,name) SELECT n AS id,'' AS name FROM numSequence;

TRUNCATE TABLE edges
GO

INSERT INTO edges (head,tail)
  SELECT TOP (5*500/2) a.id head,b.id tail
  FROM nodes a,nodes b
  WHERE a.id!=b.id
  ORDER BY NEWID()

8.8 ストアド・プロシジャ

8.8.1 連番データの作成

MySQL

DROP TABLE IF EXISTS numSequence;
CREATE TABLE numSequence (n INT);

PostgreSQL, Oracle, DB2

CREATE TABLE numSequence (n INT);

（DB2ではステートメント終了文字を「@」にしてください。）

SQL Server

IF EXISTS (SELECT * FROM dbo.sysobjects
  WHERE id = OBJECT_ID(N'numSequence') AND
    OBJECTPROPERTY(id, N'IsUserTable') = 1)
  DROP TABLE numSequence
GO

CREATE TABLE numSequence (n INT)
GO

プロシジャ:makeNumSeq

（DB2ではステートメント終了文字を「@」にしてください。）

別の方法

PostgreSQL

TRUNCATE TABLE nodes;

INSERT INTO nodes (id,name)
  SELECT id,'' AS name
  FROM GENERATE_SERIES (1,500,1) x (id);

Oracle

TRUNCATE TABLE nodes;

INSERT INTO nodes (id,name)
WITH x
AS (
  SELECT level AS id FROM DUAL
  CONNECT BY level<=500
)
SELECT id,'' AS name FROM x;

DB2

DELETE FROM nodes;

INSERT INTO nodes (id,name)
WITH x (id) AS (
  SELECT 1 FROM SYSIBM.SYSDUMMY1
  UNION ALL
  SELECT id+1 FROM x WHERE id<500)
SELECT id,'' AS name FROM x;

（DB2ではステートメント終了文字を「@」にしてください。）

8.8.2 最短経路長

テーブル:paths

共通

CREATE TABLE paths(
  startNode INT NOT NULL,
  endNode INT NOT NULL,
  length INT NOT NULL,
  CONSTRAINT paths_pri PRIMARY KEY (startNode,endNode)
);
CREATE INDEX s_idx ON paths (startNode);
CREATE INDEX e_idx ON paths (endNode);
CREATE INDEX l_idx ON paths (length);

プロシジャ:shortestPath

（DB2ではステートメント終了文字を「@」にしてください。）

結果の確認

共通

SELECT MAX(length) FROM paths;

グラフが大きいときは、次のSQL文を実行しないでください。

共通

SELECT * FROM paths ORDER BY startNode,endNode;

Download in other formats:

Plain Text