MySQL の日付範囲で欠落している日付を埋めるにはどうすればよいですか?

MySQL の日付範囲で欠落している日付を入力します

場合によっては、日付範囲内の日付が欠落しているテーブルが発生することがあります。これにより、データ分析とプロットのプロセスが中断される可能性があります。このギャップを埋めるために、MySQL は、指定された数値で欠落している日付を埋めるソリューションを提供します。

特定の日付に関連付けられたスコアを記録するテーブルの例を考えてみましょう。一部の日付が省略された場合、私たちの目標は、それらをプレースホルダー値 (例: 0) で補完して、連続した範囲を取得することです。

課題への対応

MySQL にはネイティブの再帰機能がありませんが、NUMBERS テーブルのトリックを使用して、自動インクリメント列に基づいて日付シーケンスを生成できます。

日付シーケンスを生成

1. NUMBERS テーブルの作成:

DROP TABLE IF EXISTS `numbers`;
CREATE TABLE `numbers` (
  `id` int(10) unsigned NOT NULL auto_increment,
   PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

2. テーブルに記入:

INSERT INTO `numbers`
  ( `id` )
VALUES
  ( NULL ); -- 添加足够多的空行以覆盖所需的日期范围

これにより、日付範囲に必要な数の行が生成されます。 (注: 挿入する行数は日付範囲に応じて調整する必要があります。NULL を挿入するだけでは十分な行を自動的に生成できません)

3. 日付シーケンスの構築:

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';

日付「2010-06-06」と「2010-06-14」を、YYYY-MM-DD 形式の希望の開始日と終了日で置き換えます。

不足している日付を入力してください

1. 左結合の作成:

SELECT `x`.`timestamp` AS `timestamp`,
          COALESCE(`y`.`score`, 0) AS `cnt`
FROM (SELECT DATE_FORMAT(DATE_ADD('2010-06-06', INTERVAL `n`.`id` - 1 DAY), '%Y-%m-%d') AS `timestamp`
             FROM `numbers` `n`
            WHERE DATE_ADD('2010-06-06', INTERVAL `n`.`id` - 1 DAY) <= '2010-06-14') x
LEFT JOIN `y` ON STR_TO_DATE(`y`.`date`, '%Y-%m-%d') = `x`.`timestamp`;

y を実際のデータテーブルに置き換え、それに応じて日付形式を調整します。 (フォーマット変換による問題を避けるため、統一された YYYY-MM-DD フォーマットを使用することをお勧めします)

このクエリを実行すると、指定された範囲内の日付の完全なセットが取得されます。欠損値は指定された 0 値で埋められます。 (注: numbers日付範囲全体が確実にカバーされるように、テーブルに十分な行を事前に設定する必要があります。)

以上がMySQL の日付範囲で欠落している日付を埋めるにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。