以下のような数字を返すだけビューを作成しておくと、何かと便利なことが多い。
/*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
;
どのような場面に使うのかというと、以前紹介した1年分の年月日を生成する時や、歯抜けのIDを全て求める時などである。
これらのSQL文に上記のビューを適用すれば、発行するSQL文を短くすることができる。
実際に1年分の年月日を生成するのSQL文を書き換えてみよう。
比較しやすいように、SQL文を再掲。
/*1年分の年月日生成*/
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文に今回紹介したビューを適用すると、以下のようになる。
/*1年分の年月日生成*/
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を使用して計測)。