问试图避免使用查询中的聚合进行双重全扫描

EN

CREATE TABLE "*****"."RUN_D" 
(   
"WHSE_CODE" CHAR(12 BYTE), 
"RUN_NUM" NUMBER(*,0), 
"SHEET_NUM" NUMBER(*,0), 
"SHEET_SEQ" NUMBER(*,0), 
"COUNT_DATE" DATE, 
"COUNTED" CHAR(1 BYTE), 
"ITEM_NUM" CHAR(30 BYTE), 
"BIN_CODE" CHAR(10 BYTE), 
"STOCK_UOM" CHAR(10 BYTE), 
"ON_HAND" NUMBER(12,3), 
"SNAP_COST" NUMBER(15,5), 
"COUNTED_OH" NUMBER(12,3), 
"COUNTER_NAME" CHAR(20 BYTE), 
"USER_CODE" CHAR(30 BYTE), 
"COUNT_VARIANCE" CHAR(1 BYTE), 
"ORG_CODE" CHAR(2 BYTE), 
"DIV_CODE" CHAR(8 BYTE), 
"LOT_CODE" CHAR(20 BYTE), 
"UPDATED" CHAR(1 BYTE)
)

SELECT 
  tot.run_num, tot.whse_code, TRUNC(tot.count_date) AS Count_Date,
  CAST((vary.Variance_Count / COUNT(tot.item_num) * 100) AS DECIMAL(10,2)) AS Percentage_Count_Variance,  
  CAST((vary.Variance_Cost / SUM(tot.snap_cost * tot.on_hand) * 100) AS DECIMAL(10,2)) AS Variance_Percentage,  
  CAST(vary.Variance_Cost AS DECIMAL(10,2)) AS Total_Value_Variance
FROM run_d tot
JOIN 
(SELECT run_num, COUNT(item_num) AS Variance_Count, SUM(snap_cost * (counted_oh - on_hand)) AS Variance_Cost
 FROM run_d 
 WHERE counted_oh - on_hand != 0
 GROUP BY run_num) vary ON tot.run_num = vary.run_num 
WHERE TRUNC(tot.count_date) BETWEEN TO_DATE('07/01/2014','MM/DD/YYYY') AND TO_DATE('07/21/2014','MM/DD/YYYY')
GROUP BY tot.run_num, tot.whse_code, vary.Variance_Count, vary.Variance_Cost,   TRUNC(tot.count_date)
ORDER BY tot.whse_code, tot.run_num