テーブルをまたいで共通のカラム名を使うことがある。
ID とか数量とか備考とか、そういうものだ。
そして共通のカラム名を使うものの中でも、「テーブルごとに独自の価値観をもっているもの」と「テーブルをまたいで共通の価値観をもっているもの」に分かれる。
テーブル連結に用いる ID は後者にあたる。
では一般的によく使われるカラムとして、数量や備考についてはどうだろう。
そのまま「数量」「備考」という一般性が過ぎる名称であればテーブル独自でいいかもしれない。そのテーブルにとっての「数量」「備考」ということは読み解ける。
しかしもう少し意味を絞って、仮に「注文数量」であればその桁数は受注系のテーブルと発注系のテーブルで合わせたいし、仮に「住所」であればその文字列長は合わせたい。
合っていないことが単にいやなだけであれば別にいい。
しかし、まれに同一の価値観をもつ同一名称の日付を表すカラムが、テーブルによって DATE 型だったり YYYYMMDD 形式の文字列型だったりすることがある。
こういうものは開発現場に余計な混乱を招いたり、比較処理時にわざわざ関数を用いたりして妙なコストが発生してしまう。
そしてやはり、いやだ。
そういったものを抽出するための SQL を、ふと試したくなったのでメモしておく。
環境
- Oracle 11g
サンプルデータ
テーブル作成
-- 倉庫
CREATE TABLE WAREHOUSES (
WAREHOUSE_ID INTEGER -- 倉庫ID
,NAME INTEGER -- 倉庫名
,ZIP_CODE VARCHAR2(7) -- 倉庫郵便番号
,ADDRESS VARCHAR2(200) -- 倉庫住所
,PRIMARY KEY (WAREHOUSE_ID)
);
-- 商品在庫
CREATE TABLE STOCK_ITEMS (
WAREHOUSE_ID INTEGER -- 倉庫ID
,ITEM_ID INTEGER -- 商品ID
,AMOUNT NUMBER(10,2) -- 在庫個数
,PRIMARY KEY (WAREHOUSE_ID, ITEM_ID)
);
-- 商品発送
CREATE TABLE ITEM_SENDINGS (
WAREHOUSE_ID INTEGER -- 倉庫ID
,ITEM_ID INTEGER -- 商品ID
,ITEM_SENDING_ID INTEGER -- 商品発送ID
,AMOUNT NUMBER(5,2) -- 発送個数
,ZIP_CODE INTEGER -- 発送先郵便番号
,ADDRESS VARCHAR2(500) -- 発送先住所
,PRIMARY KEY (WAREHOUSE_ID, ITEM_ID, ITEM_SENDING_ID)
);
- 相違点は以下。
- 倉庫テーブルの郵便番号と商品発送テーブルの郵便番号の型。 (ZIP_CODE)
- 倉庫テーブルの在庫個数と商品発送テーブルの発送個数の型の数値サイズ。 (AMOUNT)
- 倉庫テーブルの住所と商品発送テーブルの住所の文字列長。 (ADDRESS)
- その他、今回の記事では絡んでこないが商品テーブルが存在するものとする。
前提知識
DB 上に存在するテーブルとカラムの組み合わせは以下の SQL にて取得可能である。
SELECT *
FROM ALL_TAB_COLUMNS;
ALL_TAB_COLUMNS のうち、今回使うのは以下。
- TABLE_NAME (テーブル名)
- COLUMN_NAME (カラム名)
- COLUMN_ORDER (テーブル内でそのカラムが何番目のものかを指す。ソートに用いる)
- DATA_TYPE (型。 NUMBER とか VARCHAR2 とか)
- DATA_LENGTH (データ長)
- DATA_PRECISION (NUMBER 型全体の長さ)
- DATA_SCALE (NUMBER 型の小数点以下の長さ)
実施
SQL
SELECT
COLUMN_NAME
-- テーブル A
,MIN(TABLE_NAME) AS TABLE_NAME_A
,MIN(DATA_TYPE) AS DATA_TYPE_A
,MIN(DATA_LENGTH) AS DATA_LENGTH_A
,CASE WHEN MIN(DATA_TYPE) = 'NUMBER' THEN MIN(DATA_PRECISION)
ELSE NULL
END AS DATA_PRECISION_A
,CASE WHEN MIN(DATA_TYPE) = 'NUMBER' THEN MIN(DATA_SCALE)
ELSE NULL
END AS DATA_SCALE_A
-- テーブル B
,MAX(TABLE_NAME) AS TABLE_NAME_B
,MAX(DATA_TYPE) AS DATA_TYPE_B
,MAX(DATA_LENGTH) AS DATA_LENGTH_B
,CASE WHEN MAX(DATA_TYPE) = 'NUMBER' THEN MAX(DATA_PRECISION)
ELSE NULL
END AS DATA_PRECISION_B
,CASE WHEN MAX(DATA_TYPE) = 'NUMBER' THEN MAX(DATA_SCALE)
ELSE NULL
END AS DATA_SCALE_B
FROM
(
SELECT
A.COLUMN_NAME
,A.TABLE_NAME
,A.OWNER
,A.DATA_TYPE
,A.DATA_LENGTH
,A.DATA_PRECISION
,A.DATA_SCALE
-- 二重に同じものを出力するのを避ける用のキー
,LEAST(A.TABLE_NAME, B.TABLE_NAME) || '*'
|| GREATEST(A.TABLE_NAME, B.TABLE_NAME) AS KEY_NAME
FROM
ALL_TAB_COLUMNS A
,ALL_TAB_COLUMNS B
WHERE
A.TABLE_NAME <> B.TABLE_NAME
AND A.COLUMN_NAME = B.COLUMN_NAME
AND (
A.DATA_TYPE <> B.DATA_TYPE
OR A.DATA_LENGTH <> B.DATA_LENGTH
OR (
A.DATA_TYPE = 'NUMBER'
AND (
A.DATA_PRECISION <> B.DATA_PRECISION
OR A.DATA_SCALE <> B.DATA_SCALE
)
)
)
-- システム系の排除
AND A.OWNER NOT IN ('SYS', 'SYSTEM', 'CTXSYS', 'MDSYS', 'APEX_040000')
AND B.OWNER NOT IN ('SYS', 'SYSTEM', 'CTXSYS', 'MDSYS', 'APEX_040000')
-- APEX$ なんとかいう名前のテーブルを比較対象から排除
AND A.TABLE_NAME NOT LIKE 'APEX$%'
AND B.TABLE_NAME NOT LIKE 'APEX$%'
-- 汎用性がありすぎるカラム名を比較対象から排除
AND A.COLUMN_NAME NOT IN ('ID', 'NAME')
)
GROUP BY
COLUMN_NAME
,KEY_NAME
ORDER BY
COLUMN_NAME
,KEY_NAME
;
結果
以下のように抽出される。
| COLUMN_NAME | TABLE_NAME_A | DATA_TYPE_A | DATA_LENGTH_A | DATA_PRECISION_A | DATA_SCALE_A | TABLE_NAME_B | DATA_TYPE_B | DATA_LENGTH_B | DATA_PRECISION_B | DATA_SCALE_B |
|---|---|---|---|---|---|---|---|---|---|---|
| ADDRESS | ITEM_SENDINGS | VARCHAR2 | 200 | WAREHOUSES | VARCHAR2 | 500 | ||||
| AMOUNT | ITEM_SENDINGS | NUMBER | 22 | 5 | 2 | STOCK_ITEMS | NUMBER | 22 | 10 | 2 |
| ZIP_CODE | ITEM_SENDINGS | NUMBER | 7 | 0 | WAREHOUSES | VARCHAR2 | 22 |
そういう構造を準備しといてなんだが、在庫の商品個数と発送の商品個数で最大桁数に差があるのは別に問題があるとは思わない。そこの最終的な判断は人が行う。
感想
目標は達成できている。
しかし、想定よりも動作がやや重い。もう少し軽くできないものか。
あと APEX なんとかいうのがよくわからない。あとで調べてみよう。