Oracle事件计数查询 [英] Oracle Event Count Query

问题描述

我的SAMPLE表具有以下五列:

sample_id (PK) (NUMBER)
sampled_on (DATE)
received_on (DATE)
completed_on (DATE)
authorized_on (DATE)

我想要一个每小时每小时行(受给定的日期范围限制)和五列的查询:

I would like a query with one row per hour (constrained by a given date range) and five columns:

1. 小时YYYY-MM-DD HH24
2. 该小时内采样的样本数
3. 该小时内收到的样本数量
4. 该小时内完成的样本数量
5. 该小时内授权的样品数量

1. The hour YYYY-MM-DD HH24
2. Number of samples sampled during that hour
3. Number of samples received during that hour
4. Number of samples completed during that hour
5. Number of samples authorized during that hour

请提供查询或至少指向正确方向的点.

Please provide a query or at least a point in the right direction.

赏金重新开放:
+300声誉，这是第一个将 Rob van Wijk 的答案(单次访问样本)纳入视图的人，在该视图中，我可以按日期范围有效地进行查询(start_date/end_date或start_date/num_days).

Reopened with bounty:
  +300 reputation for the first person to incorporate Rob van Wijk's answer (single access to sample) into a view where I can efficiently query by date range (start_date/end_date or start_date/num_days).

推荐答案

尝试:

CREATE OR REPLACE VIEW my_view AS
WITH date_bookends AS (
  SELECT LEAST(MIN(t.sampled_on), MIN(t.received_on), MIN(t.completed_on), MIN(t.authorized_on)) 'min_date'
         GREATEST(MAX(t.sampled_on), MAX(t.received_on), MAX(t.completed_on), MAX(t.authorized_on)) 'max_date'
    FROM SAMPLE t),
    all_hours AS (
  SELECT t.min_date + numtodsinterval(LEVEL - 1,'hour') date_by_hour
    FROM date_bookends t
CONNECT BY LEVEL <= ( t.max_date - t.min_date + 1) * 24)
SELECT h.date_by_hour,
       COUNT(CASE WHEN h.hour = TRUNC(s.sampled_on,'hh24') THEN 1 END) sampled#
       COUNT(CASE WHEN h.hour = TRUNC(s.received_on,'hh24') THEN 1 END) received#
       COUNT(CASE WHEN h.hour = TRUNC(s.completed_on,'hh24') THEN 1 END) completed#
       COUNT(CASE WHEN h.hour = TRUNC(s.authorized_on,'hh24') THEN 1 END) authorized#
  FROM all_hours h
CROSS JOIN sample s
  GROUP BY h.hour

不使用子查询分解:

CREATE OR REPLACE VIEW my_view AS
SELECT h.date_by_hour,
       COUNT(CASE WHEN h.hour = TRUNC(s.sampled_on,'hh24') THEN 1 END) sampled#
       COUNT(CASE WHEN h.hour = TRUNC(s.received_on,'hh24') THEN 1 END) received#
       COUNT(CASE WHEN h.hour = TRUNC(s.completed_on,'hh24') THEN 1 END) completed#
       COUNT(CASE WHEN h.hour = TRUNC(s.authorized_on,'hh24') THEN 1 END) authorized#
  FROM (SELECT t.min_date + numtodsinterval(LEVEL - 1,'hour') date_by_hour
         FROM (SELECT LEAST(MIN(t.sampled_on), MIN(t.received_on), MIN(t.completed_on), MIN(t.authorized_on)) 'min_date'
                            GREATEST(MAX(t.sampled_on), MAX(t.received_on), MAX(t.completed_on), MAX(t.authorized_on)) 'max_date'
                       FROM SAMPLE t) t
CONNECT BY LEVEL <= ( t.max_date - t.min_date + 1) * 24) h
CROSS JOIN sample s
GROUP BY h.hour

查询两次访问SAMPLES表-第一次获得最早的&构造date_by_hour值的最晚日期.

The query accesses the SAMPLES table twice - the first time to get the earliest & latest date to frame the construction of the date_by_hour value.

这篇关于Oracle事件计数查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！