テーブルをまたいで共通のカラム名を使うことがある。

ID とか数量とか備考とか、そういうものだ。

そして共通のカラム名を使うものの中でも、「テーブルごとに独自の価値観をもっているもの」と「テーブルをまたいで共通の価値観をもっているもの」に分かれる。

テーブル連結に用いる ID は後者にあたる。

では一般的によく使われるカラムとして、数量や備考についてはどうだろう。

そのまま「数量」「備考」という一般性が過ぎる名称であればテーブル独自でいいかもしれない。そのテーブルにとっての「数量」「備考」ということは読み解ける。

しかしもう少し意味を絞って、仮に「注文数量」であればその桁数は受注系のテーブルと発注系のテーブルで合わせたいし、仮に「住所」であればその文字列長は合わせたい。

合っていないことが単にいやなだけであれば別にいい。

しかし、まれに同一の価値観をもつ同一名称の日付を表すカラムが、テーブルによって DATE 型だったり YYYYMMDD 形式の文字列型だったりすることがある。

こういうものは開発現場に余計な混乱を招いたり、比較処理時にわざわざ関数を用いたりして妙なコストが発生してしまう。

そしてやはり、いやだ。

そういったものを抽出するための SQL を、ふと試したくなったのでメモしておく。

環境

Oracle 11g

サンプルデータ

テーブル作成

create_table

-- 倉庫
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_all_tab_columns

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_falsely_similar_columns

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 なんとかいうのがよくわからない。あとで調べてみよう。