如何根据用电记录计算用电量？

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

所以我的问题是如何在 PostgreSQL 9.4.4 中完成这一切，以便我的查询仅返回 10 个最耗电的（device_id、power_conspiration）对？

示例表：

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 值的简化示例（以小时为单位创建）：

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 https://stackoverflow.com/questions/32254431/how-to-optimize-sql-query-with-window-functions看看我最终是如何解决这个问题的。

为此，您需要的一些元素是：

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 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 问题。