Clickhouse offset与fetch函数

关注微信公共号：小程在线

关注CSDN博客：程志伟的博客

 ClickHouse server version 21.6.6

OFFSET指定开始从查询结果集中返回行之前要跳过的行数。

FETCH指定查询结果中可以包含的最大行数。

WITH TIES选项用于根据ORDER BY子句返回结果集中最后一个位置的任何附加行。

dblab-VirtualBox :)CREATE TABLE test_fetch(a Int, b Int) ENGINE = Memory;

CREATE TABLE test_fetch
(
    `a` Int,
    `b` Int
)
ENGINE = Memory

Query id: ca402595-58fb-447a-9c70-f242b5eccc57

Ok.

0 rows in set. Elapsed: 0.005 sec.

dblab-VirtualBox :) INSERT INTO test_fetch VALUES(1,1),(2,1),(3,4),(1,3),(5,4),(0,6),(5,7);

INSERT INTO test_fetch VALUES

Query id: 73d70ac5-eaaf-4a51-beae-a39efd24f367

Ok.

7 rows in set. Elapsed: 0.004 sec. 

dblab-VirtualBox :) select * from test_fetch;

SELECT *
FROM test_fetch

Query id: eb8c4d73-20d3-48be-a6d8-a4babc066796

┌─a─┬─b─┐
│ 1 │ 1 │
│ 2 │ 1 │
│ 3 │ 4 │
│ 1 │ 3 │
│ 5 │ 4 │
│ 0 │ 6 │
│ 5 │ 7 │
└───┴───┘

7 rows in set. Elapsed: 0.004 sec.

dblab-VirtualBox :) SELECT * FROM test_fetch ORDER BY a OFFSET 3 ROW FETCH FIRST 3 ROWS ONLY;

SELECT *
FROM test_fetch
ORDER BY a ASC
LIMIT 3, 3

Query id: 33ae7ab9-b0e3-486c-8a11-b4e02da65f44

┌─a─┬─b─┐
│ 2 │ 1 │
│ 3 │ 4 │
│ 5 │ 7 │
└───┴───┘

3 rows in set. Elapsed: 0.008 sec. 

dblab-VirtualBox :) SELECT * FROM test_fetch ORDER BY a OFFSET 3 ROW FETCH FIRST 3 ROWS WITH TIES;

SELECT *
FROM test_fetch
ORDER BY a ASC
LIMIT 3, 3
 WITH TIES

Query id: 8dcd558c-3d26-4415-9b46-a7bad81e0399

┌─a─┬─b─┐
│ 2 │ 1 │
│ 3 │ 4 │
│ 5 │ 4 │
│ 5 │ 7 │
└───┴───┘

4 rows in set. Elapsed: 0.004 sec.