【MySQL】該当月単位での集計はできたので、週単位の集計にまで絞り込みたい

アクセス集計を月単位でしていたのですが、週単位の集計を作成中です。

以下の先月の集計結果を出すSQLがあり、

【全ユーザ数1月】結果１４

SELECT COUNT( * ) 
FROM (
SELECT DISTINCT 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' ) 
) AS b

週単位に絞りこむために、以前ここで質問して教わったやり方を参考にし、COUNT()の部分に、

SELECT
COUNT(DATE_FORMAT(`a`.`access_day`, '%m') = MONTH(DATE_SUB( now( ) , INTERVAL 1 
MONTH )) AND TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 0 OR NULL) as `Last_1st`
,COUNT(DATE_FORMAT(`a`.`access_day`, '%m') = MONTH(DATE_SUB( now( ) , INTERVAL 1 
MONTH )) AND TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 1 OR NULL) as `Last_2nd`
,COUNT(DATE_FORMAT(`a`.`access_day`, '%m') = MONTH(DATE_SUB( now( ) , INTERVAL 1 
MONTH )) AND TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 2 OR NULL) as `Last_3rd`
,COUNT(DATE_FORMAT(`a`.`access_day`, '%m') = MONTH(DATE_SUB( now( ) , INTERVAL 1 
MONTH )) AND TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 3 OR NULL) as `Last_4th`
,COUNT(DATE_FORMAT(`a`.`access_day`, '%m') = MONTH(DATE_SUB( now( ) , INTERVAL 1 
MONTH )) AND TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 4 OR NULL) as `Last_5th`
,COUNT(DATE_FORMAT(`a`.`access_day`, '%m') = MONTH(NOW()) AND TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 0 OR NULL) as `This_1st`
,COUNT(DATE_FORMAT(`a`.`access_day`, '%m') = MONTH(NOW()) AND TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 1 OR NULL) as `This_2nd`
,COUNT(DATE_FORMAT(`a`.`access_day`, '%m') = MONTH(NOW()) AND TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 2 OR NULL) as `This_3rd`
,COUNT(DATE_FORMAT(`a`.`access_day`, '%m') = MONTH(NOW()) AND TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 3 OR NULL) as `This_4th`
,COUNT(DATE_FORMAT(`a`.`access_day`, '%m') = MONTH(NOW()) AND TRUNCATE((DATE_FORMAT(`a`.`access_day`, '%d') - 1) / 7, 0) = 4 OR NULL) as `This_5th`
 
FROM (
SELECT DISTINCT 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' ) 
) AS b

と入れてみたのですが、a.access_dayが不明とエラーになりました。
そこで、FROMの中のSELECT文中に、SELECT DISTINCT user_id,a.access_day AS c
とし、b.cとして、a.access_dayを参照できるようにしたのですが、今度は数が合わなくなりました。

恐らく、SELECTの中に入れたためだと思うのですが、ここに入れないと参照できなくなると考えられます。
何か、週単位の集計で解決策ありますでしょうか？

行動規範の内容に同意します

回答1件

ベストアンサー

こんにちは。

シンプルに以下のようにするだけでは、欲しい情報は得られませんか？

