基本查询

语法

select_statement ::=  SELECT ( select_clause | '*' )
                      FROM table_name
                      WHERE where_clause
                      [ SAMPLE BY time_interval [ OFFSET offset_interval ] [ FILL fill_option ] ]
                      [ GROUP BY group_by_clause ]
                      [ ORDER BY ordering_clause ]
                      [ LIMIT integer [ OFFSET integer ] ]
select_clause    ::=  selector [ AS identifier ] ( ',' selector [ AS identifier ] )
selector         ::=  tag_name
                      | field_name
                      | function_name '(' [ selector ( ',' selector )* ] ')'
                      | COUNT '(' '*' ')'
where_clause     ::=  relation ( ( AND | OR ) relation )*
relation         ::=  ( field_name | tag_name ) operator term
operator         ::=  '=' | '<' | '>' | '<=' | '>=' | '!=' | IN | CONTAINS | CONTAINS KEY
group_by_clause  ::=  field_name ( ',' field_name )*
ordering_clause  ::=  field_name [ ASC | DESC ] ( ',' field_name [ ASC | DESC ] )*

说明

• 查询条件需要带上时间范围：time > xxx and time < xxx。

• 目前limit是后过滤，效率不高，建议您增加限制条件减少扫描的数据量。

示例

• 统计设备IDF07A1260某一时刻上报的所有数据条数：

  SELECT count(*) FROM sensor WHERE device_id = 'F07A1260' AND time = '2021-04-22 15:33:00';
  +--------+
  | EXPR$0 |
  +--------+
  | 1      |
  +--------+
  1 rows in set (97 ms)

• 查询设备IDF07A1260在一段时间内的所有测点：

  SELECT * FROM sensor WHERE device_id = 'F07A1260' AND time >= '2021-04-22 15:33:00' AND time < '2021-04-22 15:33:20';
  +-----------+----------+---------------------------+-------------+-----------+
  | device_id |  region  |           time            | temperature | humidity  |
  +-----------+----------+---------------------------+-------------+-----------+
  | F07A1260  | north-cn | 2021-04-22T15:33:00+08:00 | 12.100000   | 45.000000 |
  | F07A1260  | north-cn | 2021-04-22T15:33:10+08:00 | 13.200000   | 47.000000 |
  +-----------+----------+---------------------------+-------------+-----------+
  2 rows in set (57 ms)

• 查询表 sensor 中所有的device_id：

  SELECT DISTINCT device_id FROM sensor;
  +----------+
  |          |
  +----------+
  | F07A1260 |
  +----------+
  1 rows in set (114 ms)

• 查询设备IDF07A1260在一段时间内的最大温度：

  SELECT max(temperature) AS max_temp FROM sensor WHERE device_id = 'F07A1260' AND time >= '2021-04-22 15:33:00' AND time < '2021-04-22 15:33:20';
  +-----------+
  | max_temp  |
  +-----------+
  | 13.200000 |
  +-----------+
  1 rows in set (103 ms)