AWS Athena (Presto) 偏移支持 [英] AWS Athena (Presto) OFFSET support

问题描述

我想知道 AWS Athena 中是否支持 OFFSET.对于 mysql，以下查询正在运行，但在 athena 中，它给了我错误.任何示例都会有所帮助.

I would like to know if there is support for OFFSET in AWS Athena. For mysql the following query is running but in athena it is giving me error. Any example would be helpful.

select * from employee where empSal >3000 LIMIT 300 OFFSET 20

select * from employee where empSal >3000 LIMIT 300 OFFSET 20

推荐答案

Athena 基本上是由 Presto 管理的.由于 Presto 311 你可以使用 OFFSET m LIMIT n 语法或 ANSI SQL 等效:OFFSET m ROWS FETCH NEXT n ROWS ONLY.

Athena is basically managed Presto. Since Presto 311 you can use OFFSET m LIMIT n syntax or ANSI SQL equivalent: OFFSET m ROWS FETCH NEXT n ROWS ONLY.

您可以在 超越 LIMIT，Presto 遇到了 OFFSET 和 TIES.

对于旧版本(包括撰写本文时的 AWS Athena)，您可以使用 row_number() 窗口函数实现OFFSET + LIMIT.

For older versions (and this includes AWS Athena as of this writing), you can use row_number() window function to implement OFFSET + LIMIT.

例如，代替

SELECT * FROM elb_logs
OFFSET 5 LIMIT 5 -- this doesn't work, obviously

你可以执行

SELECT * FROM (
    SELECT row_number() over() AS rn, * FROM elb_logs)
WHERE rn BETWEEN 5 AND 10;

注意:执行引擎仍然需要从底层表中读取 offset+limit 行，但这仍然比将所有这些行发送回客户端并在那里获取子列表要好得多.

Note: the execution engine will still need to read offset+limit rows from the underlying table, but this is still much better than sending all these rows back to the client and taking a sublist there.

警告:请参阅 https://stackoverflow.com/a/45114359/65458 了解为何在查询中避免 OFFSET通常是个好主意.

Warning: see https://stackoverflow.com/a/45114359/65458 for explanation why avoiding OFFSET in queries is generally a good idea.

这篇关于AWS Athena (Presto) 偏移支持的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！