Teradata - 使用 TOP 限制结果

发布于 2024-12-03 03:11:44 字数 212 浏览 2 评论 0原文

我正在尝试使用 JDBC 从 Teradata 获取大量记录。我需要将这个集合分成几个部分，并在 select 中使用“Top N”子句。但我不知道如何设置“偏移量”，就像我们在 MySQL 中所做的那样 -

   SELECT * FROM tbl LIMIT 5,10

以便下一个 select 语句将从第 (N+1) 个位置获取记录。

原文

I am trying to fetch a huge set of records from Teradata using JDBC. And I need to break this set into parts for which I'm using "Top N" clause in select.
But I dont know how to set the "Offset" like how we do in MySQL -

   SELECT * FROM tbl LIMIT 5,10

so that next select statement would fetch me the records from (N+1)th position.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

谷夏 2024-12-10 03:11:44

RANK 和 QUALIFY 我相信是您的朋友，

例如

 SEL RANK(custID), custID 
 FROM mydatabase.tblcustomer
 QUALIFY RANK(custID) < 1000 AND RANK(custID) > 900
 ORDER BY custID;

RANK(field) 将（概念上）检索结果集的所有行，
按 ORDER BY 字段对它们进行排序，并为其分配一个递增的排名 ID。

QUALIFY 允许您通过限制返回到限定表达式的行来对其进行切片，现在可以合法地查看 RANK。

需要明确的是，我返回查询中的第 900-1000 行 select all from custommers，
不会返回 ID 在 900 到 1000 之间的客户。

RANK and QUALIFY I beleive are your friends here

for example

 SEL RANK(custID), custID 
 FROM mydatabase.tblcustomer
 QUALIFY RANK(custID) < 1000 AND RANK(custID) > 900
 ORDER BY custID;

RANK(field) will (conceptually) retrieve all the rows of the resultset,
order them by the ORDER BY field and assign an incrementing rank ID to them.

QUALIFY allows you to slice that by limiting the rows returned to the qualification expression, which now can legally view the RANKs.

To be clear, I am returning the 900-1000th rows in the query select all from cusotmers,
NOT returning customers with IDs between 900 and 1000.

回复收藏 0 原文

奢华的一滴泪 2024-12-10 03:11:44

您还可以在 Teradata 上使用 ROW_NUMBER 窗口聚合。

SELECT ROW_NUMBER() OVER (ORDER BY custID) AS RowNum_
     , custID
  FROM myDatabase.myCustomers
QUALIFY RowNum_ BETWEEN 900 and 1000;

与 RANK 窗口聚合不同，ROW_NUMBER 将为您提供一个序列，无论您在可选分区集上排序的列是否唯一。

只是另一个需要考虑的选择。

You can also use the ROW_NUMBER window aggregate on Teradata.

SELECT ROW_NUMBER() OVER (ORDER BY custID) AS RowNum_
     , custID
  FROM myDatabase.myCustomers
QUALIFY RowNum_ BETWEEN 900 and 1000;

Unlike the RANK windows aggregate, ROW_NUMBER will provide you a sequence regardless of whether the column you are ordering over the optional partition set is unique or not.

Just another option to consider.

回复收藏 0 原文

~没有更多了~