需要简化 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_datetemp_date来自 CTE 的位置。