How to calculate power consumption from power records?

Question

I have a table that contains the power values ​​(kW) for devices. Values ​​are read from each device once a minute and inserted into a table with a time stamp. I need to calculate the energy consumption (kWh) for a given time interval and return the 10 most energy-consuming devices. Right now I'm asking for results for a given time interval and doing the calculations in the backend, looping all the records. This works great with a small number of devices and with a short period of time, but in real use I could have thousands of devices and a long period of time.

So my question is, how can I do all this in PostgreSQL 9.4.4 so that my query returns only the 10 most energy-consuming (device_id, power_consumption) pairs?

Example table:

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

Simple data example:

 | 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 | 

Required query results:

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

A simplified example (created_at in hours) how I calculate the 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 

EDIT: check this one to find out how I decided to solve it.

Answer 1

If someone accidentally wonders, then this is how I decided it. I followed David's instructions and did 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; 

EDIT: Modified calculation suggested in the comments.

Answer 2

Some of the elements you will need for this:

• Sum () of aggregation to calculate the total number of records
• Functions Lag () / Lead () to calculate for a given record what was the "previous" or "next" value of the record.

So where you can get the current created_tasks and power records for a given row, in SQL you will probably use the window () windowing function to get the created_at and power records to write for the same device identifier, which has the next highest value for created_at.

The docs for Lead () are here: http://www.postgresql.org/docs/9.4/static/functions-window.html

If for each row you calculated energy consumption by reference to the “next” entry, you can use Sum () to summarize all the calculated permissions for this single device.

When you have calculated the power per device, you can use ORDER BY and LIMIT to select the most popular n power consuming devices.

Step by step, if you are not sure how to dive and just write the final SQL - after each step, make sure that you have SQL that you understand, and which returns only the data you need:

• Start small by selecting the data rows you need.
• Design the Lead () function by defining the appropriate sections and ordering to get the next line.
• Add power calculation per line.
• Define the Sum () function and group it by device ID.
• Add the ORDER BY and LIMIT clauses.

If you are having problems with any of these steps, each of them will make a decent StackOverflow question.