每隔30分钟在数据库上计算重复出现的记录

I need some help on the below

下面我需要一些帮助

i have the table "Data" getting data stored on it like the below

我有一个表“数据”来获取存储在其中的数据，如下所示

+------------+----------+-----------
| 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

and so on for 10 items can go to the table at different times during the day from 09:00 till 23:00

10个项目可以在白天的不同时间从上午9点到晚上11点

and i have another table "ItemsPerInterval" like the below

我还有另一个表格“ItemsPerInterval”，如下所示

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

and so on where the interval is increasing on 30 minutes basis like 09:00 , 09:30 , 10:00, 10:30 ... till 23:00

如果间隔增加到30分钟比如09:00 09:30 10:00 10:30…直到11点

What i want to do is to insert data on the table ItemsPerInterval to include the recurring count for the items on the table "Data" (item1, item2 ,item3 ,.... item10) and store them upon interval basis (09:00:00, 09:30:00, 10:00:00, 10:30:00, .... 23:00:00) so that each interval - for example - 09:00:00 will have the recurring count for all items that has been registered on the Data table from 09:00:00 till 09:29:59 and so one for the rest of intervals up till 23:00:00

我想做的是插入数据表ItemsPerInterval包括重复计数的物品放在桌子上“数据”(item1,第二条,item3 ....item10)并将它们存储在间隔的基础上(09:00:00、09:30:00 10:00:00,10:30:00,....23:00:00)因此，每个间隔——例如——09:00:00将具有从00:00到09:29 . 59在数据表上注册的所有项的循环计数，因此，在其余间隔中，直到23:00:00有一个重复计数

EDIT **1

编辑* * 1

so eventually all below intervals must be registered on the ItemsPerInterval table and hold the count for each item of the 10 items that has been reported with in the interval time window

因此，最终所有以下间隔都必须在ItemsPerInterval表上注册，并保存在interval time窗口中报告的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

/编辑* * 1

i did some readings about routines / procedures but i don't know the difference and don't know how to use it to implement the above.

我做了一些关于例程/程序的阅读，但我不知道其中的区别，也不知道如何使用它来实现上面的内容。

i am using Software: MySQL Software version: 5.5.24 with PHP.

我使用的是软件:MySQL软件版本:5.5.24和PHP。

Appreciate your support

感谢你的支持

Thank you

谢谢你！

1 个解决方案

#1

You could use this query:

您可以使用这个查询:

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

If you need to insert the resulting rows into a new table, just add this line at the beginning:

如果需要将结果行插入到新表中，只需在开头添加这一行:

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

Edit

编辑

If you also want to show all intervals, even if they have no values, I think that the best you can do is to have a table Intervals that contains all intervals that you need:

如果你也想显示所有的间隔，即使它们没有值，我认为你所能做的最好的是有一个包含所有你需要的间隔的表间隔:

CREATE TABLE Intervals (
  reginterval time
);

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

This query returns all intervals, combined with all dates present in your table:

此查询返回所有间隔，并结合表中所有日期:

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

And this query returns the rows you need:

这个查询返回你需要的行:

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;

Please see fiddle here.

请在这里看到小提琴。

How does this work

这是如何工作的

The idea here is to convert regtime, which is a field that contains a time, to the number of seconds since the beginning of the day, using TIME_TO_SEC function:

这里的想法是使用TIME_TO_SEC函数将regtime(包含时间的字段)转换为一天开始后的秒数:

TIME_TO_SEC(regtime)

we then divide this number to 60*30 which is the number of second in 30 minutes, keeping only the integer part:

然后把这个数除以60*30，也就是30分钟内秒数，只保留整数部分:

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

then we multiply the integer part back to 60*30 to obtain the number of seconds, rounded by 60*30 seconds (30 minutes).

然后我们将整数部分乘以60*30，得到秒数，四舍五入60*30秒(30分钟)。

with SEC_TO_TIME we convert the number of seconds back to a time field.

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

#1