代码之家 › 专栏 › 技术社区 › Chuck Burgess

选择空日期表示没有来自Oracle的数据日期?

union join oracle sql

Chuck Burgess · 技术社区 · 14 年前

我有一系列按秒存储的度量。我正试图编制一份报告,以完成以下工作:

按小时计算的平均度量
最大每小时
最小每小时
包括小时的日期/时间没有数据

这个查询几乎产生正确的结果。但是,有数据的日子里,我不希望产生空值的联合结果。我想把它们放下。

SELECT HOUR, METRIC, MINIMUM, MAXIMUM FROM (
select
  to_char(day,'DD-MON-YY "-" HH24":00"') AS HOUR,
  round(avg(duration), 2) AS METRIC,
  round(min(duration), 2) AS MINIMUM,
  round(max(duration), 2) AS MAXIMUM
from log_events
where day > to_date('08-NOV-10', 'DD-MON-YY')
  and day < to_date('08-NOV-10', 'DD-MON-YY') + 2
  and company = 'company A'
  and component = 'api layer'
  and event_label = 'execution request'
  group by to_char(day,'DD-MON-YY "-" HH24":00"')
  UNION
  select to_char(trunc(to_date('08-NOV-10', 'DD-MON-YY'))+(level-1)/24, 'DD-MON-YY "-" HH24":00"') HOUR,
     null as METRIC,
     null as MINIMUM,
     null as MAXIMUM
  from dual connect by level <= 48
)
order by HOUR;

我甚至不确定这是否是查询数据的正确方法。有什么建议吗?

1 回复 | 直到 14 年前

Martin Schapendonk 14 年前

创建一个包含小时的内联表,并将其左键连接到日志事件表:

select
  dummy_hours.hour,
  log_aggregate.METRIC,
  log_aggregate.MINIMUM,
  log_aggregate.MAXIMUM
from (
  select to_char(trunc(to_date('08-NOV-10', 'DD-MON-YY'))+(level-1)/24, 'DD-MON-YY "-" HH24":00"') HOUR
  from dual connect by level <= 48
) dummy_hours, (
  select to_char(day,'DD-MON-YY "-" HH24":00"') AS HOUR,
  round(avg(duration), 2) AS METRIC,
  round(min(duration), 2) AS MINIMUM,
  round(max(duration), 2) AS MAXIMUM
  from log_events
  where day between to_date('08-NOV-10', 'DD-MON-YY') and to_date('08-NOV-10', 'DD-MON-YY') + 2
  and day > to_date('08-NOV-10', 'DD-MON-YY')
  and day < to_date('08-NOV-10', 'DD-MON-YY') + 2
  and company = 'company A'
  and component = 'api layer'
  and event_label = 'execution request'
  group by to_char(day,'DD-MON-YY "-" HH24":00"')
) log_aggregate
where dummy_hours.hour = log_aggregate.hour(+)
order by dummy_hours.hour;

推荐文章

Johnny T · 基于当前值的SQL合并表[重复]

8 月前

Shukurullox Komiljonov · 从记录中获得相互和解。使用SQL

8 月前

John D · 需要为NULL或NOT NULL的WHERE子句

8 月前

ojek · 如何对SQL结果进行分组和编号?

8 月前

senek · 如何在PL/SQL中将选择结果(列)放入数组中

8 月前

Sax · 规范化Google表格(第一步)

8 月前

BarÄ±Å UÅaklÄ± · 如何在ON冲突更新中使用CTE中的值

8 月前

Jatin · 检索卷计数的动态sql抛出错误语法错误[关闭]

8 月前

Andrus · 如何在sql中查找第二个匹配项

8 月前

Nebula Tech · 在SQL Server中搜索字符串中单词的多次出现

8 月前