質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.61%

  • SQL

    2327questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

  • データマイニング

    19questions

    データマイニングは、購買履歴やクレジットカードの利用履歴、電話の通話履歴など企業にある大量のデータを解析して、その中に隠れたパターンやルールを探し出す技術です。DMと略されることもあります。

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

解決済

回答 1

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 837
退会済みユーザー

退会済みユーザー

========== 追記2です ===========
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で実現できました。
ご尽力いただきまして本当にありがとうございました。感謝いたします。
またよろしくお願いいたします。

==============================
========== 追記1です ===========
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のみで生成したいのですが方法が思いつかずつまずいております。

イメージ説明
5月時点でゲーム1の利用者だった人は2人ゲーム1を利用継続し、一人がゲーム2に移動したことを示したいです。

SQLお強い方、よろしくお願いいたします。
  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • hirohiro

    2015/07/23 17:57 編集

    横軸が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どちらからの移動履歴を採用します? 改行も削除もできない……。

    キャンセル

  • 退会済みユーザー

    退会済みユーザー

    2015/07/23 18:43

    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

    2015/07/23 20:24 編集

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

    キャンセル

  • 退会済みユーザー

    退会済みユーザー

    2015/07/23 20:49

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

    キャンセル

回答 1

checkベストアンサー

+2

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

横方向に列を繋げるようなものは 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 |
+-----------+-------+-------+-------+

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2015/07/23 20:45

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.61%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る

  • SQL

    2327questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

  • データマイニング

    19questions

    データマイニングは、購買履歴やクレジットカードの利用履歴、電話の通話履歴など企業にある大量のデータを解析して、その中に隠れたパターンやルールを探し出す技術です。DMと略されることもあります。