CAMUSのこくばん −クエリの板書所−

■[SQLServer][SQL]ORDER BY して TOP 1 で出してほしくない例

久々にSQL Serverいじってます。

超レスポンス悪いんですけどみてもらえます？といわれて出されたSQLにこんなのがおいでになりました。

SELECT 
  COL_KEY,
  (SELECT TOP 1 COL_NUM
     FROM TBL_B B
    WHREE B.COL_KEY = A.COL_KEY
    ORDER BY COL_NUM DESC) COL_NUM
  FROM TBL_A A
 WEHRE A.COL_DATE > '2009/04/01' 

実行プラン見ると、サブクエリ部分で98％も食っておいでになる。きゅう。(>_<)

SELECT 
  COL_KEY,
  (SELECT MAX(COL_NUM)
     FROM TBL_B B
    WHREE B.COL_KEY = A.COL_KEY) COL_NUM
  FROM TBL_A A
 WEHRE A.COL_DATE > '2009/04/01' 

これでええやんー。

Permalink | コメント(0) | トラックバック(0) | 21:33

■[SQLなお絵かき]まんだらと呼ばれたもの

最近、SQLを書く前とか、ヒトの書いたSQLとかを絵に描いてみるようにしています。

絵に描いていると「マインドマップ？」とか言われちゃったりするんですが、「まんだら」と呼んでくれる後輩がいて、その呼び方が気に入っちゃったので、「まんだら」と呼ぶようになりました。

自分がSQLを書く前に描くまんだらがこんな感じです。(ぼかしているのはわざとです…)

色がアレなのはちょっとぐらい気持ちをアゲ気味にしたかったからですから気にしないでください。

このSQLを描くときに思ってたのは、とにかく複雑すぎて判らん！ってことでした。

ある業務の工程ごとの状態を出力してほしいって言うリクエストだったので、工程ごとにSELECT文を書いたのをUNION ALLでがっちゃんこしてみたっていうものでOKだろう…というところまでは頭の中でイメージできてたのですが、実際どうしたらいいんかサッパリ判らなくて絵を描いてみたって感じです。

「ある業務の工程ごと」っていうのもぬわ〜ってなるぐらい複雑だったのですが、視覚化するとぬわ〜っがむぅ…ぐらいに軽減されたので、個人的には効果はまぁまぁあるかと。

ただ、自分で描き方のルールを決めてないので、気分によって色が変わったり、描きかたが変わったりしてしまうので、あんまり他人が読むドキュメントとして残しておくレベルのもんじゃなかったりするのが、難点です。

で、ヒトの書いたSQLをまんだら化するときは、ノートにフリクションペンでぐりぐりーっと描きます。

こっちはよく「マインドマップ？」と訊かれます。(ぼかしているのはわざとではないんですが、いい感じにボケてくれたんです…)

こっちもあんまりルール化はしてないんですが、まだ試行錯誤中ですね…。

とりあえず、見てすぐに INNER JOIN と OUTER JOIN ぐらいの区別はつけるように、OUTER JOIN は片側がぶら下がってる(LEFT or RIGHT)のか、FULL なのかが判るといいカナと。

今のところ、INNER JOIN と EXISTS句/IN句 との区別や、OUTER JOINとサブクエリの区別がついていないところがよろしくないと思ってます。

何とかせねば…。

ちょっと精進したら、またここでいろいろさらしてみます。

なんかいいアイデアあったらください…なんて書いてみたりして。(^^;)

では、よいお年を。

Permalink | コメント(2) | トラックバック(1) | 22:45

■[Oracle][SQL]OracleのCase式のメモ：その後

OracleのCase式のメモとか書いておきながら、その後を書いていなかったです。

いま、Oracle10g(10.2)系と戯れています。

例えば、

create table tbl_a (
    key1        char(10),
    column_type char(1),
    column_a    char(10),
    column_b    varchar2(10)
);

みたいなテーブルがあったとして、

select
    case column_type
        when 'A' then column_a
        else          column_b
    end      d_results
from 
    tbl_a
;

って書いたら、「ORA-12704: キャラクタ・セットが一致しません」がでます。

TRIMでも使ったらVARCHAR2になりよるから、こうしたらええやん…

select
    case column_type
        when 'A' then trim(column_a)
        else          column_b
    end      d_results
from 
    tbl_a
;

って書いても、「ORA-12704: キャラクタ・セットが一致しません」がでます。(涙)

なのに、

select
    decode(column_type, 
           'A', column_a,
           column_b)      d_results
from 
    tbl_a
;

って書いたら、何にも怒られないんですっ。(怒)

なんでなんですか、おらくるさんっ！

Permalink | コメント(4) | トラックバック(0) | 21:16

■[Oracle][SQL]View内部でUnionしてると…

これもなんか美しくなくてうにゃー。

さっきと同じようでちょっと違うテーブル使おう。

