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

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

 /*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を使用して計測)。

2010年08月08日(日) 22時48分  

1年分の年月日を生成する

1年分の年月日を生成する場合、大抵はExcelを使ったり、プログラムを作って生成しまう場合が多い。

この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文をプログラムを書くよりも早いかもしれない。

2010年01月09日(土) 23時43分  

歯抜けのIDを全て求める

ここ最近のアクセスログを閲覧してみたところ、歯抜けのIDの求め方を探している方が多いことに気づいたので、自分でもちょっと考えてみた。

過去のエントリで紹介した方法は歯抜けの最小値を求めるもので、今回は指定した範囲の歯抜けを全て求めるものである。

今回の方法でもmin関数を使って歯抜けの最小値は求めることができる。

動作検証はMySQLで行なっているけれど特別な関数は全く使用していないので、他のデータベースでもほんの少し修正(Oracleの場合はfrom dualを追加するとか)するだけで動作すると思う。

作成したtablenameテーブルのIDカラムは数値型で、現在(2, 3, 5, 7, 11)が使用済みであり、歯抜け状態になっている。

mysql>  select id from tablename;
+----+
| id |
+----+
|  2 |
|  3 |
|  5 |
|  7 |
| 11 |
+----+
5 rows in set (0.05 sec)

このtablenameテーブルから使用されていないID(1, 4, 6, 8, 9, 10)を求めるSQL文は、以下のようになった。

mysql>  select id
    ->  from (
    ->  select (ones.num + tens.num + 1) id
    ->  from (
    ->  select 0 num union all
    ->  select 1 num union all
    ->  select 2 num union all
    ->  select 3 num union all
    ->  select 4 num union all
    ->  select 5 num union all
    ->  select 6 num union all
    ->  select 7 num union all
    ->  select 8 num union all
    ->  select 9 num) ones
    ->  cross join
    ->  (
    ->  select 0 num union all
    ->  select 10 num union all
    ->  select 20 num union all
    ->  select 30 num union all
    ->  select 40 num union all
    ->  select 50 num union all
    ->  select 60 num union all
    ->  select 70 num union all
    ->  select 80 num union all
    ->  select 90 num) tens
    ->  ) dummy
    ->  where dummy.id not in (select tablename.id from tablename)
    ->  and dummy.id < (select max(tablename.id) from tablename);
+----+
| id |
+----+
|  1 |
|  4 |
|  6 |
|  8 |
|  9 |
| 10 |
+----+
6 rows in set (0.05 sec)

自分が持っている知識で思いついたのは、上記の他にはexcept(差集合)演算を用いた方法(下記)。

けれどもMySQLではexcept(差集合)演算がまだサポートされていないので、以下のSQL文はOracleで動作検証。

Oracleでexcept(差集合)演算を行なうには、minus演算子を使う。

 select id
 from (
   select (ones.num + tens.num + 1) id
   from (
     select 0 num from dual union all
     select 1 num from dual union all
     select 2 num from dual union all
     select 3 num from dual union all
     select 4 num from dual union all
     select 5 num from dual union all
     select 6 num from dual union all
     select 7 num from dual union all
     select 8 num from dual union all
     select 9 num from dual
   ) ones
   cross join
   (
     select 0 num from dual union all
     select 10 num from dual union all
     select 20 num from dual union all
     select 30 num from dual union all
     select 40 num from dual union all
     select 50 num from dual union all
     select 60 num from dual union all
     select 70 num from dual union all
     select 80 num from dual union all
     select 90 num from dual
   ) tens
 ) dummy
 where dummy.id < (select max(tablename.id) from tablename)
 minus
 select id from tablename;

最初のSQL文との違いは、where節の条件のうち

    -> dummy.id not in (select tablename.id from tablename)

の条件文が削除されて、

 minus
 select id from tablename;

が追加される。

どちらにしても、もっとスマートで冴えた方法があると思う。きっと。

なので、ここで紹介した歯抜けのIDを求める方法は、数多くある方法のひとつ(いや、ふたつか)として参考にしていただければ幸いである。

ちなみに上記の最初に紹介したSQL文は、初めてのSQLの10章「結合」で紹介されているSQL文の応用である(書籍を奥から引っ張り出して確認しました)。

同書の内容はMySQLを基準に書かれているが、MySQL特有の機能に依存している部分は少なく、ところどころでOracleやSQL Serverとの差異や互換性を例に挙げてSQLの基本構文を紹介している(同書によれば、本文中のサンプルはOracleやSQL Serverでも動作するらしい)。

これからSQLを勉強する方や、データベーススペシャリストを勉強する方にも、興味があれば一読をお勧めする。

4873112818.09.MZZZZZZZ.jpgAmazon.co.jp: 初めてのSQL

2009年12月26日(土) 23時11分  

MySQLでOracleのrownumのような行番号を付加する

MySQLでOracleのrownumのような行番号を付加する方法として、Web上でよく見かけるのは以下のようなユーザー変数を使ったSQL文で、2つのSQL文を発行しなければならない。

set @i:=0;
select @i:=@i+1 as rownum,user_id from user;

しかし、kotori::logで紹介されていた方法を用いれば、1回のSQL文で付加することができる。メモメモ。

select @i:=@i+1 as rownum,user_id from (select @i:=0) as dummy,user;

なるほど。サブクエリでユーザー変数を初期化しているのね。

で、ふとorder by節で並び替えた時の挙動が気になったので、早速試してみた。

全部で4つ試していて、始めの2つがテーブルのidカラムで並び替え(昇順、降順)、残りの2つ(3つ目と4つ目)が生成したrownumで並び替えたものである。

1つ目。

mysql>  select @i := @i + 1 rownum, id
    ->  from (select @i := 0) dummy, tablename
    ->  order by id asc;
+--------+----+
| rownum | id |
+--------+----+
|      1 |  2 |
|      2 |  3 |
|      3 |  5 |
|      4 |  7 |
|      5 | 11 |
+--------+----+
5 rows in set (0.00 sec)

2つ目。

mysql>  select @i := @i + 1 rownum, id
    ->  from (select @i:= 0) dummy, tablename
    ->  order by id desc;
+--------+----+
| rownum | id |
+--------+----+
|      1 | 11 |
|      2 |  7 |
|      3 |  5 |
|      4 |  3 |
|      5 |  2 |
+--------+----+
5 rows in set (0.00 sec)

3つ目

mysql>  select @i := @i + 1 rownum, id
    ->  from (select @i := 0) dummy, tablename
    ->  order by rownum asc;
+--------+----+
| rownum | id |
+--------+----+
|      1 |  2 |
|      2 |  3 |
|      3 |  5 |
|      4 |  7 |
|      5 | 11 |
+--------+----+
5 rows in set (0.00 sec)

4つ目

mysql>  select @i := @i + 1 rownum, id
    ->  from (select @i := 0) dummy, tablename
    ->  order by rownum desc;
+--------+----+
| rownum | id |
+--------+----+
|      5 | 11 |
|      4 |  7 |
|      3 |  5 |
|      2 |  3 |
|      1 |  2 |
+--------+----+
5 rows in set (0.00 sec)

う~ん。2つ目のSQL文の結果が予想していた結果と異なっている。予想していた結果は4つ目のSQL文と同様の結果だったのだけれど……。

Oracleで以下のようなSQL文を書いて確認してみたが、どちらも4つ目と同じ結果。

 select rownum, id from tablename order by id desc;
 select rownum, id from tablename order by rownum desc;

となると2つ目のSQL文は、4つ目のSQLと比べて

@i := @i + 1 rownum

を評価するタイミングが違うということになる。

2つ目のSQL文では、idカラムで並び替えた後に評価されているように見える。

というわけで調べてみたところ、MySQLのサイトの8.4. ユーザによって定義された変数のページで以下のような説明が。

ユーザ変数の評価順序は定義されておらず、与えられたクエリ内の要素に基づいて変更されることがあります。SELECT @a, @a := @a+1 ...では、MySQLは@aを先に評価し次に割り当てが実行されるように見えますが、クエリの変更(例えばGROUP BYHAVINGまたはORDER BY節による変更)は評価順序を変更する可能性があります。

このエントリで紹介したrownumもどきは、使用する時に注意が必要ですな。

2009年12月19日(土) 23時11分  

歯抜けの最小値を探す

分析関数の衝撃(前編)ですでに答えは出ているのだけれど、同じ結果が抽出できるクエリを作ってしまったので、メモ。

select min(id + 1) as gap
from (
  select 0 as id
  union
  select id
  from tablename
) as dummy
where id + 1 not in (
  select id
  from tablename
);

MySQLで動作検証済み。

(1, 2, 3, 4, 5)のうち、(3)が抜けて(1, 2, 4, 5)となっている場合は、(3)が取得できる。

(1, 2, 3, 4, 5)のうち、(1)が抜けて(2, 3, 4, 5)となっている場合は、ちゃんと(1)が取得できる。

(1, 2, 3, 4, 5)のいずれも抜けがなければ、(6)が返却される。

ちなみに、(1, 2, 3, 4, 5)のいずれも抜けがない場合にnullを返却するようにしたい場合は、where節に” id + 1 <= 5″を追加する。

select min(id + 1) as gap
from (
  select 0 as id
  union
  select id
  from tablename
) as dummy
where id + 1 not in (
  select id
  from tablename
) and id + 1 <= 5;

2008年11月22日(土) 22時33分