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

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

新規登録して質問してみよう
ただいま回答率
85.50%
MySQL

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

SQL

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

Q&A

解決済

1回答

2590閲覧

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

sato_day

総合スコア20

MySQL

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

SQL

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

0グッド

1クリップ

投稿2016/05/16 04:03

編集2016/05/16 05:09

こんにちは。

新規登録したユーザーを母数とし、
そのユーザーが、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日ずつ行がずれてしまいます。。

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

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

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

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答1

0

ベストアンサー

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

sql

1SELECT 2 new_counts.time, 3 new_counts.new, 4 IFNULL(1day_counts.count, 0) AS 1day, 5 IFNULL(3day_counts.count, 0) AS 3day, 6 IFNULL(7day_counts.count, 0) AS 7day 7FROM ( 8 SELECT DATE(m.time) AS time, COUNT(DISTINCT m.id) AS new 9 FROM master AS m 10 GROUP BY DATE(m.time) 11) AS new_counts 12LEFT OUTER JOIN ( 13 SELECT DATE(m.time) AS time, COUNT(DISTINCT m.id) AS count 14 FROM master AS m 15 INNER JOIN login AS l 16 ON m.id = l.id AND ADDDATE(DATE(m.time), 1) = DATE(l.time) 17 GROUP BY DATE(m.time) 18) AS 1day_counts 19 ON new_counts.time = 1day_counts.time 20LEFT OUTER JOIN ( 21 SELECT DATE(m.time) AS time, COUNT(DISTINCT m.id) AS count 22 FROM master AS m 23 INNER JOIN login AS l 24 ON m.id = l.id AND ADDDATE(DATE(m.time), 3) = DATE(l.time) 25 GROUP BY DATE(m.time) 26) AS 3day_counts 27 ON new_counts.time = 3day_counts.time 28LEFT OUTER JOIN ( 29 SELECT DATE(m.time) AS time, COUNT(DISTINCT m.id) AS count 30 FROM master AS m 31 INNER JOIN login AS l 32 ON m.id = l.id AND ADDDATE(DATE(m.time), 7) = DATE(l.time) 33 GROUP BY DATE(m.time) 34) AS 7day_counts 35 ON new_counts.time = 7day_counts.time 36ORDER BY new_counts.time;

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

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

投稿2016/05/16 07:31

KiyoshiMotoki

総合スコア4791

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

sato_day

2016/05/16 10:20

なるほど、、かなり複雑になりますね。。 とりあえず数字は COUNT(distinct(CASE WHEN t1.time= (t2.time)+1 THEN t1.id ELSE 0 END)) as 1day ででるので、これで凌ぎます! ありがとうございましたー
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問