php - MySQL读取、计算和回写

Question

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

我的 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                |       |
+-----------+-----------+------+-----+---------------------+-------+

我当前存储的数据(随着音量变化而更新)是

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

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

然后将数据写回表“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    |       |
+------------+------------+------+-----+---------+-------+ 

现在我尝试使用以下 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)

打印输出是这样的

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

更新!!!!

亲爱的，下面的代码就像我想要的那样工作。我确实使用 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

我还有一个要求。我想要实现的最后一步是现在让脚本从上个月和当月获取值并创建负计算。这将代表一个月的冷水和热水使用量。抱歉 eng/slo 措辞混搭(冷是 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这个表会更容易选择以前的值

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 |

Answer 1

使用纯 MySQL 查询，会更容易和更快:尝试使用 UPDATE 和 SELECT。

MySQL - UPDATE query based on SELECT Query

编辑

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

INSERT with SELECT

好吧，我有更多时间享受这个，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

你可以在这个 sqlfiddle: http://sqlfiddle.com/#!2/67e65/1 中检查选择的结果

与您的表有一些表结构差异，以便我们轻松插入时间戳:我设置默认时间戳 CURRENT_TIMESTAMP，因此任何添加的行都将获得当前时间。如您所见，通过此查询，您可以直接从数据库中插入，而无需更多的脚本计算。 sum 和 if 用于仅从我们从 SELECT 最大值查询中获得的三行中获取正确的值(查看有关此的更多信息SQL Select only rows with Max Value on a Column 中的问题)。

在那之后，您的脚本只需运行此查询，您就会在表中获得新的数据，随时可供选择，XD。抱歉没有在您的 Pyton 脚本中编写整个代码，我来自 PHP，不想弄乱它。