mysql - 使用sql过程填写 'gap rows'

Question

我有一个包含每小时值的表，类似于:

|ID  |Date           |Value |
|1   |2016-1-1 01:00 |12    |
|2   |2016-1-1 02:00 |23    |
|3   |2016-1-1 03:00 |45    |  <-- Notice gap of 3 hours
|4   |2016-1-1 06:00 |12    |
|5   |2016-1-1 07:00 |3     |

我想调用一个过程或 sql 来用相应的日期和 0 值填充这些空白

在 07:15 调用的过程的示例输出:

|Date           |Value |
|2016-1-1 01:00 |12    |
|2016-1-1 02:00 |23    |
|2016-1-1 03:00 |45    |
|2016-1-1 04:00 |0     |  <-- Notice gap now filled
|2016-1-1 05:00 |0     |  <-- Notice gap now filled
|2016-1-1 06:00 |12    |
|2016-1-1 07:00 |3     |

最好，我还希望它能填补当前时间之前的任何空白。 (例如，如果我在 2016 年 1 月 1 日 05:15 调用此过程，它将包括间隙行 04:00 和 05:00。

虽然我没有太多尝试分享，但我尝试在插入之前/之后使用触发器来填补空白，但遇到了不允许在触发表 (MySQL) 上发生插入/更新的问题。在 sql 后以编程方式执行此操作是可能的，但如果可行，我想使用 sql 执行此操作!

Answer 1

• 我们将创建一个示例表来存储按小时计算的数据/值。

• 然后我们将在调用时创建一个过程来检查最后一个完成小时的数据存在于表中。

  [最后完成的小时意味着如果当前时间是 2016-02-12 13:10:53 那么最后完成的时间是被视为“2016-02-12 12:00:00”。]

• 如果表中存在最后一小时的数据，则程序与那个小时无关。否则程序为最后一个完成的小时插入一行，如下所示:

  (Date,value) ("2016-02-12 12:00:00",0).

下面是实现:

表格:

CREATE TABLE `hourlyTable` (
`ID`  int(11) NOT NULL AUTO_INCREMENT ,
`Date`  datetime NULL DEFAULT NULL ,
`value`  int(11) NULL DEFAULT NULL ,
PRIMARY KEY (`ID`)
);

存储过程:

delimiter //
CREATE PROCEDURE `HourlyProcedure`()
BEGIN
      DECLARE my_value INT DEFAULT -1;

              select `value` INTO my_value FROM hourlytable WHERE `Date` = DATE_FORMAT(NOW() - INTERVAL 1 HOUR,"%Y-%m-%d %H:00:00");

        IF my_value = -1 THEN
             INSERT INTO hourlytable 
                  SET `Date` = DATE_FORMAT(NOW() - INTERVAL 1 HOUR,"%Y-%m-%d %H:00:00"),
                      `value` = 0;
        END IF;
END//

事件:
接下来我们将创建一个事件，该事件将在每小时的第 5 分钟触发此过程调用。

CREATE EVENT `HourlyProcedureCallEvent`
ON SCHEDULE EVERY 1 HOUR STARTS '2016-02-12 13:05:00'
ON COMPLETION PRESERVE
ENABLE
DO
CALL HourlyProcedure();

注意:您的 MySQL 服务器中必须有 event_scheduler ON。这是设置 event_scheduler ON 的查询。

SET GLOBAL event_scheduler = ON;

进一步说明:

只关心最后一个小时完成的数据更容易处理。但是，如果您真的想通过存储过程填补多个空白，那么以下过程可能会很方便。

delimiter //
CREATE  PROCEDURE `MultipleGapFillerProcedure`(start INT,end INT)
BEGIN
      DECLARE my_value INT DEFAULT -1;

        label1 : 
            WHILE start <= end DO 
                        select `value` INTO my_value FROM hourlytable WHERE `Date` = DATE_FORMAT(NOW() - INTERVAL start HOUR,"%Y-%m-%d %H:00:00");

            IF my_value = -1 THEN
             INSERT INTO hourlytable 
                  SET `Date` = DATE_FORMAT(NOW() - INTERVAL start HOUR,"%Y-%m-%d %H:00:00"),
                      `value` = 0;

            END IF;
            SET my_value = -1;
            SET start = start + 1;
        END WHILE label1;

END//

• 如果您这样调用 MultipleGapFillerProcedure

  CALL MultipleGapFillerProcedure(1,3);

• 然后这个程序将尝试插入最后连续三个小时数据到表中(仅当不存在时)。
• 最后，我认为最好将此任务留给您的应用程序水平。