---------- ↓ ここから ↓ ----------
SELECT
COUNT( DATE_FORMAT( access_day , '%m' ) = MONTH( DATE_SUB( NOW() , INTERVAL 1 MONTH ) ) AND TRUNCATE((DATE_FORMAT( access_day , '%d' ) - 1 ) / 7 , 0 ) = 0 OR NULL ) AS Last_1st
, COUNT( DATE_FORMAT( access_day , '%m' ) = MONTH( DATE_SUB( NOW() , INTERVAL 1 MONTH ) ) AND TRUNCATE((DATE_FORMAT( access_day , '%d' ) - 1 ) / 7 , 0 ) = 1 OR NULL ) AS Last_2nd
, COUNT( DATE_FORMAT( access_day , '%m' ) = MONTH( DATE_SUB( NOW() , INTERVAL 1 MONTH ) ) AND TRUNCATE((DATE_FORMAT( access_day , '%d' ) - 1 ) / 7 , 0 ) = 2 OR NULL ) AS Last_3rd
, COUNT( DATE_FORMAT( access_day , '%m' ) = MONTH( DATE_SUB( NOW() , INTERVAL 1 MONTH ) ) AND TRUNCATE((DATE_FORMAT( access_day , '%d' ) - 1 ) / 7 , 0 ) = 3 OR NULL ) AS Last_4th
, COUNT( DATE_FORMAT( access_day , '%m' ) = MONTH( DATE_SUB( NOW() , INTERVAL 1 MONTH ) ) AND TRUNCATE((DATE_FORMAT( access_day , '%d' ) - 1 ) / 7 , 0 ) = 4 OR NULL ) AS Last_5th
, COUNT( DATE_FORMAT( access_day , '%m' ) = MONTH( NOW() ) AND TRUNCATE( ( DATE_FORMAT( access_day , '%d' ) - 1) / 7, 0 ) = 0 OR NULL) AS This_1st
, COUNT( DATE_FORMAT( access_day , '%m' ) = MONTH( NOW() ) AND TRUNCATE( ( DATE_FORMAT( access_day , '%d' ) - 1) / 7, 0 ) = 1 OR NULL) AS This_2nd
, COUNT( DATE_FORMAT( access_day , '%m' ) = MONTH( NOW() ) AND TRUNCATE( ( DATE_FORMAT( access_day , '%d' ) - 1) / 7, 0 ) = 2 OR NULL) AS This_3rd
, COUNT( DATE_FORMAT( access_day , '%m' ) = MONTH( NOW() ) AND TRUNCATE( ( DATE_FORMAT( access_day , '%d' ) - 1) / 7, 0 ) = 3 OR NULL) AS This_4th
, COUNT( DATE_FORMAT( access_day , '%m' ) = MONTH( NOW() ) AND TRUNCATE( ( DATE_FORMAT( access_day , '%d' ) - 1) / 7, 0 ) = 4 OR NULL) AS This_5th
FROM
access_record
WHERE
access_type = 101
---------- ↑ ここまで ↑ ----------

ご確認お願い致します。
また、ご希望通りでない場合ご指摘頂けると幸いです。

投稿2016/02/12 01:43

退会済みユーザー

総合スコア0

Satochan24

2016/02/12 01:59

回答有難うございます。さっそく試してみました。すると、集計結果に、重複したuser_idが含まれているようです。月間の集計では、DISTINCTを使って、重複なしの集計で合計１４人のところ、上記SQLだと、２３人になっています。できれば、月間に合わせて、重複なしの集計にしたいところなのですが… 可能でしょうか？

退会済みユーザー

2016/02/12 02:23

こんにちは。ご指摘ありがとうございます。 1点ご質問させて頂きますと、以下のようなデータの場合はどうカウントされますか？ user_id,access_day 0001,2016-01-05 0001,2016-01-12 0001,2016-01-19 0001,2016-01-26 月間（2016-01）で集約すれば 1 となりますが、週間で集約した場合、各週でのカウントは 1 となりますが、月間は 4 となってしまいます。そうした場合、SELECT句で先月の第1週～今月の4週までのどこに含めればよいのでしょう。質問に質問で申し訳ございませんが、よろしくお願い致します。

Satochan24

2016/02/12 02:53

回答有難うございます。そうですね。２通りのカウントの仕方がありますね。でも、あくまで月間集計の内訳を確認したいというイメージなので、月間で集約したいと思います。その場合、一番最初の週に含めたいと考えています。

退会済みユーザー

2016/02/12 03:04

