influxdb 基操 之数学运算

数学运算符遵循标准的操作顺序。也就是说，括号优先于除法和乘法，它优先于加法和减法。例如5 / 2 + 3 * 2 = (5 / 2) + (3 * 2)和5 + 2 * 3 - 2 = 5 + (2 * 3) - 2。

内容

• 数学运算符
  • 加成
  • 减法
  • 乘法
  • 师
  • 模
  • 按位AND
  • 按位OR
  • 按位异或
  • 数学运算符的常见问题
• 不支持的运营商

数学运算符

加成

使用常量执行加法。

<span style="color:#b1b6ff"><code class="language-sql"><span style="color:#22adf6"><strong>SELECT</strong></span> <span style="color:#32b08c">"A"</span> <span style="color:#22adf6">+</span> <span style="color:#ff6db0">5</span> <span style="color:#22adf6"><strong>FROM</strong></span> <span style="color:#32b08c">"add"</span></code></span>

<span style="color:#b1b6ff"><code class="language-sql"><span style="color:#22adf6"><strong>SELECT</strong></span> <span style="color:#22adf6">*</span> <span style="color:#22adf6"><strong>FROM</strong></span> <span style="color:#32b08c">"add"</span> <span style="color:#22adf6"><strong>WHERE</strong></span> <span style="color:#32b08c">"A"</span> <span style="color:#22adf6">+</span> <span style="color:#ff6db0">5</span> <span style="color:#22adf6">></span> <span style="color:#ff6db0">10</span></code></span>

在两个字段上执行添加。

<span style="color:#b1b6ff"><code class="language-sql"><span style="color:#22adf6"><strong>SELECT</strong></span> <span style="color:#32b08c">"A"</span> <span style="color:#22adf6">+</span> <span style="color:#32b08c">"B"</span> <span style="color:#22adf6"><strong>FROM</strong></span> <span style="color:#32b08c">"add"</span></code></span>

<span style="color:#b1b6ff"><code class="language-sql"><span style="color:#22adf6"><strong>SELECT</strong></span> <span style="color:#22adf6">*</span> <span style="color:#22adf6"><strong>FROM</strong></span> <span style="color:#32b08c">"add"</span> <span style="color:#22adf6"><strong>WHERE</strong></span> <span style="color:#32b08c">"A"</span> <span style="color:#22adf6">+</span> <span style="color:#32b08c">"B"</span> <span style="color:#22adf6">>=</span> <span style="color:#ff6db0">10</span></code></span>

减法

用常数执行减法。

<span style="color:#b1b6ff"><code class="language-sql"><span style="color:#22adf6"><strong>SELECT</strong></span> <span style="color:#ff6db0">1</span> <span style="color:#22adf6">-</span> <span style="color:#32b08c">"A"</span> <span style="color:#22adf6"><strong>FROM</strong></span> <span style="color:#32b08c">"sub"</span></code></span>

<span style="color:#b1b6ff"><code class="language-sql"><span style="color:#22adf6"><strong>SELECT</strong></span> <span style="color:#22adf6">*</span> <span style="color:#22adf6"><strong>FROM</strong></span> <span style="color:#32b08c">"sub"</span> <span style="color:#22adf6"><strong>WHERE</strong></span> <span style="color:#ff6db0">1</span> <span style="color:#22adf6">-</span> <span style="color:#32b08c">"A"</span> <span style="color:#22adf6"><=</span> <span style="color:#ff6db0">3</span></code></span>

用两个字段执行减法。

<span style="color:#b1b6ff"><code class="language-sql"><span style="color:#22adf6"><strong>SELECT</strong></span> <span style="color:#32b08c">"A"</span> <span style="color:#22adf6">-</span> <span style="color:#32b08c">"B"</span> <span style="color:#22adf6"><strong>FROM</strong></span> <span style="color:#32b08c">"sub"</span></code></span>

<span style="color:#b1b6ff"><code class="language-sql"><span style="color:#22adf6"><strong>SELECT</strong></span> <span style="color:#22adf6">*</span> <span style="color:#22adf6"><strong>FROM</strong></span> <span style="color:#32b08c">"sub"</span> <span style="color:#22adf6"><strong>WHERE</strong></span> <span style="color:#32b08c">"A"</span> <span style="color:#22adf6">-</span> <span style="color:#32b08c">"B"</span> <span style="color:#22adf6"><=</span> <span style="color:#ff6db0">1</span></code></span>

乘法

用常数执行乘法运算。

