[PR]ネットとスマホ まとめておトク!

正規化されてない下記のようなテーブルがあります。

テープル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文等アドバイスの程、よろしくお願い致します。

このQ&Aに関連する最新のQ&A

A 回答 (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}
    • good
    • 0

こんなことかな。



クエリー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.商品コード;

ただし、サンプルのデータがきれいに分離できるケースだから出来たのであって、
実際の大量のデータからうまく分離できる保証はありません。

また、こういうのは正規化とはいいません。

この回答への補足

解答ありがとうございます。

得意先   商品コード
D      5
D      7

↑テーブル1にはこういうレコードもあるため、不可です。
ちなみに、1得意先に対して商品コードが10種類くらい
登録されている場合もあります。

蛇足ですが、クエリー3の
INNER JOIN テーブル1 AS A ON B.得意先 = A.得意先
の部分はいらないかもしれないですね。

補足日時:2011/05/21 04:41
    • good
    • 0

> これを以下のようにテーブル2つに分けたいです。



?テーブル1をテーブル2、テーブル3に分けたいのですか。

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

この回答への補足

もともとテーブル1だけが存在していてずっと使っていたのですが、
正規化されておらず扱いにくいので
テーブル2とテーブル3を新規に追加して
テーブル1のデーターを移行したいです。

テーブル3とテーブル2では、
まず親テーブルであるテーブル3のレコードをセットしたいので
「テーブル3を抽出するSQL文がどうしても思いつきません。」
と書きました。

分かりにく表現で申し訳ございません。

補足日時:2011/05/19 00:31
    • good
    • 0

得られる結果は、以下のイメージですか?


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.商品コード

この回答への補足

テーブル2とテーブル3に値がセットできればそういう使い方になりますね。

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

補足日時:2011/05/19 00:21
    • good
    • 0

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!


人気Q&Aランキング