使用预言机查询进行操作(乘法、除法等)

Question

我有这些查询（oracle），我需要对结果进行操作，然后应用AVG，所以它将是AVG（（result*7）/40）

我试过把它括在括号等中，但它不起作用。

SELECT
((fn_adjusted_date(CHG_INFRASTRUCTURE_CHANGE.Completed_Date))-(fn_adjusted_date(CHG_INFRASTRUCTURE_CHANGE.Submit_Date)))
FROM CHG_INFRASTRUCTURE_CHANGE
WHERE  fn_adjusted_date(CHG_INFRASTRUCTURE_CHANGE.Completed_Date) 
BETWEEN  '01-01-2015'  AND  '31-01-2015'
AND 
((fn_adjusted_date(CHG_INFRASTRUCTURE_CHANGE.Completed_Date))-(fn_adjusted_date(CHG_INFRASTRUCTURE_CHANGE.Submit_Date))) < (SELECT 
PERCENTILE_DISC(0.95) within group (order by(( fn_adjusted_date(CHG_INFRASTRUCTURE_CHANGE.Completed_Date) )-( fn_adjusted_date(CHG_INFRASTRUCTURE_CHANGE.Submit_Date) )))
FROM CHG_INFRASTRUCTURE_CHANGE WHERE  fn_adjusted_date(CHG_INFRASTRUCTURE_CHANGE.Completed_Date)
BETWEEN  '01-01-2015'  AND  '31-01-2015')

这是我尝试过的

AVG((SELECT
((fn_adjusted_date(CHG_INFRASTRUCTURE_CHANGE.Completed_Date))-(fn_adjusted_date(CHG_INFRASTRUCTURE_CHANGE.Submit_Date)))
FROM CHG_INFRASTRUCTURE_CHANGE
WHERE  fn_adjusted_date(CHG_INFRASTRUCTURE_CHANGE.Completed_Date) 
BETWEEN  '01-01-2015'  AND  '31-01-2015'
AND 
((fn_adjusted_date(CHG_INFRASTRUCTURE_CHANGE.Completed_Date))-(fn_adjusted_date(CHG_INFRASTRUCTURE_CHANGE.Submit_Date))) < (SELECT 
PERCENTILE_DISC(0.95) within group (order by(( fn_adjusted_date(CHG_INFRASTRUCTURE_CHANGE.Completed_Date) )-( fn_adjusted_date(CHG_INFRASTRUCTURE_CHANGE.Submit_Date) )))
FROM CHG_INFRASTRUCTURE_CHANGE WHERE  fn_adjusted_date(CHG_INFRASTRUCTURE_CHANGE.Completed_Date)
BETWEEN  '01-01-2015'  AND  '31-01-2015'))*7)/40)

我得到一个SQLSyntaxErrorException ORA-00900

Answer 1

假设原始查询运行正确，这是包装查询的正确方法 -

select (avg(fn_value)*7)/40 FROM (
SELECT
((fn_adjusted_date(CHG_INFRASTRUCTURE_CHANGE.Completed_Date))-(fn_adjusted_date(CHG_INFRASTRUCTURE_CHANGE.Submit_Date))) as fn_value
FROM CHG_INFRASTRUCTURE_CHANGE
WHERE  fn_adjusted_date(CHG_INFRASTRUCTURE_CHANGE.Completed_Date) 
BETWEEN  '01-01-2015'  AND  '31-01-2015'
AND 
((fn_adjusted_date(CHG_INFRASTRUCTURE_CHANGE.Completed_Date))-(fn_adjusted_date(CHG_INFRASTRUCTURE_CHANGE.Submit_Date))) < (SELECT 
PERCENTILE_DISC(0.95) within group (order by(( fn_adjusted_date(CHG_INFRASTRUCTURE_CHANGE.Completed_Date) )-( fn_adjusted_date(CHG_INFRASTRUCTURE_CHANGE.Submit_Date) )))
FROM CHG_INFRASTRUCTURE_CHANGE WHERE  fn_adjusted_date(CHG_INFRASTRUCTURE_CHANGE.Completed_Date)
BETWEEN  '01-01-2015'  AND  '31-01-2015'))

请注意，我采用了原始查询，

将其括在括号中，并从原始查询中选择结果。其结果可以通过您喜欢的任何数学运算。