AWS Athena上的偏移 [英] OFFSET on AWS Athena
问题描述
我想使用 LIMIT 和 OFFSET 子句在AWS Athena上运行查询。我认为前者受支持，而后者不受支持。可以使用其他方法来模拟此功能吗？
I would like to run a query on AWS Athena with both a LIMIT and an OFFSET clause. I take it the former is supported while the latter is not. Is there any way of emulating this functionality using other methods?
推荐答案
使用 OFFSET
Using OFFSET for pagination is very inefficient, especially for an analytic database like Presto that often has to perform a full table or partition scan. Additionally, the results will not necessarily be consistent between queries, so you can have duplicate or missing results when navigating between pages.
在 OLTP 数据库，例如MySQL或PostgreSQL，最好在索引上使用范围查询，在该索引中，您可以跟踪上一页中看到的最后一个值。
In an OLTP database like MySQL or PostgreSQL, it's better to use a range query over an index, where you keep track of the last value seen on the previous page.
在像Presto这样的 OLAP 数据库中，最好缓存结果集并使用缓存的数据执行分页。您不想每次用户单击转到另一个页面时都对数十亿或数万亿的行运行昂贵的查询。
In an OLAP database like Presto, it's better to cache the result set and perform pagination using the cached data. You don't want to run an expensive query over billions or trillions of rows each time the user clicks to go to a different page.
请参阅这些文章，以获取更长的解释。问题和索引方法：
See these articles for a longer explanation of the problem and the index approach:
- http://use-the-index-luke.com/no-offset
- http://use-the-index-luke.com/sql/partial-results/fetch-next-page
这篇关于AWS Athena上的偏移的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
