在事件表中计数行，按时间范围分组，很多

想象一下我有一张这样的table：

CREATE TABLE `Alarms` (
    `AlarmId` INT UNSIGNED NOT NULL AUTO_INCREMENT
        COMMENT "32-bit ID",

    `Ended` BOOLEAN NOT NULL DEFAULT FALSE
        COMMENT "Whether the alarm has ended",

    `StartedAt` TIMESTAMP NOT NULL DEFAULT 0
        COMMENT "Time at which the alarm was raised",

    `EndedAt` TIMESTAMP NULL
        COMMENT "Time at which the alarm ended (NULL iff Ended=false)",

    PRIMARY KEY (`AlarmId`),

    KEY `Key4` (`StartedAt`),
    KEY `Key5` (`Ended`, `EndedAt`)
) ENGINE=InnoDB;

现在，对于gui，我要生成：
至少有一个警报处于“活动”状态的天数列表
每天有多少警报启动
每天有多少警报结束
其目的是为用户提供一个下拉框，用户可以从中选择一个日期来查看当天活动的任何警报（开始于之前或期间，结束于期间或之后）。比如说：

+-----------------------------------+
| Choose day                      ▼ |
+-----------------------------------+
|   2017-12-03 (3 started)          |
|   2017-12-04 (1 started, 2 ended) |
|   2017-12-05 (2 ended)            |
|   2017-12-16 (1 started, 1 ended) |
|   2017-12-17 (1 started)          |
|   2017-12-18                      |
|   2017-12-19                      |
|   2017-12-20                      |
|   2017-12-21 (1 ended)            |
+-----------------------------------+

我可能会强制对警报进行年龄限制，以便在一年后存档/删除警报。这就是我们正在研究的规模。
我预计每天有从零到上万的警报。
我的第一个想法相当简单：

(
    SELECT
        COUNT(`AlarmId`) AS `NumStarted`,
        NULL AS `NumEnded`,
        DATE(`StartedAt`) AS `Date`
    FROM `Alarms`
    GROUP BY `Date`
)
UNION
(
    SELECT
        NULL AS `NumStarted`,
        COUNT(`AlarmId`) AS `NumEnded`,
        DATE(`EndedAt`) AS `Date`
    FROM `Alarms`
    WHERE `Ended` = TRUE
    GROUP BY `Date`
);

