Oracle SQL - HAVING和分析函数的执行顺序

分析函数是基于最终的结果集进行开窗的，所以HAVING比分析函数先执行

 ↓ 没有HAVING时，MAX(col3) over()是A2

SQL> WITH subq_a AS
  2   (SELECT 'A' col1, 'A1' col2, 10 col3
  3      FROM dual
  4    UNION ALL
  5    SELECT 'A' col1, 'A1' col2, -5 col3
  6      FROM dual
  7    UNION ALL
  8    SELECT 'A' col1, 'A2' col2, 10 col3
  9      FROM dual
 10    UNION ALL
 11    SELECT 'A' col1, 'A2' col2, -15 col3
 12      FROM dual)
 13  SELECT col1, col2, SUM(col3) s_col3, MAX(col2) over() x_col2
 14    FROM subq_a
 15   GROUP BY col1, col2
 16  /
 
COL1 COL2     S_COL3 X_COL2
---- ---- ---------- ------
A    A1            5 A2
A    A2           -5 A2

 ↓ 有HAVING时，分析函数在HAVING执行过滤后的结果集上开窗计算结果，MAX(col3) over()变成A1

SQL> WITH subq_a AS
  2   (SELECT 'A' col1, 'A1' col2, 10 col3
  3      FROM dual
  4    UNION ALL
  5    SELECT 'A' col1, 'A1' col2, -5 col3
  6      FROM dual
  7    UNION ALL
  8    SELECT 'A' col1, 'A2' col2, 10 col3
  9      FROM dual
 10    UNION ALL
 11    SELECT 'A' col1, 'A2' col2, -15 col3
 12      FROM dual)
 13  SELECT col1, col2, SUM(col3) s_col3, MAX(col2) over() x_col2
 14    FROM subq_a
 15   GROUP BY col1, col2
 16  HAVING SUM (col3) > 0
 17  /
 
COL1 COL2     S_COL3 X_COL2
---- ---- ---------- ------
A    A1            5 A1