- PR -

レコードを一意に特定することのできるカラムの組み合わせ

1|2 次のページへ»
投稿者投稿内容
未記入
会議室デビュー日: 2007/04/09
投稿数: 5
投稿日時: 2007-07-04 01:45
皆様のお知恵をお貸し下さい。

あるテーブルに複合プライマリキー {カラムA、B、C、D、E、F} 設定されているとします。このときテーブルのレコードは {カラムA、B、C、D、E、F} で必ず一意に特定されると思うのですが、テーブルに含まれるデータによっては6つ全てのカラムを使用せずとも一意となることがあると思います。

例えば、
以下のようなデータの場合、{カラムA、B、C} で一意となっています。

A B C D E F
------------
1 a .......
1 b .......
2 c .......
2 d e .....
2 d f .....

そこで、『レコードを一意に特定することのできるカラムの組み合わせ』を調べる良い方法があれば教えて下さい。

現在私は、

SELECT DISTINCT COUNT(*) FROM テーブル GROUP BY カラム[,カラム,…]の結果が1件、値が1 ⇔ レコードを一意に特定することのできるカラムの組み合わせ

としています。
かつのり
ぬし
会議室デビュー日: 2004/03/18
投稿数: 2015
お住まい・勤務地: 札幌
投稿日時: 2007-07-04 14:17
提示されているSQLをベースに全組み合わせを、
プログラム等で検証してみてはいかがでしょうか。
未記入
会議室デビュー日: 2007/04/09
投稿数: 5
投稿日時: 2007-07-04 20:49
かつのりさん、お返事ありがとうございます。

>提示されているSQLをベースに全組み合わせを、
プログラム等で検証してみてはいかがでしょうか。

実は既にjavaにて上記を実装し正常に動作していることを確認済みなのですが
…テーブルのデータが多い場合や、検証するカラムの組み合わせが多い場合な
ど、非常に時間がかかってしまいます。

それでSQLにてもっとスマート方法があるのでは？？と思いまして、、
とんくま
ベテラン
会議室デビュー日: 2005/08/02
投稿数: 56
お住まい・勤務地: 東京
投稿日時: 2007-07-04 21:36
もう少し簡単に出来ないものかと思いますが、今のところこれが限界です。
（更新１： C(combination)の計算を簡略化。）
（更新２： CASE の WHEN 条件を反対にし、... ELSE ' ' END となるようにした。また、コードの簡単な説明を追加。）
　
尚、POSSTR(combination,'A') = 0 は、combination が 文字'A'を含まないという意味です。
また、R(combination) は、redundant_combination から 0〜ｎ文字を除いた結果を生成しています。
例えば、'ABC ' に対し、
'ABC ', 'AB ', 'A C ', ' BC ', 'A ', ' B ', ' C ' を生成します。
このうち、元の組合せと違う組合せが一つでも元の redundant_combination に存在すれば（今の場合、' BC 'が存在します）その組合せは冗長と判断し結果から除きます。その部分が
コード:

        WHERE a||b||c||d||e||f <> combination

          AND combination

              IN (SELECT a||b||c||d||e||f

                    FROM redundant_combination

                 )

です。
　
コード:

------------------------------ Commands Entered ------------------------------

SELECT * FROM Test

 ORDER BY a, b, c, d, e, f;

------------------------------------------------------------------------------



A B C D E F

- - - - - -

1 a   J 1 A

1 b   K 2 B

2 c   L 2 A

2 d e M 3 B

2 d f N 3 C



  5 record(s) selected.

テストのため列の属性は全て CHAR(1)にしています。

コード:

------------------------------ Commands Entered ------------------------------

