Teradata SQL用法之排名和分页

1. 选取排序后的前N条记录

select top 3 e_name from ptemp.cuishen_temp_20100707
order by id

select top 3 * from ptemp.cuishen_temp_20100707
order by id

2. 用rank给记录排名

默认是降序排名

select rank(e_name), id, e_name from ptemp.cuishen_temp_20100707_2
qualify rank(e_name) <> 2

也可以指定asc关键字进行升序排名

select rank(e_name asc), id, e_name from ptemp.cuishen_temp_20100707_2
qualify rank(e_name asc) <= 2

其中

qualify rank(e_name asc) <= 2

子句表示对结果集进行限制，选取e_name字段升序排名前2的记录

限定条件当然也可以这样写：

select rank(name asc), id, name from ptemp.cuishen_temp_20100707
qualify rank(name asc) >= 2 and rank(name asc) <= 5

3. 分页查询

可以用row_number关键字来进行分页查询，例如：

select * from ptemp.cuishen_temp_20100707
qualify row_number() over(order by id) >= 2 and row_number() over(order by id) <= 5

4. row_number和rank的区别

row_number：顾名思意，就是行号，不管记录相不相同，行号都是不同的。
rank：对于不同的记录排名当然是不同的，而对于相同的记录排名是相同的，这就是为什么分页查询不用rank来做的原因。

5. 可以用PARTITION BY关键字进行去重排名查询

select * from ptemp.cuishen_temp_20100707
QUALIFY ROW_NUMBER() OVER(PARTITION BY id ORDER BY id) = 1

上句表示：按id字段排序，取每段重复id记录的TOP 1。千万注意：这个不能用rank来做。

评论

3 楼 cuishen 2010-07-27  

icefishc 写道

用qualify 来分页效率怎么样？

关于网友的这个疑问，我今天特地测试了一把：

测试平台： 3节点TD， 75个AMPs
测试时间： 早上跑ETL中，TD繁忙时段

测试结果：

40万的数据量: 按索引字段排序分页耗时大概是1秒以内
40万的数据量: 按非索引字段排序分页耗时大概是1秒以内

600万的数据量: 按索引字段排序分页耗时大概是7秒
600万的数据量: 按非索引字段排序分页耗时大概是40秒

2千万的数据量： 按索引字段排序分页耗时大概是1分30秒
2千万的数据量： 按非索引字段排序分页耗时大概是2分48秒

由于相同字段不同时间跑，不同的字段，字段大小、类型不同，对测试结果都是有影响的，所以以上测试结果只是一个大概的参考值。

但是可以确定的是：这种qualify分页方式，尽管是能实现功能，但是在处理百万级以上的数据量时效率还是比较低下的，只适用于十万级以下的数据量。

2 楼 icefishc 2010-07-26  

用qualify 来分页效率怎么样？

1 楼 yongdi2 2010-07-26  

国内还是很少有人能接触到这个产品的，曲高和寡