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

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

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

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

PHP

PHPは、Webサイト構築に特化して開発されたプログラミング言語です。大きな特徴のひとつは、HTMLに直接プログラムを埋め込むことができるという点です。PHPを用いることで、HTMLを動的コンテンツとして出力できます。HTMLがそのままブラウザに表示されるのに対し、PHPプログラムはサーバ側で実行された結果がブラウザに表示されるため、PHPスクリプトは「サーバサイドスクリプト」と呼ばれています。

Q&A

解決済

1回答

3645閲覧

【追記】アクセスログの表から新規ユーザとリピーターの割合(月ごと→週ごと)が知りたい

Satochan24

総合スコア113

MySQL

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

PHP

PHPは、Webサイト構築に特化して開発されたプログラミング言語です。大きな特徴のひとつは、HTMLに直接プログラムを埋め込むことができるという点です。PHPを用いることで、HTMLを動的コンテンツとして出力できます。HTMLがそのままブラウザに表示されるのに対し、PHPプログラムはサーバ側で実行された結果がブラウザに表示されるため、PHPスクリプトは「サーバサイドスクリプト」と呼ばれています。

0グッド

0クリップ

投稿2016/01/15 05:32

編集2016/02/09 08:47

2016/2/9追記
無理だったら全然いいのですが、
掲題の件、週ごとの集計は可能でしょうか?
週ごとというのは、2月第1週、第2週で集計します。

以前、ここで教えてもらった月ごとの集計SQLは、下記の通りです。
いろいろ、部分的に分けて、MySQLで検証してみた感じでは、難しそうでした。

SELECT (a.access_month) AS access, b.newer_count AS newer, a.all_count - b.newer_count AS repeater_count, a.all_count AS all_count FROM ( SELECT access_month, count( * ) AS all_count FROM ( SELECT DISTINCT user_id, date_format( access_day, '%Y年%m月' ) AS access_month FROM access_record WHERE access_type =101 ) AS c GROUP BY access_month)a LEFT JOIN ( SELECT access_month, count( * ) AS newer_count FROM ( SELECT user_id, min( date_format( access_day, '%Y年%m月' ) ) access_month FROM access_record GROUP BY user_id ) AS d GROUP BY access_month)b ON a.access_month = b.access_month ORDER BY `a`.`access_month` DESC

(以前の質問内容)
アプリのアクセスログを取得している表があるのですが、
この表から、新規ユーザとリピーターの割合を知りたいと思っています。また、取得したデータはPHPでWebページに
表示させたいので、できれば1つの問い合わせで出力できるとページも作りやすいと思っています。

何とか、新規ユーザはカウントできたのですが、リピーターで苦戦しています。全体数をカウントして、新規ユーザを引けば、リピータになるのではと考えていますが・・・

何かカウントできる方法、あるいは、もっと別の方法で
新規・リピータともカウントできるという方法があれば
教えてください。

【アクセスログの表の一部】

access_day | user_id | access_type

2016-01-15 | 1306 | 101
2016-01-15 | 1305 | 101
2015-12-28 | 1298 | 102
2015-12-28 | 1298 | 101

※表の説明
・access_type:101はアプリ最初のメニュー表示なので、
アクセスした場合、必ず記録される。
・同じaccess_typeは同じuser_idからは1日1回しか記録されない。

【新規ユーザ取得のSQL】
・ユーザーごとの最小アクセス日、MIN(access_day)を取得すれば、それが新規ユーザのアクセス日だと考え、そのデータ数を月ごとにカウントしました。

