mysql - 计算日期之间更改的记录

Question

我有三个表tickets和threads和threadstatuses，这里有简化的架构-

CREATE TABLE `tickets` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `subject` varchar(255) NOT NULL,
  `ticketStatuses_id` int(10) unsigned NOT NULL,
  `createdAt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updatedAt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00'
  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=83670708 DEFAULT CHARSET=utf8;

CREATE TABLE `threads` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `tickets_id` int(10) unsigned NOT NULL,
  `body` longtext NOT NULL,
  `assign_ticketStatuses_id` int(10) unsigned DEFAULT NULL,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=31046 DEFAULT CHARSET=utf8;

CREATE TABLE `ticketstatuses` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

您可以看到线程和票证都引用了票证状态。这使我可以显示票证何时更改状态。我需要查询的是获取给定日期范围内每天具有每种状态的工单数量的汇总。因此，用户可以询问“告诉我过去 7 天内每天有多少张票打开或关闭(或其他)”。这里的最终目标是构建一个时间序列图表。

不管怎样，我已经拼凑了一个不可否认的过于复杂且性能不佳的查询，该查询为我提供了一天的结果，但我不知道如何修复它以给出每天的计数。我不需要知道当天有多少票更改为该状态(这很容易)，但在某一天有该状态。这是我想出的-

    SELECT SUM(total) as total, name
    FROM (
        SELECT COUNT(th.id) as total, ts.name
        FROM ticketstatuses ts
        INNER JOIN threads th ON ts.id = th.assign_ticketStatuses_id
        LEFT OUTER JOIN threads nextThread ON th.tickets_id = nextThread.tickets_id
            AND nextThread.createdAt > th.createdAt
            AND nextThread.assign_ticketStatuses_id IS NOT NULL
        WHERE /* Need something different here for the range */ DATE('2015-03-04') BETWEEN DATE(th.createdAt) AND DATE(nextThread.createdAt)
        GROUP BY th.assign_ticketStatuses_id

        UNION

        /* Gives me tickets that are new (have not had a status change) */
        SELECT SUM(c) as total, name
        FROM (
            SELECT COUNT(t.id) c, ts.name as name
            FROM ticketstatuses ts
            INNER JOIN tickets t ON ts.id = t.ticketStatuses_id
            INNER JOIN threads th ON th.tickets_id = t.id
            WHERE th.assign_ticketStatuses_id IS NULL
            GROUP BY th.tickets_id
            HAVING COUNT(th.id) = 1
        ) recs
        GROUP BY recs.name
    ) recs
    GROUP BY recs.name

Answer 1

(编辑:更新的查询)我对 MySQL 很生疏，我的机器上不再安装它，但我认为你可以这样做:

SELECT dte, name, COUNT(name) FROM
  (SELECT ts.name, Date_Format(COALESCE(th.updatedAt, th.createdAt), '%Y-%m-%d') as dte 
  FROM threads th
  INNER JOIN tickets t ON t.id = th.tickets_id
  INNER JOIN ticketstatuses ts ON th.assign_ticketStatuses_id = ts.id) AS j
GROUP BY dte, name

我相当确定这会给您一个按日期分组的计数列表，然后按 ticketStatus.name 分组，但我更愿意接受对 MySQL 更专业的人进行更正。与此同时，here is a SQLFiddle上述查询的内容