SQLで実現したいことがあります（ユーザ利用遷移表）

Question

========== 追記２です ===========
hirohiro さん>>
コメントありがとうございます。

利用日, UserID, GameName
5/1, ID1, Game1
6/2, ID1, Game2
6/3, ID1, Game3

この場合ですと、
結果は

5月\6月	Game1	Game2	Game3
Game1	0		1		1
Game2	0		0		0
Game3	0		0		0

となって欲しいのです。
ngyukiさんが作ってくださったSQLで実現できました。
ご尽力いただきまして本当にありがとうございました。感謝いたします。
またよろしくお願いいたします。

==============================
========== 追記１です ===========
hirohiro さん>>
コメント欄ですと見にくいかと思いましたのでこちらでも同内容を書かせていただきます。

例えば、5月にGame1を利用していたユーザが、6月にどのGameを利用しているかを知りたいです。重複はして良いです。
ですので

利用日, UserID, GameName
5/1, ID1, Game1
5/2, ID1, Game2
6/1, ID1, Game1
6/2, ID1, Game2
6/3, ID1, Game3

この場合ですと、
Game1の5月の利用者（ID1）が6月にはGame1, Game2, Game3全てを利用し、
Game2の5月の利用者（ID1）も6月にはGame1, Game2, Game3全てを利用し、
Game3の5月の利用者はいないので、
結果は

5月\6月	Game1	Game2	Game3
Game1	1		1		1
Game2	1		1		1
Game3	0		0		0

となって欲しいのです。

どうぞよろしくお願いいたします。

お世話になります。

このような「ゲーム利用履歴」テーブルがあるとします。（わかりやすくするために各期間でユーザIDでソートしています。また、色も遷移表と対応させました。）

その時、以下のような 月単位 ユーザ利用遷移表 をSQLのみで生成したいのですが方法が思いつかずつまずいております。

５月時点でゲーム１の利用者だった人は２人ゲーム１を利用継続し、一人がゲーム２に移動したことを示したいです。

SQLお強い方、よろしくお願いいたします。

Comments

[hirohiro]

横軸が5月のログイン記録、縦軸が6月のログイン記録のように見えるのですが、次のようなデータの場合はどうするのでしょう？
----->
5/1, ID1, Game1 |
5/2, ID1, Game2 |
6/1, ID1, Game1 |
6/2, ID1, Game2 |
6/3, ID1, Game3 <-----

Game1の行は　1,0,0　(Game2->Game1の移動は無視)
Game2の行は　0,1,0　(Game1->Game2は移動は無視)
でいいとして、
Game3の行は、Game1,Game2どちらからの移動履歴を採用します？

改行も削除もできない……。

[退会済みユーザー]

hirohiro さん>>
コメントありがとうございます。
説明不足で申し訳ありませんでした。

例えば、5月にGame1を利用していたユーザが、6月にどのGameを利用しているかを知りたいです。重複はして良いです。
ですのでこの場合ですと、
Game1の5月の利用者（ID1）が6月にはGame1, Game2, Game3全てを利用し、
Game2の5月の利用者（ID1）も6月にはGame1, Game2, Game3全てを利用し、
Game3の5月の利用者はいないので、
結果は
-----> 1, 1, 1 | 1, 1, 1 | 0, 0, 0 <-----
となって欲しいのです。

どうぞよろしくお願いいたします。

[hirohiro]

返信ありがとうございます。理解しました。縦軸は5月ですね。
SQL書いてたらngyukiさんの回答と同じようになったので私の解答は控えます。

[退会済みユーザー]

hirohiroさん>>
おっしゃる通りです。
はい、ngyukiさんのご回答により解決いたしました。
ご尽力感謝いたします。
ありがとうございました。
またよろしくお願いいたします。

Answer 1

希望されている結果と少し違いますが・・・

横方向に列を繋げるようなものは SQL はとても苦手です。なので下記の結果を、

5月/6月    Game1    Game2    Game3
Game1    1        1        1
Game2    1        1        1
Game3    0        0        0

を、次のように読み替えています。

+-------+-------+-----+
| 5月   | 6月   | num |
+-------+-------+-----+
| Game1 | Game1 |   1 |
| Game1 | Game2 |   1 |
| Game1 | Game3 |   1 |
| Game2 | Game1 |   1 |
| Game2 | Game2 |   1 |
| Game2 | Game3 |   1 |
+-------+-------+-----+

この結果から、アプリケーションのロジックで元の表は導出できると思います。

select
  t5.GameName as "5月", t6.GameName as "6月", count(*) as num
from
(
  select UserID, GameName from t where month(利用日) = 5 group by UserID, GameName
) t5
inner join
(
  select UserID, GameName from t where month(利用日) = 6 group by UserID, GameName
) t6
on
  t5.UserID = t6.UserID
group by
  t5.GameName, t6.GameName

MySQL でやっています。日付の条件 month(利用日) = 6 は、もう少しマシな方法に読み替えてもらえればと思います。

---

無理やり縦横の表形式にするならこんなかんじでしょうか。

select
  GameName as "5月/6月",
  sum(case when GameName6 = 'Game1' then num else 0 end) as Game1,
  sum(case when GameName6 = 'Game2' then num else 0 end) as Game2,
  sum(case when GameName6 = 'Game3' then num else 0 end) as Game3
from
(
    select GameName from t group by GameName
) a
left join
(
    select
      t5.GameName as GameName5, t6.GameName as GameName6, count(*) as num
    from
    (
      select UserID, GameName from t where month(利用日) = 5 group by UserID, GameName
    ) t5
    inner join
    (
      select UserID, GameName from t where month(利用日) = 6 group by UserID, GameName
    ) t6
    on
      t5.UserID = t6.UserID
    group by
      t5.GameName, t6.GameName
) x
on
  a.GameName = x.GameName5
group by
  GameName5
order by GameName

+-----------+-------+-------+-------+
| 5月/6月   | Game1 | Game2 | Game3 |
+-----------+-------+-------+-------+
| Game1     |     1 |     1 |     1 |
| Game2     |     1 |     1 |     1 |
| Game3     |     0 |     0 |     0 |
+-----------+-------+-------+-------+

Comments

[退会済みユーザー]

ngyukiさん>>
ありがとうございます。
やりたかったことが実現できました。
inner join などは単体であればどのような働きをするのか理解しているつもりですが、このような使い方があるのかととても感動しております。
助かりました。
ご回答本当にありがとうございました。