Oracle - SQL，从日期范围内提取时间。

Question

我试图编写一个select语句来返回特定日期范围内特定时间范围内的平均值。例如，我想知道avg(max_percent_util)在过去7天的时间框架内，下午4点到11点，全天和上午8点到下午6点。到目前为止，这就是我所拥有的，我不确定这个查询会在这一点上结束。

select 
sdpt.DOWN_DESC, 
avg(sdpt.max_percent_util) seven_day_prime,
avg(sday.max_percent_util) seven_day,
avg(sdb.max_percent_util) seven_day_business

from 

(select down_desc, hour_stamp, max_percent_util from downstream_hour_facts 
where ((hour_stamp >= sysdate-8 AND to_char(hour_stamp, 'HH24:MI') >= '16:00' AND to_char(hour_stamp, 'HH24:MI') <= '23:00')
or (hour_stamp >= sysdate-7 AND to_char(hour_stamp, 'HH24:MI') >= '16:00' AND to_char(hour_stamp, 'HH24:MI') <= '23:00')
or (hour_stamp >= sysdate-6 AND to_char(hour_stamp, 'HH24:MI') >= '16:00' AND to_char(hour_stamp, 'HH24:MI') <= '23:00')
or (hour_stamp >= sysdate-5 AND to_char(hour_stamp, 'HH24:MI') >= '16:00' AND to_char(hour_stamp, 'HH24:MI') <= '23:00')
or (hour_stamp >= sysdate-4 AND to_char(hour_stamp, 'HH24:MI') >= '16:00' AND to_char(hour_stamp, 'HH24:MI') <= '23:00')
or (hour_stamp >= sysdate-3 AND to_char(hour_stamp, 'HH24:MI') >= '16:00' AND to_char(hour_stamp, 'HH24:MI') <= '23:00')
or (hour_stamp >= sysdate-2 AND to_char(hour_stamp, 'HH24:MI') >= '16:00' AND to_char(hour_stamp, 'HH24:MI') <= '23:00')
or (hour_stamp >= sysdate-1 AND to_char(hour_stamp, 'HH24:MI') >= '16:00' AND to_char(hour_stamp, 'HH24:MI') <= '23:00'))) sdpt,

(select down_desc, hour_stamp, max_percent_util from downstream_hour_facts 
where ((hour_stamp >= sysdate-8) or (hour_stamp >= sysdate-7) or (hour_stamp >= sysdate-6)
or (hour_stamp >= sysdate-5) or (hour_stamp >= sysdate-4) or (hour_stamp >= sysdate-3)
or (hour_stamp >= sysdate-2) or (hour_stamp >= sysdate-1))) sday,

(select down_desc, hour_stamp, max_percent_util from downstream_hour_facts 
where ((hour_stamp >= sysdate-8 AND to_char(hour_stamp, 'HH24:MI') >= '8:00' AND to_char(hour_stamp, 'HH24:MI') <= '18:00')
or (hour_stamp >= sysdate-7 AND to_char(hour_stamp, 'HH24:MI') >= '08:00' AND to_char(hour_stamp, 'HH24:MI') <= '18:00')
or (hour_stamp >= sysdate-6 AND to_char(hour_stamp, 'HH24:MI') >= '08:00' AND to_char(hour_stamp, 'HH24:MI') <= '18:00')
or (hour_stamp >= sysdate-5 AND to_char(hour_stamp, 'HH24:MI') >= '08:00' AND to_char(hour_stamp, 'HH24:MI') <= '18:00')
or (hour_stamp >= sysdate-4 AND to_char(hour_stamp, 'HH24:MI') >= '08:00' AND to_char(hour_stamp, 'HH24:MI') <= '18:00')
or (hour_stamp >= sysdate-3 AND to_char(hour_stamp, 'HH24:MI') >= '08:00' AND to_char(hour_stamp, 'HH24:MI') <= '18:00')
or (hour_stamp >= sysdate-2 AND to_char(hour_stamp, 'HH24:MI') >= '08:00' AND to_char(hour_stamp, 'HH24:MI') <= '18:00')
or (hour_stamp >= sysdate-1 AND to_char(hour_stamp, 'HH24:MI') >= '08:00' AND to_char(hour_stamp, 'HH24:MI') <= '18:00'))) sdb
where sdpt.down_desc = sday.down_desc and sday.down_desc = sdb.down_desc
group by sdpt.DOWN_DESC
order by sdpt.down_desc

我希望重新创建的示例输出：

• 度量:利用数据
• 前24小时: 7.15%
• 7天滚动(全时)：7.12%
• 7天轧制黄金时段(4-12)：7.12%

H 1107天滚动营业时数(9-5)：7.12%<代码>H 211F 212

Answer 1

查询如此缓慢的原因之一是，您不是在露营日期，而是在比较字符串；使用TO_CHAR()。你也做了很多多余的工作。

下面是一个示例，它只解析数据一次(而不是三次)，避免进行字符串比较，因此应该快速地将数据过滤到所需的8天和1天。

WITH
  filtered_data AS
(
  SELECT
    down_desc,
    hour_stamp,
    TRUNC(hour_stamp)                date_stamp,
    hour_stamp - TRUNC(hour_stamp)   time_stamp
  FROM
    downstream_hour_facts
  WHERE
    hour_stamp >= TRUNC(sysdate) - 8
)
SELECT
  down_desc,
  AVG(CASE WHEN date_stamp >= TRUNC(sysdate)
           THEN max_percent ELSE NULL END)          today,
  AVG(CASE WHEN date_stamp <  TRUNC(sysdate)
           THEN max_percent ELSE NULL END)          seven_day_all,
  AVG(CASE WHEN date_stamp <  TRUNC(sysdate)
            AND time_stamp >= TO_TIMESTAMP('16:00', 'HH24:MI')
            AND time_stamp <  TO_TIMESTAMP('23:00', 'HH24:MI')
           THEN max_percent ELSE NULL END)          seven_day_prime,
  AVG(CASE WHEN date_stamp <  TRUNC(sysdate)
            AND time_stamp >= TO_TIMESTAMP('08:00', 'HH24:MI')
            AND time_stamp <  TO_TIMESTAMP('16:00', 'HH24:MI')
           THEN max_percent ELSE NULL END)          seven_day_business
FROM
  filtered_data
GROUP BY
  down_desc
ORDER BY
  down_desc

编辑

我也注意到你用一种“危险的”错误的方式记录数据.

子查询1可以为度量返回9条记录。子查询2可以返回该度量的3条记录。子查询3可以返回该度量的4条记录。

但你只能用公制加入他们。来自SQ1的所有9条记录都匹配来自SQ2的所有3条记录(现在有27条记录)，它们都匹配来自SQ3的所有4条记录(现在有108条记录)。

当连接数据集时，要非常小心，每条记录都连接到需要它的地方，并且只连接到需要它的地方。

(上面的单个解析版本无论如何都应该完全避免这个问题。)