Teradata中的rank() 和 row

Teradata中的rank() 和 row"/>

Teradata中的rank() 和 row

Teradata数据库中也有和oracle类似的分析函数，功能基本一样。示例如下：

• RANK() 函数

SELECT * FROM salestbl ORDER BY 1,2;storeid     prodid      sales
----------- -------	---------
1001 	    A 		100000.00		
1001 	    C 		60000.00		
1001 	    D 		35000.00		
1001 	    F 		150000.00		
1002 	    A 		40000.00		
1002 	    C 		35000.00		
1002 	    D 		25000.00		
1003 	    A 		30000.00		
1003 	    B 		65000.00		
1003 	    C 		20000.00		
1003 	    D 		50000.00	

按sales排序，找出top 3的记录。

SELECT storeid,prodid,sales,RANK() OVER (ORDER BY sales DESC) AS Rank_Sales
FROM salestbl
QUALIFY rank_sales <= 3;storeid  prodid        sales   Rank_Sales
-----------  ------  -----------  -----------1001  F         150000.00            11001  A         100000.00            21003  B          65000.00            3

找出销售额top3的prodid

SELECT Prodid,Sumsales,RANK( ) OVER (ORDER BY Sumsales DESC) AS "Ranking"
FROM (SELECT prodid,SUM(sales)FROM salestblGROUP BY 1) AS dt(Prodid, Sumsales)
QUALIFY Ranking <= 3;Prodid     Sumsales      Ranking
------  -----------  -----------
A         170000.00            1
F         150000.00            2
C         115000.00            3

用rank() 按sales降序排序，如果sales相同，则排名相同。

SELECT itemid, salesdate, sales, RANK() OVER (ORDER BY sales DESC)
WHERE salesdate BETWEEN DATE '2004-01-01' AND DATE '2004-03-01'AND itemid = 10
FROM daily_sales_2004;itemid   salesdate        sales  Rank(sales)
-----------  ----------  -----------  -----------10  2004-01-10       550.00            110  2004-02-17       550.00            110  2004-02-20       450.00            310  2004-02-06       350.00            410  2004-02-27       350.00            410  2004-01-05       350.00            410  2004-01-03       250.00            710  2004-02-03       250.00            710  2004-01-25       200.00            910  2004-01-02       200.00            910  2004-01-21       150.00           1110  2004-02-01       150.00           1110  2004-01-01       150.00           1110  2004-01-31       100.00           14

• ROW_NUMBER () 函数

用row_number() 按sales降序排序时，即使sales相同，排名也不同。

SELECT itemid,salesdate,sales,ROW_NUMBER() OVER (ORDER BY sales DESC)
WHERE salesdate BETWEEN DATE '2004-01-01' AND DATE '2004-03-01'AND itemid = 10
FROM daily_sales_2004;itemid   salesdate        sales  Row_Number()
-----------  ----------  -----------  ------------10  2004-01-10       550.00             110  2004-02-17       550.00             210  2004-02-20       450.00             310  2004-02-06       350.00             410  2004-02-27       350.00             510  2004-01-05       350.00             610  2004-01-03       250.00             710  2004-02-03       250.00             810  2004-01-25       200.00             910  2004-01-02       200.00            1010  2004-01-21       150.00            1110  2004-02-01       150.00            1210  2004-01-01       150.00            1310  2004-01-31       100.00            14

Qualify

使用ROW_NUMBER行号排序函数实现

SELECT *
FROM (SELECT STATISTICAL_DATE,PROVINCE_CODE,TOTAL_SCORE,ROW_NUMBER() OVER(PARTITION BY PROVINCE_CODE ORDER BY TOTAL_SCORE) AS SCORE_ROWNUMBERFROM XXX) T1
WHERE T1.SCORE_ROWNUMBER = 1

上面的实现方式都比较复杂，语句较多，teradata 中的qualify 函数，提供了一种更为简便的方式：

SELECT STATISTICAL_DATE,PROVINCE_CODE,TOTAL_SCORE
FROM XXX 
QUALIFY ROW_NUMBER() OVER(PARTITION BY PROVINCE_CODE ORDER BY TOTAL_SCORE) = 1

或者可以这么写：

SELECT STATISTICAL_DATE,PROVINCE_CODE,TOTAL_SCORE,ROW_NUMBER() OVER(PARTITION BY PROVINCE_CODE ORDER BY TOTAL_SCORE) AS SCORE_RANK
FROM XXX 
QUALIFY SCORE_RANK = 1

需要注意的是：
当WHERE, GROUP BY和QUALIFY顺序：WHERE –> GROUP–>QUALIFY

QUALIFY与WHERE、HAVING的不同在于QUALIFY和Ordered Analytical Functions一起使用。后面两中方式任何一种换做WHERE或HAVING都是报错的