数値を返すだけのビューって便利だよね

以下のような数字を返すだけビューを作成しておくと、何かと便利なことが多い。

 /*0～9までの数値を返すだけのVIEW*/
 create view vnum as
   select 0 as n union all
   select 1 as n union all
   select 2 as n union all
   select 3 as n union all
   select 4 as n union all
   select 5 as n union all
   select 6 as n union all
   select 7 as n union all
   select 8 as n union all
   select 9 as n
 ;

どのような場面に使うのかというと、以前紹介した１年分の年月日を生成する時や、歯抜けのIDを全て求める時などである。

これらのSQL文に上記のビューを適用すれば、発行するSQL文を短くすることができる。

実際に１年分の年月日を生成するのSQL文を書き換えてみよう。

比較しやすいように、SQL文を再掲。

 /*１年分の年月日生成*/
 select date_format(date_add('20100101', interval(number) day), '%Y%m%d') as yyyymmdd
 from (
   select hundred.n + tens.n + ones.n as number
   from (
     select 0 as n union all
     select 1 as n union all
     select 2 as n union all
     select 3 as n union all
     select 4 as n union all
     select 5 as n union all
     select 6 as n union all
     select 7 as n union all
     select 8 as n union all
     select 9 as n
   ) as ones cross join (
     select 0 as n union all
     select 10 as n union all
     select 20 as n union all
     select 30 as n union all
     select 40 as n union all
     select 50 as n union all
     select 60 as n union all
     select 70 as n union all
     select 80 as n union all
     select 90 as n
   ) as tens cross join (
     select 0 as n union all
     select 100 as n union all
     select 200 as n union all
     select 300 as n
   ) as hundred
 ) as dummy
 where date_format(date_add('20100101', interval(number) day), '%Y%m%d') < '20110101'
 order by yyyymmdd asc;

このSQL文に今回紹介したビューを適用すると、以下のようになる。

 /*１年分の年月日生成*/
 select date_format(date_add('20100101', interval(number) day), '%Y%m%d') as yyyymmdd
 from (
   --select hundred.n * 100 + tens.n * 10 + ones.n as number
   --from vnum as ones
   --cross join vnum as tens
   --cross join (select n from vnum where n between 0 and 3) as hundred
   select hundred.n + tens.n + ones.n as number
   from vnum as ones
   cross join (select n * 10 as n from vnum) as tens
   cross join (select n * 100 as n from vnum where n between 0 and 3) as hundred
 ) as dummy
 where date_format(date_add('20100101', interval(number) day), '%Y%m%d') < '20110101'
 order by yyyymmdd asc;

以上、数値を返すだけのビューって、作っておくと使い回しができて便利だよね、というお話でした。

ちなみにテーブルを作っても良いのであれば、内部でunion文をいくつも使っているビューよりもテーブルのほうが有利なのは、explainの結果を見れば明らかですな。というか、見なくても(ry。

2010/08/09追記: SQL文を一部修正。

「select hundred.n * 100 + tens.n * 10 + ones.n as number～」(修正前)と「select hundred.n + tens.n + ones.n as number～」(修正後)とでは、10000回実行した場合、ビューの場合では0.18secの差が、テーブルの場合では0.08secの差が出たので(私の環境での話。benchmarkを使用して計測)。