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 进行分页是非常低效的，特别是对于像 Presto 这样的分析型数据库，它经常需要执行全表或分区扫描.此外，查询之间的结果不一定一致，因此在页面之间导航时可能会出现重复或缺失的结果.

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.

在像 MySQL 或 PostgreSQL 这样的 OLTP 数据库中，最好对索引使用范围查询，您可以在其中跟踪上一页上看到的最后一个值.

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屋！