如何以小时为单位从oracle数据库中获取数据

i have one table in my database say mytable, which contents request coming from other source. There is one column in this table as Time, which stores date and time(e.g. 2010/07/10 01:21:43) when request was received. Now i want to fetch the data from this table on hourly basis for each day. Means i want count of requests database receive in each hours of a day. e.g.for 1 o'clock to 2 o'clock say count is 50 ..like this.. I will run this query at the end of day. So i will get requests received in a day group by each hour.

我的数据库中有一个表说mytable,其中的内容请求来自其他来源。此表中有一列为Time,它在收到请求时存储日期和时间(例如2010/07/10 01:21:43)。现在我想每小时从这个表中获取数据。意味着我希望数据库在一天中的每个小时内收到数量。例如,从1点钟到2点钟,计数是50 ..像这样..我将在一天结束时运行此查询。所以我会按小时收到一天中收到的请求。

Can anybody help me in this.

任何人都可以帮助我。

I want query which will take less time to fetch the data as my database size is huge.

我想要查询,因为我的数据库大小很大,所以需要更少的时间来获取数据。

Any othre way than OMG Ponies answer.

任何比OMG小马更回答的方式。

2 个解决方案

#1

Use the TO_CHAR function to format the DATETIME column, so you can GROUP BY it for aggregate functions:

使用TO_CHAR函数格式化DATETIME列,因此您可以将GROUP BY用于聚合函数:

  SELECT TO_CHAR(t.time, 'YYYY-MM-DD HH24') AS hourly,
         COUNT(*) AS numPerHour
    FROM YOUR_TABLE t
GROUP BY TO_CHAR(t.time, 'YYYY-MM-DD HH24')

#2

Why don't you create another table that stores the count and the date. Create a database job that will run hourly and put the count and sysdate in the new table. Your code will be just querying the new table.

为什么不创建另一个存储计数和日期的表。创建一个每小时运行一次的数据库作业,并将count和sysdate放在新表中。您的代码将只是查询新表。

create table ohtertable_count (no_of_rows number, time_of_count date);

Your database job, that will run hourly will be something like

您每小时运行的数据库作业将是类似的

insert into othertable_count 
select count(1), sysdate
from othertable;

And you will query the table othertable_count instead of querying your original table.

#1