MySQL读取，计算和回写

I need help with some MySQL commands.

我需要一些MySQL命令的帮助。

My MySQL table look's like this

我的MySQL表看起来像这样

    mysql> DESCRIBE sensor_readout;
+-----------+-----------+------+-----+---------------------+-------+
| Field     | Type      | Null | Key | Default             | Extra |
+-----------+-----------+------+-----+---------------------+-------+
| timestamp | timestamp | NO   |     | 0000-00-00 00:00:00 |       |
| sensor_id | char(2)   | NO   | PRI | NULL                |       |
| volume    | int(4)    | NO   | PRI | NULL                |       |
+-----------+-----------+------+-----+---------------------+-------+

My current stored data (it is updating on volume change) is

我当前存储的数据(它正在更新音量变化)是

mysql> SELECT * FROM sensor_readout;
+---------------------+-----------+--------+
| timestamp           | sensor_id | volume |
+---------------------+-----------+--------+
| 2014-04-01 11:27:16 | 22        |   3327 |
| 2014-04-01 12:44:00 | 22        |   3328 |
| 2014-04-01 11:27:13 | 23        |   2643 |
| 2014-04-01 11:54:44 | 23        |   2644 |
| 2014-04-01 11:27:14 | 24        |   3407 |
| 2014-04-01 11:55:03 | 24        |   3408 |
+---------------------+-----------+--------+

What I would like to do is that whenever the script runs (I guess python but any other script suggestion is welcome) the script would take last volume record (by timestamp) on sensors id 22, 23 and 24. Perform mathematical calculation on 22 and 23 volume.

我想做的是每当脚本运行时(我猜python但欢迎任何其他脚本建议)脚本将采用传感器id 22,23和24上的最后一个音量记录(按时间戳)。在22和22执行数学计算23卷。

