我有一个历史表，其中包含作为bigint的starttime以及获取或摄入的文档
我需要计算每天每小时处理多少文件。我创建了这个，但它不太好用，我不需要运行总数。
SELECT DISTINCT
EXTRACT(hour from TO_TIMESTAMP(starttime)) as hour
,count(*) OVER (ORDER BY EXTRACT(hour from TO_TIMESTAMP(starttime))) as count
FROM repohistory where activitytype like '%ingest%' OR activitytype like '%fetch%' and resultcode = 'OK'
ORDER BY 1;
我该怎么做？
我使用的是Postgres v9.1.13
谢谢，
select date_trunc('hour', to_timestamp(starttime)) as hour, count(*) as total
from repohistory
where
(activitytype like '%ingest%' or activitytype like '%fetch%')
and resultcode = 'ok'
group by 1
order by 1
请注意，您的条件可能是错误的