impala 偏移條款

一般來(lái)說(shuō)，select查詢的resultset中的行從0開(kāi)始。使用offset子句，我們可以決定從哪里考慮輸出。 例如，如果我們選擇偏移為0，結(jié)果將像往常一樣，如果我們選擇偏移為5，結(jié)果從第五行開(kāi)始。

語(yǔ)法

以下是Impala中的biasclause的語(yǔ)法。

select data from table_name Group BY col_name;

例

假設(shè)我們?cè)跀?shù)據(jù)庫(kù)my_db中有一個(gè)名為customers的表，其內(nèi)容如下 -

[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的升序排列表中的記錄，并使用limit和order by子句將記錄數(shù)限制為4，如下所示。

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

以下是偏移子句的示例。 這里，我們按照id的順序在customers表中獲取記錄，并從第0行開(kāi)始打印前四行。

[quickstart.cloudera:21000] > select * from customers order by id limit 4 offset 0;

執(zhí)行時(shí)，上述查詢給出以下結(jié)果。

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的行開(kāi)始從客戶表獲取四個(gè)記錄，如下所示。

[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