-PR-
解決済み

SQLでの集計

  • 困ってます
  • 質問No.5702954
  • 閲覧数381
  • ありがとう数3
  • 気になる数3
  • 回答数2
  • コメント数0

お礼率 26% (4/15)

下記の様に、「複数のitemを買っているuserと購入されたitem」のテーブルと、

user item
----------------------------------
田中 AAAA
田中 CCCC
田中 EEEE
北野 DDDD
北野 BBBB
北野 AAAA
小堺 CCCC
小堺 EEEE
松本 EEEE
松本 KKKK
松本 CCCC
松本 DDDD
浜田 BBBB
浜田 DDDD


下記の様な IDに紐付いた 「item」のテーブルから、

ID  item
----------------------------------
1   AAAA
2   BBBB
3   CCCC
4   DDDD
5   EEEE


下記の様に各itemと各itemを買った場合に一緒に買われるitemの一覧を結果
として表示させたいのですが、クエリの作り方が思い浮かばず、困っています。
※)可能であれば、買われたitemを表示する際にはbuy1から(左側から)同時購入
回数の多いitemを重複せずに並べて表示したい

ID  item  buy1  buy3  buy4  buy5  buy6・・・・
----------------------------------
1  AAAA CCCC BBBB DDDD EEEE
2  BBBB DDDD AAAA
3  CCCC AAAA EEEE DDDD KKKKK
4  DDDD AAAA BBBB CCCC EEEE
5  EEEE AAAA CCCC KKKKK


尚、IDと紐付いているitem数は決まっていますが、買われるitemの種類は上記
の様にIDが1~5だけではなく、集計してみないと判らない状況です。


今の所、SQLはACCESS(2003)上にて手打ちしています。

以上、ご教示のほど、宜しくお願い致します。
通報する
  • 回答数2
  • 気になる3
    質問をブックマークします。
    マイページでまとめて確認できます。

質問者が選んだベストアンサー

  • 回答No.2
レベル13

ベストアンサー率 55% (772/1382)

常々言っていることですが、理論の構築が大切です。
目的は「商品から見て、同時に購入される他の商品」
ということですよね。図解は以下の通りです。
商品-顧客⇒顧客-商品(但し、右の商品は左の商品以外)
buy1とかbuy2が何を意味するか不明なのですが、購入された
数という意味なら、右側商品のレコード件数になります。
尚、後々のため、右側の商品IDも取っておきましょう。
これをそのままSQLにします。

SELECT Max(C.id) As id,A.item,B.item as douji,
Max(D.id) As douid,Count(B.item) As buy
FROM ((tbl As A INNER JOIN tbl AS B
ON A.user=B.user) INNER JOIN item As C
ON A.item=C.item) INNER JOIN item AS D
ON B.item=D.item WHERE B.item<>A.item
GROUP BY A.item,B.item

これで縦には並ぶようになると思います。これをqryという
クエリとして登録します。これを横にするにはもう少し
工夫が必要になります。「横に並べる」ためには列の位置を
決めるための項目が必要なので、以下のように仮定します。
(1)レコード件数の多い順
(2)レコード件数が同じ場合は同時購入商品IDの小さい順

SELECT A.id,A.item,A.douji,Count(B.id)+1 AS COL
FROM qry AS A LEFT JOIN qry AS B ON
A.id=B.id AND (A.buy<b.buy
OR (A.buy=B.buy AND A.douid>B.douid))
GROUP BY A.id,A.item,A.douji

※Count(B.id)+1 これが並び順を示す列位置項目です。
このクエリをqry2として登録します。
縦を横にするにはAccessは便利で、Transferクエリを
使います。

TRANSFORM Max(A.douji)
SELECT A.id,A.item FROM qry2 AS A
GROUP BY A.id,A.item PIVOT A.COL

横に並べる方式が今一つ不明ですが、イメージに
近いものができると思います。
お礼コメント
ami96

お礼率 26% (4/15)

ありがとうございます。かなり参考になり、想定していた結果に殆ど合致したものが出来ました!
投稿日時 - 2010-03-06 13:14:45

その他の回答 (全1件)

  • 回答No.1
レベル10

ベストアンサー率 32% (55/170)

はじめまして、通りすがりと申します。

ACCESSのsqlがどの程度の事が出来るのかは存じませんが、上記表の条件では
普通に考えてできないと思います。
何故なら、最初のアルゴリズムとして二つ目の表(Idとitemの表)から一つ目の表(Userとitem)
をimteで紐づけ、紐づけた一つ目の表で同じuserの人を抽出することになると思いますが、ここで
抽出したユーザーは同じタイミングで購入した人とは限らないと思います。
よって、一つ目の表に同じタイミングで買ったという項目が必要と思います。
また、最終的にできる表は、可変長のテーブルのためsqlで生成するのは無理と思います。
流れとしましては、結果の表から推測すると、1レコードずつ条件に合致するsqlでデータを
抜き出し(1)、PGによって上記最終的な表を形成するという2段構えになると思います(2)。

 (1) item user item2   抽出条件
      1  田中 AAAA  001
      1  田中 EEEE  001

          ↓
 
 (2) 田中 AAAA EEEE
 
この例では、同時購入数が多い人順については考慮されていないため、(2)の表を作る過程で
仮想項目(項目数)を設け、項目数(降順)にてソートし出力した結果が解となると思います。

他に考え方もあるかも知れませんが、これは一つの考え方です。
 
 
 
お礼コメント
ami96

お礼率 26% (4/15)

ありがとうございました。参考にさせていただきました!
投稿日時 - 2010-03-06 13:15:43


このQ&Aで解決しましたか?
AIエージェント「あい」

こんにちは。AIエージェントの「あい」です。
あなたの悩みに、OKWAVE 3,500万件のQ&Aを分析して最適な回答をご提案します。

関連するQ&A
このQ&Aにこう思った!同じようなことあった!感想や体験を書こう
このQ&Aにはまだコメントがありません。
あなたの思ったこと、知っていることをここにコメントしてみましょう。

その他の関連するQ&A、テーマをキーワードで探す

キーワードでQ&A、テーマを検索する
-PR-

特集


抽選で合計100名様にプレゼント!

ピックアップ

ページ先頭へ