TERADATAは分析関数の値でさらにフィルタできる
TERADATAのSQLには、独自拡張されたQUALIFY句があります。
QUALIFY句を使うと、分析関数の値でさらにフィルタをかけることができます。
例えば、金額を大きい順に並べて100番までの商品を取得したい場合は次のようなSQLになります。
select
productcode
, ammount
, rank() over(order by ammount desc)
from sample
qualify rank() over(order by ammount desc) <= 100
こんな感じで使えます。他のDBだと、一度分析関数の副問合せの結果にさらに条件を入れたりすることになりますが、上記のようにとても綺麗なSQLで書くことができます。
上のSQLはあまり例が良くなくて、単純にammountをソートして、top 100 や limit 100を指定すれば同じことになります。（Oracleだと、前は副問合せ後rownumで条件をつけるやり方でしたが、今も同じですかね。。）
もう少し複雑にして、商品カテゴリ別に、金額を大きい順に並べて100番までの商品を取得したい場合にすると次のようになります。
select
productcategory
,productcode
, ammount
, rank() over(partition by productcategory order by ammount desc)
from sample
qualify rank() over(partition by productcategory order by ammount desc) <= 100
こうなると他のDBでは、分析関数の結果を副問合せにして後、フィルタをつけることになりますが、TERADATAでは副問合せの必要がなくなります。
qualify句があるため、TERADATAでは条件の指定は次の３通りとなります。
- where : データ抽出時にフィルタする。
- having :データ集計後にフィルタする。
- qualify :データの結果に対して、分析関数の値でフィルタする。
検索に特化しているだけあって隙が無いと思います。
最後に
分析関数自体が、あまり親しみ易くないと感じています。（最初は、どういう結果が返ってくるのかさっぱりわかりませんでした）
- sum関数のover句内でorder by した際には、 rows between unbounded preceding and current row がデフォルトで指定される などの動き
- 各DBで実装されている関数が違う
- 通常のSQLを習得してても分析関数を使われると調べるのに時間がかかる
などとっつきづらい点があります。
前Oracleの業務システムで分析関数使ったら、そもそもこんな書き方すんなから始まり、order by がコストが高いのでやめろと色々言われたことがありました。。
でも、使えるととても便利です。
分析関数(Window関数)を理解できた時の思い出を次に書いてみました。
crmprogrammer38.hatenablog.com
crmprogrammer38.hatenablog.com