SELECT COUNT(CASE WHEN date_format(b.c, '%Y-%m') = date_format(now(), '%Y-%m') THEN 1 ELSE NULL END) AS 'this',
COUNT(CASE WHEN date_format(b.c, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 1 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS 'one',
COUNT(CASE WHEN date_format(b.c, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 2 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS 'two',
COUNT(CASE WHEN date_format(b.c, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 3 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS 'three',

(一部省略)

COUNT(CASE WHEN date_format(b.c, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 11 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS 'eleven', COUNT(CASE WHEN date_format(b.c, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 12 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS 'year'

FROM (SELECT a.user_id, MIN( a.access_day ) AS c
FROM access_record AS a
GROUP BY a.user_id
) AS b

これで、
今月 1か月前 2ヶ月前 3ヶ月前 4ヶ月前
新規ユーザー 8 4 34 29 3

のような表の形で取得できたので、PHPでWeb表示ができました。
また、実際のデータを数えて、カウント数も相違ないことを確認しました。

【リピーター数取得のSQL】
次にリピータ数ですが、access_type:101は全てのユーザが記録されるので、101のみにしぼり、その月(例では12月)の全ユーザ数(重複なし)を出しました。

【12月全ユーザ数9】
SELECT DISTINCT a.user_id
FROM access_record AS a
GROUP BY a.user_id, a.access_type, a.access_day
HAVING a.access_type =101
AND date_format( a.access_day, '%Y-%m' ) = date_format( DATE_SUB( now( ) , INTERVAL 1
MONTH ) , '%Y-%m' )

これで9と表示できたので1ヶ月前の新規ユーザ数4を引けば、5がリピータ数になると考えました。
、1文で過去の月も表示できるよう、COUNT文の中に、条件を記載して、

SELECT
COUNT(
CASE WHEN date_format( b.c, '%Y-%m' ) = date_format( DATE_SUB( now( ) , INTERVAL 1
MONTH ) , '%Y-%m' )
THEN 1
ELSE NULL
END ) AS 'one'
FROM
(
SELECT DISTINCT a.user_id, a.access_day AS c
FROM access_record AS a
GROUP BY a.user_id, a.access_type, a.access_day
HAVING a.access_type =101
) AS b

上記を実行したら、予想(9)に反して、12月の101の全アクセス数の16が表示されました。
恐らく、内側のSELECTの中に、a.access_day AS cを入れたためだと思うのですが、ここに記載しないと外側のCOUNTで条件指定できなくて困っています。

何か対策、あるいは、もっと効率的に直接、リピータ数(そもそも新規ユーザ数も)を取得できる方法が
ありますでしょうか?

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

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

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

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

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

guest

回答1

0

ベストアンサー

ご質問のSQLのサブクエリーは(access_dayではなく)月を返すようにし、その月をベースに集計すれば良いと思います。

  • 月ごとの新規ユーザー数
  • 月ごとの全アクセス数

この2つの結果セットを外部結合することで「月ごとの全アクセス数-新規ユーザー数=リピーター数」になると思います。

Oracleですがご参考までに。
※「substr(access_day, 1, 7)」は「date_format( b.c, '%Y-%m' )」と同義です。

SQL

1select a.access_month, b.newer_count, a.all_count - b.newer_count as repeater_count 2from ( 3 select substr(access_day, 1, 7) as access_month, count(*) as all_count 4 from access_record 5 where access_type = 101 6 group by substr(access_day, 1, 7) 7 ) a 8 left join ( 9 select access_month, count(*) as newer_count 10 from ( 11 select user_id, min(substr(access_day, 1, 7)) access_month 12 from access_record 13 group by user_id 14 ) 15 group by access_month 16 ) b on a.access_month = b.access_month

分析関数を使えばもっとすっきりするかもしれません。


2016/01/21 追記
left joinより上の部分をdistinctにして重複ユーザーを除去しました。

SQL

1select a.access_month, b.newer_count, a.all_count - b.newer_count as repeater_count 2from ( 3 select access_month, count(*) as all_count 4 from ( 5 select distinct user_id, substr(access_day, 1, 7) as access_month 6 from access_record 7 where access_type = 101 8 ) 9 group by access_month 10 ) a 11 以下同じ

投稿2016/01/15 06:51

編集2016/01/21 03:44
jcs502ulf

総合スコア307

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

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

Satochan24

2016/01/15 07:32

回答有難うございました。 完全に解読できなかったのですが、 substr(access_day, 1, 7)の部分を、date_format( access_day, '%Y-%m' ) に変えて実行すればよかったでしょうか? MySQLで実行したところ、 #1248 - Every derived table must have its own alias というエラーが出てしまいましたが、エイリアスの指定が抜けているということでしょうか?
jcs502ulf

2016/01/15 07:53

SQLはオラクル用なので、単純にsubstr部分のみを置き換えただけではエラーが発生すると思います。適宜MySQL用に読み替えてください。 date_fromat...はaccess_dayの年月部分を取得している認識です。
Satochan24

2016/01/19 05:34

MySQLの書式に下記のように直したら、出力できました! SELECT a.access_month, b.newer_count, a.all_count - b.newer_count AS repeater_count FROM ( SELECT date_format( access_day, '%Y-%m' ) AS access_month, count( * ) AS all_count FROM access_record WHERE access_type =101 GROUP BY date_format( access_day, '%Y-%m' ) ) AS a LEFT JOIN ( SELECT access_month, count( * ) AS newer_count FROM ( SELECT user_id, min( date_format( access_day, '%Y-%m' ) ) AS access_month FROM access_record GROUP BY user_id ) AS b GROUP BY access_month)b ON a.access_month = b.access_month 有難うございます。 ただ、気になったのが、12月のユーザ数、新規4人はいいのですが、 リピーター数12人と出てきて、確かに総アクセス数16と等しいのですが、 user_idの重複なしで表を見ながらカウントした場合、最初にも記入したように重複なしuser_idは全部で9人、リピータは5人でした。 そもそも、リピータ数をその月のuser_id重複を含めてカウントするのかどうかという カウントの仕方にも寄るかと思いますが、このカウントの仕方で良いのか、あるいは、SQLの修正で重複なしも出力可能でしょうか? (説明が分かりづらくてすみません。)
jcs502ulf

2016/01/19 09:03

実際のデータを挙げてもらえるとイメージしやすいのですが、重複を排除したいのであれば、各SQLをさらにサブクエリ化して、distinct user_id, access_monthで取得できると思います。
Satochan24

2016/01/19 09:46

回答有難うございます。 DISTINCT user_idで試したのですが、結果は変わらなかったですね。 12月のデータだけですが、 access_day | user_id | 2015-12-01 | 1271 | 2015-12-01 | 1289 | 2015-12-02 | 1292 | 2015-12-03 | 1246 2015-12-03 | 1271 2015-12-03 | 1289 2015-12-03 | 1295 2015-12-04 | 1295 2015-12-08 | 1287 2015-12-08 | 1289 2015-12-11 | 1271 2015-12-16 | 1296 2015-12-16 | 1297 2015-12-17 | 1297 2015-12-18 | 1298 2015-12-18 | 1298 この16件すべて、access_type=101で、 1295,1296,1297,1298はこの月に初めて登場する新規ユーザです。
Satochan24

2016/01/20 05:28

リピータ数、新規ユーザー数のカウント方法をどう数えるか、重複はどうするのかは、 いろいろやり方があると思いますが、 現在のアクセスタイプの取得の仕方では、あくまで目安だと思いますので、 現在取得できた、新規ユーザ(最小アクセス日がその月)、 リピータ(アクセスタイプ101の全ユーザ数から、新規ユーザ数を引いた数、重複あり)で行きたいと思います。
jcs502ulf

2016/01/21 03:47

SQLを追記しました。distinctを使うと重複なしユーザー数は9を返します。 (新規ユーザーは他の月のデータも必要なので確認はできませんが)
Satochan24

2016/01/22 02:45

回答有難うございました。 DISTINCTのSQLは後ほど、確認させて頂きたいと思いますが、 今日、新たに、新規ユーザ数取得で、問題が発見されました。 下記のSQLでMIN(access_day)で取得できるはずだったんですが、 12月のデータは合ってたのですが、 アクセスタイプの取得を開始したのが、8月からなので、 普通に考えたら、8月はアクセス数全てが新規ユーザのはずだと思うのですが、 実際には、59人中、17人が新規ユーザとなってしまいました。 何か、原因、解決策等ありますでしょうか? (そもそもの、新規ユーザ取得SQLのロジックが破たんしているようで…) すみません。 SELECT COUNT(CASE WHEN date_format(b.c, '%Y-%m') = date_format(now(), '%Y-%m') THEN 1 ELSE NULL END) AS 'this', COUNT(CASE WHEN date_format(b.c, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 1 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS 'one', COUNT(CASE WHEN date_format(b.c, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 2 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS 'two', COUNT(CASE WHEN date_format(b.c, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 3 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS 'three', COUNT(CASE WHEN date_format(b.c, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 4 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS 'four', COUNT(CASE WHEN date_format(b.c, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 5 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS 'five', COUNT(CASE WHEN date_format(b.c, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 6 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS 'six', COUNT(CASE WHEN date_format(b.c, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 7 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS 'seven', COUNT(CASE WHEN date_format(b.c, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 8 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS 'eight', COUNT(CASE WHEN date_format(b.c, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 9 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS 'nine', COUNT(CASE WHEN date_format(b.c, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 10 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS 'ten', COUNT(CASE WHEN date_format(b.c, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 11 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS 'eleven', COUNT(CASE WHEN date_format(b.c, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 12 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS 'year' FROM (SELECT a.user_id, MIN( a.access_day ) AS c FROM access_record AS a GROUP BY a.user_id ) AS b
Satochan24

2016/01/25 06:00

回答有難うございました。 DISTINCTを使ったuser_idで、重複なしのカウントができました。 表の形式が他の表とちがってしまいましたが、必要な情報は取得できるようになりました。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.50%

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

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

質問する

関連した質問