こんにちは。最初の週に含める場合以下のような形でいかがでしょうか。 ---------- ↓ ここから ↓ ---------- SELECT COUNT( DATE_FORMAT( a.access_day , '%m' ) = MONTH( DATE_SUB( NOW() , INTERVAL 1 MONTH ) ) AND TRUNCATE((DATE_FORMAT( a.access_day , '%d' ) - 1 ) / 7 , 0 ) = 0 OR NULL ) AS `Last_1st` , COUNT( DATE_FORMAT( a.access_day , '%m' ) = MONTH( DATE_SUB( NOW() , INTERVAL 1 MONTH ) ) AND TRUNCATE((DATE_FORMAT( a.access_day , '%d' ) - 1 ) / 7 , 0 ) = 1 OR NULL ) AS `Last_2nd` , COUNT( DATE_FORMAT( a.access_day , '%m' ) = MONTH( DATE_SUB( NOW() , INTERVAL 1 MONTH ) ) AND TRUNCATE((DATE_FORMAT( a.access_day , '%d' ) - 1 ) / 7 , 0 ) = 2 OR NULL ) AS `Last_3rd` , COUNT( DATE_FORMAT( a.access_day , '%m' ) = MONTH( DATE_SUB( NOW() , INTERVAL 1 MONTH ) ) AND TRUNCATE((DATE_FORMAT( a.access_day , '%d' ) - 1 ) / 7 , 0 ) = 3 OR NULL ) AS `Last_4th` , COUNT( DATE_FORMAT( a.access_day , '%m' ) = MONTH( DATE_SUB( NOW() , INTERVAL 1 MONTH ) ) AND TRUNCATE((DATE_FORMAT( a.access_day , '%d' ) - 1 ) / 7 , 0 ) = 4 OR NULL ) AS `Last_5th` , COUNT( DATE_FORMAT( a.access_day , '%m' ) = MONTH( NOW() ) AND TRUNCATE( ( DATE_FORMAT( a.access_day , '%d' ) - 1) / 7, 0 ) = 0 OR NULL) AS `This_1st` , COUNT( DATE_FORMAT( a.access_day , '%m' ) = MONTH( NOW() ) AND TRUNCATE( ( DATE_FORMAT( a.access_day , '%d' ) - 1) / 7, 0 ) = 1 OR NULL) AS `This_2nd` , COUNT( DATE_FORMAT( a.access_day , '%m' ) = MONTH( NOW() ) AND TRUNCATE( ( DATE_FORMAT( a.access_day , '%d' ) - 1) / 7, 0 ) = 2 OR NULL) AS `This_3rd` , COUNT( DATE_FORMAT( a.access_day , '%m' ) = MONTH( NOW() ) AND TRUNCATE( ( DATE_FORMAT( a.access_day , '%d' ) - 1) / 7, 0 ) = 3 OR NULL) AS `This_4th` , COUNT( DATE_FORMAT( a.access_day , '%m' ) = MONTH( NOW() ) AND TRUNCATE( ( DATE_FORMAT( a.access_day , '%d' ) - 1) / 7, 0 ) = 4 OR NULL) AS `This_5th` FROM ( SELECT user_id , MIN( access_day ) AS access_day FROM access_record WHERE access_type = 101 AND date_format( access_day , '%Y-%m' ) = date_format( DATE_SUB( NOW() , INTERVAL 1 MONTH ) , '%Y-%m' ) GROUP BY user_id ) AS a ---------- ↑ここまで ↑ ---------- 何度もお手数おかけしますが、試してみてください。宜しくお願い致します。

退会済みユーザー

2016/02/12 03:17

こんにちは。少しミスりました。上記ですと、先月分のみしかカウントされないかと思いますので、昼ご飯食べたらまた考えます。失礼しました。

退会済みユーザー

2016/02/12 04:05

