SQL在同一SELECT语句中存在DISTINCT和AVG时的执行顺序

Question

我使用Oracle 11g。这个SQL语句将以什么顺序被"解析"?

假设col2中有许多重复的值:

SELECT DISTINCT col1, AVG(col2)
FROM table1
GROUP BY col1

将它:1. 删除所有重复的col1 - col2数据组合，然后在这个简化的结果集上对col2做一个平均值，OR2. 首先对col2进行汇总平均，然后对这个结果集进行区分?

Answer 1

示例应该是不言自明的:

SQL> create table testDistinct (col1, col2) as(
  2    select 1, 100 from dual union all
  3    select 1, 10  from dual union all
  4    select 1, 10  from dual union all
  5    select 2, 50  from dual union all
  6    select 3, 1   from dual union all
  7    select 3, 100 from dual
  8  );
Table created.
SQL> select col1, avg(col2)
  2  from testDistinct
  3  group by col1;
      COL1  AVG(COL2)
---------- ----------
         1         40
         2         50
         3       50,5
SQL> select DISTINCT col1, avg(col2)
  2  from testDistinct
  3  group by col1;
      COL1  AVG(COL2)
---------- ----------
         1         40
         2         50
         3       50,5

对DISTINCT的结果应用GROUP得到:

SQL> select col1, avg(col2)
  2  from (
  3      select DISTINCT col1, col2
  4      from testDistinct
  5  )
  6  group by col1;
      COL1  AVG(COL2)
---------- ----------
         1         55
         2         50
         3       50,5