按小时查询优化小组

问题描述：

请帮助优化我的查询。它看起来太笨重。我想有一个更好的方法在sql中按小时查询优化小组

与时间（日期时间），在那里工作是人口和人口日期时间里面存放auditdate列的表dauditnew。

查询按小时计数返回行数。

SELECT t.Hour, COUNT(t.Hour) as Count FROM dauditnew d, 
    (SELECT 0 as Hour FROM DUAL UNION 
    SELECT 1 FROM DUAL UNION 
    SELECT 2 FROM DUAL UNION 
    SELECT 3 FROM DUAL UNION 
    SELECT 4 FROM DUAL UNION 
    SELECT 5 FROM DUAL UNION 
    SELECT 6 FROM DUAL UNION 
    SELECT 7 FROM DUAL UNION 
    SELECT 8 FROM DUAL UNION 
    SELECT 9 FROM DUAL UNION 
    SELECT 10 FROM DUAL UNION 
    SELECT 11 FROM DUAL UNION 
    SELECT 12 FROM DUAL UNION 
    SELECT 13 FROM DUAL UNION 
    SELECT 14 FROM DUAL UNION 
    SELECT 15 FROM DUAL UNION 
    SELECT 16 FROM DUAL UNION 
    SELECT 17 FROM DUAL UNION 
    SELECT 18 FROM DUAL UNION 
    SELECT 19 FROM DUAL UNION 
    SELECT 20 FROM DUAL UNION 
    SELECT 21 FROM DUAL UNION 
    SELECT 22 FROM DUAL UNION 
    SELECT 23 FROM DUAL) t 
where 
    d.auditdate >= TO_DATE('25.04.2017 ' || t.Hour, 'dd.mm.yyyy HH24') and 
    d.auditdate <= TO_DATE('25.04.2017 ' || t.Hour || '_59_59', 'dd.mm.yyyy HH24_MI_SS') 
group by t.Hour

编辑你的问题，并提供样本数据和预期结果。 –

在这个问题中没有PL/SQL - 它完全是SQL。 – MT0

答

你想开始是这样的：

select trunc(d.auditdate, 'HH24') as hh, count(*) 
from dauditnew d 
where d.auditdate >= '2017-04-25' and d.auditdate < '2017-04-26' 
group by trunc(d.auditdate, 'HH24') 
order by hh;

如果错过时间，那么你可以使用一个left join以此为子查询。

非常感谢 – user149691

按小时查询优化小组

相关推荐