曜日ごとのカウントの平均を求めたい

次のようなテーブルから期間を指定して下のような曜日ごとのカウント（count）とその平均（ave）と
その期間内にその曜日が何回出現したか（week_count）を求めたいのですが

DROP TABLE IF EXISTS t1;
CREATE TABLE IF NOT EXISTS t1 (
id int(11) NOT NULL,
created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO t1 (id, created) VALUES (1, '2016-07-10 00:00:00');
INSERT INTO t1 (id, created) VALUES (2, '2016-07-17 00:00:00');
INSERT INTO t1 (id, created) VALUES (3, '2016-07-17 00:01:00');
INSERT INTO t1 (id, created) VALUES (4, '2016-07-18 00:00:00');


week　count　　ave　week_count
0　　　　　3　　　1.5　　　　2
1　　　　　1　　　0.5　　　　2
2　　　　　0　　　0　　　　　1
3　　　　　0　　　0　　　　　1
4　　　　　0　　　0　　　　　1
5　　　　　0　　　0　　　　　1
6　　　　　0　　　0　　　　　1

どのようなSQL文にすれば良いのでしょうか？
曜日ごとのカウントだけなら次のSQL文

select count(*) as count, DATE_FORMAT(created, '%w') as week, created from t1 where created between '2016-07-10 00:00:00' and '2016-07-18 23:59:59' group by DATE_FORMAT(created, '%w');

で取得できるのですが・・・

No.2 ベストアンサー 回答者： yambejp 回答日時： 2016/07/19 16:15

では以下のとおり確認ください。

//とりあえずその期間のデータの全日程をとるためt2テーブルを作る
CREATE TABLE t2(d DATE UNIQUE);

//プロシージャを設定
DROP PROCEDURE IF EXISTS modify_t2;
DELIMITER //
CREATE PROCEDURE modify_t2()
BEGIN
TRUNCATE t2;
SELECT @a:=MIN(DATE_FORMAT(created,'%Y-%m-%d')) from t1;
SELECT @b:=MAX(DATE_FORMAT(created,'%Y-%m-%d')) from t1;
WHILE @a<=@b DO
INSERT IGNORE INTO t2 VALUES(@a);
SET @a=@a+INTERVAL 1 DAY;
END WHILE;
END
//

//プロシージャを呼び出し
CALL modify_t2();

※この時点で2016-07-10~2016-07-18のデータができている

//結果表示
SELECT t3.week,COALESCE(t4.count,0) AS count,COALESCE(t4.count,0)/t3.week_count AS AVE,t3.week_count
FROM(
SELECT DATE_FORMAT(d,'%w') AS week,COUNT(*) AS week_count FROM t2 GROUP BY week
) AS t3
LEFT JOIN (
SELECT DATE_FORMAT(created,'%w') AS week,COUNT(*) AS count FROM t1 GROUP BY week
) AS t4 ON t3.week=t4.week;

この回答へのお礼

ご回答ありがとうございます。
試しましたが、理想とする結果が得られました。
ありがとうございます。
それにしても想像してたより複雑な記述が要求される処理だったんですね

お礼日時：2016/07/19 18:33

No.1 回答者： yambejp 回答日時： 2016/07/19 09:35

>その期間内にその曜日が何回出現したか（week_count）

一番最小の '2016-07-10 00:00:00'から、
一番最大の '2016-07-18 00:00:00'までの期間の曜日をカウントするということ？

これは集計じゃないですね
最小値と最大値の日付差を7で割った値とあまりを使って
適宜計算するのが妥当。

集計するなら最小値から始まり最大値までを埋める日付の集合を
テンポラリなどに流し込んで集計することになります。

この回答へのお礼

ご回答ありがとうございます。
なるほど、week_countについてはそのようにして求めたいと思います。
ただこの場合の平均はどのようなSQL文にすれば良いのでしょうか？

お礼日時：2016/07/19 15:22