sql - 如何从电量记录中计算出用电量？

Question

我有一个表格，其中包含设备的功率值 (kW)。每分钟从每个设备读取一次值，并将其插入带有时间戳的表中。我需要做的是计算给定时间跨度的功耗 (kWh) 并返回 10 个最耗电的设备。现在我查询给定时间跨度的结果并在后端循环所有记录进行计算。这适用于少量设备和短时间跨度，但在实际用例中我可能有数千台设备和长时间跨度。

所以我的问题是，我如何才能在 PostgreSQL 9.4.4 中完成这一切，以便我的查询只返回 10 个最耗电的(device_id，power_consumption)对？

示例表:

CREATE TABLE measurements (
  id         serial primary key,
  device_id  integer,
  power      real,
  created_at timestamp
);

简单数据示例:

| id | device_id | power |               created_at |
|----|-----------|-------|--------------------------|
|  1 |         1 |    10 | August, 26 2015 08:23:25 |
|  2 |         1 |    13 | August, 26 2015 08:24:25 |
|  3 |         1 |    12 | August, 26 2015 08:25:25 |
|  4 |         2 |   103 | August, 26 2015 08:23:25 |
|  5 |         2 |   134 | August, 26 2015 08:24:25 |
|  6 |         2 |     2 | August, 26 2015 08:25:25 |
|  7 |         3 |    10 | August, 26 2015 08:23:25 |
|  8 |         3 |    13 | August, 26 2015 08:24:25 |
|  9 |         3 |    20 | August, 26 2015 08:25:25 |

想要查询的结果:

| id | device_id | power_consumption |
|----|-----------|-------------------|
|  1 |         1 |              24.0 |
|  2 |         2 |             186.5 |
|  3 |         3 |              28.0 |

简化示例(以小时为单位创建)我如何计算千瓦时值:

data = [
    [
        { 'id': 1, 'device_id': 1, 'power': 10.0, 'created_at': 0 },
        { 'id': 2, 'device_id': 1, 'power': 13.0, 'created_at': 1 },
        { 'id': 3, 'device_id': 1, 'power': 12.0, 'created_at': 2 }
    ],
    [
        { 'id': 4, 'device_id': 2, 'power': 103.0, 'created_at': 0 },
        { 'id': 5, 'device_id': 2, 'power': 134.0, 'created_at': 1 },
        { 'id': 6, 'device_id': 2, 'power': 2.0, 'created_at': 2 }
    ],
    [
        { 'id': 7, 'device_id': 3, 'power': 10.0, 'created_at': 0 },
        { 'id': 8, 'device_id': 3, 'power': 13.0, 'created_at': 1 },
        { 'id': 9, 'device_id': 3, 'power': 20.0, 'created_at': 2 }
    ]
]

# device_id: power_consumption
results = { 1: 0, 2: 0, 3: 0 }

for d in data:
    for i in range(0, len(d)):
        if i < len(d)-1:
            # Area between two records gives us kWh
            # X-axis is time(h)
            # Y-axis is power(kW)
            x1 = d[i]['created_at']
            x2 = d[i+1]['created_at']
            y1 = d[i]['power']
            y2 = d[i+1]['power']
            # Area between two records gives us kWh
            # X-axis is time(h)
            # Y-axis is power(kW)
            x1 = d[i]['created_at']
            x2 = d[i+1]['created_at']
            y1 = d[i]['power']
            y2 = d[i+1]['power']

            results[d[i]['device_id']] += ((x2-x1)*(y2+y1))/2

print results

编辑:检查 this看看我是如何解决这个问题的。

Answer 1

为了做到这一点，您需要的一些元素是:

1. Sum() 聚合，计算多条记录的总和
2. Lag()/Lead() 函数，计算给定记录的“上一个”或“下一个”记录的值。

因此，对于给定的行，您可以获得当前的 created_at 和 power 记录，在 SQL 中，您可能会使用 Lead() 窗口函数来获取具有相同设备 ID 的记录的 created_at 和 power 记录created_at 的下一个最高值。

Lead() 的文档在这里:http://www.postgresql.org/docs/9.4/static/functions-window.html

当您通过引用“下一个”记录为每一行计算功耗时，您可以使用 Sum() 来汇总该设备的所有计算功率。

当你计算出每个设备的功率后，你可以使用 ORDER BY 和 LIMIT 来选择前 n 个耗电设备。

要遵循的步骤，如果您没有信心投入其中并只编写最终的 SQL - 在每个步骤之后确保您了解您的 SQL，并且它只返回您需要的数据:

1. 从小处着手，选择您需要的数据行。
2. 制定 Lead() 函数，定义适当的分区和顺序子句以获得下一行。
3. 添加每行的功率计算。
4. 定义 Sum() 函数，并按设备 ID 分组。
5. 添加 ORDER BY 和 LIMIT 子句。

如果您在执行这些步骤中的任何一个时遇到问题，它们都会成为一个不错的 StackOverflow 问题。