WITH redundant_combination AS (

SELECT a,b,c,d,e,f

  FROM (SELECT DISTINCT

               CASE WHEN a IS NOT NULL THEN 'A' ELSE ' ' END

             , CASE WHEN b IS NOT NULL THEN 'B' ELSE ' ' END

             , CASE WHEN c IS NOT NULL THEN 'C' ELSE ' ' END

             , CASE WHEN d IS NOT NULL THEN 'D' ELSE ' ' END

             , CASE WHEN e IS NOT NULL THEN 'E' ELSE ' ' END

             , CASE WHEN f IS NOT NULL THEN 'F' ELSE ' ' END

             , COUNT(*)

          FROM Test

         GROUP BY CUBE(a,b,c,d,e,f)

       ) C(a,b,c,d,e,f,cnt)

 GROUP BY a,b,c,d,e,f

HAVING MAX(cnt) = 1

)

SELECT a||b||c||d||e||f AS Minimum_Combination

  FROM redundant_combination

 WHERE NOT EXISTS

       (SELECT *

          FROM TABLE

               (SELECT DISTINCT

                       CASE WHEN POSSTR(combination,'A') = 0 THEN a ELSE ' ' END

                     ||CASE WHEN POSSTR(combination,'B') = 0 THEN b ELSE ' ' END

                     ||CASE WHEN POSSTR(combination,'C') = 0 THEN c ELSE ' ' END

                     ||CASE WHEN POSSTR(combination,'D') = 0 THEN d ELSE ' ' END

                     ||CASE WHEN POSSTR(combination,'E') = 0 THEN e ELSE ' ' END

                     ||CASE WHEN POSSTR(combination,'F') = 0 THEN f ELSE ' ' END

                 FROM (SELECT DISTINCT c1||c2||c3||c4||c5||c6

                         FROM (VALUES ' ','A') P1(c1)

                            , (VALUES ' ','B') P2(c2)

                            , (VALUES ' ','C') P3(c3)

                            , (VALUES ' ','D') P4(c4)

                            , (VALUES ' ','E') P5(c5)

                            , (VALUES ' ','F') P6(c6)

                        WHERE c1||c2||c3||c4||c5||c6 <> ''

                      ) C(combination)

               ) R(combination)

        WHERE a||b||c||d||e||f <> combination

          AND combination

              IN (SELECT a||b||c||d||e||f

                    FROM redundant_combination

                 )

      )

 ORDER BY 1 DESC;

------------------------------------------------------------------------------



MINIMUM_COMBINATION

-------------------

A C E             

A    F            

 BC               

 B   F            

   D              

    EF            



  6 record(s) selected.

　

[ メッセージ編集済み 編集者: とんくま 編集日時 2007-07-04 21:48 ]

[ メッセージ編集済み 編集者: とんくま 編集日時 2007-07-05 00:18 ]

[ メッセージ編集済み 編集者: とんくま 編集日時 2007-07-05 01:20 ]
platini
大ベテラン
会議室デビュー日: 2002/12/03
投稿数: 193
投稿日時: 2007-07-04 21:42
題意をカラムの組合せを知りたい（つまり、結果としてほしいのは列名*ｎ個であって、
その内訳　レコードの値の組合せのSetを取得したいのではない）という
主旨であろう（実際、そのように読めるので）ものと考えました。

思いつきレベルです。試してません。

ダミーテーブルに全量コピー
考えられる組合せごと（合計で　6C1 + 6C2 + 6C3 + 6C4 + 6C5 個数分調べる
ことになりますかね。仮に6C1で答え見つかれば、
それで打ち切ってもいいんでしょうけど。　)に
Unique Indexを貼ることを試みる。

成功時　その組合せOK！--->INDEXをDROP
失敗時　その組合せNG!
でどうでしょうか。
DBMSとして何を使用されているか存じませんが、
これなら、同じプログラムでもスクリプトレベル（例：PL/SQL)で
処理できるのでは。
少なくともSQLでデータ取得して検証するよりは早いかと。

---------------------------------------------------------
時間差で先にSQLで回答があった。MyMethodはスマートではないですね。

[ メッセージ編集済み 編集者: platini 編集日時 2007-07-04 21:43 ]

