Teradata数据库中也有和oracle类似的分析函数，功能基本一样。示例如下：
|
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
|
rank() over (partition by ... order by ... )用法
按storeid分组，然后在每个storeid内对sales降序排序。
|
SELECT storeid, prodid, sales, RANK()
OVER (PARTITION BY storeid ORDER BY sales DESC)AS Rank_Sales
FROM salestbl
QUALIFY Rank_Sales <= 3
;
storeid prodid sales Rank_Sales
------- ------- --------- ---------
1001 F 150000 1
1001 A 100000 2
1001 C 60000 3
1002 A 40000 1
1002 C 35000 2
1002 D 25000 3
1003 B 65000 1
1003 D 50000 2
1003 A 30000 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() 按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
|