How to use SQL to generate statistical tables with fixed time intervals

Source: http://bbs.chinaunix.net

• Core tips: There is a table incoming_call. to count the number of calls from time A to time B every second of Time X, I only use SQL. Should I write it out? You are welcome to give it a try and you cannot understand what you mean. The table incoming_call is as follows: time action call in 0 .....

There is a table incoming_call. to count the number of calls from time A to time B every second of Time X, I only use SQL. Should I write it out?
You are welcome to give it a try.

I don't understand what you mean.
The table incoming_call is as follows:
Time action
00:01 call in
00:02 call in
00:05 call in
00:08 call in
Call in
Call in
Call in
00:21 call in
00:22 call in
Call in
Call in
......
Assume that you want to display the number of calls every 10 minutes from:
Interval call_times
--- 4
--- 3
--- 4
......
Ask experts how to write an SQL statement.
Select substr (to_char (time, 'mi'), 1, 1) time, sum (count (Action) from incoming_call
Group by substr (to_char (time, 'mi'), 1, 1)
Try
Thank you for your advice.
What if the interval is 3 minutes? What if the time interval is specified?
In addition, the time is not just one hour. Incoming_call is an example.
Welcome to DX for a try
You have found the method:
Suppose T0, T1, interval
Select
T0 + Ceil (time-T0)/interval), count (*)
Where time between T0 and T1
Group by t0 + Ceil (time-T0)/interval)
This is a signal, and a lot of functions are required to fill in the blanks. This is a frequently used SQL statement, hoping to give you some inspiration.

CU is the place where everyone can freely discuss it. Low-level posts are funny, but I also hope that DX users will not simply kill them, so that I will wait for them to fight. I am afraid to post posts.

Completed the function. Assume that the interv interval is measured in minutes:
Select to_date ('& t0', 'yyyy/MM/dd hh: MI: ss') + (A-1)/1440 * & interv,
To_date ('& t0', 'yyyy/MM/dd hh: MI: ss') + A/1440 * & interv, count (*)
From (
Select
Decode (Ceil (time-to_date ('& t0', 'yyyy/MM/dd hh: MI: ss') * 1440/& interv,
Ceil (time-to_date ('& t0', 'yyyy/MM/dd hh: MI: ss') * 1440/& interv)

From incoming_call
Where time between to_date ('& t0', 'yyyy/MM/dd hh: MI: ss ')
And to_date ('& t1', 'yyyy/MM/dd hh: MI: ss ')
)
Group by to_date ('& t0', 'yyyy/MM/dd hh: MI: ss') + (A-1)/1440 * & interv,
To_date ('& t0', 'yyyy/MM/dd hh: MI: ss') + A/1440 * & interv;