如何每小时从Oracle数据库获取数据

Question

我在我的数据库中有一张表，叫做mytable，其中包含来自其他来源的请求。这个表中有一个名为Time的列，用于存储请求接收的日期和时间（例如2010/07/10 01:21:43）。现在我想按小时获取每天的数据。也就是说，我想要每个小时内数据库接收到的请求数量。例如，从1点到2点，数量是50，以此类推。我将在一天结束时运行此查询。因此，我将获得按每小时分组的一天内接收到的请求总数。

有人能帮我吗？

我想要一个查询，它可以更快地获取数据，因为我的数据库非常大。

除了OMG Ponies的答案，还有其他方法吗？

- MAS1

---

“Huge”是什么意思？OMG Ponies查询需要多长时间？你需要它花费多长时间？ - Matthew Watson

谢谢大家，我通过对OMG Ponies的回答进行了一些微小的更改得到了我的答案。 - MAS1

Answer 1

使用TO_CHAR函数对DATETIME列进行格式化，以便可以对其进行聚合函数的分组：

  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')

- OMG Ponies

---

@MAS1：抱歉，但我已经重新阅读了您的问题，没有看到需要JOIN的任何暗示。 - OMG Ponies

@MAS1 这不是一个连接(join)，而是使用了表别名。如果你只需要当天的数据，你可以在 FROM 和 GROUP BY 子句之间添加 WHERE t.time BETWEEN TRUNC(sysdate) AND TRUNC(sysdate + 1)；或者你可以将其参数化以使用传递进来的日期。 - Alex Poole

@Alex 我认为你想说的是 (sysdate + 1) 而不是 (sysdate + 10) - Tony Andrews

@MAS1：冒昧地说，如果您的表中的Time字段还没有索引，请尽可能添加一个。如果查询仍然太慢，请考虑为当前日期的数据在表上创建单独的分区。 - user359040

@OMG：WHERE 子句仍然可以使用索引将其限制为单日，并避免获取匹配记录的数据块的需求（尽管我认为我在某个地方读到 count(*) 可能会取消它的一些效果，而 count(1) 可能更高效 - 必须有一天进行测试）。 - Alex Poole

Answer 2

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

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.

- sh_kamalh

---

+1，感谢您的建议。但是我不能创建另一个表。 - MAS1

Answer 3

获取每小时数据使用以下代码：

select 
s.req_submit_date,
EXTRACT(HOUR FROM to_timestamp(s.req_submit_date,'yyyy-mm-dd hh24:mi:ss')) hr ,count(*)
from your_table s
where s.req_submit_date >= to_date('20230414', 'yyyymmdd') 
and s.req_submit_date < to_date('20230416', 'yyyymmdd')
group by s.req_submit_date,EXTRACT(HOUR FROM to_timestamp(s.req_submit_date,'yyyy-mm-dd hh24:mi:ss'))
order by s.req_submit_date,hr;

- Raj Asthana

Answer 4

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;

- kavehmb