每30分钟间隔计算数据库中重复记录的数量

Question

每30分钟间隔计算数据库中重复记录的数量

3

我需要在下面得到一些帮助：

我有一个名为“Data”的表，其中存储了以下数据。

+------------+----------+-----------
| regdate    | regtime  | items              
+------------+----------+-----------
| 2013-03-02 | 09:12:03 | item1   
| 2013-03-02 | 10:12:05 | item1          
| 2013-03-02 | 15:12:07 | item2    
| 2013-03-02 | 20:12:09 | item3       
| 2013-03-02 | 21:12:11 | item4    
| 2013-03-02 | 22:12:14 | item3

“10项”等内容的表格可以在一天中不同的时间从“09:00至23:00”进行。我还有另一个名为“ItemsPerInterval”的表格，如下所示：

+------------+-------------+-------------+-------------+-------------+
| regdate    | reginterval | item1       | item2       | item3       |
+------------+-------------+-------------+-------------+-------------+
| 2013-03-01 | 09:00:00    | 0           | 0           | 0           |
+------------+-------------+-------------+-------------+-------------+

这里的时间间隔是以30分钟为基础递增的，例如09:00, 09:30, 10:00, 10:30, 直至23:00。

我想要做的是在表格 ItemsPerInterval 中插入数据，包括表格 "Data" (item1, item2 ,item3 ,.... item10) 的循环计数，并按照时间区间存储它们 (09:00:00, 09:30:00, 10:00:00, 10:30:00, .... 23:00:00)，以便每个时间间隔 - 例如 - 09:00:00 将对在 Data 表格上从 09:00:00至09:29:59 注册的所有项目进行循环计数，并且对于其余的时间间隔一直到23:00:00也是如此。

编辑**1

因此，最终必须在ItemsPerInterval表中注册以下所有间隔，并保存在该时间间隔内报告的10个项目的每个项目的计数。

09:00:00
09:30:00
10:00:00
10:30:00
11:00:00
11:30:00
12:00:00
12:30:00
13:00:00
13:30:00
and so on .....
.
.
.
22:00:00
22:30:00
23:00:00

/EDIT **1

我阅读了一些有关程序和例程的内容，但我不知道它们之间的区别，也不知道如何使用它来实现上述目标。

我正在使用：软件：MySQL 软件版本：5.5.24 与PHP一起。

感谢您的支持

谢谢

- Ahmed ElGamil

是否存在 regtime 超出 09:00-23:00 范围的情况，如果有，应该如何处理？ - Wrikken

1

将结果存储在新表中并没有太多意义，因为您始终可以使用查询从表数据中检索此数据，该查询将按间隔选择和分组项目。 - michi

而根据半小时四舍五入，您可能需要使用类似这样的代码：FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(regtime)/1800)*1800); - Wrikken

@Wrikken 是的，由于人为错误，有些项目将在此范围之外注册，但我稍后会添加另一个表来跟踪超出范围的项目。因此，就目前而言，请忽略超出范围的项目。 - Ahmed ElGamil

1

@AhmedElGamilI 如果你想要不存在的时间间隔（即没有发生任何事情），那么在 LEFT JOIN 中，数据必须来自于某个地方，因此你可能需要创建一个虚拟表格，其中包含 09:00, 09:30....22:30, 23:00 的序列。 - Wrikken

显示剩余2条评论

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- fthiella · Accepted Answer

你可以使用这个查询：

SELECT
  regdate,
  SEC_TO_TIME(TRUNCATE(TIME_TO_SEC(regtime)/(60*30),0)*(60*30)) reginterval,
  COUNT(CASE WHEN items='item1' THEN 1 END) item1,
  COUNT(CASE WHEN items='item2' THEN 1 END) item2,
  COUNT(CASE WHEN items='item3' THEN 1 END) item3,
  COUNT(CASE WHEN items='item4' THEN 1 END) item4
FROM Data
GROUP BY
  regdate,
  reginterval

如果您需要将结果行插入到新表中，只需在开头添加此行：

INSERT INTO ItemsPerInterval (regdate, reginterval, item1, item2, item3, item4)
SELECT ...

编辑

如果您想显示所有间隔，即使它们没有值，我认为最好的方法是创建一个包含所需所有间隔的表Intervals：

CREATE TABLE Intervals (
  reginterval time
);

INSERT INTO Intervals VALUES
('09:00:00'),
('09:30:00'),
('10:00:00'),
...

这个查询返回您表中所有时间间隔和日期的组合：

SELECT Dates.regdate, Intervals.reginterval
FROM
  (SELECT DISTINCT regdate FROM Data) Dates,
  Intervals

而这个查询将返回你所需的行：

SELECT
  di.regdate,
  di.reginterval,
  COUNT(CASE WHEN Data.items='item1' THEN 1 END) item1,
  COUNT(CASE WHEN Data.items='item2' THEN 1 END) item2,
  COUNT(CASE WHEN Data.items='item3' THEN 1 END) item3,
  COUNT(CASE WHEN Data.items='item4' THEN 1 END) item4
FROM (
  SELECT Dates.regdate, Intervals.reginterval
  FROM (SELECT DISTINCT regdate FROM Data) Dates,
       Intervals
  ) di
  LEFT JOIN Data
   ON di.regdate=Data.regdate
      AND di.reginterval=SEC_TO_TIME(TRUNCATE(TIME_TO_SEC(Data.regtime)/(60*30),0)*(60*30))
GROUP BY
  regdate,
  reginterval;

请查看fiddle 这里。 如何工作 这里的想法是使用TIME_TO_SEC函数将包含时间的字段regtime转换为自当天开始以来的秒数。

TIME_TO_SEC(regtime)

我们将这个数字除以60*30，即30分钟内的秒数，只保留整数部分：

TRUNCATE(number_of_seconds/(60*30), 0)

然后我们将整数部分乘以60 * 30，以获取以60 * 30秒（30分钟）为单位舍入的秒数。

使用SEC_TO_TIME，我们将秒数转换回时间字段。