
次のようなテーブルから期間を指定して下のような曜日ごとのカウント(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ベストアンサー
- 回答日時:
では以下のとおり確認ください。
//とりあえずその期間のデータの全日程をとるため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;
ご回答ありがとうございます。
試しましたが、理想とする結果が得られました。
ありがとうございます。
それにしても想像してたより複雑な記述が要求される処理だったんですね
No.1
- 回答日時:
>その期間内にその曜日が何回出現したか(week_count)
一番最小の '2016-07-10 00:00:00'から、
一番最大の '2016-07-18 00:00:00'までの期間の曜日をカウントするということ?
これは集計じゃないですね
最小値と最大値の日付差を7で割った値とあまりを使って
適宜計算するのが妥当。
集計するなら最小値から始まり最大値までを埋める日付の集合を
テンポラリなどに流し込んで集計することになります。
ご回答ありがとうございます。
なるほど、week_countについてはそのようにして求めたいと思います。
ただこの場合の平均はどのようなSQL文にすれば良いのでしょうか?
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルの関数について教えて...
-
エクセルで最後の文字だけ置き...
-
[MySQL] UNIQUE制約の値を更新...
-
SQL Left Join で重複を排除す...
-
#1136 - Column count doesn't ...
-
クエリ表示と、ADOで抽出したレ...
-
エラー 1068 (42000): 複数の主...
-
SQLにて特定の文字を除いた検索...
-
Excel2000でレーダーチャートの...
-
sqlで、600行あるテーブルを100...
-
ある条件の最大値+1を初番する...
-
select文のwhere句に配列を入れ...
-
Access パラメータクエリをcsv...
-
MySQLでエラーがでます
-
updateを1行ずつ実行したい。
-
SELECT~LIKE~の結果が変
-
Mysql でレコードを追加した時...
-
EXPLAINのtypeがALLのときの対...
-
一つ前のレコードの値と減算し...
-
副問合せの書き方について
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルで最後の文字だけ置き...
-
エクセルの関数について教えて...
-
VIEWの元のテーブルのindexって...
-
エラー 1068 (42000): 複数の主...
-
SQLサーバから、項目の属性(型...
-
select文のwhere句に配列を入れ...
-
SQL Left Join で重複を排除す...
-
Access パラメータクエリをcsv...
-
マイクラPC版のコマンドで効率...
-
【Transact-sql】 execの結果を...
-
sqlで、600行あるテーブルを100...
-
SQLにて特定の文字を除いた検索...
-
1テーブル&複数レコードの更新...
-
PL/SQLの変数について
-
WordpressのContact form 7でzi...
-
inner joinをすると数がおかし...
-
バインド変数について
-
複数テーブルのGROUP BY の使い...
-
MySQLのint型で001と表示する方...
-
updateを1行ずつ実行したい。
おすすめ情報