MySQL _ 抜けデータ補完

side menu

  • めも一覧

  • GD 関連

    • GDによる画像読み書き簡易化関数の作成
    • GD 利用、bitmap 形式画像の読み書き
    • カウントダウン日数画像出力ソース配布と設置解説
  • SQL関連一覧

    • MySQLでの日付期間比較
    • SQLiteでの日付期間比較
    • SQLiteでの連続日付view作成=カレンダーテーブル
    • SQLiteでの曜日計算
    • MySQLでヒストグラム作成
    • 入れ子集合モデルでサイトマップ by SQLite
    • 入れ子集合モデルでサイトマップ by MySQL
    • ランク付け:by MySQL
    • 抜けデータ補完:by MySQL
    • カンマ区切りデータ:by MySQL
    • row_number関数を利用して当番表作成:by MySQL8,mariaDB10
    • window関数解説:by MySQL8,mariaDB10
  • その他

    • phpによる月齢計算クラス
  • miztools のサイトマップ
     このサイトのrss
    miztools の更新履歴

MySQL : 抜けデータ補完

日付や連番データで、抜けのある行を補完する考え方を記録しておくことにしました。
以下は、MySQL 5.6 で検証しています

  • まずは元データ作成

    CREATE TABLE `test` (
      `id` INT PRIMARY KEY,
      `title` VARCHAR(50) NOT NULL,
      `moddate` DATE
    );
    
    insert into `test` values
      (1,'test 1','2018-01-10'), (2,'nest','2018-01-12'), (3,'over','2018-01-13')
    , (6,'pertial','2018-01-15'),(7,'pre', '2018-01-16'), (10,'seven','2018-01-18' );
    

  • 連番補完

    • 下準備:連番テーブルを作成しておく

      0-9の数値データだけのテーブルが一つあれば、自己結合すれば10の累乗で連番を得ることができる。
      自己結合時、並びの最初のものから0-9がリストされるので、一桁目用から順に並べるようにする。

      create table  `num` (  `n` INT primary key );
      insert into `num` values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
      -- ex. 0 から 99 の連番テーブル
      select n2.n * 10 + n1.n  as n from num n1 cross join num n2 ;
      -- ex. 0 から 999 の連番テーブル
      select n3.n*100 + n2.n * 10 + n1.n  as n from num n1 , num n2 , num n3 ;
      -- よく使う桁数でviewを作っておくと便利
      create view `num99` as select  n2.n * 10 + n1.n  as n from num n1 cross join num n2 ;
      

    • test テーブルのid連番補完

      連番view から補完したい範囲指定して、testテーブルをleft join する。
      select num.n as num , t.*
        from `num99` as num left join test as t on num.n = t.id
        where  num.n between 1 and 11
        order by num.n ;
      
      --  結果
      +-----+------+---------+------------+
      | num | id   | title   | moddate    |
      +-----+------+---------+------------+
      |   1 |    1 | test 1  | 2018-01-10 |
      |   2 |    2 | nest    | 2018-01-12 |
      |   3 |    3 | over    | 2018-01-13 |
      |   4 | NULL | NULL    | NULL       |
      |   5 | NULL | NULL    | NULL       |
      |   6 |    6 | pertial | 2018-01-15 |
      |   7 |    7 | pre     | 2018-01-16 |
      |   8 | NULL | NULL    | NULL       |
      |   9 | NULL | NULL    | NULL       |
      |  10 |   10 | seven   | 2018-01-18 |
      |  11 | NULL | NULL    | NULL       |
      +-----+------+---------+------------+
      

  • test テーブルの日付補完

    • 期間指定する場合

      例:期間を 2018-01-11 から 2018-01-20 として、この期間10日分の行を出力したいとき。
       先の連番viewの数値を開始日付に足して連続日付テーブルサブクエリとして使う。 このサブクエリで先に期間を限定しておけば、テンポラリー展開領域の節約になる。
      また、where句では、select句で作った計算カラムのエイリアスを使えないが、having句ではエイリアスを使えるので、以下のようにすると計算式記述が減る。
      select d.basedate as basedate, t.*
        from ( select date_add('2018-01-11', interval n day) as basedate
               from num99   having  basedate <= '2018-01-20'
             ) as d 
          left join test as t  on d.basedate = t.moddate
        order by d.basedate ;
      
      --  結果
      +------------+------+---------+------------+
      | basedate   | id   | title   | moddate    |
      +------------+------+---------+------------+
      | 2018-01-11 | NULL | NULL    | NULL       |
      | 2018-01-12 |    2 | nest    | 2018-01-12 |
      | 2018-01-13 |    3 | over    | 2018-01-13 |
      | 2018-01-14 | NULL | NULL    | NULL       |
      | 2018-01-15 |    6 | pertial | 2018-01-15 |
      | 2018-01-16 |    7 | pre     | 2018-01-16 |
      | 2018-01-17 | NULL | NULL    | NULL       |
      | 2018-01-18 |   10 | seven   | 2018-01-18 |
      | 2018-01-19 | NULL | NULL    | NULL       |
      | 2018-01-20 | NULL | NULL    | NULL       |
      +------------+------+---------+------------+
      

    • 元テーブルに存在する最小最大日付を期間とする

      連続日付テーブルサブクエリの期間指定部分で、さらにサブクエリを使って、元データの最小最大日付を指定する。
      select d.basedate as basedate, t.*
        from ( select date_add( (select min(moddate) from test) , interval n day) as basedate
               from num99 having basedate <= (select max(moddate) from test) 
             ) as d 
          left join test as t  on d.basedate = t.moddate
        order by d.basedate ;
      
      --  結果
      +------------+------+---------+------------+
      | basedate   | id   | title   | moddate    |
      +------------+------+---------+------------+
      | 2018-01-10 |    1 | test 1  | 2018-01-10 |
      | 2018-01-11 | NULL | NULL    | NULL       |
      | 2018-01-12 |    2 | nest    | 2018-01-12 |
      | 2018-01-13 |    3 | over    | 2018-01-13 |
      | 2018-01-14 | NULL | NULL    | NULL       |
      | 2018-01-15 |    6 | pertial | 2018-01-15 |
      | 2018-01-16 |    7 | pre     | 2018-01-16 |
      | 2018-01-17 | NULL | NULL    | NULL       |
      | 2018-01-18 |   10 | seven   | 2018-01-18 |
      +------------+------+---------+------------+
      

[設置日 2018-06-17] : 初回設置

| ページtopへ | miztools top | site map | cake.org