Expanded group by for report development and expanded group by for reports
In practical use, for example, in a data warehouse, multi-dimensional analysis of data is often required, not only the results of standard grouping (equivalent
Group by), you also need subtotal (partial column grouping in simple group by) and aggregate (not grouping) in different dimensions, so that
Provides multi-angle data analysis. For this complex grouping requirement, simple group by is difficult to achieve this purpose. Of course, we can
UNION or union all is used to combine grouping results of different dimensions, but the performance is often poor. In this case, we can use Expansion
Show group by to meet most of the multi-dimensional grouping problems in practical use.
1. extended group by overview extended group by for multi-dimensional data statistics, mainly manifested in:. ROLLUP, CUBE, and grouping sets extend the group by clause to provide a wide range of GROUPING statistics functions. B. three extended GROUPING functions: GROUPING, GROUPING_ID, and GROUP_ID provide auxiliary functions for extended group by, for example, provides functions such as the grouping level for different result rows, distinguishing NULL values, creating meaningful reports, sorting summary results, and filtering result rows. extended group by can be grouped BY repeated columns, grouped BY Composite Columns, partial GROUPING, and connected GROUPING. In addition, grouping sets can accept CUBE and ROLLUP operations as parameters, these functions make the extended group by more powerful.
2. ROLLUP2.1 the assumption that union all implements the ROLLUP function has the following requirements:. calculate the salaries of each position in each department and B. calculate the total salaries of all positions in each department. c. count the total salaries of all positions in all departments. d. the Department name, position name, and accumulated salary value must be displayed.
-- Requirement 1: select d. dname, e. job, sum (e. sal) sum_sal from dept d, emp ewhere d. deptno = e. deptno group by d. dname, e. jobunion all -- requirement 2: select d. dname, null, sum (e. sal) sum_sal from dept d, emp e where d. deptno = e. deptno group by d. dnameunion all -- requirement 3: select null, null, sum (e. sal) sum_sal from dept d, emp ewhere d. deptno = e. deptno
The above Code uses the Execution Plan (set autotrace on) to find that the indexes of the EMP and DEPT tables need to be accessed multiple times. If the structure of the tables is complex in actual use, the performance will be seriously affected.
2.2 ROLLUP groups start from Oracle 8i, and Oracle uses ROLLUP to expand group by. It allows calculation of standard groups and subtotal and total of corresponding dimensions. The ROLLUP syntax structure is as follows: SELECT... GROUP BY ROLLUP, then, the column is progressively reduced from right to left to calculate the subtotal of the higher level until all the columns are selected and the total is calculated. If n columns are specified in ROLLUP, there are n + 1 grouping methods throughout the computing process.
-- Use ROLLUP to implement the requirements in section 2.1 select d. dname, e. job, sum (e. sal) from dept d, emp e where d. deptno = e. deptno group by rollup (d. dname, e. job );
ROLLUP groups have directionality. From the preceding results, we can see that ROLLUP (d. dname, e. job) The grouping process is:. standard GROUP: group by (d. dname, e. job), grouping each position in each department; B. decrease from right to left: group by (d. dname, null), in fact, this null is not necessary, it is only convenient for analysis, this process is the subtotal of the previous level group, that is, for each dname value, calculate the subtotal across all jobs. c. total: equivalent to group by (null, null ). For example, ROLLUP (a, B, c)
Example: implement the following requirements:. calculate the salaries of each standard group (per year), department, and position, and B. calculate the total salary of all positions in the Department for each start time (year). c. calculate the total salary of all positions in all departments for each start time (year). d. the total salary, showing the start time (year), department name, and position name
With t (
Select to_char (e. hiredate, 'yyyy') hireyear, d. dname, e. job, sum (e. sal) sum_sal from emp e, dept d
Where e. deptno = d. deptno group by rollup (to_char (e. hiredate, 'yyyy'), d. dname, e. job ))
Select rownum, t. * from t;
Next, analyze the results of the above Code:
Because the ROLLUP grouping process has directionality, you can change the report results and meaning by changing the order of columns in ROLLUP, if you need to query the standard group, calculate the subtotal of all departments of each job, and the final total, the code is: select e. job, d. dname, sum (e. sal) sum_sal from emp e, dept dwhere e. deptno = d. deptno group by rollup (e. job, d. dname );
Part 1 ROLLUP groups move some columns out of ROLLUP and place them in group by. In this way, the total number is definitely gone, and some subtotal is gone. Requirement: do not need the subtotal of salaries for all positions in all departments for each start time (year), or select to_char (hiredate, 'yyyy'), d. dname, e. job, sum (e. sal) sum_sal from emp e, dept d where e. deptno = d. deptno group by to_char (hiredate, 'yyyy'), d. dname, rollup (e. job); <=> select to_char (hiredate, 'yyyy'), d. dname, e. job, sum (e. sal) sum_sal from emp e, dept dwhere e. deptno = d. deptno group by to_char (hiredate, 'yyyy'), d. dname, e. jobunion allselect null, sum (e. sal) sum _ Sal from emp e, dept dwhere e. deptno = d. deptno group by to_char (hiredate, 'yyyy'), d. dname; Note: By moving hiredate and dname from ROLLUP, you can remove the subtotal and total salaries of all positions in all departments for each start time (year, in the end, only the subtotal of the standard group and all positions of each induction time (year) and department are queried. 2.4 ROLLUP summarizes the standard grouping first. Based on the standard grouping, the system moves the column from right to left and then performs a subtotal with a higher level.
3. CUBECUBE collects statistics on all possible groups in different dimensions to generate cross reports. This requirement is more precise than ROLLUP and includes the statistical results of ROLLUP, in addition, there are other grouping results (subtotal ). 3.1 CUBE grouping CUBE syntax structure: SELECT... group by cube (grouping_column_reference_list) if n columns are specified in the CUBE, there are two grouping modes in the computing process: power (2, n.
Replace section 2.2 with ROLLUP (dname, job) with CUBEselect d. dname, e. job, sum (e. sal) from dept d, emp ewhere d. deptno = e. deptno group by cube (d. dname, e. job); analyze the corresponding grouping level of CUBE (dname, job:
3.2 some CUBE groups are the same as ROLLUP, and some CUBE operations can be performed to remove the total and some unwanted subtotal operations, such as the group bycube (d. dname, e. job) to group by d. dname CUBE (e. the total and group by job are excluded. Select d. dname, e. job, sum (e. sal) sum_sal from dept d, emp e where d. deptno = e. deptno group by d. dname, cube (e. job); <=> select d. dname, e. job, sum (e. sal) sum_sal from dept d, emp ewhere d. deptno = e. deptno group by d. dname, e. jobunion allselect null, null, sum (e. sal) sum_sal from dept d, emp ewhere d. deptno = e. deptno group by d. dname;
3.3 CUBE Summary: Aggregate first, subtotal, and then group by standard
4. grouping sets implements the two multi-dimensional data statistics methods described earlier in subtotal, namely ROLLUP and CUBE. Their output results are produced by the rows of the corresponding GROUPING accompanied by the subtotal rows, they generate standard groups, various subtotal and total, but sometimes we only care about a single column GROUPING to obtain information about subtotal in other dimensions, so we need to use grouping sets to expand the GROUPING, it is provided by Oracle9i. For example, group by grouping sets (a, B, c) are equivalent to group by a, group by B, and GROUP BY c, so as to obtain subtotal information for other dimensions. There are n GROUPING classes for grouping sets in n columns. 4.1 grouping sets grouping syntax structure: SELECT... group by grouping sets (grouping_column_reference_list) Change ROLLUP in section 2.2 to GROUPING SETSselect to_char (e. hiredate, 'yyyy') hireyear, d. dname, e. job, sum (e. sal) sum_sal from emp e, dept dwhere e. deptno = d. deptno group by grouping sets (to_char (e. hiredate, 'yyyy'), d. dname, e. job); note: the results of grouping sets are the results of union all After GROUPING by a single column. The results of grouping sets are irrelevant to the column order, and the results are unordered.
Part 1 grouping sets group select d. dname, to_char (e. hiredate, 'yyyy') hireyear, e. job, sum (e. sal) sum_sal from dept d, emp ewhere d. deptno = e. deptno group by d. dname, grouping sets (to_char (e. hiredate, 'yyyy'), e. job); <=> select d. dname, to_char (e. hiredate, 'yyyy') hiredate, null job, sum (e. sal) sum_sal from dept d, emp e
Where d. deptno = e. deptno group by d. dname, to_char (e. hiredate, 'yyyy') union allselect d. dname, null, e. job, sum (e. sal) sum_sal from dept d, emp e
Where d. deptno = e. deptno group by d. dname, e. job;
The preceding statement calculates the total number of employees in each department for each start time (year) and the total number of employees in each department for each start time (year.
4.3 As the grouping sets parameter, the CUBE and GROUPING operations can accept ROLLUP and CUBE as their parameters. The grouping sets operation groups only single columns, but does not provide the aggregate function, if you need grouping sets to provide the aggregate function, you can use ROLLUP or CUBE as the parameter of grouping sets. Rewrite the previous grouping sets (d. dname, e. job) to provide the aggregate function. Select d. dname, e. job, sum (e. sal) from dept d, emp ewhere d. deptno = e. deptno group by grouping sets (rollup (d. dname), rollup (e. job); <=> select d. dname, null job, sum (e. sal) sum_sal from dept d, emp e where d. deptno = e. deptno group by rollup (d. dname) union allselect null dname, e. job, sum (e. sal) sum_sal from dept d, emp ewhere d. deptno = e. deptno group by rollup (e. job); Note: The preceding statement generates two total rows because ROLLUP or CUBE acts as the GROUPING SE The parameter of TES is equivalent to union all for each ROLLUP or CUBE operation. Duplicate rows can be removed using DISTINCT. grouping sets cannot be accepted by ROLLUP and CUBE as parameters, ROLLUP and CUBE cannot act as parameters for each other.
4.4 grouping sets the result of summarizing grouping sets has no relationship with the column order, and the result order is unordered.
5. Combination column grouping, connection group, repeated column grouping, connection group, and repeated column grouping are all features of Oracle 9i. Composite Columns include multiple columns in parentheses to treat multiple columns as a whole, such as group by rollup (a, B), c) it is equivalent to group by rollup (x, c), where x is equivalent to the combination column (a, B. Composite Columns are generally common in conditions. For example, in -- where in, select empno, ename, job from emp where (empno, ename) in (7369, 'Smith '), (7499, 'allen'); is a comparison between a common column ROLLUP and a combination column ROLLUP (similar to CUBE and grouping sets)
To remove some subtotal, and ensure that the final result is aggregated.
The connected GROUP allows multiple ROLLUP, CUBE, and grouping sets operations after the group by operation, so that the GROUPING level is more and the report is more precise.
In fact, no matter the same type of connected groups or different types of connected groups, the last group-level type is the product of each extended group-level type. The group level is a Cartesian product. For example, for the same type of connection group ROLLUP (a, B) and ROLLUP (c), the final result is 3*2 = 6 grouping levels. for different types of connection groups, ROLLUP (a, B ), grouping sets (c) Have 3*1 = 3 GROUPING levels.
Repeated column groups are columns allowed to be repeated in group by. For example, complex composite column groups in ROLLUP may be used, such as group by rollup (a, (a, B )) group by a, ROLLUP (a, B) all belong to repeated columns.
5.1 composite column grouping A composite column grouping can filter some subtotal or calculate some additional subtotal functions. The previous sections of ROLLUP and some cubes do not have a total. You can use Composite Columns to implement some ROLLUP and some CUBE functions. Requirement:. standard grouping of departments, employment time (year), and positions B. calculate the subtotal for each department based on the onboarding time (year) and Position c. total select d. dname, to_char (e. hiredate, 'yyyy') hireyear, e. job, sum (e. sal) sum_sal from dept d, emp ewhere d. deptno = e. deptno group by rollup (d. dname, (to_char (e. hiredate, 'yyyy'), e. job); both CUBE and ROLLUP operations can be converted into corresponding grouping sets by combining column GROUPING, for example, ROLLUP (a, B, c) the equivalent grouping sets are grouping sets (a, B, c), (a, B), (a), NULL), CUBE (a, B, c) the equivalent grouping sets are grouping sets (a, B, c), (a, B), (a, c), (B, c), (), (B), (c), NULL );
5.2 connection GROUP is only available in Oracle 9i. It allows group by to be followed BY multiple ROLLUP, CUBE, and grouping sets, the GROUPING level of a join group is a Cartesian Product consisting of each ROLLUP, CUBE, and grouping sets group. For example, ROLLUP (a, B) and ROLLUP (c, d, e) have a group statistical level of 3*4 = 12. Select d. dname, e. job, to_char (e. hiredate, 'yyyy') hireyear, sum (sal) sum_sal from dept d, emp ewhere d. deptno = e. deptno group by rollup (d. dname, e. job), rollup (to_char (e. hiredate, 'yyyy'); group by rollup (d. dname, e. job), ROLLUP (to_char (e. hiredate), 'yyyy') implements six grouping results, which is equivalent to the Cartesian product of two ROLLUP, as shown in the following table:
CUBE and grouping sets are similar. Using connected groups, the CUBE can be converted using ROLLUP:. when there is only one column, for example, ROLLUP (a) and CUBE (a) are the same, there are two statistical methods; B. when there are n columns, for example, CUBE (a, B, c) can be converted into join groups of ROLLUP (a), ROLLUP (B), and ROLLUP (c, that is to say, if the CUBE with n columns is converted into ROLLUP, it needs to be split and converted into a single-column ROLLUP connection group. Select d. dname, e. job, to_char (e. hiredate, 'yyyy') hireyear, sum (e. sal) sum_sal from dept d, emp ewhere d. deptno = e. deptno group by rollup (d. dname), rollup (e. job), rollup (to_char (e. hiredate, 'yyyy'); <=> group by cube (d. dname, e. job, to_char (hiredate, 'yyyy'); note: the connection GROUP is generally the same type of connection GROUP. Different connection groups, such as group by rollup... CUBE... is not commonly used unless there are complex requirements.
5.3 duplicate column grouping is also available in Oracle 9i, that is, repeated columns are allowed after group. Select d. dname, e. job, sum (e. sal) sum_sal from dept d, emp ewhere d. deptno = e. deptno group by d. dname, rollup (d. dname, e. job); <=> select d. dname, e. job, sum (e. sal) sum_sal from dept d, emp e where d. deptno = e. deptno group by d. dname, e. jobunion allselect null, null, sum (e. sal) sum_sal from dept d, emp ewhere d. deptno = e. deptno group by d. dnameunion allselect null, null, sum (e. sal) sum_sal from dept d, emp ewhere d. deptno = e. deptno group by d. dname;
5.4 composite column grouping, connection group, repeated column grouping summary. composite Columns mainly remove unnecessary subtotal to retain the total; B. join groups are operated on according to the Cartesian Product Form of each extended group. The grouping type is more detailed. For example, ROLLUP connects to a group and implements functions similar to CUBE.
6. three extended GROUPING functions: GROUPING, GROUPING_ID, and GROUP_ID:. use the GROUPING function to create meaningful reports and filter results. B. use the GROUPING_ID function to filter and sort the results. c. use the GROUP_ID function to remove duplicate rows.
6.1 for the extended group by clause, for example, ROLLUP and CUBE generate a standard GROUP, a series of subtotal and aggregate, so that the column values of some rows in the query results will be NULL. NULL has special significance in extending group by. The column value in the result row is NULL, which generally means the subtotal or total of the column, however, NULL may also be the NULL of the original data (such as emp. mgr = NULL), so the GROUPING function is introduced to handle the NULL problem in the result of the extended group by group:. it only accepts one parameter, which is from columns in ROLLUP, CUBE, and grouping sets; B. the GROUPING function returns 1 to the subtotal or aggregate column; otherwise, 0 is returned. If the value of the subtotal or aggregate column is NULL, but the original data may also be NULL, the GROUPING function is often used to identify that NULL in the final result row exists in the original data, or the value of the subtotal or aggregate column, which is often used with the DECODE function. 6.1.1 format a report and generate a meaningful report select d. dname, e. mgr, sum (e. sal) from dept d, emp e where d. deptno = e. deptno group by rollup (d. dname, e. mgr); <= for each dname, calculate the subtotal across mgr columns. In the above results, the mgr columns of rows 9th and 11th are NULL, and it is impossible to distinguish which column is a subtotal, in this case, you can use the GROUPING function to differentiate. Select d. dname, e. mgr, sum (e. sal), grouping (mgr) from dept d, emp e where d. deptno = e. deptno group by rollup (d. dname, e. mgr); in the above results, the GROUPING (mgr) of the 8th rows is 0, 11th Behavior 1. The mgr columns of all the 8th rows are not subtotal columns, and 11 rows are. The following uses DECODE + GROUPING to create a meaningful report: select decode (grouping (d. dname), 1, 'total _ DEPT ', d. dname) dname, decode (grouping (e. mgr), 1, 'subtotal _ DEPT ', nvl (to_char (e. mgr), 'boss') mgr, sum (e. sal) sum_sal from dept d, emp ewhere d. deptno = e. deptno group by rollup (d. dname, e. mgr); if you want to put the subtotal of all mgr columns behind the display, and the position is between the total, how can this requirement be solved? (For details, see GROUPING_ID.) 6.1.2 to filter some grouping results, GROUPING_ID is generally used to replace the requirement: For group by rollup (d. dname, e. mgr, e. job) results are retained together with the standard group select d. dname, e. mgr, e. job, sum (e. sal) sum_sal from dept d, emp e where d. deptno = e. deptno group by rollup (d. dname, e. mgr, e. job) having grouping (d. dname) = 1 or grouping (e. job) = 0;
6.2 The GROUPING_ID function GROUPING is used to generate meaningful reports and filter group levels. The GROUPING_ID function is mainly used to filter group levels and sort results (display sorting ). No matter whether the results of ROLLUP, CUBE, and grouping sets have the default sequence, they are unreliable. The GROUPING_ID function can accept a pair of parameters. These parameters come from columns in ROLLUP, CUBE, and grouping sets (the parameter source is consistent with the GROUPING function) and are calculated from left to right by column, if this column is a grouping column, it is 0. If it is a subtotal or total of this column, it is 1. Then, the calculation results are made up of binary sequences (bit vectors) in column order ), finally, convert the bitvector to the decimal number. For example, for CUBE (a, B), the result of GROUPING_ID (a, B) is shown in:
The benefit of GROUPING_ID is that you can calculate multiple columns to obtain the grouping level of this column. It can be seen that colum_list in GROUPING_ID (column_list) is consistent with the extended group, so the GROUPING_ID value type must be consistent with the number of corresponding extended groups: for example, CUBE (a, B, c) there are eight GROUPING_ID (a, B, c) values, and four GROUPING_ID (a, B, c) values of ROLL (a, B, c.
GROUPING_ID has the same value range and is related to the number of columns. For example, if n columns exist, the value range of GROUPING_ID is [0-2 ^ n-1] 6.2.1. The GROUPING_ID function needs to filter some grouping results: rewrite the example of GROUPING filtering result in section 6.1 and use GROUPING_ID to implement the same function. Analyze ROLLUP (d. dname, e. mgr, e. job) the result of using the GROUPING_ID function. Note that the GROUPING_ID function is generally used, and the column sequence must be consistent with that in ROLLUP, CUBE, and grouping sets.
From the table, we can clearly see that to achieve this requirement, as long as GROUPING_ID (d. dname, e. mgr, e. job) takes 0 and 7. Select d. dname, e. mgr, e. job, sum (e. sal) sum_sal from dept d, emp e where d. deptno = e. deptno group by rollup (d. dname, e. mgr, e. job) having grouping_id (d. dname, e. mgr, e. job) in (0, 7)
Kingdee K3ERP sales report development: how to count the number of data displayed on the first line in the second line
Set nocount on select case when grouping (v1.Fdate) = 1 THEN 1 ELSE 0 end as Fdate_Grouping,
CONVERT (varchar, v1.Fdate) AS Fdate_NOGrouping,
Case when grouping (v1.Fdate) = 1 THEN 101
ELSE 0 end as FSumSort, SUM (case when t4.FName = '(Henan) Retail 'then IsNull (u1.Fauxqty, 0) ELSE 0 END) AS Fauxqty1,
SUM (case when t4.FName = '(China East) China Resources Vanguard Limited Company 'then IsNull (u1.Fauxqty, 0) ELSE 0 END) AS Fauxqty2,
SUM (case when t4.FName = '(South China) China Resources Vanguard Limited Company 'then IsNull (u1.Fauxqty, 0) ELSE 0 END) AS Fauxqty3,
SUM (IsNull (u1.Fauxqty, 0) AS Fauxqty281, IDENTITY (int, 1, 1) AS column_name into # CrossTab FROM ICStockBill v1 Inner Join ICStockBillEntry u1 on v1.FInterID = u1.FInterID
Inner Join t_Organization t4 on v1.FSupplyID = t4.FItemID
Left outer join t_SubMessage t7 on v1.FSaleStyle = t7.FInterID
Inner Join t_Stock t8 on u1.FDCStockID = t8.FItemID
Left outer join t_Emp t9 on v1.FFManagerID = t9.FItemID
Left outer join t_Emp t10 on v1.FSManagerID = t10.FItemID
Inner Join t_User t11 on v1.FBillerID = t11.FUserID
Inner Join t_ICItem t14 on u1.FItemID = t14.FItemID
Inner Join t_MeasureUnit t17 on u1.FUnitID = t17.FItemID
Left outer join t_User t24 on v1.Fcheckerid = t24.FUserID
Inner Join t_MeasureUnit t30 on t14.FUnitID = t30.FItemID
Left outer join t_SubMessage t40 on v1.FMarketingStyle = t40.FInterID
Left outer join v_ICTransType t70 on u1.FSourceTranType = t70.FID
Left outer join ICVoucherTpl t16 on v1.FPlanVchTplID = t16.FInterID
Left outer join ICVoucherTpl t13 on v1.FActualVchTplID = t13.FInterID
Left out... the remaining full text>
Java help for developing reports
Add column alias;
Select month, category, count ('complaint class') as 'complaint statistics', count ('complaint class') as 'complaint statistics', count ('report class ') as 'reporting statistics', count (total) as total from table;
If the complaint is a sub-table of the business, replace the corresponding count () with (select count () from word table );
