需要简化SQL查询（在四个位置输入日期）

Question

我创建了一个查询，它计算多个表中多个总和的平均值。这需要每周运行一次，以及代码的制作方式，我现在每次都需要在查询中更改4个日期。我认为这可以更有效地完成，但我不确定如何。

Select ROUND(

(Select sum (calls) 
      FROM (SELECT sum(ski.ANSTIME) AS calls 
      FROM SYNONYMS syn 
      JOIN SKILL ski on (syn.value = ski.split) 
      WHERE syn.ITEM_TYPE = 'split' 
      AND (SELECT (timestamp '1970-01-01 00:00:00 GMT' +numtodsinterval(ski.starttime_utc, 'SECOND')) 
       at time zone 'Europe/Warsaw' 
       FROM dual) >= '17-07-17 00:00:00 EUROPE/WARSAW' -- Date to be altered every week 
      AND (SELECT (timestamp '1970-01-01 00:00:00 GMT' +numtodsinterval(ski.starttime_utc, 'SECOND')) 
       at time zone 'Europe/Warsaw' 
       FROM dual) <= '24-07-17 00:00:00 EUROPE/WARSAW' -- Date to be altered every week 

      UNION ALL 

      SELECT sum(vdn.ANSTIME) AS calls 
      FROM SYNONYMS syn 
      JOIN VDN vdn on (syn.value = vdn.vdn) 
      WHERE syn.ITEM_TYPE = 'vdn' 
      AND (SELECT (timestamp '1970-01-01 00:00:00 GMT' +numtodsinterval(vdn.starttime_utc, 'SECOND')) 
       at time zone 'Europe/Warsaw' 
       FROM dual) >= '17-07-17 00:00:00 EUROPE/WARSAW' -- Date to be altered every week 
      AND (SELECT (timestamp '1970-01-01 00:00:00 GMT' +numtodsinterval(vdn.starttime_utc, 'SECOND')) 
       at time zone 'Europe/Warsaw' 
       FROM dual) <= '24-07-17 00:00:00 EUROPE/WARSAW')) -- Date to be altered every week 

/-- devided by 

      (SELECT sum (calltime) 
      FROM  (SELECT sum(ski.acdcalls) AS calltime 
      FROM SYNONYMS syn 
      JOIN SKILL ski on (syn.value = ski.split) 
      WHERE syn.ITEM_TYPE = 'split' 
      AND (SELECT (timestamp '1970-01-01 00:00:00 GMT' +numtodsinterval(ski.starttime_utc, 'SECOND')) 
       at time zone 'Europe/Warsaw' 
       FROM dual) >= '17-07-17 00:00:00 EUROPE/WARSAW' -- Date to be altered every week 
      AND (SELECT (timestamp '1970-01-01 00:00:00 GMT' +numtodsinterval(ski.starttime_utc, 'SECOND')) 
       at time zone 'Europe/Warsaw' 
       FROM dual) <= '24-07-17 00:00:00 EUROPE/WARSAW' -- Date to be altered every week 

      UNION ALL 

      SELECT sum(vdn.acdcalls) AS calltime 
      FROM SYNONYMS syn 
      JOIN VDN vdn on (syn.value = vdn.vdn) 
      WHERE syn.ITEM_TYPE = 'vdn' 
      AND (SELECT (timestamp '1970-01-01 00:00:00 GMT' +numtodsinterval(vdn.starttime_utc, 'SECOND')) 
       at time zone 'Europe/Warsaw' 
       FROM dual) >= '17-07-17 00:00:00 EUROPE/WARSAW' -- Date to be altered every week 
      AND (SELECT (timestamp '1970-01-01 00:00:00 GMT' +numtodsinterval(vdn.starttime_utc, 'SECOND')) 
       at time zone 'Europe/Warsaw' 
       FROM dual) <= '24-07-17 00:00:00 EUROPE/WARSAW')) -- Date to be altered every week 

,0) AS average 
FROM dual 

Answer 1

如果我理解正确你想生成一些每周汇总，以便代替输入日期，你可以尝试使用trunc(sysdate)的第二次约会和trunc(sysdate - 7)第一。

第二种可能性是创建临时表（或只是with语句），它将保存单个日期并将其加入到您的查询中。相反<= '24-07-17 00:00:00 EUROPE/WARSAW'你将有<= temp_date其中temp_date来自CTE。