3- 分组操作

 

1．聚集函数 (Aggregate Functions)语法：

    aggregate_function([DISTINCT | ALL] expression)

 

2． NULL和聚集函数：

 

A． COUNT(Column_Name) ignores NULLs, whereas COUNT(*) doesn't.The reason COUNT(*)         doesn't ignore NULLs is because it counts rows, not COLUMN values. The concept of     NULL doesn't apply to a row as a whole.   

 

B． GROUPING also doesn't ignore Nulls, All other aggregate functions ignore Nulls.

   

C． SELECT COUNT(*), SUM(SALE_PRICE), AVG(SALE_PRICE)

 　　 FROM CUST_ORDER;

         COUNT(*)       SUM(SALE_PRICE) AVG(SALE_PRICE)

        --------------- --------------- ---------------

             20             788      56.2857143

 Note that AVG(SALE_PRICE) is not equal to SUM(SALE_PRICE) / COUNT(*). If it were,       the result of AVG(SALE_PRICE) would have been 788 / 20 = 39.4. But, since the AVG        function ignores NULLS, it divides the total sale price by 14, and not by 20 (788      / 14 = 56.2857143).

 

There may be situations where we want an average to be taken over all the rows in       a table, not just the rows with non-NULL values for the column in question. In these        situations we have to use the NVL function within the AVG function call to assign 0 (or some other useful value) to the column in place of any NULL values.

        SELECT AVG(NVL(SALE_PRICE,0)) FROM CUST_ORDER;

 

3．使用 Distinct和All：

An important thing to note here is that ALL doesn't cause an aggregate function to consider NULL values.( 使用All不会使聚集函数在执行时将空值包含在结果集中，只能使用NVL函数间接将空值转换成某个固定值，以使其能被包含在结果集中。)

 

the aggregate functions that take more than one argument as input don't allow the use of DISTINCT. These include CORR, COVAR_POP, COVAR_SAMP, and all the linear regression functions.

 

In addition, some functions that take only one argument as input don't allow the use of DISTINCT. This category includes STTDEV_POP, STDDEV_SAMP, VAR_POP, VAR_SAMP, and GROUPING

 

4． Group by 子句：

If we have a mix of aggregate and nonaggregate expressions in the SELECT list, SQL expects that we are trying to perform a GROUP BY operation, and we must also specify all nonaggregate expressions in the GROUP BY clause. (如果我们在 Select列表中包含了聚集表达式、非聚集表达式的话，SQL会认为我们试图执行一个Group by操作，所以我们必须在Group by子句中明确地指出所有的非聚集表达式)

 

Syntactically, it is not mandatory to include all the expressions of the GROUP BY clause in the SELECT list(语法上来说，并没有强制要求所有出现在 Group by子句中的表达式必须也出现在Select的列表中)

 

注： A.所有出现在Select列表中的非聚集表达式必须出现在Group by子句中。

    B.所有出现在 Group by子句中的表达式不一定必须出现在Select子句中。

 

例： Wrong： SELECT CUST_ID,CUST_NBR,COUNT(ORDER_NB)

                             FROM CUST_ORDER

                          GROUP BY CUST_ID
     
       Correct： SELECT CUST_ID,CUST_NBR,COUNT(ORDER_NB)

                   FROM CUST_ORDER

                GROUP BY CUST_ID,CUST_NBR

        Correct Also： SELECT COUNT(ORDER_NB)

                             FROM CUST_ORDER

                          GROUP BY CUST_ID  

 

 

If we have a constant in our SELECT list, we don't need to include it in the GROUP BY clause. However, including the constant in the GROUP BY clause doesn't alter the result. Therefore, both the following statements will produce the same output:

 

SELECT 'CUSTOMER', CUST_NBR, COUNT(ORDER_NBR)

 FROM CUST_ORDER

GROUP BY CUST_NBR;

 

SELECT 'CUSTOMER', CUST_NBR, COUNT(ORDER_NBR)

 FROM CUST_ORDER

GROUP BY 'CUSTOMER', CUST_NBR;

 

Finally, we can't use a group function (aggregate function) in the GROUP BY clause

 

When we GROUP BY a column that contains NULL values for some rows, all the rows with NULL values are placed into a single group and presented as one summary row in the output. Since the GROUP BY clause inherently performs an ORDER BY on the group by columns, the row containing the NULL value is put at the end. If we want this row to be the first row in the output, we can perform an ORDER BY on SALE_PRICE in descending order

 

While producing summary results using the GROUP BY clause, we can filter records from the table based on a WHERE clause, as in the following example, which produces a count of orders in which the sale price exceeds $25.00 for each customer:

 

SELECT CUST_NBR, COUNT(ORDER_NBR)

 FROM CUST_ORDER

WHERE SALE_PRICE > 25

GROUP BY CUST_NBR;

 

 CUST_NBR COUNT(ORDER_NBR)

---------- ----------------

    231                4

    244                2

    264                2

    288                2

 

While executing a SQL statement with a WHERE clause and a GROUP BY clause, Oracle first applies the WHERE clause and filters out the rows that don't satisfy the WHERE condition. The rows that satisfy the WHERE clause are then grouped using the GROUP BY clause.

 

The SQL syntax requires that the WHERE clause must come before the GROUP BY clause.

 

5． Having字句：

The HAVING clause is used to put a filter on the groups created by the GROUP BY clause.

例： SELECT CUST_NBR, COUNT(ORDER_NBR)

            FROM CUST_ORDER

        GROUP BY CUST_NBR

        HAVING CUST_NBR < 260;

   

    CUST_NBR   COUNT(ORDER_NBR)

    ---------- ----------------

        201                2

        231                6

         244                2

The previous example is a poor use of the HAVING clause, because that clause only references unsummarized data. It's more efficient to use WHERE CUST_NBR < 260 instead of HAVING CUST_NBR < 260, because the WHERE clause eliminates rows prior to summarization, whereas HAVING eliminates groups post-summarization.

 

例： SELECT CUST_NBR, COUNT(ORDER_NBR)

   　　 FROM CUST_ORDER

　   GROUP BY CUST_NBR

       HAVING COUNT(ORDER_NBR) > 2;

 

    CUST_NBR COUNT(ORDER_NBR)

   ---------- ----------------

       231                6

       255                6

 

This is an appropriate use for HAVING, because the results of the aggregate function cannot be determined until after the grouping takes place.

 

注： Having字句即可以用非聚集表达式作过滤条件，也可以用聚集表达式作过滤条件。

 

    A.当用非聚集表达式作过滤条件时，其结果等同于使用 Where字句，而且由于Where字句是在

　　分组前进行过滤，其效率比起分组后再进行过滤的效率更高。

 

    B.Having字句适用于对聚集表达式进行过滤的情况，因为此时必须依靠分组的结果进行过滤，　　  

       而 Where字句无法进行统计。

 

    C.Having字句的语法格式和 Where字句的语法格式很相似，但是对Having字句的语法有一个特  殊的限制： 
        Having字句的过滤条件必须引用自Select列表或者Group by字句

 

    D.Having字句和 Group by字句的先后顺序并不重要，我们可以将Having字句放在Group by字　句之后，也可以
        放在Group by字句之前

 

    E.Where字句和 Having字句可以同时出现在查询语句中，当我们这样做时，首先Where字句被首先执行，所有不
       符合Where条件的记录将被过滤而不会出现在Group by的分组中，Group by    字句对已过滤的记录进行
      分组统计，最后Having字句对分组后的结果进行过滤