如何从用电记录中计算功耗？ [英] How to calculate power consumption from power records?

问题描述

我有一张表，其中包含设备的功率值（kW）。每分钟从每个设备读取一次值，并将其插入带有时间戳的表中。我需要做的是计算给定时间跨度的功耗（kWh），并返回10个最耗电的设备。现在，我查询给定时间范围内的结果，并在后端循环所有记录中进行计算。这在少量设备和较短时间范围内都可以正常工作，但在实际使用情况下，我可以有数千个设备且时间跨度很长。

I have table which contains power values (kW) for devices. Values are read from each device once a minute and inserted into table with timestamp. What I need to do is calculate power consumption (kWh) for given time span and return 10 most power consuming devices. Right now I query results for given time span and do calculation in backend looping all records. This works fine with small amount of devices and with short time span, but in real use case I could have thousands of devices and long time span.

所以我的问题是我可以在PostgreSQL 9.4.4中做到所有这些，以便我的查询仅返回10个最耗电的（device_id，power_consumption）对吗？

So my question is how could I do this all in PostgreSQL 9.4.4 so that my query would return only 10 most power consuming (device_id, power_consumption) pairs?

示例表：

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 |

我如何计算kWh值的简化示例（小时内created_at）：

Simplified example (created_at in hours) how I calculate kWh value:

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

编辑：选中此，看看我最终如何解决这个问题。

Check this to see how I ended up solving this.

推荐答案

如果有人碰巧想知道同一件事，这就是我如何解决这个问题。

我按照David的指示进行了以下操作：

If someone happens to wonder same thing here is how I solved this.
I followed instructions by David and made this:

SELECT
  t.device_id,
  sum(len_y*(extract(epoch from date_trunc('milliseconds', len_x)))7200) AS total
FROM (
    SELECT
      m.id,
      m.device_id,
      m.power,
      m.created_at,
      m.power+lag(m.power) OVER (
        PARTITION BY device_id
        ORDER BY m.created_at
      ) AS len_y,
      m.created_at-lag(m.created_at) OVER (
        PARTITION BY device_id
        ORDER BY m.created_at
      ) AS len_x
    FROM
      mes AS m
  WHERE m.created_at BETWEEN '2015-08-26 13:39:57.834674'::timestamp
    AND '2015-08-26 13:43:57.834674'::timestamp
) AS t
GROUP BY t.device_id
ORDER BY total
DESC LIMIT 10;

编辑：如注释中建议的那样更改了计算。

Changed calculation as suggested in comments.

这篇关于如何从用电记录中计算功耗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！