create table tbl_a (
    key1        char(10),
    column_type char(1),
    column_a    char(10),
    column_b    nvarchar2(10),
    num1        number(10, 2)
);

コレを使ってこんなViewを作ったとする。

create or replace view vw_a as
select
    key1,
    column_a,
    num1
from
    tbl_a
where
    column_type = 'A'
union all
select
    key1,
    column_b column_a,
    num1
from
    tbl_a
where
    column_type = 'B'
;

で、このViewに対して、こんなSQLを発行したら、型が違うと怒られる。

select
    column_a,
    sum(num1) num1
from
    vw_a
group by 
    column_a
;

しゃーないので、↓こんなふうにCASTかましてやると、上のSQLも動くようになった。

create or replace view vw_a as
select
    key1,
    cast(column_a as nvarchar2(10) column_a,
    num1
from
    tbl_a
where
    column_type = 'A'
union all
select
    key1,
    column_b column_a,
    num1
from
    tbl_a
where
    column_type = 'B'
;

で、ここからが本題。上記で型があわんという怒られ方をするようなデータを持っている場合。

creaet materialized view mv_a
--ここは省略
as 
select
    column_a,
    sum(num1) num1
from
    vw_a
group by 
    column_a
;

というのを作ると型があわんから作れへんわーと怒られます。

どうも、Viewの中のCASTは効いてない模様です…。なんでー…？

creaet materialized view mv_a
--ここは省略
as 
select
    cast( column_a as nvarchar2(10)) column_a,
    sum(num1) num1
from
    vw_a
group by 
    cast( column_a as nvarchar2(10))
;

結局こういうぶさいくなことをしなきゃならんかったという…orz

Permalink | コメント(0) | トラックバック(0) | 21:41

■[Oracle][SQL]OracleのCASE式メモ

昨日から悩んでいて、今日(しかも帰り際)、CASE式を使っているときに不思議な(困った)現象があることが発覚…。

WHERE句でCASE式を使って出力がCHAR型の場合、なぜか「ORA-12704: キャラクタ・セットが一致しません。」エラーが…。(汗)

特定の場合だけなのか、再現性があるのかわからないので、後で環境のあるところで試すためのメモを書いておきます。

結果は後ほど。

CREATE TABLE t_test (
    c_char01  CHAR(5),
    c_char02  CHAR(5),
    c_vchar   VARCHAR2(5)
);

INSERT INTO t_test values ('A', 'A', 'A    ');

SELECT 1
  FROM t_test t
 WHERE CASE t.c_char01
           WHEN t.c_char02 THEN t.c_vchar
           ELSE NULL END = t.c_char01
;

SELECT 1
  FROM t_test t
 WHERE CASE t.c_char01
           WHEN t.c_char02 THEN t.c_char02
           ELSE NULL END = t.c_char01
;

SELECT 1
  FROM t_test t
 WHERE CASE t.c_char01
           WHEN t.c_char02 THEN t.c_char02
           ELSE t.c_vchar END = t.c_char01
;

SELECT 1
  FROM t_test t
 WHERE CASE t.c_vchar
           WHEN t.c_char01 THEN t.c_char02
           ELSE t.c_vchar END = t.c_char01
;

SELECT 1
  FROM t_test t
 WHERE CASE t.c_vchar
           WHEN t.c_vchar THEN t.c_char02
           ELSE t.c_char01 END = t.c_char01
;

さて、どうなるかな…？

会社とおうちとでバイナリなメモをやり取りしにくいので、結果もうろ覚えのまま書く事になるかも。

Permalink | コメント(0) | トラックバック(0) | 22:02

■[SQL]IS NOT FALSEが使えないばやい

NULLとUNKNOWNを積極的に活用するSQLの書き方ってのをはてブ経由でいまさら見つけて、私もブックマークしてみたりなんかしちゃったりなんかしちゃったりして。

例）ユーザID，職業，年齢を条件として検索する．どれも必須項目でない．

SELECT *

FROM Table_Name

WHERE

(

　　　　　　user_id = ?

　　AND　　 profession = ?

　　AND 　　age >= ?

　　AND 　　age < ?

)IS NOT FALSE

ORDER BY user_id

はてブのコメントを見てたら、Oracleで使えないとかあったので、かなり無理やりですが、Oracleで使えるようなのにしてみますた。

SELECT *
FROM Table_Name
WHERE
      exists (select '7' from dual
              where user_id = ?
              union all
              select '7' from dual
              where ? is null)
   OR exists (select '7' from dual
              where profession = ?
              union all
              select '7' from dual
              where ? is null)
   OR exists (select '7' from dual
              where age >= ?
              union all
              select '7' from dual
              where ? is null)
ORDER BY user_id

exists の中の union でつないでいる二つの select の where 句の ? のところには同じ値がはいるようにしてくださいな。

Permalink | コメント(4) | トラックバック(0) | 22:51