Spark SQL 指南

OFFSET 子句

描述

OFFSET 子句用于指定在开始返回 SELECT 语句返回的行之前要跳过的行数。通常，此子句与 ORDER BY 子句一起使用，以确保结果是确定的。

语法

OFFSET integer_expression

参数

示例

CREATE TABLE person (name STRING, age INT);
INSERT INTO person VALUES
    ('Zen Hui', 25),
    ('Anil B', 18),
    ('Shone S', 16),
    ('Mike A', 25),
    ('John A', 18),
    ('Jack N', 16);

-- Skip the first two rows.
SELECT name, age FROM person ORDER BY name OFFSET 2;
+-------+---+
|   name|age|
+-------+---+
| John A| 18|
| Mike A| 25|
|Shone S| 16|
|Zen Hui| 25|
+-------+---+

-- Skip the first two rows and returns the next three rows.
SELECT name, age FROM person ORDER BY name LIMIT 3 OFFSET 2;
+-------+---+
|   name|age|
+-------+---+
| John A| 18|
| Mike A| 25|
|Shone S| 16|
+-------+---+

-- A function expression as an input to OFFSET.
SELECT name, age FROM person ORDER BY name OFFSET length('SPARK');
+-------+---+
|   name|age|
+-------+---+
|Zen Hui| 25|
+-------+---+

-- A non-foldable expression as an input to OFFSET is not allowed.
SELECT name, age FROM person ORDER BY name OFFSET length(name);
org.apache.spark.sql.AnalysisException: The offset expression must evaluate to a constant value ...