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

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

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

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

Q&A

解決済

3回答

13642閲覧

【MySQL】1週間単位でアクセス数を集計したい

Satochan24

総合スコア113

MySQL

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

0グッド

2クリップ

投稿2016/02/05 06:53

以前、こちらで質問して、1ヶ月毎のアクセス数集計のSQLを
助けて頂いたのですが、できれば1週間ごとの集計も見たい状況になってきました。
単純にMONTHを1 WEEKに直してもうまくいかず(数が会わない)、
また、アクセス日からの1週間前ではなく、その月の第1週、第2週で集計したいと思っています。

以前の月ごとの集計SQLは下記の通りです。修正する方法、ありますでしょうか?

SELECT a.access_type, COUNT(CASE WHEN date_format(a.access_day, '%Y-%m') = date_format(now(), '%Y-%m') THEN 1 ELSE NULL END) AS 'this', COUNT(CASE WHEN date_format(a.access_day, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 1 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS 'one', COUNT(CASE WHEN date_format(a.access_day, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 2 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS 'two', COUNT(CASE WHEN date_format(a.access_day, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 3 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS 'three', COUNT(CASE WHEN date_format(a.access_day, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 4 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS 'four', COUNT(CASE WHEN date_format(a.access_day, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 5 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS 'five', COUNT(CASE WHEN date_format(a.access_day, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 6 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS 'six', COUNT(CASE WHEN date_format(a.access_day, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 7 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS 'seven', COUNT(CASE WHEN date_format(a.access_day, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 8 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS 'eight', COUNT(CASE WHEN date_format(a.access_day, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 9 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS 'nine', COUNT(CASE WHEN date_format(a.access_day, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 10 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS 'ten', COUNT(CASE WHEN date_format(a.access_day, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 11 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS 'eleven', COUNT(CASE WHEN date_format(a.access_day, '%Y-%m') = date_format(DATE_SUB(now(),INTERVAL 12 MONTH), '%Y-%m') THEN 1 ELSE NULL END) AS 'year' FROM access_record AS a GROUP BY a.access_type ORDER BY this DESC,one DESC,two DESC,three DESC,four DESC,five DESC,six DESC,seven DESC,eight DESC,nine DESC,ten DESC,eleven DESC,a.access_type

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

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

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

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

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

guest

回答3

0

ベストアンサー

以前のというのはこちらですかね?
https://teratail.com/questions/20428

第n週…の定義が分からないと何とも…なのですが、
とりあえず「1~7日を第1週、8~14日を第2週…」ということにすると、以下のような感じでいけます。

書き方は自分流なので、提示いただいているクエリとはまた違った感じですが…

MySQL

1SELECT 2 `a`.`access_type` 3 ,COUNT(TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 0 OR NULL) as `1st` 4 ,COUNT(TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 1 OR NULL) as `2nd` 5 ,COUNT(TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 2 OR NULL) as `3rd` 6 ,COUNT(TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 3 OR NULL) as `4th` 7 ,COUNT(TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 4 OR NULL) as `5th` 8FROM 9 `access_record` AS `a` 10WHERE 11 `a`.`access_day` >= '2016-02-01 00:00:00' 12AND 13 `a`.`access_day` < '2016-03-01 00:00:00' 14GROUP BY 15 `a`.`access_type`

「日付から1引いた数字を7で割り、小数点以下を切り捨てた数字」が
0であれば1週目、1であれば2週目…という感じです。
曜日の概念を入れたい場合もこれをアレンジすればなんとかなるかと思います。

以下、求められている回答ではないと思いますが:
クエリの速度や可読性を考えると、アプリケーション側で集計するなどした方が良い可能性もあります。
また、可能であれば、集計用にサマリレコード的なものを追加した方が良いかもしれません。

投稿2016/02/05 09:01

takushi168

総合スコア228

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

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

Satochan24

2016/02/05 09:21

回答有難うございます。 すごい!ちゃんと月ごとの集計と比較すると数も合ってます! ありがとうございます。こんな方法があるんですね。 ついでにお聞きしたいのですが、WHEREの部分、毎月自動で変わるようにするには、 now()とか使って現在時刻から変わるようにすれば良いでしょうか? 速度、可読性…社内のサーバで見るだけなので特に問題ないと思いますが、アプリケーション側とは、PHPとかでということでしょうか? サマリレコード!?調べておきます。
takushi168

2016/02/08 01:11

>速度・可読性・サマリレコード アプリ側でというのは、おっしゃるとおり、PHP等ですね。 SELECT文でまるごと普通に取ってから、そちらで集計するなど…ですが、 社内で見るだけというレベルでしたらSQLだけでも構わないと思います。 サマリについてもアプリ側の変更が必要になるやつですが、 例えば「週番号」カラムを一つ追加して、アクセスログを記録していくタイミングで hirohiroさんの書かれているような週番号を合わせて記録してしまえば、 それを使って比較的簡単なSELECT文を書くだけで済みます。 また、定期的(数分に1回とか)に集計自体を行って集計テーブルに書き込んでしまえば、 もっと単純にそれをSELECTするだけで済みます。 …が、これも社内で見るだけというレベルでしたら気にされなくて大丈夫だと思います。
Satochan24

2016/02/08 01:53

回答有難うございます。 そうなんですね。 ただ、基本、MySQLの読み取り権限しか与えられていないので、 新たに列追加や集計テーブル作成はできないはずです。 もう少し、自分で教えて頂いたSQLを検証してみて、また進捗等コメントしたいと思います。
takushi168

2016/02/08 04:16 編集

>MySQLの読み取り権限しか与えられていないので なるほど、了解です。ではSQLでなんとかする方向になりますね。 >WHEREの部分、毎月自動で変わるようにするには、now()とか使って現在時刻から変わるようにすれば良いでしょうか? すみません、今更ですが読み飛ばしてました! こちらおっしゃる通りで大丈夫だと思います。 例えば YEAR(NOW()) MONTH(NOW()) DAY(NOW()) で年月日が取れますので、こういうのを上手く組み込んでみる感じで。
Satochan24

2016/02/08 05:30

何度もすみません。 どんどん欲張りになってきてしまって、できれば2ヶ月分の表を見れたほうが、比較もしやすいと思いました。 上記コードを2つ用意して、LEFT JOINあるいは、RIGHT JOINを使えば、データがない access_typeの項目も表示されるようになるのでは!?と思い、試してみたのですが、 文法エラーが多発してしまいうまくいきません。 そもそも、上記コードを利用して2ヶ月分の表示は可能でしょうか?
takushi168

2016/02/08 06:46

いえ、こちらも小出しな感じですみません…。 どういうSQLを書いてエラーになったのかをそのまま出していただけるとありがたいです。 単純に、1月分と2月分を別々に取ってきて…という感じで、2回SELECTするのではマズいんですかね? であれば、単純に2ヶ月分取るなら↓のような感じですかねー。 すごいやっつけな感じですし、動的にするならまた要アレンジですが… SELECT `a`.`access_type` ,COUNT(DATE_FORMAT(`a`.`access_day`, '%m') = 1 AND TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 0 OR NULL) as `jan_1st` ,COUNT(DATE_FORMAT(`a`.`access_day`, '%m') = 1 AND TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 1 OR NULL) as `jan_2nd` ,COUNT(DATE_FORMAT(`a`.`access_day`, '%m') = 1 AND TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 2 OR NULL) as `jan_3rd` ,COUNT(DATE_FORMAT(`a`.`access_day`, '%m') = 1 AND TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 3 OR NULL) as `jan_4th` ,COUNT(DATE_FORMAT(`a`.`access_day`, '%m') = 1 AND TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 4 OR NULL) as `jan_5th` ,COUNT(DATE_FORMAT(`a`.`access_day`, '%m') = 2 AND TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 0 OR NULL) as `feb_1st` ,COUNT(DATE_FORMAT(`a`.`access_day`, '%m') = 2 AND TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 1 OR NULL) as `feb_2nd` ,COUNT(DATE_FORMAT(`a`.`access_day`, '%m') = 2 AND TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 2 OR NULL) as `feb_3rd` ,COUNT(DATE_FORMAT(`a`.`access_day`, '%m') = 2 AND TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 3 OR NULL) as `feb_4th` ,COUNT(DATE_FORMAT(`a`.`access_day`, '%m') = 2 AND TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 4 OR NULL) as `feb_5th` FROM `access_record` AS `a` WHERE `a`.`access_day` >= '2016-01-01 00:00:00' AND `a`.`access_day` < '2016-03-01 00:00:00' GROUP BY `a`.`access_type` WHERE句での指定範囲を2ヶ月にして、 COUNT関数内で何月かをチェックしています。 ただ、 >データがないaccess_typeの項目も表示されるようになるのでは!? というのは解決していませんね…。 そこまで見たい場合、access_typeのマスタテーブルとJOINする必要がありますね。
Satochan24

2016/02/08 07:35

回答有難うございます。 なるほど、という感じです。 2回SELECTする形でもいいのですが、PHPで表形式で表示させる場合、多少 操作が面倒になるかなと思い、一度で取得できる方法を検討していました。 因みに、JOINで結合しようとしていた無理矢理なSQLは下記です。 もし、修正方法があれば教えて頂けると助かります。 SELECT `a`.`access_type` ,COUNT(TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 0 OR NULL) as `1st` ,COUNT(TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 1 OR NULL) as `2nd` ,COUNT(TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 2 OR NULL) as `3rd` ,COUNT(TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 3 OR NULL) as `4th` ,COUNT(TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 4 OR NULL) as `5th` FROM `access_record` AS `a` WHERE `a`.`access_day` >= date_format(DATE_SUB(now(),INTERVAL 1 MONTH), '%Y-%m') AND `a`.`access_day` < date_format(now(), '%Y-%m') GROUP BY `a`.`access_type` LEFT OUTER JOIN( SELECT `b`.`access_type` ,COUNT(TRUNCATE((DATE_FORMAT(`b`.`access_day`, '%d') - 1) / 7, 0) = 0 OR NULL) as `1st` ,COUNT(TRUNCATE((DATE_FORMAT(`b`.`access_day`, '%d') - 1) / 7, 0) = 1 OR NULL) as `2nd` ,COUNT(TRUNCATE((DATE_FORMAT(`b`.`access_day`, '%d') - 1) / 7, 0) = 2 OR NULL) as `3rd` ,COUNT(TRUNCATE((DATE_FORMAT(`b`.`access_day`, '%d') - 1) / 7, 0) = 3 OR NULL) as `4th` ,COUNT(TRUNCATE((DATE_FORMAT(`b`.`access_day`, '%d') - 1) / 7, 0) = 4 OR NULL) as `5th` FROM `access_record` AS `b` WHERE `b`.`access_day` >= date_format(DATE_SUB(now(),INTERVAL 2 MONTH), '%Y-%m') AND `b`.`access_day` < date_format(DATE_SUB(now(),INTERVAL 1 MONTH), '%Y-%m') GROUP BY `b`.`access_type` AS d) AS c ON a.access_type = c.d
takushi168

2016/02/08 08:43

JOINはFROM句に並べて書きますね。 (SELECT ... FROM ... LEFT OUTER JOIN ... ON ... WHERE ... という順) 長くなるので細かい部分は「...」で省略しちゃいますが、直すとすればおそらく↓のような感じかと思います。 「前月のSELECT」「前々月のSELECT」の両方をサブクエリとして扱っています。 ただ、これだと「前月はアクセスがなかったが前々月はアクセスがあった人」は出ないです。 SELECT `a`.`access_type` ,`a`.`1st` AS `a_1st` , ... ,`b`.`1st` AS `b_1st` , ... FROM ( SELECT ... FROM `access_record` AS `a` WHERE ...(※前月で期間指定) GROUP BY `a`.`access_type` ) AS `a` LEFT OUTER JOIN ( SELECT ... FROM `access_record` AS `b` WHERE ...(※前々月で期間指定) GROUP BY `b`.`access_type` ) AS `b` ON `a`.`access_type` = `b`.`access_type`
Satochan24

2016/02/08 09:41

ご丁寧に有難うございます。 こういう記述になるんですね! 明日、実際に試してみようと思います。 因みに、前のSQLで、カウントがないアクセスタイプも全て表示させる方法はありますでしょうか? 何度もすみません。
takushi168

2016/02/09 03:21

access_typeの一覧のようなテーブルはありますか? もしあるなら、↓のような感じで大丈夫だと思います。 (仮に、access_type一覧のテーブルを access_type_master とします) SELECT `m`.`access_type` ... FROM `access_type_master` as `m` LEFT OUTER JOIN (...) AS `a` ON `m`.`access_type` = `a`.`access_type` LEFT OUTER JOIN (...) AS `b` ON `m`.`access_type` = `b`.`access_type` サブクエリ「a」「b」の中身は前回のままです。
Satochan24

2016/02/09 07:31

度々、有難うございます。 すみません。一つ前のSQLを試させて頂いてるのですが、AS a とAS bが2回ずつ出てきてる ようです。それぞれ、大きいくくりの方をAS c 、AS d に直して、c.1st、d.1stにしたのですが、syntaxエラーが出ている状況です。原因等わかりますでしょうか?すみません。
Satochan24

2016/02/09 07:35

修正した1つ前のSQLはこちらです。 実行前にphpmyadmin上で、色分けが正しくされていないので、どこか文法がおかしいと思うのですが… SELECT `a`.`access_type` ,`c`.`1st` AS `a_1st` ,`d`.`1st` AS `b_1st` FROM ( SELECT COUNT(TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 0 OR NULL) as `1st` ,COUNT(TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 1 OR NULL) as `2nd` ,COUNT(TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 2 OR NULL) as `3rd` ,COUNT(TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 3 OR NULL) as `4th` ,COUNT(TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 4 OR NULL) as `5th FROM `access_record` AS `a` WHERE `a`.`access_day` >= date_format(DATE_SUB(now(),INTERVAL 1 MONTH), '%Y-%m') AND `a`.`access_day` < date_format(now(), '%Y-%m') GROUP BY `a`.`access_type` ) AS `c` LEFT OUTER JOIN ( SELECT COUNT(TRUNCATE((DATE_FORMAT(`b`.`access_day`, '%d') - 1) / 7, 0) = 0 OR NULL) as `1st` ,COUNT(TRUNCATE((DATE_FORMAT(`b`.`access_day`, '%d') - 1) / 7, 0) = 1 OR NULL) as `2nd` ,COUNT(TRUNCATE((DATE_FORMAT(`b`.`access_day`, '%d') - 1) / 7, 0) = 2 OR NULL) as `3rd` ,COUNT(TRUNCATE((DATE_FORMAT(`b`.`access_day`, '%d') - 1) / 7, 0) = 3 OR NULL) as `4th` ,COUNT(TRUNCATE((DATE_FORMAT(`b`.`access_day`, '%d') - 1) / 7, 0) = 4 OR NULL) as `5th` FROM `access_record` AS `b` WHERE `b`.`access_day` >= date_format(DATE_SUB(now(),INTERVAL 2 MONTH), '%Y-%m') AND `b`.`access_day` < date_format(DATE_SUB(now(),INTERVAL 1 MONTH), '%Y-%m') GROUP BY `b`.`access_type` ) AS `d` ON `a`.`access_type` = `b`.`access_type`
takushi168

2016/02/09 08:28

多分以下修正すれば動くと思います。 ・「5th」のクォートが閉じられていない ・サブクエリのSELECT両方ともで「access_type」を取得する必要がある ・外側のSELECTの「access_type」は「`a`.」でなく「`c`.」にする ・最後の「ON」のエイリアスもaとbではなくcとdにする syntaxエラーで、 「(省略)for the right syntax to use near ~」 みたいに表示されていませんか? (環境によっては出ない等あるかもしれません) もし表示されているようでしたら、「near ~」に表示されている付近をチェックすると異常が見つかったりします。
takushi168

2016/02/09 08:31

また、サブクエリ内であれば同名のエイリアスは大丈夫なはずです。 ↓みたいな簡単な例で試してみてください。 SELECT * FROM (SELECT * FROM (SELECT * FROM `テーブル名` AS `a`) AS `a`) AS `a`
Satochan24

2016/02/09 09:03

回答有難うございます。 何とか、1番目のSQLは出力されるようになりました。 2番目は明日、確認してみます。 以下、修正したSQLです。 SELECT `c`.`access_type` ,`c`.`1st` AS `a_1st` ,`d`.`1st` AS `b_1st` FROM ( SELECT a.access_type,COUNT(TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 0 OR NULL) as `1st` ,COUNT(TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 1 OR NULL) as `2nd` ,COUNT(TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 2 OR NULL) as `3rd` ,COUNT(TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 3 OR NULL) as `4th` ,COUNT(TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 4 OR NULL) as `5th` FROM `access_record` AS `a` WHERE `a`.`access_day` >= date_format(DATE_SUB(now(),INTERVAL 1 MONTH), '%Y-%m') AND `a`.`access_day` < date_format(now(), '%Y-%m') GROUP BY `a`.`access_type` ) AS `c` LEFT OUTER JOIN ( SELECT b.access_type,COUNT(TRUNCATE((DATE_FORMAT(`b`.`access_day`, '%d') - 1) / 7, 0) = 0 OR NULL) as `1st` ,COUNT(TRUNCATE((DATE_FORMAT(`b`.`access_day`, '%d') - 1) / 7, 0) = 1 OR NULL) as `2nd` ,COUNT(TRUNCATE((DATE_FORMAT(`b`.`access_day`, '%d') - 1) / 7, 0) = 2 OR NULL) as `3rd` ,COUNT(TRUNCATE((DATE_FORMAT(`b`.`access_day`, '%d') - 1) / 7, 0) = 3 OR NULL) as `4th` ,COUNT(TRUNCATE((DATE_FORMAT(`b`.`access_day`, '%d') - 1) / 7, 0) = 4 OR NULL) as `5th` FROM `access_record` AS `b` WHERE `b`.`access_day` >= date_format(DATE_SUB(now(),INTERVAL 2 MONTH), '%Y-%m') AND `b`.`access_day` < date_format(DATE_SUB(now(),INTERVAL 1 MONTH), '%Y-%m') GROUP BY `b`.`access_type` ) AS `d` ON `c`.`access_type` = `d`.`access_type`
Satochan24

2016/02/10 01:16

2番目のSQLについて、 access_type一覧のテーブルはないですね。 アクセストランザクションとして定義されています。 恐らく、アプリの中で、こういう処理をしたら、このaccess_typeの番号を送信みたいな 感じで送られて、access_recordというテーブルのaccess_type欄に番号が記録されるようです。 その場合、access_typeを全て表示させる方法はないでしょうか? テーブルとして持ってない以上、どんな数値があるかは、送信元のアプリのみぞ知るという感じになるかと思います。
takushi168

2016/02/10 02:32

なるほど…。 SELECT文では重複なしで値を取ってくる方法がありまして、 SELECT DISTINCT `access_type` FROM `access_record` とやれば、access_type一覧が取れるはずです。 ということで、これをaccess_type一覧テーブル代わりに使って、 わたしが書いたSQLの `access_type_master` の部分を、上記SELECTをサブクエリにして差し替えればOKのはずです。
Satochan24

2016/02/10 07:11

回答、有難うございます。 試してみたのですが、unknown column 'a.access_type' in 'field list' 列名が不明なようです。 どこか、間違ってますでしょうか? SELECT `m`.`access_type` FROM (SELECT DISTINCT `access_type` FROM `access_record`) as `m` LEFT OUTER JOIN (SELECT a.access_type,COUNT(TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 0 OR NULL) as `1st` ,COUNT(TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 1 OR NULL) as `2nd` ,COUNT(TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 2 OR NULL) as `3rd` ,COUNT(TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 3 OR NULL) as `4th` ,COUNT(TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 4 OR NULL) as `5th` FROM `access_record`) AS `a` ON `m`.`access_type` = `a`.`access_type` LEFT OUTER JOIN (SELECT b.access_type,COUNT(TRUNCATE((DATE_FORMAT(`b`.`access_day`, '%d') - 1) / 7, 0) = 0 OR NULL) as `1st` ,COUNT(TRUNCATE((DATE_FORMAT(`b`.`access_day`, '%d') - 1) / 7, 0) = 1 OR NULL) as `2nd` ,COUNT(TRUNCATE((DATE_FORMAT(`b`.`access_day`, '%d') - 1) / 7, 0) = 2 OR NULL) as `3rd` ,COUNT(TRUNCATE((DATE_FORMAT(`b`.`access_day`, '%d') - 1) / 7, 0) = 3 OR NULL) as `4th` ,COUNT(TRUNCATE((DATE_FORMAT(`b`.`access_day`, '%d') - 1) / 7, 0) = 4 OR NULL) as `5th` FROM `access_record`) AS `b` ON `m`.`access_type` = `b`.`access_type`
takushi168

2016/02/10 08:18

2つ目・3つ目のサブクエリ内のテーブルにエイリアスが付いていませんね。 簡略化して書くと、今はサブクエリ内↓のような感じになっています。 「SELECT `a`.`列名` FROM `テーブル名`」 これだと「a」はどこにも定義されていませんので、エラーになってしまいます。 「SELECT `列名` FROM `テーブル名`」 「SELECT `a`.`列名` FROM `テーブル名` AS `a`」 のいずれかにする必要がありますね。
Satochan24

2016/02/10 09:37

回答有難うございました。 できました!できました!アクセスタイプが全て表示できました。 有難うございました。 最終的にどうまとめるか検討中です。
Satochan24

2016/02/10 10:19

最終的に以下のようなSQLにまとめたのですが、最初の行に値がすべて合計されてしまいました。何か考えられる原因ありますでしょうか? access_typeを出力がある項目だけ表示させるSQLでは、問題なく成功しています。 SELECT `m`.`access_type`, This_1st, This_2nd, This_3rd, This_4th, This_5th, Last_1st, Last_2nd, Last_3rd, Last_4th, Last_5th FROM (SELECT DISTINCT `access_type` FROM `access_record`) as `m` LEFT OUTER JOIN (SELECT a.access_type,COUNT(TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 0 OR NULL) as `This_1st` ,COUNT(TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 1 OR NULL) as `This_2nd` ,COUNT(TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 2 OR NULL) as `This_3rd` ,COUNT(TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 3 OR NULL) as `This_4th` ,COUNT(TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 4 OR NULL) as `This_5th` FROM `access_record`AS a WHERE `a`.`access_day` >= date_format(DATE_SUB(now(),INTERVAL 1 MONTH), '%Y-%m') AND `a`.`access_day` < date_format(now(), '%Y-%m')) AS `a` ON `m`.`access_type` = `a`.`access_type` LEFT OUTER JOIN (SELECT b.access_type,COUNT(TRUNCATE((DATE_FORMAT(`b`.`access_day`, '%d') - 1) / 7, 0) = 0 OR NULL) as `Last_1st` ,COUNT(TRUNCATE((DATE_FORMAT(`b`.`access_day`, '%d') - 1) / 7, 0) = 1 OR NULL) as `Last_2nd` ,COUNT(TRUNCATE((DATE_FORMAT(`b`.`access_day`, '%d') - 1) / 7, 0) = 2 OR NULL) as `Last_3rd` ,COUNT(TRUNCATE((DATE_FORMAT(`b`.`access_day`, '%d') - 1) / 7, 0) = 3 OR NULL) as `Last_4th` ,COUNT(TRUNCATE((DATE_FORMAT(`b`.`access_day`, '%d') - 1) / 7, 0) = 4 OR NULL) as `Last_5th` FROM `access_record`AS b WHERE `b`.`access_day` >= date_format(DATE_SUB(now(),INTERVAL 2 MONTH), '%Y-%m') AND `b`.`access_day` < date_format(DATE_SUB(now(),INTERVAL 1 MONTH), '%Y-%m')) AS `b` ON `m`.`access_type` = `b`.`access_type` ORDER BY `m`.`access_type`
takushi168

2016/02/12 01:27

サブクエリ2つからGROUP句が抜けちゃってますね。 ・GROUP BY `a`.`access_type` ・GROUP BY `b`.`access_type` をしかるべきところに入れてやればOKのはずです。
Satochan24

2016/02/12 05:09

回答有難うございます。 FROM `access_record`AS a の後に、GROUP~ をつけたのですが、そうすると、そのあとのWHEREをHAVINGに変える必要があって、 すると、HAVINGで使用しているaccess_dayをGROUP BYにも含める必要があり… 結局表示がおかしくなりました。 そろそろ限界に近いので、アクセスタイプを無理に全て表示させないやり方で 進めようかと思っています。
takushi168

2016/02/12 06:44

GROUP句はWHEREの後であれば大丈夫だと思いますよ!
Satochan24

2016/02/12 09:13

なぜか、文法エラーでうまくいきませんでした。 来週、火曜以降に再チャレンジするかもしれません。
Satochan24

2016/02/16 02:41

今度こそ大丈夫だろうと思って、下記のSQLを実行したら、 #1064 - という謎のエラーが出てしまいました。 調べてみると、SQLの文法エラーらしいのですが… どこかおかしいところありますでしょうか? 【アクセスタイプ全て表示】 SELECT `m`.`access_type`, This_1st, This_2nd, This_3rd, This_4th, This_5th, Last_1st, Last_2nd, Last_3rd, Last_4th, Last_5th FROM (SELECT DISTINCT `access_type` FROM `access_record`) as `m` LEFT OUTER JOIN (SELECT a.access_type,COUNT(TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 0 OR NULL) as `This_1st` ,COUNT(TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 1 OR NULL) as `This_2nd` ,COUNT(TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 2 OR NULL) as `This_3rd` ,COUNT(TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 3 OR NULL) as `This_4th` ,COUNT(TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 4 OR NULL) as `This_5th` FROM `access_record`AS a WHERE `a`.`access_day` >= date_format(DATE_SUB(now(),INTERVAL 1 MONTH), '%Y-%m') AND `a`.`access_day` < date_format(now(), '%Y-%m')) AS `a` GROUP BY `a`.`access_type` ON `m`.`access_type` = `a`.`access_type` LEFT OUTER JOIN (SELECT b.access_type,COUNT(TRUNCATE((DATE_FORMAT(`b`.`access_day`, '%d') - 1) / 7, 0) = 0 OR NULL) as `Last_1st` ,COUNT(TRUNCATE((DATE_FORMAT(`b`.`access_day`, '%d') - 1) / 7, 0) = 1 OR NULL) as `Last_2nd` ,COUNT(TRUNCATE((DATE_FORMAT(`b`.`access_day`, '%d') - 1) / 7, 0) = 2 OR NULL) as `Last_3rd` ,COUNT(TRUNCATE((DATE_FORMAT(`b`.`access_day`, '%d') - 1) / 7, 0) = 3 OR NULL) as `Last_4th` ,COUNT(TRUNCATE((DATE_FORMAT(`b`.`access_day`, '%d') - 1) / 7, 0) = 4 OR NULL) as `Last_5th` FROM `access_record`AS b WHERE `b`.`access_day` >= date_format(DATE_SUB(now(),INTERVAL 2 MONTH), '%Y-%m') AND `b`.`access_day` < date_format(DATE_SUB(now(),INTERVAL 1 MONTH), '%Y-%m')) AS `b` GROUP BY `b`.`access_type` ON `m`.`access_type` = `b`.`access_type` ORDER BY `m`.`access_type`
takushi168

2016/02/17 01:59

GROUP句の位置が…。 集計したいのはサブクエリの中身なので、カッコの中に入れて ... WHERE ~ AND ~ GROUP ~ ) AS `a` ですね。(bの方も)
Satochan24

2016/02/17 09:14

回答有難うございます! 試してみたら、出来てました。 最終的にPHPで作成できたら、本件クローズ処理します。
takotakot

2016/02/17 10:34

Satochan24 様、コメントが長くなると見にくいので、質問に追記した方が良いと(私は)考えています。 SQL の文法については、勉強された方がいいですね。
Satochan24

2016/02/18 01:23

ご指摘有難うございます。 今後、気をつけるようにします。 SQLも、もっと勉強したいと思います。
guest

0

SQL

1SELECT DATE_FORMAT(MIN(access_day),"%y-%m") AS, w AS, access_type, count(*) AS2FROM ( 3 SELECT access_type, access_day, week(access_day,2) AS w, DATE_FORMAT(access_day,"%Y") AS y 4 FROM access_record 5) tb 6GROUP BY y, w, access_type 7ORDER BY y, w desc, access_type 8LIMIT 53

(動作チェックはしていませんのでエラーや誤動作はあるかも知れません。)

もし現在の出力フォーマットに拘りが無ければ、このようなSQLはいかがでしょう?
(↓予測出力結果フォーマット)

月, 週, access_type, 数 2015-12 53 1 99 2015-12 53 2 12 2015-12 52 1 53 2015-12 52 2 30 2015-12 51 1 2 .....

注意点は3つ

  1. week関数は月のではなく年の第何週かを返す 参考ページ
  2. 第一週の定義が不明
  3. このSQLではデータの無い週は出力されない

[1]のため、12月第一週は「1」ではなく「49」や「48」になります。
[2]このSQLでは日曜~土曜を1週間とし、月を跨る週は日曜を含む側の月に所属しています。
[3]countの結果がゼロのレコードは出力されません。ひょっとするとこれのために不採用かも知れませんね。

最後のlimit 53は一年分なら52か53週なのでお好みで。

投稿2016/02/05 15:07

編集2016/02/08 06:51
hirohiro

総合スコア2068

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

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

Satochan24

2016/02/08 01:28

コメント遅くなり申し訳ありません。 回答有難うございました。 確認中なのですが、SQLを実行すると、 Unknown column 'access_day' in 'field list' と出てしまいます。 access_count AS a にして、access_day を tb.a.access_day という風にはできないですよね!?二重に別名を付けた場合の指定の仕方とかありますでしょうか?あるいは、そもそも別の原因なのか…
hirohiro

2016/02/08 06:50

あああ、すみません。サブクエリにaccess_day書き忘れていますね。 >>SELECT access_type,week(access_day,2) AS w, DATE_FORMAT(access_day,"%Y") AS y これは >>SELECT access_type,week(access_day,2) AS w, DATE_FORMAT(access_day,"%Y") AS y, access_day こうですね。本文書き直しておきます
Satochan24

2016/02/08 07:07

コメント有難うございます。 表示されることを確認しました。 全体的に少し見づらいかなぁということと、 表示が2015-12以前となっていて、 2016-1のデータが一番最後になっていました。 あとは、実際のPHPで表示方法を調整するだけかなぁと思いました。 参考にさせて頂きます。 有難うございました。
guest

0

面白そうなので参戦してみます。
カレンダーでの週となると以下の感じでどうでしょう?
(MySQLの環境がないので試せていません。イメージということでm(_ _)m)

SELECT a.access_type, ,SUM(WHEN CASE WEEK(DATE_SUB(DATE_FORMAT(CURDATE(),"%Y-%m-01"), INTERVAL 0 DAY)) = WEEK(a.access_day) THEN 1 ELSE 0 END) '1' ,SUM(WHEN CASE WEEK(DATE_SUB(DATE_FORMAT(CURDATE(),"%Y-%m-01"), INTERVAL 7 DAY)) = WEEK(a.access_day) THEN 1 ELSE 0 END) '2' ,SUM(WHEN CASE WEEK(DATE_SUB(DATE_FORMAT(CURDATE(),"%Y-%m-01"), INTERVAL 14 DAY)) = WEEK(a.access_day) THEN 1 ELSE 0 END) '3' ,SUM(WHEN CASE WEEK(DATE_SUB(DATE_FORMAT(CURDATE(),"%Y-%m-01"), INTERVAL 21 DAY)) = WEEK(a.access_day) THEN 1 ELSE 0 END) '4' ,SUM(WHEN CASE WEEK(DATE_SUB(DATE_FORMAT(CURDATE(),"%Y-%m-01"), INTERVAL 28 DAY)) = WEEK(a.access_day) THEN 1 ELSE 0 END) '5' ,SUM(WHEN CASE WEEK(DATE_SUB(DATE_FORMAT(CURDATE(),"%Y-%m-01"), INTERVAL 31 DAY)) = WEEK(a.access_day) THEN 1 ELSE 0 END) '6' FROM access_record AS a GROUP BY a.access_type

月初めの日にちの週番号を取得して、それと同じ週番号は1週目、その7日後は2週目としていくやり方です。

6週目が無い場合は次の月が出てしまうところが、ちょっと苦しいですが。

参考
「8月の第3土日」をデータベース化するには?
【MySQL】SQL発行時に日時計算(加算・減算)する方法

投稿2016/02/05 10:19

nobuhito

総合スコア146

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

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

Satochan24

2016/02/08 01:35

コメント遅くなり申し訳ありません。 回答頂き有難うございました。 上記SQLを試してみたところ、2行目でSQL syntax;になってしまいました。 SUMをCOUNTに変えたり、行末を AS '1' にしてみましたが、改善しませんでした。 何か原因等考えられますでしょうか?
nobuhito

2016/02/08 01:56

ごめんなさい。間違ってました。 `WHEN CASE` ではなく `CASE WHEN` ですね。
Satochan24

2016/02/08 06:58 編集

回答有難うございます。 SUMをCOUNTに直して、CASE WHENで実行したのですが、全ての列(1、2、3、4、5、6)で同じ数値になってしまいました。どこか実行でミスしたのでしょうか?
Satochan24

2016/02/08 04:47

同じ数値というのは、全ての列で同じという意味で、行は値が違います。
Satochan24

2016/02/08 05:05

すみません。勘違いしてました。COUNTじゃなくて、SUMで良かったんですね! SUMに直したらキチンと集計結果が出て、数も合いました。 有難うございます。 どの方法でいくか、少し検証させてください。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.50%

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

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

質問する

関連した質問