Impala offset 子句
一般来说,结果集中的行 select 查询从 0 开始。使用 offset 子句,我们可以决定从哪里考虑输出。例如,如果我们选择偏移量为 0,结果将和往常一样,如果我们选择偏移量为 5,结果从第五行开始。
语法
以下是语法 offset Impala 中的子句。
select data from table_name Group BY col_name;
例子
假设我们有一个名为 顾客 在数据库中 my_db 其内容如下:
[quickstart.cloudera:21000] > select * from customers; Query: select * from customers +----+----------+-----+-----------+--------+ | id | name | age | address | salary | +----+----------+-----+-----------+--------+ | 3 | kaushik | 23 | Kota | 30000 | | 6 | Komal | 22 | MP | 32000 | | 1 | Ramesh | 32 | Ahmedabad | 20000 | | 5 | Hardik | 27 | Bhopal | 40000 | | 2 | Khilan | 25 | Delhi | 15000 | | 8 | ram | 22 | vizag | 31000 | | 9 | robert | 23 | banglore | 28000 | | 7 | ram | 25 | chennai | 23000 | | 4 | Chaitali | 25 | Mumbai | 35000 | +----+----------+-----+-----------+--------+ Fetched 9 row(s) in 0.51s
你可以按照 id 的升序排列表中的记录,并将记录数限制为 4,使用 limit and order by 子句如下图所示。
Query: select * from customers order by id limit 4 +----+----------+-----+-----------+--------+ | id | name | age | address | salary | +----+----------+-----+-----------+--------+ | 1 | Ramesh | 32 | Ahmedabad | 20000 | | 2 | Khilan | 25 | Delhi | 15000 | | 3 | kaushik | 23 | Kota | 30000 | | 4 | Chaitali | 25 | Mumbai | 35000 | +----+----------+-----+-----------+--------+ Fetched 4 row(s) in 0.64s
下面是一个例子 offset 子句。在这里,我们正在获取记录 顾客 表按照他们的 id 的顺序并打印从 0 开始的前四行 th row.
[quickstart.cloudera:21000] > select * from customers order by id limit 4 offset 0;
执行时,上述查询给出以下结果。
Query: select * from customers order by id limit 4 offset 0 +----+----------+-----+-----------+--------+ | id | name | age | address | salary | +----+----------+-----+-----------+--------+ | 1 | Ramesh | 32 | Ahmedabad | 20000 | | 2 | Khilan | 25 | Delhi | 15000 | | 3 | kaushik | 23 | Kota | 30000 | | 4 | Chaitali | 25 | Mumbai | 35000 | +----+----------+-----+-----------+--------+ Fetched 4 row(s) in 0.62s
同样的方法,你可以从 顾客 从偏移量为 5 的行开始的表,如下所示。
[quickstart.cloudera:21000] > select * from customers order by id limit 4 offset 5; Query: select * from customers order by id limit 4 offset 5 +----+--------+-----+----------+--------+ | id | name | age | address | salary | +----+--------+-----+----------+--------+ | 6 | Komal | 22 | MP | 32000 | | 7 | ram | 25 | chennai | 23000 | | 8 | ram | 22 | vizag | 31000 | | 9 | robert | 23 | banglore | 28000 | +----+--------+-----+----------+--------+ Fetched 4 row(s) in 0.52s