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

How to fetch data from oracle database in hourly basis

提问by MAS1

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，其中的内容请求来自其他来源。该表中有一列作为时间，用于存储收到请求时的日期和时间（例如 2010/07/10 01:21:43）。现在我想每天每小时从这个表中获取数据。意味着我想要数据库在一天中的每个小时收到的请求数。egfor 1 o'clock to 2 o'clock say count is 50 ..like this..我将在一天结束时运行这个查询。所以我会每小时收到一天组收到的请求。

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 Ponies 回答之外的任何其他方式。

回答by OMG Ponies

Use the TO_CHAR functionto 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')

回答by sh_kamalh

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.

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

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;

并且您将查询表 othertable_count 而不是查询您的原始表。

回答by kavehmb

SELECT To_char(your_column, 'YYYY-MM-DD HH24') AS hourly, 
       Count(*)                              AS numPerHour 
FROM   your_table 
WHERE  your_column > '1-DEC-18' 
       AND your_column < '4-DEC-18' 
GROUP  BY To_char(your_column, 'YYYY-MM-DD HH24') 
ORDER  BY hourly;