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

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

ただいまの
回答率

90.48%

  • MySQL

    6036questions

    MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

  • SQL

    2485questions

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

新規登録者の1日後、3日後ログイン数

解決済

回答 1

投稿 編集

  • 評価
  • クリップ 1
  • VIEW 457

sato_day

score 14

こんにちは。

新規登録したユーザーを母数とし、
そのユーザーが、1日後、3日後、7日後に
何人ログインしているかを抽出したいのですがうまくいきません。。

登録情報テーブル
■master
.time → 登録日(timestamp型)
.id  →ユーザーid

ログインテーブル
■login
.time → ログイン日(timestamp型)
.id  →ユーザーid

新規ユーザーは以下で抽出しました。

SELECT  
  t1.time, 
  COUNT(distinct(CASE WHEN t1.time= t2.time THEN t1.id ELSE 0 END)) as new
FROM (
  SELECT 
    MAX(DATE(time) AS time, 
    id
  FROM login
  WHERE time >= '2016-05-01'
  GROUP BY DATE(time),id
) AS t1 
LEFT OUTER JOIN (
  SELECT 
    DATE(time) AS time, 
    id
  FROM master
  GROUP BY DATE(time),id
) AS t2 
  ON t1.id = t2.id
Group by t1.time
order by t1.time DESC

この日ごとの新規ユーザーが1日後、3日後、7日後に
ログインしているかを抽出したいのです。
(1日後、3日後、7日後の間にログインではなく
ぴったりその日にログインしているかを抽出したい)

以下のように抽出したいのですがどのようにクエリを追加すればよろしいでしょうか。。

time  new  1day  3day  7day
2016-05-16 48
2016-05-15 56 
2016-05-14 87 43
2016-05-13 65 47 33

COUNT(distinct(CASE WHEN t1.time= (t2.time)+1 THEN t1.id ELSE 0 END)) as 1day
とかにすると数字は出ますが、
上記のように表記されず、1日、3日ずつ行がずれてしまいます。。

  • 気になる質問をクリップする

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

回答 1

checkベストアンサー

0

こんな感じでしょうか?
新規登録数、1・3・7日後のログイン数をそれぞれサブクエリで計算し、登録日で結合するイメージです。

SELECT 
    new_counts.time, 
    new_counts.new, 
    IFNULL(1day_counts.count, 0) AS 1day, 
    IFNULL(3day_counts.count, 0) AS 3day, 
    IFNULL(7day_counts.count, 0) AS 7day 
FROM (
    SELECT DATE(m.time) AS time, COUNT(DISTINCT m.id) AS new 
    FROM master AS m 
    GROUP BY DATE(m.time)
) AS new_counts 
LEFT OUTER JOIN (
    SELECT DATE(m.time) AS time, COUNT(DISTINCT m.id) AS count 
    FROM master AS m 
    INNER JOIN login AS l 
        ON m.id = l.id AND ADDDATE(DATE(m.time), 1) = DATE(l.time) 
    GROUP BY DATE(m.time)
) AS 1day_counts 
    ON new_counts.time = 1day_counts.time 
LEFT OUTER JOIN (
    SELECT DATE(m.time) AS time, COUNT(DISTINCT m.id) AS count 
    FROM master AS m 
    INNER JOIN login AS l 
        ON m.id = l.id AND ADDDATE(DATE(m.time), 3) = DATE(l.time) 
    GROUP BY DATE(m.time)
) AS 3day_counts 
    ON new_counts.time = 3day_counts.time 
LEFT OUTER JOIN (
    SELECT DATE(m.time) AS time, COUNT(DISTINCT m.id) AS count 
    FROM master AS m 
    INNER JOIN login AS l 
        ON m.id = l.id AND ADDDATE(DATE(m.time), 7) = DATE(l.time) 
    GROUP BY DATE(m.time)
) AS 7day_counts 
    ON new_counts.time = 7day_counts.time 
ORDER BY new_counts.time;


ただし、詳しいテーブル構造(特にインデックスの有無)を提示いただいていないため、パフォーマンスについては一切、考慮していません。
そのため、運用に耐えられないくらい遅い可能性があります(たぶん、遅いです)。

また、前回のご質問で指摘させていただいた通り、新規登録した人が1人もいない日のデータは、取得できません。

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2016/05/16 19:20

    なるほど、、かなり複雑になりますね。。

    とりあえず数字は
    COUNT(distinct(CASE WHEN t1.time= (t2.time)+1 THEN t1.id ELSE 0 END)) as 1day

    ででるので、これで凌ぎます!
    ありがとうございましたー

    キャンセル

関連した質問

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

  • MySQL

    6036questions

    MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

  • SQL

    2485questions

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