MySQLはどのように範囲の欠落した日付を埋めるのですか?
質問
私は2つの列、日付とスコアを持つテーブルを持っています。それは過去30日間1つのそれぞれについて、最大30エントリを持っています。
date score
-----------------
1.8.2010 19
2.8.2010 21
4.8.2010 14
7.8.2010 10
10.8.2010 14
私の問題は、いくつかの日付が欠落していることです - 私は見てみたい。
date score
-----------------
1.8.2010 19
2.8.2010 21
3.8.2010 0
4.8.2010 14
5.8.2010 0
6.8.2010 0
7.8.2010 10
...
単一のクエリから必要なのは 19,21,9,14,0,0,10,0,0,14... つまり、欠落している日付は0で埋められるということです。
私はすべての値を取得する方法を知っているし、サーバー側の言語で日付を繰り返し、空白を欠落させる。しかし、これはmysqlで行うことが可能で、結果を日付でソートし、不足している部分を取得します。
編集:このテーブルには、UserIDという名前の別のカラムがあり、私は30.000人のユーザーを持っており、それらのいくつかはこのテーブルのスコアを持っています。私は各ユーザーの過去30日間のスコアを必要とするため、30日前の日付は毎日削除しています。理由は、私は過去30日間のユーザーの活動のグラフを作成し、グラフをプロットするために、私はカンマで区切られた30の値が必要だからです。だから私はクエリで私にUSERID=10203活動を取得し、クエリは私に30スコア、過去30日間のそれぞれを得ることができると言うことができます。私は今より明確であることを願っています。
どのように解決するのですか?
MySQL には再帰的な機能がないので、NUMBERS テーブルのトリックを使用することになります。
-
増分する数字だけを保持するテーブルを作成します - auto_incrementを使用すると簡単に行えます。
DROP TABLE IF EXISTS `example`.`numbers`; CREATE TABLE `example`.`numbers` ( `id` int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-
を使用してテーブルを入力します。
INSERT INTO `example`.`numbers` ( `id` ) VALUES ( NULL )
...必要な数だけ値を指定します。
-
使用する 日付の追加 を使用して、NUMBERS.id値に基づいて日数を増やして、日付のリストを作成します。 2010-06-06" と "2010-06-14" をそれぞれの開始日と終了日に置き換えてください(ただし、同じフォーマット、YYYY-MM-DDを使用します)。
SELECT `x`.* FROM (SELECT DATE_ADD('2010-06-06', INTERVAL `n`.`id` - 1 DAY) FROM `numbers` `n` WHERE DATE_ADD('2010-06-06', INTERVAL `n`.`id` -1 DAY) <= '2010-06-14' ) x
-
時間部分に基づいて、データのテーブルにLEFT JOINします。
SELECT `x`.`ts` AS `timestamp`, COALESCE(`y`.`score`, 0) AS `cnt` FROM (SELECT DATE_FORMAT(DATE_ADD('2010-06-06', INTERVAL `n`.`id` - 1 DAY), '%m/%d/%Y') AS `ts` FROM `numbers` `n` WHERE DATE_ADD('2010-06-06', INTERVAL `n`.`id` - 1 DAY) <= '2010-06-14') x LEFT JOIN TABLE `y` ON STR_TO_DATE(`y`.`date`, '%d.%m.%Y') = `x`.`ts`
日付の書式を維持したい場合は DATE_FORMAT関数 :
DATE_FORMAT(`x`.`ts`, '%d.%m.%Y') AS `timestamp`
関連
-
[解決済み] SQL ServerでSELECTからUPDATEする方法とは?
-
[解決済み] MySQLでdatetimeとtimestampのどちらのデータ型を使用すべきですか?
-
[解決済み] PHPでSQLインジェクションを防ぐにはどうしたらいいですか?
-
[解決済み] MySQLでコマンドラインを使用してSQLファイルをインポートするにはどうすればよいですか?
-
[解決済み] SQLテーブルで重複する値を検索する
-
[解決済み] MySQLのAUTO_INCREMENTをリセットする方法
-
[解決済み] MySQLの複数行を1つのフィールドに連結することはできますか?
-
[解決済み] 各グループの最後のレコードを取得する - MySQL
-
[解決済み] 最初の行への結合方法
-
[解決済み] ある列の最大値を持つ行を取得する
最新
-
nginxです。[emerg] 0.0.0.0:80 への bind() に失敗しました (98: アドレスは既に使用中です)
-
htmlページでギリシャ文字を使うには
-
ピュアhtml+cssでの要素読み込み効果
-
純粋なhtml + cssで五輪を実現するサンプルコード
-
ナビゲーションバー・ドロップダウンメニューのHTML+CSSサンプルコード
-
タイピング効果を実現するピュアhtml+css
-
htmlの選択ボックスのプレースホルダー作成に関する質問
-
html css3 伸縮しない 画像表示効果
-
トップナビゲーションバーメニュー作成用HTML+CSS
-
html+css 実装 サイバーパンク風ボタン
おすすめ
-
MySQL インタビューの質問 - ハッシュインデックスを設定する方法
-
MysqlからElasticsearchにデータを同期させる方法を説明します。
-
[解決済み] ユニークなテーブル/エイリアスではない
-
[解決済み】文字列からuniqueidentifierに変換する際に変換に失敗する - 2つのGUIDの場合
-
MySQLにおけるvarchar型とchar型の違い
-
sql エラーを解決する オペランドには1つの列が含まれている必要があります。
-
Unknown character set.を解決する。MySQLデータベースインポート時の'utf8mb4'エラーの解消
-
[解決済み] どのトランザクションが「テーブルメタデータのロック待ち」状態を引き起こしているかを確認するにはどうすればよいですか?
-
[解決済み] mysqladminのフラッシュホストでブロックを解除する方法
-
[解決済み] MySQLデータベースのテーブルのサイズを取得する方法は?