Mysql 查找两个其他值之间的聚合

Question

我正在使用一个非常讨厌的表架构，不幸的是我无法更改它，因为它是由我们的 SCADA 程序定义的。有一个模拟浮点值(电源使用)和一个数字整数值(机器设置)。我需要能够找到每个机器设置的功率使用的最小值、最大值和平均值。

所以基本上每次记录新的机器设置(intvalue)时，我都需要在下一次机器设置之前的总用电量(floatvalue)。我也希望能够按 intvalue 进行分组，例如，这样我就可以获得整个月的这些汇总数字。

到目前为止，我已经尝试过连接和嵌套查询，但我什么都做不了。我也找不到任何这样的例子，因为它的表格设计太差了。

在此处找到表架构:http://www.sqlfiddle.com/#!9/29164/1

数据:

    | tagid | intvalue | floatvalue |              t_stamp |
    |-------|----------|------------|----------------------|
    |     2 |        9 |     (null) | 2019-07-01T00:01:58Z |
    |     1 |   (null) |      120.2 | 2019-07-01T00:02:00Z |
    |     1 |   (null) |      120.1 | 2019-07-01T00:02:31Z |
    |     2 |       11 |     (null) | 2019-07-01T00:07:58Z |
    |     1 |   (null) |      155.9 | 2019-07-01T00:08:00Z |
    |     1 |   (null) |      175.5 | 2019-07-01T00:10:12Z |
    |     1 |   (null) |      185.5 | 2019-07-01T00:10:58Z |
    |     2 |        2 |     (null) | 2019-07-01T00:11:22Z |
    |     1 |   (null) |       10.1 | 2019-07-01T00:11:22Z |
    |     1 |   (null) |         12 | 2019-07-01T00:13:58Z |
    |     1 |   (null) |        9.9 | 2019-07-01T00:14:21Z |
    |     2 |        9 |     (null) | 2019-07-01T00:15:38Z |
    |     1 |   (null) |      120.9 | 2019-07-01T00:15:39Z |
    |     1 |   (null) |      119.2 | 2019-07-01T00:16:22Z |

期望的输出:

| intvalue |   min |   avg |   max |
|----------|-------|-------|-------|
|        2 |   9.9 |  10.7 |    12 |
|        9 | 119.2 | 120.1 | 120.9 |
|       11 | 155.9 | 172.3 | 185.5 |

这可能吗？

Answer 1

您可以在 SELECT 子句中用子查询填充缺失的 intvalues:

select t.*, (
  select t1.intvalue
  from sqlt_data_1_2019_07 t1
  where t1.t_stamp <= t.t_stamp
    and t1.intvalue is not null
  order by t1.t_stamp desc
  limit 1
) as group_int
from sqlt_data_1_2019_07 t
order by t.t_stamp;

结果会是

| tagid | intvalue | floatvalue | t_stamp             | group_int |
| ----- | -------- | ---------- | ------------------- | --------- |
| 2     | 9        |            | 2019-07-01 00:01:58 | 9         |
| 1     |          | 120.2      | 2019-07-01 00:02:00 | 9         |
| 1     |          | 120.1      | 2019-07-01 00:02:31 | 9         |
| 2     | 11       |            | 2019-07-01 00:07:58 | 11        |
| 1     |          | 155.9      | 2019-07-01 00:08:00 | 11        |
| 1     |          | 175.5      | 2019-07-01 00:10:12 | 11        |
| 1     |          | 185.5      | 2019-07-01 00:10:58 | 11        |
| 2     | 2        |            | 2019-07-01 00:11:22 | 2         |
| 1     |          | 10.1       | 2019-07-01 00:11:22 | 2         |
| 1     |          | 12         | 2019-07-01 00:13:58 | 2         |
| 1     |          | 9.9        | 2019-07-01 00:14:21 | 2         |
| 2     | 9        |            | 2019-07-01 00:15:38 | 9         |
| 1     |          | 120.9      | 2019-07-01 00:15:39 | 9         |
| 1     |          | 119.2      | 2019-07-01 00:16:22 | 9         |

现在您可以简单地根据子查询的结果进行分组:

select (
    select t1.intvalue
    from sqlt_data_1_2019_07 t1
    where t1.t_stamp <= t.t_stamp
      and t1.intvalue is not null
    order by t1.t_stamp desc
    limit 1
  ) as group_int,
  min(floatvalue) as min,
  avg(floatvalue) as avg,
  max(floatvalue) as max
from sqlt_data_1_2019_07 t
group by group_int
order by group_int;

你得到:

| group_int | min   | avg                | max   |
| --------- | ----- | ------------------ | ----- |
| 2         | 9.9   | 10.666666666666666 | 12    |
| 9         | 119.2 | 120.10000000000001 | 120.9 |
| 11        | 155.9 | 172.29999999999998 | 185.5 |

View on DB Fiddle