こんにちは。今度はどうでしょうか。 ---------- ↓ ここから ↓ ---------- SELECT COUNT( DATE_FORMAT( a.access_day , '%m' ) = MONTH( DATE_SUB( NOW() , INTERVAL 1 MONTH ) ) AND TRUNCATE((DATE_FORMAT( a.access_day , '%d' ) - 1 ) / 7 , 0 ) = 0 OR NULL ) AS `Last_1st` , COUNT( DATE_FORMAT( a.access_day , '%m' ) = MONTH( DATE_SUB( NOW() , INTERVAL 1 MONTH ) ) AND TRUNCATE((DATE_FORMAT( a.access_day , '%d' ) - 1 ) / 7 , 0 ) = 1 OR NULL ) AS `Last_2nd` , COUNT( DATE_FORMAT( a.access_day , '%m' ) = MONTH( DATE_SUB( NOW() , INTERVAL 1 MONTH ) ) AND TRUNCATE((DATE_FORMAT( a.access_day , '%d' ) - 1 ) / 7 , 0 ) = 2 OR NULL ) AS `Last_3rd` , COUNT( DATE_FORMAT( a.access_day , '%m' ) = MONTH( DATE_SUB( NOW() , INTERVAL 1 MONTH ) ) AND TRUNCATE((DATE_FORMAT( a.access_day , '%d' ) - 1 ) / 7 , 0 ) = 3 OR NULL ) AS `Last_4th` , COUNT( DATE_FORMAT( a.access_day , '%m' ) = MONTH( DATE_SUB( NOW() , INTERVAL 1 MONTH ) ) AND TRUNCATE((DATE_FORMAT( a.access_day , '%d' ) - 1 ) / 7 , 0 ) = 4 OR NULL ) AS `Last_5th` , COUNT( DATE_FORMAT( a.access_day , '%m' ) = MONTH( NOW() ) AND TRUNCATE( ( DATE_FORMAT( a.access_day , '%d' ) - 1) / 7, 0 ) = 0 OR NULL) AS `This_1st` , COUNT( DATE_FORMAT( a.access_day , '%m' ) = MONTH( NOW() ) AND TRUNCATE( ( DATE_FORMAT( a.access_day , '%d' ) - 1) / 7, 0 ) = 1 OR NULL) AS `This_2nd` , COUNT( DATE_FORMAT( a.access_day , '%m' ) = MONTH( NOW() ) AND TRUNCATE( ( DATE_FORMAT( a.access_day , '%d' ) - 1) / 7, 0 ) = 2 OR NULL) AS `This_3rd` , COUNT( DATE_FORMAT( a.access_day , '%m' ) = MONTH( NOW() ) AND TRUNCATE( ( DATE_FORMAT( a.access_day , '%d' ) - 1) / 7, 0 ) = 3 OR NULL) AS `This_4th` , COUNT( DATE_FORMAT( a.access_day , '%m' ) = MONTH( NOW() ) AND TRUNCATE( ( DATE_FORMAT( a.access_day , '%d' ) - 1) / 7, 0 ) = 4 OR NULL) AS `This_5th` FROM ( SELECT user_id , access_day FROM access_record WHERE access_type = 101 AND date_format( access_day , '%Y-%m' ) >= date_format( DATE_SUB( NOW() , INTERVAL 1 MONTH ) , '%Y-%m' ) AND date_format( access_day , '%Y-%m' ) <= date_format( NOW() , '%Y-%m' ) GROUP BY user_id , date_format( access_day , '%Y-%m' ) ) AS a ---------- ↑ここまで ↑ ---------- 宜しくお願い致します。

Satochan24

2016/02/12 05:05

何度も回答有難うございます。大丈夫そうです。少し様子見させてください。因みに、SQL拝見させて頂いたのですが、どのよう修正されたのかがイマイチ… 絞り込みできたポイントなどがあれば教えてください。

退会済みユーザー

2016/02/12 05:31

こんにちは。逆に何度もコメントして見辛くしてしまい大変申し訳ございませんでした。 >因みに、SQL拝見させて頂いたのですが、どのよう修正されたのかがイマイチ… WHERE句の条件を変えました。前月から当月までを抽出範囲としました。絞り込みのポイントですが、まず母体となるテーブルをクエリで抽出しますが、そのテーブルは月毎、ユーザー毎でGROUP BYしたデータになります。【データ】 user_id,access_day 0001,2016-01-08 0001,2016-01-15 0001,2016-01-22 0001,2016-02-05 0002,2016-01-08 0002,2016-01-15 0002,2016-02-12 0003,2016-01-15 0003,2016-02-12 【結果】 user_id,access_day 0001,2016-01-08 0001,2016-02-05 0002,2016-01-08 0002,2016-02-12 0003,2016-01-15 0003,2016-02-12 この結果を元に、各週にカウントしていく感じです。 ※access_dayはユーザー毎の月毎最小アクセス日付になります。（MIN関数を付けるの忘れたので、明示的にしてあげたほうが確実です。申し訳ない。）いかがでしょうか。認識の違い等ございましたらご指摘頂けると幸いです。

Satochan24

2016/02/12 06:57

回答ありがとうございました。なるほど。そういうことなんですね。教えて頂いた方向でSQLの作成を進めたいと思います。大変有難うございました。

行動規範の内容に同意します