And than write back the data to table "poraba". 22+23 is representing "voda_mrzla". 24 would be voda_topla. With the date and month of entry to the database (don't need time).

然后将数据写回表“poraba”。 22 + 23表示“voda_mrzla”。 24将是voda_topla。随着进入数据库的日期和月份(不需要时间)。

mysql> DESCRIBE poraba;
+------------+------------+------+-----+---------+-------+
| Field      | Type       | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+-------+
| mesec      | date       | NO   |     | NULL    |       |
| voda_mrzla | varchar(5) | NO   |     | NULL    |       |
| voda_topla | varchar(5) | NO   |     | NULL    |       |
+------------+------------+------+-----+---------+-------+ 

For now I tried with the following python script (see below). But I am stuck there. Only thing I can do is create readout from last value on each sensor_id

现在我尝试使用以下python脚本(见下文)。但我被困在那里。我唯一能做的就是从每个sensor_id的最后一个值创建读数

import MySQLdb

#establish connection to MySQL. You'll have to change this for your database.
conn = MySQLdb.connect('localhost', '*****', '****', 'moteino') 

cursor=conn.cursor()



sql = """SELECT sensor_id, volume 
         FROM sensor_readout 
         WHERE sensor_id = 22  
         ORDER BY timestamp DESC LIMIT 1"""

sql1 = """SELECT sensor_id, volume 
         FROM sensor_readout 
         WHERE sensor_id = 23 
         ORDER BY timestamp DESC LIMIT 1"""

sql2 = """SELECT sensor_id, volume 
         FROM sensor_readout 
         WHERE sensor_id = 24 
         ORDER BY timestamp DESC LIMIT 1"""

cursor.execute(sql1)
data1=cursor.fetchall()

cursor.execute(sql2)
data2=cursor.fetchall()

cursor.execute(sql)
data=cursor.fetchall()


print(data, data1, data2)

The print output is like this

打印输出是这样的

((('22', 3331L),), (('23', 2647L),), (('24', 3412L),))

UPDATE!!!!

Sweet guys the following code worked just as I wanted. I did change timestamp with time_recorded so minimize the timestamp confusion.

甜蜜的下面的代码就像我想要的那样工作。我确实用time_recorded更改了时间戳,因此最小化时间戳混淆。

    -- Direct insert into DDBB
INSERT INTO poraba ( voda_mrzla, voda_topla )

  -- Get the values, with the right operations
  SELECT
    SUM( IF(sensor_id = 22 OR sensor_id = 23, volume, 0 ) ) voda_mrzla
  , SUM( IF ( sensor_id = 24, volume, 0 ) ) voda_topla
  FROM ( 
  -- Get only the values with max timestamp for each sensor
  SELECT s.`time_recorded`, s.sensor_id, s.volume
  FROM sensor_readout s
  INNER JOIN (
      SELECT sensor_id, MAX(`time_recorded`) mts
      FROM sensor_readout
      GROUP BY sensor_id
  ) maxS
  ON ( s.`time_recorded` = maxS.mts AND s.sensor_id = maxS.sensor_id )
  ) maxTime

I do have another favor to ask. The last step I would like to achieve is now for the script to take value from previous and current month and creates minus calculation. This will than represent the one month of used cold and hot water. Sorry for eng/slo wording mash-up (cold is voda_mrzla and hot is voda_topla)

我还有另外一个问题。我想要实现的最后一步是让脚本从前一个月和当前月份获取值并创建减去计算。这将代表一个月的用过的冷水和热水。对不起eng / slo措辞mash-up(冷是voda_mrzla,热是voda_topla)

       mysql> DESCRIBE usage_per_month;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| difference_cold | varchar(10) | NO   |     | NULL    |       |
| difference_hot  | varchar(10) | NO   |     | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+

Mybe this table would be easier to select previous value

Mybe这个表更容易选择以前的值

mysql> SELECT * FROM poraba;
+---------------------+------------+------------+-----------+
| mesec               | voda_mrzla | voda_topla | id_poraba |
+---------------------+------------+------------+-----------+
| 2014-03-03 16:19:08 | 5985       | 3417       |         1 |
| 2014-04-03 20:57:51 | 5978       | 3412       |         2 |

2 个解决方案

#1

Use pure MySQL query, is going to be easier and faster: Try UPDATE with SELECT.

使用纯MySQL查询,将更容易,更快:尝试使用SELECT UPDATE。

MySQL - UPDATE query based on SELECT Query

MySQL - 基于SELECT Query的UPDATE查询

EDIT

I misread your question, in your case you should use INSERT with SELECT, :P

我误解了你的问题,在你的情况下你应该使用INSERT和SELECT,:P

INSERT with SELECT

INSERT与SELECT

Ok, I had more time to enjoy this, XD:

好的,我有更多的时间来享受这个,XD:

-- Direct insert into DDBB
INSERT INTO poraba ( voda_mrzla, voda_topla )

  -- Get the values, with the right operations
  SELECT
    SUM( IF(sensor_id = 22 OR sensor_id = 23, volume, 0 ) ) voda_mrzla
  , SUM( IF ( sensor_id = 24, volume, 0 ) ) voda_topla
  FROM ( 
  -- Get only the values with max timestamp for each sensor
  SELECT s.`timestamp`, s.sensor_id, s.volume
  FROM sensor_readout s
  INNER JOIN (
      SELECT sensor_id, MAX(`timestamp`) mts
      FROM sensor_readout
      GROUP BY sensor_id
  ) maxS
  ON ( s.`timestamp` = maxS.mts AND s.sensor_id = maxS.sensor_id )
  ) maxTime

You may check the result of the select in this sqlfiddle: http://sqlfiddle.com/#!2/67e65/1

您可以在此sqlfiddle中查看select的结果:http://sqlfiddle.com/#!2/67e65/1

There are some table structure differences with your tables, to allow us insert timestamp easily: I set default timestamp CURRENT_TIMESTAMP, so any added row will get the current time. As you may see, with this query you can insert directly from the databases with no more script calculation. The sum and if are for getting only the right values from the three rows we get from the SELECT max values query (Check more info about this problem in SQL Select only rows with Max Value on a Column).

您的表有一些表结构差异,以便我们轻松插入时间戳:我设置默认时间戳CURRENT_TIMESTAMP,因此任何添加的行都将获得当前时间。正如您所看到的,使用此查询,您可以直接从数据库插入而无需更多脚本计算。 sum和if用于从SELECT max values查询中获取的三行中只有正确的值(在SQL中查看有关此问题的更多信息仅选择列上具有Max Value的行)。

After that, your script only have to run this query and you'll have your fresh data in the table, ready to be picked, XD. Sorry about not writing the whole code in your Pyton script, I'm from PHP and don't want to mess it.

之后,您的脚本只需运行此查询,您就可以在表格中获得新数据,准备好选择XD。很抱歉没有在Pyton脚本中编写整个代码,我来自PHP并且不想弄乱它。

#2

Try this query in whatever script you like:

在您喜欢的任何脚本中尝试此查询:

INSERT INTO poraba
SET voda_mrzla = (
    SELECT volume 
    FROM sensor_readout 
    WHERE sensor_id = 22  
    ORDER BY `timestamp` DESC LIMIT 1
) + (
    SELECT volume 
    FROM sensor_readout 
    WHERE sensor_id = 23  
    ORDER BY `timestamp` DESC LIMIT 1
),
voda_topla = (
    SELECT volume 
    FROM sensor_readout 
    WHERE sensor_id = 24
    ORDER BY `timestamp` DESC LIMIT 1
),
`date` = (
    SELECT `timestamp`
    FROM sensor_readout 
    ORDER BY `timestamp` DESC LIMIT 1
)