这使用了我的两个索引，以及join类型 ref 和参考类型 const ，我很高兴。我可以迭代结果集，转储非- NULL 在c++中找到的值 std::map<boost::gregorian::date, std::pair<size_t, size_t>> （然后在没有警报开始或结束的几天内“填补空白”，但从前几天起一直处于活动状态）。
我要解决的问题是，列表应该考虑基于位置的时区，但只有我的应用程序知道时区。出于逻辑上的原因，mysql会话是故意的 SET time_zone = '+00:00' 所以时间戳在utc中都被剔除了(然后使用各种其他工具对历史时区执行任何必要的特定于位置的更正，考虑到dst和诸如此类的内容。）对于应用程序的其余部分，这是很好的，但是对于这个特定的查询，它会中断日期 GROUP 惯性导航与制导。
也许我可以预先计算（在我的应用程序中）一个时间范围列表，并生成一个2n的巨大查询 UNION ed查询（其中n=要检查的“天数”）并获取 NumStarted 以及 NumEnded 这样算数：

-- Example assuming desired timezone is -05:00
-- 
-- 3rd December
(
    SELECT
        COUNT(`AlarmId`) AS `NumStarted`,
        NULL AS `NumEnded`,
        '2017-12-03' AS `Date`
    FROM `Alarms`
    -- Alarm started during 3rd December UTC-5
    WHERE `StartedAt` >= '2017-12-02 19:00:00'
      AND `StartedAt` <  '2017-12-03 19:00:00'
    GROUP BY `Date`
)
UNION
(
    SELECT
        NULL AS `NumStarted`,
        COUNT(`AlarmId`) AS `NumEnded`,
        '2017-12-03' AS `Date`
    FROM `Alarms`
    -- Alarm ended during 3rd December UTC-5
    WHERE `EndedAt` >= '2017-12-02 19:00:00'
      AND `EndedAt` <  '2017-12-03 19:00:00'
    GROUP BY `Date`
)
UNION

-- 4th December
(
    SELECT
        COUNT(`AlarmId`) AS `NumStarted`,
        NULL AS `NumEnded`,
        '2017-12-04' AS `Date`
    FROM `Alarms`
    -- Alarm started during 4th December UTC-5
    WHERE `StartedAt` >= '2017-12-03 19:00:00'
      AND `StartedAt` <  '2017-12-04 19:00:00'
    GROUP BY `Date`
)
UNION
(
    SELECT
        NULL AS `NumStarted`,
        COUNT(`AlarmId`) AS `NumEnded`,
        '2017-12-04' AS `Date`
    FROM `Alarms`
    -- Alarm ended during 4th December UTC-5
    WHERE `EndedAt` >= '2017-12-03 19:00:00'
      AND `EndedAt` <  '2017-12-04 19:00:00'
    GROUP BY `Date`
)
UNION

-- 5th December
-- [..]

但是，当然，即使我将数据库限制为一年的历史警报，也会达到730 UNION d SELECT s。我的蜘蛛感觉告诉我这是个很坏的主意。
我还能怎样生成这种时间分组统计数据呢？或者这真的很愚蠢，我应该考虑解决阻止我使用tzinfo和mysql的问题吗？
必须在mysql 5.1.73（centos 6）和mariadb 5.5.50（centos 7）上工作。

这个 UNION 这种方法实际上离可行的解决方案不远了；您可以通过创建一个临时表来实现同样的目的，而不必进行灾难性的大查询：

CREATE TEMPORARY TABLE `_ranges` (
   `Start` TIMESTAMP NOT NULL DEFAULT 0,
   `End`   TIMESTAMP NOT NULL DEFAULT 0,
   PRIMARY KEY (`Start`, `End`)
);

INSERT INTO `_ranges` VALUES
   -- 3rd December UTC-5
   ('2017-12-02 19:00:00', '2017-12-03 19:00:00'),
   -- 4th December UTC-5
   ('2017-12-03 19:00:00', '2017-12-04 19:00:00'),
   -- 5th December UTC-5
   ('2017-12-04 19:00:00', '2017-12-05 19:00:00'),
   -- etc.
;

-- Now the queries needed are simple and also quick:

SELECT
   `_ranges`.`Start`,
   COUNT(`AlarmId`) AS `NumStarted`
FROM `_ranges` LEFT JOIN `Alarms`
  ON `Alarms`.`StartedAt` >= `_ranges`.`Start`
  ON `Alarms`.`StartedAt` <  `_ranges`.`End`
GROUP BY `_ranges`.`Start`;

SELECT
   `_ranges`.`Start`,
   COUNT(`AlarmId`) AS `NumEnded`
FROM `_ranges` LEFT JOIN `Alarms`
  ON `Alarms`.`EndedAt` >= `_ranges`.`Start`
  ON `Alarms`.`EndedAt` <  `_ranges`.`End`
GROUP BY `_ranges`.`Start`;

DROP TABLE `_ranges`;

（此方法受dba.se帖子的启发。）
注意有两个 SELECT s-原版 UNION 因为不能在同一查询中两次访问临时表。但是，由于我们已经引入了其他语句（ CREATE , INSERT 以及 DROP )在这种情况下，这似乎是一个没有实际意义的问题。
在这两种情况下，每一行代表一个请求的时段，第一列等于时段的“开始”部分（这样我们就可以在resultset中识别它）。
请确保在代码中根据需要使用异常处理，以确保 _ranges 是 DROP 在你的例行公事回来之前；尽管临时表是mysql会话的本地表，但是如果您以后继续使用该会话，那么您可能需要一个干净的状态，特别是如果这个函数将被再次使用。
如果这仍然太重，例如因为你有很多时间段和 CREATE TEMPORARY TABLE 因此，它本身将变得太大，或者因为多个语句不适合您的调用代码，或者因为您的用户没有创建和删除临时表的权限，您将不得不使用简单的 GROUP BY 结束 DAY(Date) ，并确保您的用户 mysql_tzinfo_to_sql 每当系统的数据更新时。

在事件表中计数行，按时间范围分组，很多

1条答案

相关问题

热门标签

最新问答