Order_by_, Group_by_, having the usage difference

Written on the 2012-11-20 22:14 doc document.

Having

This is used in the usage of aggregate functions. When we use aggregate functions, we usually use Group by to group by first, and then perform the operation of the aggregate function. After the operation of the use of having the use of, is to judge, for example, to determine whether the value of the aggregate function is greater than a certain value and so on.

Select Customer_name,sum (Balance)

From balance

GROUP BY Customer_name

Having balance>200; Yc_rpt_getnew

Order by , Group by , having The usage difference

Order by is understood in English as a sort of row, by default ascending. The order by must be followed by a list of sorted field names, which can be multiple field names.

Group By is understood in English as a grouping. You must have an aggregate function to work with, and you need at least one group flag field to use.

What is an "aggregate function"?
such as SUM (), COUNT (), AVG () are all "aggregate functions"
The purpose of using group by IS to summarize the data.

General such as:
Select Unit name, COUNT (employee ID), SUM (employee's salary) Form [a table]
Group BY unit name
The result of this operation is to use "unit name" as the classification of the number of units and wages of the staff count.

In the order in which the SQL command format is used, group by precedes order by.

The standard format for the Select command is as follows:

SELECT select_list
[Into new_table]
From Table_source
[WHERE search_condition]
[GROUP by Group_by_expression]
[Having search_condition]

1. Group BY is a group query, and the general group by IS used in conjunction with aggregate functions

Group by has a principle that all columns following the Select Do not have columns that use aggregate functions and must appear behind group by (important)

For example, the following database tables are available:

A B
1 ABC
1 BCD

1 ASDFG

If you have the following query statement (the statement is wrong, the reason is the preceding principle)

Select A b from table group by A

The intent of the query statement is to get the following results (of course, just wishful)

A B
Abc
1 BCD

Asdfg

On the right 3 how to become one, so you need to use the aggregate function, as follows (the following is the correct way):

Select A,count (B) as Quantity from table group by A
So the result is
A Quantity
1 3

2. Having

The purpose of the WHERE clause is to remove rows that do not conform to the where condition before grouping the results of the query, that is, to filter the data before grouping, the condition cannot contain a clustering function, and the Where condition is used to display a particular row.

The HAVING clause is used to filter the groups that meet the criteria, that is, to filter the data after grouping, often including clustering functions, to display specific groups using the having condition, or to group by using multiple grouping criteria.

The HAVING clause is restricted to columns and aggregate expressions that are already defined in the SELECT statement. Typically, you need to reference an aggregate value by repeating an aggregate function expression in the HAVING clause, as you did in the SELECT statement. For example:

SELECT A count (b) from TABLE GROUP by ahaving COUNT (b) >2

Usage of Grouping:

Indicates whether to aggregate the specified expression in the group by list. In the result set, if Grouping returns 1, represents the aggregation, or if Grouping returns 0, represents non-aggregation. If group BY is specified, it can only be used in Select, have, Order by.

Comments:

GROUPING is used to differentiate between standard null values and Null values returned by rollup, CUBE, or groupingsets. A special application that returns NULL as the result of a ROLLUP, CUBE, or GROUPING sets operation. It acts as a placeholder for the column within the result set, representing the whole.

Example:

CREATE TABLE TT (Origin char (8), fruit char (8), weight INT)

INSERT tt VALUES (' North ', ' Banana ', 3)

INSERT tt VALUES (' North ', ' Peach ', 2)

INSERT tt VALUES (' Southern ', ' Orange ', 3)

INSERT tt VALUES (' North ', ' Peach ', 5)

INSERT tt VALUES (' South ', ' Banana ', 3)

INSERT tt VALUES (' Southern ', ' Peach ', 6)

INSERT tt VALUES (' North ', ' Orange ', 8)

Select

Case when (GROUPING (origin) = 1) Then ' total '

ELSE ISNULL (Origin, ' UNKNOWN ')

END as Origin,

Case when (GROUPING (fruit) = 1) Then ' subtotal '

ELSE ISNULL (fruit, ' UNKNOWN ')

END as Origin,

SUM (weight) total weight

From TT

GROUP by origin, fruit

With ROLLUP

Results:

/************************

Northern Orange 8

Northern Peach 7

North Banana 3

North Subtotal 18

Southern Orange 3

Southern Peach 6

South Banana 3

Southern Subtotal 12

Total Subtotal 30

*************************/

GROUPING (field) =1 is the corresponding field summary.
GROUPING (field) =0 the original details of the corresponding field

Oracle Rollup and Cube usage

Oracle's GROUP BY statement supports rollup and cube statements in addition to the most basic syntax. In the case of rollup (a, B, c), group by IS first performed on (A, B, c), then group by (A, B), then (a) is GroupBy, and the group by operation is finally performed on the whole table. In the case of group by CUBE (A, B, c), the group by IS first performed (A, B, C), followed by (A, B), (A, C), (a), (b, C), (b), (c), and finally the group by operation of the whole table. GROUPING_ID () can beautify the effect:

Oracle's GROUP BY statement supports rollup and cube statements in addition to the most basic syntax.
In addition to the contents of this article, you can also refer to:
Analysis Function Reference manual: http://xsb.itpub.net/post/419/33028
Examples of analysis functions use: http://xsb.itpub.net/post/419/44634

Sql> CREATE TABLE T as select * from Dba_indexes; The table is created.
Sql> Select Index_type, Status, COUNT (*) from the T Group by Index_type, status;

Index_type STATUS COUNT (*)
--------------------------- -------- ----------
LOB VALID 51
NORMAL N/A 25
NORMAL VALID 479
CLUSTER VALID 11

Here's a look at the execution results of the ROLLUP and cube statements.
Sql> Select Index_type, Status, COUNT (*) from the T Group by Rollup (Index_type,status);

Index_type STATUS COUNT (*)
--------------------------- -------- ----------
LOB VALID 51
LOB 51
NORMAL N/A 25
NORMAL VALID 479
NORMAL 504
CLUSTER VALID 11
CLUSTER 11
566
8 rows have been selected.
Sql> Select Index_type, Status, COUNT (*) from the T Group by Cube (Index_type,status);
Index_type STATUS COUNT (*)
--------------------------- -------- ----------
566
N/A 25
VALID 541
LOB 51
LOB VALID 51
NORMAL 504
NORMAL N/A 25
NORMAL VALID 479
CLUSTER 11
CLUSTER VALID 11

10 rows have been selected.
The query results are not very straightforward, and the query results are collated by the function grouping provided by Oracle.
Sql> Select Grouping (index_type) G_ind, grouping (status) G_st, Index_type,status, COUNT (*)
2 from T GROUP by Rollup (Index_type, status) Order by 1, 2;
G_ind g_st index_type STATUS COUNT (*)
---------- ---------- --------------------------- -------- ----------
0 0 LOB VALID 51
0 0 NORMAL N/a 25
0 0 NORMAL VALID 479
0 0 CLUSTER VALID 11
0 1 LOB 51
0 1 NORMAL 504
0 1 CLUSTER 11
1 1 566

8 rows have been selected.
This query result is much more intuitive, compared to groupby without the rollup statement, Rollup adds a group by statistic for Index_type and a group by statistic for all records.
That is, in the case of rollup (a, B, c), group by IS performed first for (A, B, c), then group by (A, B), then (a) group by, and then the group by operation for the whole table.

Here's a look at the cube statement.

Sql> Select Grouping (index_type) G_ind, grouping (status) G_st, Index_type,status, COUNT (*)
2 from the T Group by Cube (Index_type, status) Order by 1, 2;

G_ind g_st index_type STATUS COUNT (*)
---------- ---------- --------------------------- -------- ----------
0 0 LOB VALID 51
0 0 NORMAL N/a 25
0 0 NORMAL VALID 479
0 0 CLUSTER VALID 11
0 1 LOB 51
0 1 NORMAL 504
0 1 CLUSTER 11
1 0 N/a 25
1 0 VALID 541
1 1 566
10 rows have been selected.
Compared to rollup, Cube adds a group by statistic to the status column.
In the case of group by CUBE (A, B, c), the group by IS first performed (A, B, C), followed by (A, B), (A, C), (a), (b, C), (b), (c), and finally the group by operation of the whole table.
In addition to using the grouping function, you can also use grouping_id to identify group by results.

Sql> Select grouping_id (index_type, status) G_ind, Index_type, Status,count (*)
2 from T GROUP by Rollup (Index_type, status) order by 1;

G_ind index_type STATUS COUNT (*)
---------- --------------------------- -------- ----------
0 LOB VALID 51
0 NORMAL N/a 25
0 NORMAL VALID 479
0 CLUSTER VALID 11
1 LOB 51
1 NORMAL 504
1 CLUSTER 11
3 566

8 rows have been selected.

Sql> Select grouping_id (index_type, status) G_ind, Index_type, Status,count (*)
2 from the T Group by Cube (Index_type, status) order by 1;

G_ind index_type STATUS COUNT (*)
---------- --------------------------- -------- ----------
0 LOB VALID 51
0 NORMAL N/a 25
0 NORMAL VALID 479
0 CLUSTER VALID 11
1 LOB 51
1 NORMAL 504
1 CLUSTER 11
2 N/a 25
2 VALID 541
3 566
10 rows have been selected.
GROUPING_ID () can beautify the effect:
Select DECODE (grouping_id (C1), 1, ' total ', C1) D1,
DECODE (grouping_id (C1, C2), 1, ' Subtotal ', C2) D2,
DECODE (grouping_id (C1, C2, C1 + C2), 1, ' subtotal ', C1 + C2) D3,
Count (*),
GROUPING_ID (C1, C2, C1 + C2, C1 + 1, C2 + 1),
GROUPING_ID (C1)
From T2
GROUP BY rollup (C1, C2, C1 + C2, C1 + 1, C2 + 1);
===========================================================
1. Rollup function dedicated to report totals
Sales Reports
Guangzhou January 2000 Yuan
Guangzhou February 2500 yuan
Guangzhou 4500 Yuan
Shenzhen January 1000 yuan
Shenzhen February 2000 Yuan
Shenzhen 3000 Yuan
7500 USD in all regions
Previous query sql:
Select area,month,sum from Saleorder GROUP by Area,month
Then the total of Guangzhou, Shenzhen and all regions will need to accumulate in the program itself
1. You can actually use the following sql:
Select Area,month,sum (Total_sale) from Saleorder GROUP by rollup (Area,month)
To produce the exact same records as the report.
2. If year does not want to accumulate, can write
Select Year,month,area,sum (Total_sale) from Saleorder Group by Year,rollup (Month,area)
In addition, Oracle 9i supports the following syntax:
Select Year,month,area,sum (Total_sale) from Saleorder Group Byrollup ((year,month), area)
3. If you use Cube (area,month) instead of Rollup (area,month), you will get totals for each month in addition to the totals for each region, which is displayed at the end of the report.
4.Grouping make the total column read better
Rollup when displaying Guangzhou total, the month is listed as NULL, but a better practice should be shown as "all months" grouping is used to determine whether the current column is a total column, 1 is yes, and then use decode to convert it to "all months"
Select Decode (Grouping), 1, ' All regions ', area) Area,decode (Grouping (month), 1, ' All months ', month), SUM (money) Fromsaleorder Group by RollUp (Area,month);
2. Start With.....connect by for multi-level queries
such as personnel organization, product category, Oracle provides a very classical method
SELECT level, name, emp_id,manager_emp_id from employee START withmanager_emp_id are null CONNECT by PRIOR emp_id = Manager _emp_id;
The above statement shows the entire application, start with indicates where to start traversing the tree, if starting from the root, then its manager should be null, if starting from an employee, can be written as emp_id= ' 11 '
CONNECT by is a parent-child relationship, note the prior location
There's also a level column that shows the hierarchy of nodes
3. More Report/Analysis decision function
3.1 Basic structure of the analysis function
Analysis function () over (partion clause, ORDER BY clause, window clause)
It is difficult to speak clearly on the concept, or it is better to speak in an example.
3.2 Row_number and Rank, Dense_rank
Used to select a report such as top 3 sales
When two salesmen may have the same performance, use Rank and Dense_rank
Like what
Amount RowNum Rank Dense_rank
Zhang 34,000 Yuan 1 1 1
Li 43,000 RMB 2 2 2
Money 52,000 RMB 3 3 3
Sun 62,000 RMB 4 3 3
Ding 71,000 RMB 5 5 4
At this time, should be tied to the third of the money five and Magoroku are selected in, so with ranking function than RowNumber insurance. As for desnse or ranking to see the specific situation.
SELECT salesperson_id, SUM (tot_sales) Sp_sales, RANK () Over (ORDER bysum (tot_sales) DESC) Sales_rank from Orders GROUP by salesperson_id
3.3 NTILE The record into a B-N-D
For example, I want to get the top 25% record, or 25% of the record as a level equal treatment, the other 25% as another level equal treatment
SELECT Cust_nbr, SUM (tot_sales) Cust_sales, NTILE (4) Over (ORDER bysum (tot_sales) DESC) sales_quartile from orders GROUP B Y CUST_NBR ORDER by 3,2desc;
Ntitle (4) divides the record into 4 parts in SUM (Tot_sales).
3.4 Auxiliary Analysis columns and Windows Function
The report, in addition to the basic factual data, is expected to be next to more than the total annual sales, so far the cumulative sales, the average sales of three months before and after the column reference.
This three-month average and the cumulative sales so far are called Windows function, see the following example
SELECT month, sum (tot_sales) monthly_sales, sum (SUM (tot_sales)) over (ORDER bymonth ROWS between unbounded preceding and C urrent ROW) max_preceeding fromorders GROUP by month ORDER by month;
SELECT month, sum (tot_sales) monthly_sales, AVG (SUM (Tot_sales)) over (ORDER bymonth ROWS between 1 preceding and 1 followi NG) Rolling_avg from orders GROUPBY month ORDER by month;
The key to Windows function is a few values of Windows clauses
1 a record before preceding
1 A record after following
All records prior to unbounded preceding
Current ROW Record
4.SubQuery Summary
Subquery used every day, theoretically summed up. Subquery three kinds of
The most common style for 1.Noncorrelated subqueries.
2.Correlated subqueries Pull the parent query column into the subquery, the first time Cyt taught me to understand the half-day.
3.Inline View is also used as the most common style.
Then there are three more cases of noncorrelated subqueries.
1. Returns a row of a column where Price < (select Max from goods)
2. Return multiple rows A column where Price>= all (select Price from Goodswhere type=2)
or where not price< a (select price from goods where type=2)
The most commonly used in is actually =any ()
3. Return multiple rows Multiple columns return multiple columns at one time saving query time
UPDATE monthly_orders SET (tot_orders, Max_order_amt) = (SELECT COUNT (*), Max (Sale_price) from Cust_order) DELETE from line _item WHERE (ORDER_NBR,PART_NBR) in (SELECT Order_nbr, PART_NBR from Cust_order c)
========================================
/*--------Understand grouping sets
Select a, B, C, sum (d) from t
Group BY grouping sets (A, B, c)
is equivalent to
SELECT * FROM (
Select a, NULL, NULL, SUM (d) from the T Group by a
UNION ALL
Select null, b, NULL, SUM (d) from the T Group by B
UNION ALL
Select null, NULL, C, sum (d) from the T Group by C
)
*/

Order_by_, Group_by_, have usage differences