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

Question

我正在尝试编写一个选择语句来返回特定日期范围内特定时间范围内的平均值。例如，我想知道过去 7 天下午 4 点至晚上 11 点、全天以及上午 8 点至下午 6 点时间范围内的 avg(max_percent_util)。这是我到目前为止所拥有的，我不确定查询此时是否会结束。

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%
• 7 天滚动营业时间(9-5): 7.12%

Answer 1

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

下面是一个示例，它解析数据一次（而不是三次），避免字符串比较，因此应该快速将数据过滤到您想要的 8 天和一点天。

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 条记录）相匹配。

连接数据集时要非常小心，确保每条记录都连接到需要的位置，并且仅连接到需要的位置。

（无论如何，上面的单一解析版本应该完全避免这个问题。）