Teradata中的rank() 和 row_number() 函数以及QUALIFY

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            1
       1001  A         100000.00            2
       1003  B          65000.00            3

找出销售额top3的prodid

SELECT 
    Prodid
    ,Sumsales
    ,RANK( ) OVER (ORDER BY Sumsales DESC) AS "Ranking"
FROM (SELECT 
        prodid
        ,SUM(sales)
      FROM salestbl
      GROUP 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            1
         10  2004-02-17       550.00            1
         10  2004-02-20       450.00            3
         10  2004-02-06       350.00            4
         10  2004-02-27       350.00            4
         10  2004-01-05       350.00            4
         10  2004-01-03       250.00            7
         10  2004-02-03       250.00            7
         10  2004-01-25       200.00            9
         10  2004-01-02       200.00            9
         10  2004-01-21       150.00           11
         10  2004-02-01       150.00           11
         10  2004-01-01       150.00           11
         10  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             1
         10  2004-02-17       550.00             2
         10  2004-02-20       450.00             3
         10  2004-02-06       350.00             4
         10  2004-02-27       350.00             5
         10  2004-01-05       350.00             6
         10  2004-01-03       250.00             7
         10  2004-02-03       250.00             8
         10  2004-01-25       200.00             9
         10  2004-01-02       200.00            10
         10  2004-01-21       150.00            11
         10  2004-02-01       150.00            12
         10  2004-01-01       150.00            13
         10  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_ROWNUMBER
        FROM 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都是报错的