<span style="color:#b1b6ff"><code class="language-sql"><span style="color:#22adf6"><strong>SELECT</strong></span> <span style="color:#ff6db0">10</span> <span style="color:#22adf6">*</span> <span style="color:#32b08c">"A"</span> <span style="color:#22adf6"><strong>FROM</strong></span> <span style="color:#32b08c">"mult"</span></code></span>

<span style="color:#b1b6ff"><code class="language-sql"><span style="color:#22adf6"><strong>SELECT</strong></span> <span style="color:#22adf6">*</span> <span style="color:#22adf6"><strong>FROM</strong></span> <span style="color:#32b08c">"mult"</span> <span style="color:#22adf6"><strong>WHERE</strong></span> <span style="color:#32b08c">"A"</span> <span style="color:#22adf6">*</span> <span style="color:#ff6db0">10</span> <span style="color:#22adf6">>=</span> <span style="color:#ff6db0">20</span></code></span>

使用两个字段执行乘法。

<span style="color:#b1b6ff"><code class="language-sql"><span style="color:#22adf6"><strong>SELECT</strong></span> <span style="color:#32b08c">"A"</span> <span style="color:#22adf6">*</span> <span style="color:#32b08c">"B"</span> <span style="color:#22adf6">*</span> <span style="color:#32b08c">"C"</span> <span style="color:#22adf6"><strong>FROM</strong></span> <span style="color:#32b08c">"mult"</span></code></span>

<span style="color:#b1b6ff"><code class="language-sql"><span style="color:#22adf6"><strong>SELECT</strong></span> <span style="color:#22adf6">*</span> <span style="color:#22adf6"><strong>FROM</strong></span> <span style="color:#32b08c">"mult"</span> <span style="color:#22adf6"><strong>WHERE</strong></span> <span style="color:#32b08c">"A"</span> <span style="color:#22adf6">*</span> <span style="color:#32b08c">"B"</span> <span style="color:#22adf6"><=</span> <span style="color:#ff6db0">80</span></code></span>

乘法分布在其他运算符上。

<span style="color:#b1b6ff"><code class="language-sql"><span style="color:#22adf6"><strong>SELECT</strong></span> <span style="color:#ff6db0">10</span> <span style="color:#22adf6">*</span> (<span style="color:#32b08c">"A"</span> <span style="color:#22adf6">+</span> <span style="color:#32b08c">"B"</span> <span style="color:#22adf6">+</span> <span style="color:#32b08c">"C"</span>) <span style="color:#22adf6"><strong>FROM</strong></span> <span style="color:#32b08c">"mult"</span></code></span>

<span style="color:#b1b6ff"><code class="language-sql"><span style="color:#22adf6"><strong>SELECT</strong></span> <span style="color:#ff6db0">10</span> <span style="color:#22adf6">*</span> (<span style="color:#32b08c">"A"</span> <span style="color:#22adf6">-</span> <span style="color:#32b08c">"B"</span> <span style="color:#22adf6">-</span> <span style="color:#32b08c">"C"</span>) <span style="color:#22adf6"><strong>FROM</strong></span> <span style="color:#32b08c">"mult"</span></code></span>

<span style="color:#b1b6ff"><code class="language-sql"><span style="color:#22adf6"><strong>SELECT</strong></span> <span style="color:#ff6db0">10</span> <span style="color:#22adf6">*</span> (<span style="color:#32b08c">"A"</span> <span style="color:#22adf6">+</span> <span style="color:#32b08c">"B"</span> <span style="color:#22adf6">-</span> <span style="color:#32b08c">"C"</span>) <span style="color:#22adf6"><strong>FROM</strong></span> <span style="color:#32b08c">"mult"</span></code></span>

师

用常数进行除法。

<span style="color:#b1b6ff"><code class="language-sql"><span style="color:#22adf6"><strong>SELECT</strong></span> <span style="color:#ff6db0">10</span> <span style="color:#22adf6">/</span> <span style="color:#32b08c">"A"</span> <span style="color:#22adf6"><strong>FROM</strong></span> <span style="color:#32b08c">"div"</span></code></span>

<span style="color:#b1b6ff"><code class="language-sql"><span style="color:#22adf6"><strong>SELECT</strong></span> <span style="color:#22adf6">*</span> <span style="color:#22adf6"><strong>FROM</strong></span> <span style="color:#32b08c">"div"</span> <span style="color:#22adf6"><strong>WHERE</strong></span> <span style="color:#32b08c">"A"</span> <span style="color:#22adf6">/</span> <span style="color:#ff6db0">10</span> <span style="color:#22adf6"><=</span> <span style="color:#ff6db0">2</span></code></span>