[ メッセージ編集済み 編集者: platini 編集日時 2007-07-04 21:48 ]
未記入
会議室デビュー日: 2007/04/09
投稿数: 5
投稿日時: 2007-07-04 22:12
platiniさん、お返事ありがとうございます。

>題意をカラムの組合せを知りたい…

はい、まさにその通りです！例え一時的（今後重複する可能性はあっても）にでも
一意となっているカラムの組み合わせが知りたいです。

>Unique Indexを貼ることを試みる。

>成功時　その組合せOK！--->INDEXをDROP
>失敗時　その組合せNG!
>でどうでしょうか。
>DBMSとして何を使用されているか存じませんが、
>これなら、同じプログラムでもスクリプトレベル（例：PL/SQL)で
>処理できるのでは。
>少なくともSQLでデータ取得して検証するよりは早いかと。

インデックスを貼る方法は思いつきませんでした、、ありがとうございます。
早速試してみます！

余談ですが、、

>6C1 + 6C2 + 6C3 + 6C4 + 6C5 個数分調べることになりますかね。
>仮に6C1で答え見つかれば、それで打ち切ってもいいんでしょうけど。

仮に以降が逆の方向からも言えるのでJavaの実装は苦労しました、、

とりあえず、スクリプトを使用しても引数でテーブル名、任意数のカラム名
を渡して処理をする方針で行こうと思います。
（カラム名の指定がない場合はそのテーブルのプライマリキーカラムを設定）

とんくまさん、すいません理解するのにもう少し時間を下さい。。
かつのり
ぬし
会議室デビュー日: 2004/03/18
投稿数: 2015
お住まい・勤務地: 札幌
投稿日時: 2007-07-04 22:26
調べるのはもしかしてランタイム上でリアルタイムに調べたいってことですか？
一時的なアナライズのためという認識でした。
とんくま
ベテラン
会議室デビュー日: 2005/08/02
投稿数: 56
お住まい・勤務地: 東京
投稿日時: 2007-07-05 00:14
単にCUBEで組合せを列挙するだけでは、UNIQUEにするには余分な列を含んだ組合せが出てくると思います。（極端な例では、全ての列を含んだ組合せも出てくるでしょう。）
私の例における最後の複雑な SELECT は、余分な列を含まない組合せだけを選ぶものです。
例えば、最初の投稿者の方の例を拡張した私のサンプル・データでは (A,B,C) でもUNIQUEになりますが、この場合 A は不要で (B,C) でUNIQUE になるので (A,B,C) は除くようなロジックになっています。
　
CUBEで組合せを列挙した一例として、
コード:

------------------------------ Commands Entered ------------------------------

select distinct

CASE WHEN A IS NOT NULL THEN 'A' END as A,

CASE WHEN B IS NOT NULL THEN 'B' END as B,

CASE WHEN C IS NOT NULL THEN 'C' END as C,

CASE WHEN D IS NOT NULL THEN 'D' END as D,

CASE WHEN E IS NOT NULL THEN 'E' END as E

from (select A,B,C,D,E,

      max(count(*)) over(partition by CHAR(GROUPING(A))

                                    ||CHAR(GROUPING(B))

                                    ||CHAR(GROUPING(C))

                                    ||CHAR(GROUPING(D))

                                    ||CHAR(GROUPING(E))

                        ) as maxRecordcount

      from Test

      group by cube(A,B,C,D,E)) S

 where maxRecordcount = 1

 ORDER BY A,B,C,D,E;

------------------------------------------------------------------------------



A B C D E

- - - - -

A B C D E

A B C D -

A B C - E

A B C - -

A B - D E

A B - D -

A - C D E

A - C D -

A - C - E

A - - D E

A - - D -

- B C D E

- B C D -

- B C - E

- B C - -

- B - D E

- B - D -

- - C D E

- - C D -

- - - D E

- - - D -



  21 record(s) selected.



[ メッセージ編集済み 編集者: とんくま 編集日時 2007-07-05 00:29 ]
1|2 次のページへ»

スキルアップ／キャリアアップ（JOB@IT）