• 締切済み

正規化されていないテーブルの分割について

正規化されてない下記のようなテーブルがあります。 テープル1(主キー:得意先、商品コード) 得意先   商品コード A      1 A      2 B      5 B      6 B      7 C      1 C      2 これを以下のようにテーブル2つに分けたいです。 テーブル2(主キー:得意先) 得意先 パターン A     x B     y C     x テーブル3(主キー:パターン、商品コード) パターン 商品コード x      1 x      2 y      5 y      6 y      7 このとき、テーブル1のデーターをもとに テーブル2やテーブル3のレコードを生成したいのですが、 テーブル3を抽出するSQL文がどうしても思いつきません。 (やりたいのは、あらかじめ登録したパターンを得意先ごとに選択するという形にしたいです。) 簡単にできるような気もするのですが... パターンについては、ランダムな文字列で構わないです。 SQL文等アドバイスの程、よろしくお願い致します。

みんなの回答

回答No.4

以下の方法で、可能かと思います。 postgresql9.0では動作確認しました。sql serverもrecursiveと window関数が使えると思いますので、適時書き換えて動くかと思います。 arrayが使えなかったら、path || '-' || t2.code のようにし、 商品コードをハイフンで区切って、ひとつの文字として扱う必要があります。 --全体の流れ- 1.再帰SQLを用いて、顧客ごとに、row numberで商品に番号を割りつけ。 2.得意先ごとに商品コードを配列に順に格納。 3.商品がすべて格納された配列のみを残す。 4.配列でグループ化し、タブっている配列(商品の組み合わせ)を除外。 5.最後にもとのテーブルと結合させて、完了。 ---------- create table tbl(customer text, code text); insert into tbl values ('A', '1'), ('A', '2'), ('B', '5'), ('B', '6'), ('B', '7'), ('C', '1'), ('C', '2'), ('D', '5'), ('D', '7'); -- with recursive tbl_root(customer, code, r, root) as ( select customer, code, t1.r1, array[code] from (select *, rank() over (partition by customer order by code) as r1 from tbl) as t1 where t1.r1=1 union all select t2.customer, t2.code, t2.r2, root || t2.code from (select *, rank() over (partition by customer order by code) as r2 from tbl) as t2 inner join tbl_root as t3 on t3.customer=t2.customer and t3.r=t2.r2-1 ) select 'pt_'||tt4.customer as pattern, tt4.code,tt3.root from(select tt2.*, min(k) over (partition by tt2.root) as mk from (select tt1.customer, tt1.root , row_number() over() as k from (select customer, code, root, array_upper(root,1) as au, max(array_upper(root,1)) over (partition by customer) as maxarray from tbl_root ) as tt1 where tt1.au=tt1.maxarray group by tt1.root, tt1.customer ) as tt2 ) as tt3 inner join tbl as tt4 on tt3.customer=tt4.customer where tt3.k=tt3.mk ; -- pattern, code, root ---------------------- pt_A, 1, {1,2} pt_A, 2, {1,2} pt_B, 5, {5,6,7} pt_B, 6, {5,6,7} pt_B, 7, {5,6,7} pt_D, 5, {5,7} pt_D, 7, {5,7}

  • yorozu_ya
  • ベストアンサー率54% (76/140)
回答No.3

こんなことかな。 クエリー2: SELECT C.得意先, Min(B.商品コード) & Max(B.商品コード) AS パターン FROM (テーブル1 AS C INNER JOIN テーブル1 AS B ON C.商品コード = B.商品コード) INNER JOIN テーブル1 AS A ON B.得意先 = A.得意先 GROUP BY C.得意先; クエリー3: SELECT DISTINCT Min(B.商品コード) & Max(B.商品コード) AS パターン, D.商品コード FROM ((テーブル1 AS C INNER JOIN テーブル1 AS B ON C.商品コード = B.商品コード) INNER JOIN テーブル1 AS A ON B.得意先 = A.得意先) INNER JOIN テーブル1 AS D ON C.得意先 = D.得意先 GROUP BY C.得意先, D.商品コード; ただし、サンプルのデータがきれいに分離できるケースだから出来たのであって、 実際の大量のデータからうまく分離できる保証はありません。 また、こういうのは正規化とはいいません。

