次のテーブルの定義と挿入されたデータについて考えてみます。
CREATE MULTISET TABLE t (id INTEGER, v INTEGER); INSERT INTO t VALUES (1,1); INSERT INTO t VALUES (1,2); INSERT INTO t VALUES (1,2); INSERT INTO t VALUES (1,4); INSERT INTO t VALUES (1,5); INSERT INTO t VALUES (1,5); INSERT INTO t VALUES (1,5); INSERT INTO t VALUES (1,8); INSERT INTO t VALUES (1,);
次に、dense_rank操作を実行する、ウィンドウ集約UDFのSQL定義を示します。
REPLACE FUNCTION dense_rank (x INTEGER) RETURNS INTEGER CLASS AGGREGATE (1000) LANGUAGE C NO SQL PARAMETER STYLE SQL DETERMINISTIC CALLED ON NULL INPUT EXTERNAL;
dense_rank UDFは、このUDFの引数として渡された値のセット全体に対して、dense_rankを評価します。dense_rankでは、複数の項目の比較が等しい場合には、それらに同一のランク番号を与えます。それに続く項目には、そのランク番号の直後の番号を与えます。次に示す問合わせと結果では、rankとdense_rankのv=4に対する値に注目してください。dense_rankの値は4なのに対して、rankの4は5になります。
SELECT v, dense_rank(v) OVER (PARTITION BY id ORDER BY v ROWS UNBOUNDED PRECEDING) as dr, rank() OVER (PARTITION BY id ORDER BY v) as r FROM t ORDER BY dr;
このSELECT文の出力は、次のとおりです。
v dr r ----------- ----------- ----------- ? 1 1 1 2 2 2 3 3 2 3 3 4 4 5 5 5 6 5 5 6 5 5 6 8 6 9
dense_rank UDFのCコード例については、<Teradata Vantage™ - SQL外部ルーチン プログラミング、B035-1147>を参照してください。