Oracle example explains grouped data in detail

This article brings you relevant knowledge about Oracle, which mainly organizes issues related to grouped data. Grouping allows data to be divided into logical groups so that each group can be executed Let’s take a look at aggregate calculations. I hope it will be helpful to everyone.

Recommended tutorial: "Oracle Video Tutorial"

Grouping allows data to be divided into logical groups so that each Groups perform aggregate calculations.

1. Create a group

The group is created using the GROUP BY clause in the SELECT statement.

Example:

SELECT vend_id, count(*) as num_prodsfrom productsgroup by vend_id;

Because of the use of GROUP BY, it will not be necessary to specify each grouping to be evaluated and calculated, which is Completed automatically. The GROUP BY clause instructs Oracle to group the data and perform aggregation on each group (rather than on the entire result set).

Before using GROUP BY, here are some important rules you need to know about using it.

• The GROUP BY clause can contain as many columns as needed. It allows nested grouping, giving more fine-grained control over how data is grouped.
• If you have nested groups in the group by clause, the data will be summarized on the last specified group. In other words, when building a grouping, all columns specified will be evaluated together (so data will not be retrieved for each individual column).
• Each column listed in group by must be a retrieved column or a valid expression (not an aggregate function). If an expression is used in select, the same expression must be specified in group by. Aliases cannot be used.
• Except for aggregate calculation statements, each column in the SELECT statement should appear in the GROUP BY clause.
• If the grouping column contains a column with a NULL value, NULL will be returned as a grouping. If there are multiple rows with NULL values, they will all be grouped together.
• The GROUP BY clause must appear after the WHERE clause and before the ORDER BY clause.

2. Filter grouping

where clause is also usually used for row filtering. However, where does not apply here because where can filter specific rows, not group them. In fact, where cannot be applied to grouping.

Oracle provides another clause for this: HAVING. The only difference between where clause and having clause is: where filters rows, while having filters groups.

**Tips: **having supports all where operators

The rules of where and having have the same syntax, only the key subdivisions are different.

Example:

SELECT cust_id, COUNT(*) AS ordersFROM ordersGROUP BY cust_idHAVING COUNT(*) >= 2;

Note: The difference between having and where

Look at the difference between having and where from another angle: where filtering occurs before data, while having filtering occurs after data grouping. This is an important distinction, rows deleted by the where clause are not included in the grouping. This may change the calculated values ​​based on those used in the having clause, which in turn may affect which groups will be filtered.

Example of using where clause and having clause at the same time:

select vend_id, count(*), as num_prodsfrom productswhere prod_price>=10group by vend_idhaving count(*) > 2;

SELECT vend_id, COUNT(*) AS num_prodsFROM productsGROUP BY vend_idHAVING COUNT(*) >= 2;

3. Grouping and sorting

There is a big difference between group by and order by, even though they are usually used to accomplish the same thing.

The following table describes the difference between order by and group by

order by	group by
#Sort the generated output	Group the rows, but the output may not be in grouping order
Any column may be used (even unselected columns)	Only selected columns or expressions may be used, and all selected column expressions will be used
Never required	Required if using columns (or expressions) with aggregate functions

时常，你会发现使用GROUP BY分组的数据的确是以分组顺序输出的。但是并非总是如此，并且实际上SQL规范也并没有如此要求。而且你实际上可能希望它以不同于分组的方式进行排序。你以一种方式对数据进行分组（以获得特定于分组的聚合值），并不意味着你也希望输出以相同的方式进行排序。总是应该还提供一个显式的ORDER BY子句，即使它与GROUP BY子句完全相同。

提示：不要忘记ORDER BY

通常，无论何时使用GROUP BY子句，还应该指定一个ORDER BY子句，这是确保正确地对数据进行排序的唯一方式。永远不要依靠GROUP BY对数据排序。

为了演示同时使用GROUP BY和ORDER BY的情况，让我们看一个示例。下面的SELECT语句类似于之前使用的SELECT语句。它用于检索总价在50以上（含50）的所有订单的订单号和订单总价：

SELECT order_num, SUM(quantity*item_price) AS ordertotalFROM orderitemsGROUP BY order_numHAVING SUM(quantity*item_price) >= 50;

要按订单总价对输出进行排序，只需添加一个ORDER BY子句，如下：

SELECT order_num, SUM(quantity*item_price) AS ordertotalFROM orderitemsGROUP BY order_numHAVING SUM(quantity*item_price) >= 50ORDER BY ordertotal;

4、select子句排序

select子句和它们的顺序

子句	描述	是否必须
select	要返回的列或表达式	Y
from	要从中检索数据的表	Y（在Oracle中是必须的；在大多数其他的DBMS中则不是）
where	行级过滤（分组前过滤）	N
group by	分组规范	仅当按分组计算聚合值时是必须的
having	分组级过滤（分组后过滤）	N
order by	输出的排列顺序	N

推荐教程：《Oracle视频教程》

The above is the detailed content of Oracle example explains grouped data in detail. For more information, please follow other related articles on the PHP Chinese website!