kawaakira
質問者

補足

解答ありがとうございます。 得意先   商品コード D      5 D      7 ↑テーブル1にはこういうレコードもあるため、不可です。 ちなみに、1得意先に対して商品コードが10種類くらい 登録されている場合もあります。 蛇足ですが、クエリー3の INNER JOIN テーブル1 AS A ON B.得意先 = A.得意先 の部分はいらないかもしれないですね。

  • nora1962
  • ベストアンサー率60% (431/717)
回答No.2

> これを以下のようにテーブル2つに分けたいです。 ?テーブル1をテーブル2、テーブル3に分けたいのですか。 その一方で「テーブル3を抽出するSQL文がどうしても思いつきません。」とあるのがよく分かりません。 #1さんの回答で解決済みなら、解決済みにして、そうでないなら補足をお願いいたします。

kawaakira
質問者

補足

もともとテーブル1だけが存在していてずっと使っていたのですが、 正規化されておらず扱いにくいので テーブル2とテーブル3を新規に追加して テーブル1のデーターを移行したいです。 テーブル3とテーブル2では、 まず親テーブルであるテーブル3のレコードをセットしたいので 「テーブル3を抽出するSQL文がどうしても思いつきません。」 と書きました。 分かりにく表現で申し訳ございません。

  • bin-chan
  • ベストアンサー率33% (1403/4213)
回答No.1

得られる結果は、以下のイメージですか? A     x      1 A     x      2 B     y      5 B     y      6 B     y      7 C     x      1 C     x      2 select テーブル2.得意先, テーブル2.パターン, テーブル3.商品コード from テーブル2 inner join テーブル3 on テーブル2.パターン = テーブル3.パターン order by テーブル2.得意先, テーブル2.パターン, テーブル3.商品コード

kawaakira
質問者

補足

テーブル2とテーブル3に値がセットできればそういう使い方になりますね。 テーブル1(既存テーブル)に存在するデーターから テーブル2(新規テーブル)にセットするためのデーターと テーブル3(新規テーブル)にセットするためのデーターを どうやって引っ張りだしてくるかで悩んでいます。

