在SQL中绝对没有围绕戳的函数?每小时历史数据查询

Question

我需要获得一些特定的小时数据为一个特定的工作日回到10周。我工作的数据库是Oracle。我对时间戳字段提出了以下条件:

TO_CHAR(hy.tstamp,'HH24')='10' 
AND hy.tstamp > sysdate - 70  
AND mod(extract ( day from sysdate-1) - extract ( day from hy.tstamp), 7) =0 

有人告诉我"绝对没有围绕戳的函数"(出于性能原因?)如何在不操作时间戳字段的情况下指定条件?

Answer 1

这听起来像是抱怨性能的人从未听说过基于函数的索引。

Create index char_hy_stamp on my_table(to_char(hy.tstamp,'HH24'));

这应该避免全表扫描，这无疑是您的解释计划的一部分。只要sqlfiddle正常工作，我们就可以从这里开始。

Answer 2

如果连接到可接受范围的表，则可以过滤掉没有函数或函数索引的时间片。您可以像这样动态地创建一个表(注意，创建表时需要用到这些函数，但之后就不需要了):

SELECT
  TRUNC(SYSDATE + 6) - (7 * LEVEL) + INTERVAL '10' HOUR AS StartAt,
  TRUNC(SYSDATE + 6) - (7 * LEVEL) + INTERVAL '11' HOUR AS EndAt
FROM DUAL
CONNECT BY LEVEL <= 10

今天的日期是5/8/2013，这将给你以下内容:

STARTAT             ENDAT
------------------- -------------------
05/07/2013 10:00:00 05/07/2013 11:00:00
04/30/2013 10:00:00 04/30/2013 11:00:00
04/23/2013 10:00:00 04/23/2013 11:00:00
04/16/2013 10:00:00 04/16/2013 11:00:00
04/09/2013 10:00:00 04/09/2013 11:00:00
04/02/2013 10:00:00 04/02/2013 11:00:00
03/26/2013 10:00:00 03/26/2013 11:00:00
03/19/2013 10:00:00 03/19/2013 11:00:00
03/12/2013 10:00:00 03/12/2013 11:00:00
03/05/2013 10:00:00 03/05/2013 11:00:00

现在只需将其连接到您的查询中以获得所需的时间片，并注意您不需要函数:

WITH TimeRanges AS (
  SELECT
    TRUNC(SYSDATE + 6) - (7 * LEVEL) + INTERVAL '10' HOUR AS StartAt,
    TRUNC(SYSDATE + 6) - (7 * LEVEL) + INTERVAL '11' HOUR AS EndAt
  FROM DUAL
  CONNECT BY LEVEL <= 10
)
SELECT
  ... whatever ...
FROM your_table hy
INNER JOIN TimeRanges ON
  hy.tstamp >= TimeRanges.StartAt AND
  hy.tstamp < TimeRanges.EndAt

如果您的数据库人员明白对时间戳函数进行过滤会降低性能(除非，正如Woot4Moo指出的那样，他们实现了函数索引)，他们就会明白用于创建时间片表的函数不会影响更大的查询。