python : mysql : 找不到行时返回0

Question

表结构 - 数据以5分钟为单位记录 -

数据点 | 时间

12 | 00:00

14 | 00:05

23 | 00:10

10 | 00:15

43 | 00:25

10 | 00:40

当我查询30分钟的数据时，如果有数据，我会得到6行结果（每5分钟一个结果）。简单的查询语句是 -

select data_point
from some_table
where point_date >= start_date
AND point_date < end_date
order by point_date

现在，如果某个时间段没有数据（比如说00:20这个时间段缺失），我希望“数据点”返回0

使用REPLACE、IF、IFNULL、ISNULL这些方法在没有返回行的时候都不管用。

我以为用默认值的联合查询可以解决这个问题，但也失败了，或者说我可能没有用对。

有没有办法仅通过SQL来实现这个功能？

注意： Python 2.6 和 mysql 版本 5.1

Answer 1

你不能查询你没有的数据。

作为一个有思考能力的人，你可以说00:20的数据缺失了；但是在SQL中，没有简单的方法来更正式地定义“缺失”这个概念。

你能做的最好办法是创建一个包含所有预期时间的表。

然后，你可以在预期时间（包括00:20的0值）和实际时间（缺少00:20这个样本）之间做一个外连接，这样你就能得到你想要的结果。

Answer 2

我觉得没有简单的方法可以凭空创建不存在的记录，不过你可以自己创建一个叫做point_dates的表，里面包含你感兴趣的所有时间戳，然后把它和你的数据进行左连接：

select pd.slot, IFNULL(data_point, 0) from point_dates pd left join some_table st on st.point_date=pd.slot where point_date >= start_date AND point_date < end_date order by point_date

Answer 3

是的，你可以仅通过SQL来实现这个功能。一个解决方案是使用存储过程。下面的存储过程会产生以下输出：

start   cnt
00:05:00   1
00:10:00   0
00:15:00   1
00:20:00   0
00:25:00   1
00:30:00   0
00:35:00   1
00:40:00   0
00:45:00   0
00:50:00   0
00:55:00   2

我使用的表：

CREATE TABLE `timedata` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` datetime DEFAULT NULL,
  `c2` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

这里是存储过程（根据你的环境进行调整）：

DROP PROCEDURE IF EXISTS per5min;
DELIMITER //
CREATE PROCEDURE per5min ()
BEGIN
  DECLARE dtMin DATETIME;
  DECLARE dtMax DATETIME;
  DECLARE dtStart DATETIME;
  DECLARE dtStop DATETIME;
  DECLARE tmDiff TIME;
  DECLARE result INT UNSIGNED;
  SET @offset = 5 * 60;
  SELECT MIN(c1) into dtMin FROM timedata;
  SELECT MAX(c1) into dtMax FROM timedata;

  CREATE TEMPORARY TABLE tmp_per5min (
      start TIME,
      cnt INT UNSIGNED
  );

  SET dtStart = dtMin;
  REPEAT
    SELECT dtStart + INTERVAL @offset SECOND into dtStop;
    SELECT count(c2) into result FROM timedata WHERE c1 BETWEEN dtStart and dtStop;
    SELECT TIME(SUBTIME(dtStop,TIME(dtMin))) into tmDiff;
    INSERT INTO tmp_per5min (start,cnt) VALUES (tmDiff,result);
    SET dtStart = dtStop;
  UNTIL dtStop >= dtMax END REPEAT;

  SELECT * FROM tmp_per5min;
  DROP TABLE tmp_per5min;
END;
//
DELIMITER ;

CALL per5min();

如果你把上面的内容保存到一个叫做'per5minproc.sql'的文件中，你可以这样加载它：

shell> mysql -uroot test < per5minproc.sql

在Python中使用MySQLdb（我在MySQL Connector/Python中没有成功运行这个，真是不好意思！）：

import MySQLdb as m

if __name__ == '__main__':
    db = m.connect(user='root',db='test')
    c = db.cursor()
    c.callproc("per5min")
    print(c.fetchall())
    c.close()
    db.close()

上面的解决方案是可行的，但可能需要一些调整，比如dtStart可以作为存储过程的一个参数。而且，这确实全是SQL！