関連するQ&A

  • 片方のテーブルにないデータを取り出す

    お世話になります。 SQL文についてちょっと助けてください。 テーブルA Aキー(主) テーブルB Bキー(主) Aキー テーブルBからBキーを取り出すとき、AキーでテーブルAを見に行って存在しない場合のBキーだけを取り出したいのです。 一度のSQL発行で可能でしょうか。

  • Insert Intoでアップデートをかけたい

    テーブルから他テーブルへデータの更新をしたいと思っています。 テーブルは下記のような構成になっています。 テーブルSTOCK(主キーは商品コード) 商品コード(varchar),数量(int) A001,5 B002,10 C003,30 テーブルTOTAL(主キーは商品コード) A001,10 B002,20 やりたいことは、テーブルTOTALを更新させる事です。テーブルSTOCKの商品コードがテーブルTOTALにあれば、減算をおこないたいです。 そこで下記のSQLを書きました。 INSERT INTO テーブルTOTAL SELECT * FROM テーブルSTOCK ON DUPLICATE KEY UPDATE テーブルTOTAL.数量 = テーブルTOTAL.数量 - テーブルSTOCK.数量 更新結果 A001,5 B002,10 C003,30 商品コードがA001、B002は欲しい結果がでました。欲しい結果はC003が「-30」になることです。 テーブルTOTALにはC003の商品コードがないため、30でアップデートされてしまうのですが、なんとか「-30」で更新できるようにできないでしょうか? よろしくお願いいたします。

    • ベストアンサー
    • MySQL
  • 複数テーブルの不一致クエリについて

    SQLで、下記selectを行いたいです。 テーブルA テーブルB テーブルC これら三つのテーブルには、同一のコードが振られています。 この三つのテーブルのひとつにでも、抜けているコードを抜き出したいです。 (テーブルAのコード=テーブルBのコード=テーブルCのコードになっていないコード) 例) テーブルAに入ってるコード(1.3.5) テーブルBに入っているコード(1.4.5) テーブルCに入っているコード(1.5.6) このとき、三つのテーブルにきちんと入っているコードは、1と5だけなので、それ以外のコードを抜き出す→3.4.6 これをSQLで行う場合、簡単なSQL文はありますでしょうか? 今、考えているのは、各テーブルすべてをinner joinしたviewを作成し(これにより三つとも入ってるコードの一覧ができる)、 そのviewと、テーブルA、テーブルB、テーブルCそれぞれをひとつづつleft joinして、不一致データを取り出す方法です。 でも、そうすると、viewを作成しなくてはいけず、また、SQLをテーブルの数分(今回は3回)投げなくてはいけなくて。。 もっと、単純にかけないものでしょうか? ご存知の方、教えてください。

  • Access2003 異なるテーブルを1つのテーブルにくっつける

    こんにちは。 Access2003で、以下の2つのテーブルがあります。 テーブルA  得意先 売上高  あああ 100  いいい 200 テーブルB  得意先 仕入高  ううう 300  えええ 400  おおお 500 上記のようにフィールドが異なり、関連するID等のキーも持っていない別々のテーブルを、クエリなどで  得意先  売上高  得意先  仕入高  あああ  100    ううう  300  いいい  200    えええ  400            おおお  500 のようにテーブルAとテーブルBを横並び(?)にくっつける事は出来るのでしょうか? 得意先も別々なので、連結とかする事ができません。。 いろいろインターネットで調べたのですが、どうしても解らないので質問させて頂きました。 宜しくお願いしますm(__)m

  • 商品テーブルと2つの在庫テーブルの結合ができない

    商品のデータベースがあるんですが、一つの商品テーブルに複数の店舗の商品をまとめて登録しています。在庫数は店舗別のテーブルで分けているのですが、これをSQLで商品コードで結合ができません。 仮に商品がA、B、Cと登録されていたとして、店舗1にはAとBの商品があるため2つの在庫数を設定しており、店舗2にはBとCの商品があるため2つの在庫数を設定しています。 つまり片方のみの商品もあれば両方に存在する商品があります。 このときにSQLで商品情報を取得する場合に、結合したいのですが結果が0件となり上手く結合ができません。 商品コードがBのときにSELECT文で両店舗の在庫数を同時に取得したいし、AやCではそれぞれの店舗在庫を取得したいのですが、何か良い方法はないものでしょうか?

    • ベストアンサー
    • MySQL
  • テーブルのマッチングについて

    ORACLE10gを使用しています。 AとB同じレイアウト(キーも同じ)のテーブルがあります。 この2つのテーブルを比較して、 (1)Aテーブルのみにあるもの:1レコードCテーブルに追加 (2)Bテーブルのみにあるもの:1レコードCテーブルに追加 (3)A、B両方にあるもの:2レコードCテーブルに追加 上記に該当するレコードをCテーブルにINSERTしたいです。 実は、Cテーブルに項目が1つ追加されまして「区分」を書く必要があります。 どのパターン((1)か(2)か(3))でINSERTしたデータかです。 (1):1、(2):2、(3):3をセットする どのようにSQLを作成すると実現できるのでしょうか? ご指導よろしくおねがいします。

  • レコード削除でのテーブル修正

    初めて、データベースを作成するのですが、 基本的な部分でつまずいています。 テーブルのレコード削除において、 そのテーブルの主キーを外部キーに使用している他のテーブルの修正に関して 教えて頂きたいのですが? 例えば、テーブルが2つ(Table-A、Table-B)あり、 フィールドとして、 Table-A (1)販売員コード(主キー) (2)名前      (3)年齢 Table-B (1)顧客コード(主キー)  (2)販売員コード(外部キー) (3)注文日  (4)商品コード(外部キー) があったとします。 この時、Table-Aの販売員コード 0015番が辞めたので、削除する場合、 1.通常、0015番は、永久欠番にするのでしょうか、それとも新しい販売員に  割り当てることもあるのでしょうか? 2.もし、後者である場合、Table-Bの(2)販売員コードフィールドの0015番のすべてのデータは、  元の販売員と名前が違う訳ですから、どう対応したらよいのでしょう?   永久欠番であれば、まだ分かるのですが、一般に各テーブルの主キーは一度決めたら、 変更しないものなのか、変更もありえるのか教えて欲しいのです。 初心者なので、質問内容が質問になっていないかもしれませんが、宜しくお願いします。

  • テーブルの並び替え

    仕事であるdbを使って集計をすることになったのですが、なにぶん初心者なので分りやすくdbを並べ替えたいのですが、うまくいきません。 たとえば 主キー ID  code1 code2 222 33 あ   B  222 33 う   c  225 66 あ   c 239 77 か   B 239 77 う   d となっているものをテーブルを 主キー ID codeA codeB codeC codeD 222 33 あ   B  う c 225   66 あ   c 239   77 か   B  う  d と別テーブルへ並べ替えたいのですが、どのように 書いたらいいものか・・・。 どなたか教えてくれませんでしょうか?

  • 正規化?の戻しについて

    お世話になってます。 データの結合SQLについて教えてください。 以下の2つのテーブルがあります。 <TBL1> KEY A B --- -- -- 111 A1 B1 222 A2 B2 333 A3 B3 <TBL2> KEY COL C --- --- -- 111 1 AA 111 2 BB 222 1 CC 222 2 DD 333 1 EE 333 2 FF 正規化?を行った結果このようになったようなのですが、運用では下記イメージで利用してます。 KEY A B C1 C2 --- -- -- -- -- 111 A1 B1 AA BB 222 A2 B2 CC DD 333 A3 B3 EE FF TBL1とTBL2から、このイメージを取り出すために前任者がPL/SQLの複雑なロジックで実現しているのですが、処理が遅い状態です。 SQLのみで実現できれば、もう少し早くなるのでは?と考えております。 いろいろ考えてみましたが実現方法が思いつかないため、お知恵をお貸しください。 TBL1は特に規則性もない普通のテーブルです。 TBL2は、TBL1のキーが必ず存在します。 COLは、キーごとに1,2が固定で設定されます。結合イメージのC1,C2項目に該当します。 TBL2の件数はTBL1件数*2になります。 以上、つたない説明ですが、よい手がありましたら、ご指摘お願いします。

  • Oracle[10g]のSQL文について(改めて)

    すみません。[B]テーブルの作成がおかしい様でしたので、 改めて質問させて下さい。 Oracle[10g]のSQL文についての質問です。(No.???) [A]テーブルに [CODE](KEY) [CODE2] [NAME] のフィールドがあります。 A-1 B-1 NAME-1 A-2 B-2 NAME-2 A-3 B-3 NAME-3 [B]テーブルに [CODE3](KEY) [NAMEB] のフィールドがあります。 B-1 NAME-B-1 B-2 NAME-B-2 [C1]テーブルに [NO](KEY) [LINE](KEY) [CODE] のフィールドがあります。 X 1 A-1 X 2 A-3 Y 1 A-2 結果が [NO/LINE/CODE/NAMEB] X 1 A-1 NAME-B-1 X 2 A-3 (NULL) と、なる様にしたいのです。 SELECT C1.NO, C1.LINE,C1.CODE, Tx.NAMEB FROM C1 , ( SELECT C1.CODE, TNMx1.NAMEB FROM C1, ( SELECT CODE, CODE2, CODE3, NAMEB FROM A, B WHERE A.CODE2 = B.CODE3 ) TNMx1 WHERE C1.NO = 'X' AND C1.CODE = TNMx1.CODE(+) ) Tx WHERE C1.NO = 'X' GROUP BY C1.NO, C1.LINE,C1.CODE, Tx.NAMEB と、考えたのですが、何故か結果が X 1 A-1 (NULL) X 1 A-1 NAME-B-1 X 2 A-3 (NULL) X 2 A-3 NAME-B-1 4行もでてしまいます。 問題は、[B]テーブルです。 宜しくお願い致します。