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 | +------------+------+---------+------------+