Example Query - Analytics Database - Teradata Vantage

SQL External Routine Programming

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
iiv1628111441820.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1147
lifecycle
latest
Product Category
Teradata Vantageā„¢

The dense_rank UDF evaluates dense rank over the set of values passed as arguments to the UDF. With dense ranking, items that compare equal receive the same ranking number, and the next item(s) receive the immediately following ranking number.

Consider the following table definition and inserted data:

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,);

In the following query and result, note the difference in the rank and dense rank value for v=4. The dense rank value is 4 whereas the rank of 4 is 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;

The output from the SELECT statement is:

          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