mysql - 存储过程仅提供有限数量的结果

Question

我的 StoredProcedure 只给我结果中的最大行数等于表中的条目数。

所以 调用 SP_Dashboard_getTransactionsPerDay("2018-03-19", "2018-04-25") 会给我:

date      | total
-----------------
2018-03-19| 0
2018-03-19| 0

如果 event 表中有 2 个条目，而不是请求的完整 38 天。如果 event 中有 6 个条目，则同一调用会为我提供 6 行；如果 event 中有至少 38 个或更多条目，则为 38。

即使 event 表中没有任何条目，是否有任何方法可以获得所有请求日期的结果？

我的 SP 如下所示:

CREATE PROCEDURE `SP_Dashboard_getTransactionsPerDay`(STARTDATE DATE,ENDDATE DATE)
BEGIN


set @i := -1;

SELECT
  DATE(ADDDATE(STARTDATE, INTERVAL @i:=@i+1 DAY)) AS date,
  IFNULL(
      (
        SELECT
          COUNT(*) FROM event AS m2
        WHERE
          DATE(m2.created_at) = DATE(ADDDATE(STARTDATE, INTERVAL @i DAY))
          AND `m2`.`status` = 'ok'
      ),
      0) AS total
FROM
  event AS m1;
HAVING
  @i < DATEDIFF(ENDDATE, STARTDATE);
END

表 DDL:

CREATE TABLE `event` (
  `id` int(15) NOT NULL AUTO_INCREMENT,
  `status` enum('new','ok','error') COLLATE utf8_bin NOT NULL DEFAULT 'new',
  `created_at` datetime NOT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Answer 1

添加日历/日期表通过将以下内容添加到我的数据库来解决我的问题:

一个新表:

-- -----------------------------------------------------
-- Table `dates`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `dates`;

CREATE TABLE `dates` (
  `date` DATE NOT NULL,
  PRIMARY KEY (`date`)
)
ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8 COLLATE = utf8_bin;

自动填充新表的存储过程:

-- -----------------------------------------------------
-- StoredProcedure `SP_filldates`
-- -----------------------------------------------------
DROP PROCEDURE IF EXISTS SP_filldates;

DELIMITER $$
CREATE PROCEDURE SP_filldates(STARTDATE DATE, ENDDATE DATE)
  BEGIN
    WHILE STARTDATE <= ENDDATE DO
      INSERT INTO dates (date) VALUES (STARTDATE);
      SET STARTDATE = date_add(STARTDATE, INTERVAL 1 DAY);
    END WHILE;
  END;
$$
DELIMITER ;

CALL SP_filldates('2018-01-01', '2018-12-31');

以及我最初发布的存储过程的修改:

-- -----------------------------------------------------
-- StoredProcedure `SP_Dashboard_getTransactionsPerDay`
-- -----------------------------------------------------
DROP PROCEDURE
IF EXISTS SP_Dashboard_getTransactionsPerDay;

DELIMITER $$


CREATE PROCEDURE SP_Dashboard_getTransactionsPerDay(
  STARTDATE DATE,
  ENDDATE   DATE
)
  BEGIN

    SET @i := -1;

    SELECT
      DATE(ADDDATE(STARTDATE, INTERVAL @i := @i + 1 DAY)) AS date,
      IFNULL(
          (
            SELECT COUNT(*)
            FROM event AS m2
            WHERE
              DATE(m2.created_at) = DATE(ADDDATE(STARTDATE, INTERVAL @i DAY))
              AND `m2`.`status` = 'ok'
          ),
          0
      ) AS total
    FROM
      dates AS m1
    HAVING
      @i < DATEDIFF(ENDDATE, STARTDATE);

  END$$

DELIMITER ;

此修复适用于最多 365 行的所有请求，因为在这种情况下日期表仅包含 365 个条目。如果需要更多结果，日期表必须包含更多日期。