用两个字段进行划分。

<span style="color:#b1b6ff"><code class="language-sql"><span style="color:#22adf6"><strong>SELECT</strong></span> <span style="color:#32b08c">"A"</span> <span style="color:#22adf6">/</span> <span style="color:#32b08c">"B"</span> <span style="color:#22adf6"><strong>FROM</strong></span> <span style="color:#32b08c">"div"</span></code></span>

<span style="color:#b1b6ff"><code class="language-sql"><span style="color:#22adf6"><strong>SELECT</strong></span> <span style="color:#22adf6">*</span> <span style="color:#22adf6"><strong>FROM</strong></span> <span style="color:#32b08c">"div"</span> <span style="color:#22adf6"><strong>WHERE</strong></span> <span style="color:#32b08c">"A"</span> <span style="color:#22adf6">/</span> <span style="color:#32b08c">"B"</span> <span style="color:#22adf6">>=</span> <span style="color:#ff6db0">10</span></code></span>

部门分布在其他运营商之间。

<span style="color:#b1b6ff"><code class="language-sql"><span style="color:#22adf6"><strong>SELECT</strong></span> <span style="color:#ff6db0">10</span> <span style="color:#22adf6">/</span> (<span style="color:#32b08c">"A"</span> <span style="color:#22adf6">+</span> <span style="color:#32b08c">"B"</span> <span style="color:#22adf6">+</span> <span style="color:#32b08c">"C"</span>) <span style="color:#22adf6"><strong>FROM</strong></span> <span style="color:#32b08c">"mult"</span></code></span>

模

使用常量执行模运算。

SELECT "B" % 2 FROM "modulo"

SELECT "B" FROM "modulo" WHERE "B" % 2 = 0

对两个字段执行模运算。

SELECT "A" % "B" FROM "modulo"

SELECT "A" FROM "modulo" WHERE "A" % "B" = 0

按位AND

您可以将此运算符与任何整数或布尔值一起使用，无论它们是字段还是常量。它不适用于float或string数据类型，也不能混合整数和布尔值。

<span style="color:#b1b6ff"><code class="language-sql"><span style="color:#22adf6"><strong>SELECT</strong></span> <span style="color:#32b08c">"A"</span> <span style="color:#22adf6">&</span> <span style="color:#ff6db0">255</span> <span style="color:#22adf6"><strong>FROM</strong></span> <span style="color:#32b08c">"bitfields"</span></code></span>

<span style="color:#b1b6ff"><code class="language-sql"><span style="color:#22adf6"><strong>SELECT</strong></span> <span style="color:#32b08c">"A"</span> <span style="color:#22adf6">&</span> <span style="color:#32b08c">"B"</span> <span style="color:#22adf6"><strong>FROM</strong></span> <span style="color:#32b08c">"bitfields"</span></code></span>

<span style="color:#b1b6ff"><code class="language-sql"><span style="color:#22adf6"><strong>SELECT</strong></span> <span style="color:#22adf6">*</span> <span style="color:#22adf6"><strong>FROM</strong></span> <span style="color:#32b08c">"data"</span> <span style="color:#22adf6"><strong>WHERE</strong></span> <span style="color:#32b08c">"bitfield"</span> <span style="color:#22adf6">&</span> <span style="color:#ff6db0">15</span> <span style="color:#22adf6">></span> <span style="color:#ff6db0">0</span></code></span>

<span style="color:#b1b6ff"><code class="language-sql"><span style="color:#22adf6"><strong>SELECT</strong></span> <span style="color:#32b08c">"A"</span> <span style="color:#22adf6">&</span> <span style="color:#32b08c">"B"</span> <span style="color:#22adf6"><strong>FROM</strong></span> <span style="color:#32b08c">"booleans"</span></code></span>

<span style="color:#b1b6ff"><code class="language-sql"><span style="color:#22adf6"><strong>SELECT</strong></span> (<span style="color:#32b08c">"A"</span> <span style="color:#22adf6">^</span> <span style="color:#22adf6"><strong>true</strong></span>) <span style="color:#22adf6">&</span> <span style="color:#32b08c">"B"</span> <span style="color:#22adf6"><strong>FROM</strong></span> <span style="color:#32b08c">"booleans"</span></code></span>

按位OR

您可以将此运算符与任何整数或布尔值一起使用，无论它们是字段还是常量。它不适用于float或string数据类型，也不能混合整数和布尔值。

