本文翻譯自官網(wǎng)，官網(wǎng)地址：(https://docs.influxdata.com/influxdb/v1.7/query_language/data_exploration/)
OFFSET 和SOFFSET對返回的points和series進(jìn)行分頁。
一、OFFSET子句
OFFSET <N> 將從查詢結(jié)果的第N個(gè)points開始進(jìn)行分頁。
語法：
SELECT_clause [INTO_clause] FROM_clause
[WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause]
LIMIT_clause OFFSET <N>
[SLIMIT_clause]
OFFSET<N>中的N表示從查詢結(jié)果的第N個(gè)points開始進(jìn)行分頁。注意OFFSET必須和LIMIT搭配使用，如果只有OFFSET而沒有LIMIT，將會(huì)導(dǎo)致不一致的查詢結(jié)果。
OFFSET示例sql
- 示例1
為了對比效果更明顯，我們先看下面的sql
上面的sql查詢除了measurement的前10行
接下來看下面的sql
SELECT "water_level","location" FROM "h2o_feet"
LIMIT 3 OFFSET 4
可以看到，LIMIT 3 OFFSET 4將查詢結(jié)果的從下標(biāo)4開始的第5、6、7行總共3行顯示了出來。
- 示例2
Sql
SELECT MEAN("water_level") FROM "h2o_feet"
WHERE time >= '2015-08-18T00:00:00Z'
AND time <= '2015-08-18T00:42:00Z'
GROUP BY *,time(12m)
ORDER BY time DESC
LIMIT 2 OFFSET 2 SLIMIT 1
這個(gè)例子非常復(fù)雜，下面是逐條分解：
- The SELECT clause specifies an InfluxQL function.
- The FROM clause specifies a single measurement.
- The WHERE clause specifies the time range for the query.
- The GROUP BY clause groups results by all tags (*) and into 12-minute intervals.
- The ORDER BY time DESC clause returns results in descending timestamp order.
- The LIMIT 2 clause limits the number of points returned to two.
- The OFFSET 2 clause excludes the first two averages from the query results.
- The SLIMIT 1 clause limits the number of series returned to one.
- The SOFFSET 1 clause paginates the series returned.
如果上面的sql中沒有SOFFSET 2，則會(huì)查詢到不同的series：
二、SOFFSET子句
SOFFSET <N> 將從查詢結(jié)果的第N個(gè)series開始進(jìn)行分頁。
語法：
SELECT_clause [INTO_clause] FROM_clause [WHERE_clause]
GROUP BY *[,time(time_interval)]
[ORDER_BY_clause]
[LIMIT_clause] [OFFSET_clause]
SLIMIT_clause SOFFSET <N>
SOFFSET <N>中的N指定了開始分頁的地方，SOFFSET要跟SLIMIT子句一同搭配使用。如果只使用SOFFSET而沒有SLIMIT子句，則可能會(huì)導(dǎo)致不一致的查詢結(jié)果。
SOFFSET示例sql
- 示例1
上面的sql將h2o_feet表中tag的 location = santa_monica的所有數(shù)據(jù)。如果沒有SOFFSET 1子句，查詢結(jié)果將會(huì)變成是location = coyote_creek的數(shù)據(jù)。為了更好的說明這個(gè)問題，依次看下面的示例。
SELECT count("water_level") FROM "h2o_feet" GROUP BY *
可以看到，上面的sql查詢出每個(gè)tag的water_level字段個(gè)數(shù)。
讓我們在上面sql的基礎(chǔ)上，加上SLIMIT 1：
因?yàn)榧由狭薙LIMIT 1，所以查詢結(jié)果只展示了第一個(gè)tag的結(jié)果。
再在上面sql的基礎(chǔ)上加上SOFFSET 1：
可見，因?yàn)榧由狭薙OFFSET 1，所以查詢結(jié)果從第二個(gè)series開始展示（下標(biāo)是從0開始的）。
- 示例2
接下來看一個(gè)更復(fù)雜的sql
SELECT MEAN("water_level") FROM "h2o_feet"
WHERE time >= '2015-08-18T00:00:00Z'
AND time <= '2015-08-18T00:42:00Z'
GROUP BY *,time(12m)
ORDER BY time DESC
LIMIT 2 OFFSET 2
SLIMIT 1 SOFFSET 1
示例sql相對比較復(fù)雜，下面將逐個(gè)子句的進(jìn)行分析（挺簡單的，不翻譯了）：
- The SELECT clause specifies an InfluxQL function.
- The FROM clause specifies a single measurement.
- The WHERE clause specifies the time range for the query.
- The GROUP BY clause groups results by all tags (*) and into 12-minute intervals.
- The ORDER BY time DESC clause returns results in descending timestamp order.
- The LIMIT 2 clause limits the number of points returned to two.
- The OFFSET 2 clause excludes the first two averages from the query results.
- The SLIMIT 1 clause limits the number of series returned to one.
- The SOFFSET 1 clause paginates the series returned.
如果沒有SOFFSET 1，查詢結(jié)果將會(huì)是：
可以看到，查詢到的是另一個(gè)series的數(shù)據(jù)。
