MySQL：更新大型數據集，包括時間聚合

我有一個包含數百萬行的大型 MySQL (5.7) 表（每秒包含數據）。在某些情況下，對於大數據范圍，應根據這些值進行計算。因此，我想執行預處理，預先執行計算並將結果儲存在單獨的表中。由於某些內部原因，會執行聚合，例如，我需要一小時的公式的平均值。這是一個例子：

CREATE TABLE `datavalues` (
   `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
   `ddate` DATE NOT NULL,
   `ttime` TIME NOT NULL,
   `unixtime` BIGINT(20) NOT NULL,
   `value1` DOUBLE,
   `value2` DOUBLE,
   PRIMARY KEY (`id`),
   INDEX `unixtime_idx` (`unixtime`) 
);
CREATE TABLE `calculation_result` (
   `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
   `ddate` DATE NOT NULL,
   `ttime` TIME NOT NULL,
   `unixtime` BIGINT(20) NOT NULL,
   `result1` DOUBLE,
   PRIMARY KEY (`id`),
   INDEX `unixtime_idx` (`unixtime`) 
);

作為範例性計算，我計算 value1-value2。每小時用一個值填充計算表的初始數據查詢可能如下所示：

INSERT INTO calculation_result (ddate, ttime, unixtime, result1)
select max(ddate), STR_TO_DATE(TIME_FORMAT(max(ttime),'%H:00:00'),'%H:%i:%s'), 
UNIX_TIMESTAMP(adddate(max(ddate), INTERVAL HOUR(max(ttime)) HOUR)), AVG(value1 - value2)
FROM datavalues GROUP BY ddate, hour(ttime);

到現在為止還挺好。但是，如果計算描述發生更改，例如，從 value1 - value2 更改為 value1 + value2，我正在努力實現更新計算結果表中的 result1 列的查詢。如何有效地更新 result1 列？刪除和重新創建計算表不是一種選擇，因為它在不同的列中包含許多進一步的計算。
此外，我預計更新過程需要一些時間。由於數據是定期新導入的，如何防止calculation_result表被鎖定？也許使用數據塊的批量更新？

謝謝你的幫助。

可以UPDATE通過加入您擁有的 SELECT 來完成，只有您需要一些列的別名。

邏輯很簡單，您可以將兩個表與 ddate 和時間連接起來，例如始終01:00:00按數據和小時分組

UPDATE calculation_result cr
       INNER JOIN
   (SELECT 
       MAX(ddate) ddate,
           STR_TO_DATE(TIME_FORMAT(MAX(ttime), '%H:00:00'), '%H:%i:%s') AS ttime,
           UNIX_TIMESTAMP(ADDDATE(MAX(ddate), INTERVAL HOUR(MAX(ttime)) HOUR)) unixtime,
           AVG(value1 - value2) avgresult
   FROM
       datavalues
   GROUP BY ddate , HOUR(ttime)) t1 ON cr.ddate = t1.ddate
       AND cr.ttime = t1.ttime 
SET 
   cr.result1 = t1.result1 ,cr.unixtime = t1.unixtime

引用自：https://dba.stackexchange.com/questions/285501