<span style="color:#b1b6ff"><code class="language-sql"><span style="color:#22adf6"><strong>SELECT</strong></span> <span style="color:#32b08c">"A"</span> <span style="color:#22adf6">|</span> <span style="color:#ff6db0">5</span> <span style="color:#22adf6"><strong>FROM</strong></span> <span style="color:#32b08c">"bitfields"</span></code></span>

<span style="color:#b1b6ff"><code class="language-sql"><span style="color:#22adf6"><strong>SELECT</strong></span> <span style="color:#32b08c">"A"</span> <span style="color:#22adf6">|</span> <span style="color:#32b08c">"B"</span> <span style="color:#22adf6"><strong>FROM</strong></span> <span style="color:#32b08c">"bitfields"</span></code></span>

<span style="color:#b1b6ff"><code class="language-sql"><span style="color:#22adf6"><strong>SELECT</strong></span> <span style="color:#22adf6">*</span> <span style="color:#22adf6"><strong>FROM</strong></span> <span style="color:#32b08c">"data"</span> <span style="color:#22adf6"><strong>WHERE</strong></span> <span style="color:#32b08c">"bitfield"</span> <span style="color:#22adf6">|</span> <span style="color:#ff6db0">12</span> <span style="color:#22adf6">=</span> <span style="color:#ff6db0">12</span></code></span>

按位异或

您可以将此运算符与任何整数或布尔值一起使用，无论它们是字段还是常量。它不适用于float或string数据类型，也不能混合整数和布尔值。

<span style="color:#b1b6ff"><code class="language-sql"><span style="color:#22adf6"><strong>SELECT</strong></span> <span style="color:#32b08c">"A"</span> <span style="color:#22adf6">^</span> <span style="color:#ff6db0">255</span> <span style="color:#22adf6"><strong>FROM</strong></span> <span style="color:#32b08c">"bitfields"</span></code></span>

<span style="color:#b1b6ff"><code class="language-sql"><span style="color:#22adf6"><strong>SELECT</strong></span> <span style="color:#32b08c">"A"</span> <span style="color:#22adf6">^</span> <span style="color:#32b08c">"B"</span> <span style="color:#22adf6"><strong>FROM</strong></span> <span style="color:#32b08c">"bitfields"</span></code></span>

<span style="color:#b1b6ff"><code class="language-sql"><span style="color:#22adf6"><strong>SELECT</strong></span> <span style="color:#22adf6">*</span> <span style="color:#22adf6"><strong>FROM</strong></span> <span style="color:#32b08c">"data"</span> <span style="color:#22adf6"><strong>WHERE</strong></span> <span style="color:#32b08c">"bitfield"</span> <span style="color:#22adf6">^</span> <span style="color:#ff6db0">6</span> <span style="color:#22adf6">></span> <span style="color:#ff6db0">0</span></code></span>

数学运算符的常见问题

问题1：具有通配符和正则表达式的数学运算符

InfluxDB不支持数学运算与一个通配符（合并*）或正则表达式中SELECT的条款。以下查询无效，系统返回错误：

对通配符执行数学运算。

> SELECT * + 2 FROM "nope"
ERR: unsupported expression with wildcard: * + 2

对函数内的通配符执行数学运算。

> SELECT COUNT(*) / 2 FROM "nope"
ERR: unsupported expression with wildcard: count(*) / 2

对正则表达式执行数学运算。

> SELECT /A/ + 2 FROM "nope"
ERR: error parsing query: found +, expected FROM at line 1, char 12

对函数内的正则表达式执行数学运算。

> SELECT COUNT(/A/) + 2 FROM "nope"
ERR: unsupported expression with regex field: count(/A/) + 2

问题2：具有功能的数学运算符

目前不支持在函数调用中使用数学运算符。请注意，InfluxDB仅允许SELECT子句中的函数。

例如

<span style="color:#b1b6ff"><code class="language-sql"><span style="color:#22adf6"><strong>SELECT</strong></span> <span style="color:#ff6db0">10</span> <span style="color:#22adf6">*</span> mean(<span style="color:#32b08c">"value"</span>) <span style="color:#22adf6"><strong>FROM</strong></span> <span style="color:#32b08c">"cpu"</span></code></span>

但是会起作用

<span style="color:#b1b6ff"><code class="language-sql"><span style="color:#22adf6"><strong>SELECT</strong></span> mean(<span style="color:#ff6db0">10</span> <span style="color:#22adf6">*</span> <span style="color:#32b08c">"value"</span>) <span style="color:#22adf6"><strong>FROM</strong></span> <span style="color:#32b08c">"cpu"</span></code></span>