当前位置：文江博客话题详情

python: mysql: 当没有找到行时返回 0

发布于 2024-08-19 13:02:41 字数 710 浏览 4 评论 0原文

表结构 - 数据呈现 5 分钟。插槽 -

data_point |点_日期

12 | 12 00:00

14 | 14 00:05

23 | 23 00:10

10 | 10 00:15

43 | 43 00:25

10 | 10 00:40

当我运行查询 30 分钟时。如果存在数据，我将得到 6 行（每 5 分钟邮票一行）。简单查询 -

选择 data_point 来自 some_table 其中 point_date >= start_date AND point_date <结束日期 order by point_date

现在，当我没有特定时间段的条目时（例如，时间段 00:20 丢失），我希望“data_point”为返回为 0

当不存在时，REPLACE、IF、IFNULL、ISNULL 不起作用返回行。

我认为具有默认值的 Union 可以工作，但它也失败了，或者可能是我没有正确使用它。

有没有办法仅通过 sql 来完成此操作？

注意： Python 2.6 和 Python 2.6 mysql版本5.1

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

逆光下的微笑 2024-08-26 13:02:41

是的，您可以仅使用 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.txt”的文件中， sql'，你可以像这样加载它：

shell> mysql -uroot test < per5minproc.sql

In Python using 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 可以是 SP 的参数。
而且，这确实都是 SQL！

Yes, you can do that using SQL only. A solution would be to use a Stored Routine. The bellow Stored Procedure produces following output:

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

The table I used:

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

Here the Stored Procedure (adjust for your environment):

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();

If you save the above into a file called 'per5minproc.sql', you can load it like this:

shell> mysql -uroot test < per5minproc.sql

In Python using MySQLdb (I didn't get this working in MySQL Connector/Python, me ashamed!):

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()

The solution above works, but probably will need some tweaking, e.g. dtStart can be an argument to the SP.
And, it's indeed all SQL!

回复收藏 0 原文

清旖 2024-08-26 13:02:41

您无法查询您没有的数据。

你（作为一个有思想的人）可以声称 00:20 的数据丢失了；但是没有简单的方法可以在更正式的 SQL 意义上定义“缺失”。

您能做的最好的事情就是创建一个包含所有预期时间的表。

然后，您可以在预期时间（包括 0 表示 00:20）和实际时间（缺少 00:20 样本）之间进行外连接，您将得到您期望的结果。

回复收藏 0 原文

深海夜未眠 2024-08-26 13:02:41

我认为没有简单的方法可以凭空创建不存在的记录，但您可以自己创建一个包含您感兴趣的所有时间戳的 point_dates 表，并将其保留在您的数据上：

select pd.slot, IFNULL(数据点, 0) 来自 point_dates pd 在 st.point_date=pd.slot 上左连接 some_table st 其中 point_date >= start_date AND point_date <结束日期按 point_date 排序