AWS Athena（Presto）OFFSET支持 [英] AWS Athena (Presto) OFFSET support

问题描述

我想知道AWS Athena中是否支持OFFSET。对于mysql，以下查询正在运行，但在雅典娜中却给了我错误。任何示例都将有所帮助。

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.

从雇员中选择* empSal> 3000 LIMIT 300 OFFSET 20

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

推荐答案

雅典娜基本上由Presto管理。由于 Presto 311 ，您可以使用 OFFSET m LIMIT n 语法或ANSI SQL等效项： OFFSET m ROWS FETCH NEXT n仅ROWS 。

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.

您可以在超过L​​IMIT，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）OFFSET支持的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！