MySQL _ 抜けデータ補完

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

連番補完

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

０－９の数値データだけのテーブルが一つあれば、自己結合すれば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] : 初回設置