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
rank函数的用法,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都是报错的
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论