本文介绍SELECT的基本查询方法。

语法

select_statement ::=  SELECT ( select_clause | '*' )
                      FROM table_name
                      WHERE where_clause
                      [ SAMPLE BY time_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是后过滤,效率不高,建议您增加限制条件减少扫描的数据量。

示例

  • 统计设备ID1234ABC一段时间内的所有条数:
    > select count(*) from sensor where device_id = '1234ABC' and time >= 1619076780000 and time <= 1619076800000;
    +---------+
    | EXPR$0  |
    +---------+
    | 5       |
    +---------+
    1 row selected (0.088 seconds)
  • 查询设备ID1234ABC在一段时间内的所有测点:
    > select * from sensor where device_id = '1234ABC' and time >= 1619076780000 and time <= 1619076800000;
    +------------+-----------+----------------+--------------+-----------+
    | device_id  |  region   |      time      | temperature  | humidity  |
    +------------+-----------+----------------+--------------+-----------+
    | 1234ABC   | north-cn  | 1619076780000  | 12.1         | 45        |
    | 1234ABC   | north-cn  | 1619076790000  | 13.2         | 47        |
    | 1234ABC   | north-cn  | 1619076800000  | 10.6         | 46        |
    +------------+-----------+----------------+--------------+-----------+
    3 rows selected (0.222 seconds)
  • 查询设备ID1234ABC在一段时间内的温度和湿度:
    > select region,time,temperature,humidity from sensor where device_id = '1234ABC' and time >= 1619076780000 and time <= 1619076800000;
    +-----------+----------------+--------------+-----------+
    |  region   |      time      | temperature  | humidity  |
    +-----------+----------------+--------------+-----------+
    | north-cn  | 1619076780000  | 12.1         | 45        |
    | north-cn  | 1619076790000  | 13.2         | 47        |
    | north-cn  | 1619076800000  | 10.6         | 46        |
    +-----------+----------------+--------------+-----------+
    3 rows selected (0.092 seconds)
  • 查询设备ID1234ABC在一段时间内的最大温度:
    > select max(temperature) from sensor where device_id = '1234ABC' and time >= 1619076780000 and time <= 1619076800000;
    +---------+
    | EXPR$0  |
    +---------+
    | 13.2    |
    +---------+
    1 row selected (0.136 seconds)