1. Understanding of group
Group by is a clause of the SELECT statement. It is used to specify query grouping conditions and group query results. Only one row of records is displayed in the result set for grouping conditions of the same combination. When using group by clauses, You can aggregate data by adding aggregate functions (including count (), sum, max (), min (), and so on. The aggregate function in the group by column is used for each group. Example: Select substr (. hylb_dm, 1, 2), count (*) from dj_zt agroup by substr (. hylb_dm, 207102); --------------------- 01 67903 1795204 15005 592106 1140607 303008 5130809 394010 122911 354812 691613 100314 53715 1147116 4417 13518 67619 5747 '[null] '84 group by is used to specify grouping conditions, is the concept of a mathematical set. For example, if a column is grouped, the set of grouping conditions is 1. If there are two grouping conditions, the set of grouping conditions is 2. Therefore, queries with group by are generally grouped queries, also called combined queries. The number of combined records depends on the number of elements in the combination set (excluding duplicate elements. For example, if the combination condition is a column, the number of records in the query result set should be equal to the number of elements in the set composed of all fields in the column (in a mathematical sense) (the null field is also counted as one ). If there are two Composite Columns, the number of records is equal to the number of actually two Composite Columns. Select xz, DFMC
From ODS. dm_rpt_qyhf
Where xz = '02'
Group by xz, DFMC;
--------------------------
02 'sino-foreign Cooperation'
02 'sino-Foreign Joint Venture'
02 'foreign shares'
02 'foreign invested'
02 'operating in foreign countries, Hong Kong, Macao and Taiwan'
02 'resident representatives of foreign countries, Hong Kong, Macao and Taiwan 'select xz, DFMC
From ODS. dm_rpt_qyhf
Group by xz, DFMC;
--------------------------
01 'others'
01 'domestic company'
01 'status'
01 'joint-stock cooperation'
01 'group'
02 'sino-foreign Cooperation'
02 'sino-Foreign Joint Venture'
02 'foreign shares'
02 'foreign invested'
02 'operating in foreign countries, Hong Kong, Macao and Taiwan'
02 'resident representatives of foreign countries, Hong Kong, Macao and Taiwan'
03 'sole proprietorship'
03 'partnership'
03 'private limited'
03 'private shares'
'[Null] ''[null]' select xz, dfdm, DFMC, count (*)
From ODS. dm_rpt_qyhf
Group by xz, dfdm, DFMC; ---------------------------------------------------- 01 01 ''4
01 02 'group' 4
01 03 'share cooperation '3
01 04 'domestic company '26
01 05 'others' 2
02 01 'sino-Foreign Joint Venture '2
02 02 'sino-foreign Cooperation '4
02 03 'foreign invested '28
02 04 'foreign shares '4
02 06 'operating in foreign countries, Hong Kong, Macao and Taiwan '1
02 07 'resident representatives of foreign countries, Hong Kong, Macao and Taiwan region' 1
03 01 'private limited' 6
03 02 'private shares '4
03 03 'sole proprietorship '1
03 04 'partnership 1'
'[Null] ''[null]'' [null] '43 group by combination columns must be followed by the query select keyword. If the combination conditions are the same, only one query is retained. Therefore, select... the number of columns queried by group by must be the same. To achieve the same purpose, there are two ways: one is to merge the fields to be queried into the combination condition, one is to use an aggregate function on a non-composite condition field. Of course, you can also use an aggregate function on the composite column. There is no other way! If the number of results in each column to be queried is not equal, the result set will encounter an "non-alignment" error. Therefore, it is totally incorrect to place a column that is not a combination condition in the column to be queried without using an aggregate function. Select xzfrom ODS. dm_rpt_qyhfgroup by xz; --------------------- 010203 '[null]' group by creates a separate row for null groups for statistics when performing a combined query. See the preceding SQL statement. Group by automatically groups the composite condition columns (removing duplicate columns). Therefore, more distinct keywords are applied to the composite condition columns. However, it is not redundant to use distinct in columns with non-composite conditions (all with Aggregate functions. Select count (distinct (. qylx_zl) from dj_zt agroup by substr (. qylx_zl, 112); ----------------------- 1 3 105 2 93 6 24 5 3 96 1 67 2 28 2 19 3 2 group by can not only combine columns, you can also combine column expressions. Example: selectcount (. BS) as Hs, B. hyml_dm as hyml_dm, (select hyml_mc from dm_hyml where hyml_dm = B. hyml_dm) as hyml_mcfrom dj_zt a right Outer Join dm_hyml bon substr (. hylb_dm, 1, 2) = B. hyml_dmgroup by B. hyml_dm; --------------------------------- 2071 01 'agriculture, forestry, animal husbandry, and fishery '17952 03' manufacturing '2017 02' mining '2017 04 'electric power, gas and water production and supply '2017 05' construction' 11406 06 'transportation, warehousing and postal services' 3030 07 'information transmission, computer services and software '2017 08 'wholesale and retail '2017 09 'accommodation and catering service' 12 29 10 Financial Industry 3548 11 Real Estate Industry 6916 12 leasing and Business Services Industry 1003 13 scientific research, technical services and geological surveys industry 537 14 water conservancy, environmental and public facilities management industry '2014 15' residents' services and other services '44 16' education '2014 17 'health, social security and social welfare '2014 18' culture, sports and entertainment '2014 19 'Public Management and social organizations '020' international organizations 'can be in select... filter data by group. The keyword for filtering is having. Having is similar to where. Are used to filter the intermediate records of the query. However, each column criterion specified by the having clause must appear in an aggregate function or in a column named by the Group by clause. Unlike where, where filters data before (after query) and having filters data After grouping. Example: selectsubstr (. hylb_dm, 1, 2), count (*), sum (. zczb) from dj_zt agroup by substr (. hylb_dm, 1, 2) having max (Year (. cjrq) <> 2007; ----------------------------- 08 51308 2988475.0376 selectsubstr (. hylb_dm, 1, 2), count (*), sum (. zczb) from dj_zt agroup by substr (. hylb_dm, 1, 2) having max (Year (. cjrq) <> 2007 and count (*)> 2; -------------------------------- 08 51308 2988475.0376 another special example. Let's take a look at the comparison: Select substr (hy_d M, 1, 2), count (hy_dm) from dm_hy group by substr (hy_dm, 1, 2) Order by substr (hy_dm, 1, 2 ); ------------------------------ 01 5302 4403 62004 1405 1506 5807 2108 11709 1010 2111 612 3713 3014 2615 2116 1817 2218 3819 2 select substr (hy_dm, 1, 2), count (hy_dm) from dm_hy group by substr (hy_dm, 1, 2) having count (*)> 100 order by substr (hy_dm, 1, 2); -------------------------- 03 62008 117 from this we can see count (*) is for each group. In addition, data can be filtered and sorted before grouping, for example, select substr (. hy_dm, 1, 2) from dm_hy awhere substr (. hy_dm, 1, 2) Not Like '01' group by substr (. hy_dm, 1, 2) Order by substr (. hy_dm, 1, 2) ASC; ------------- 02030405060708091011121314151617181920
Ii. Advanced group by usage
1. Group by... with rollup rollback statisticsSelect substr (. hylb_dm, 1, 2), count (*) from dj_zt agroup by substr (. hylb_dm, 1, 2) with rollup; ------------------------- '[null] '2017 12784701 207102 67903 1795204 15005 592106 1140607 303008 5130809 394010 122911 354812 691613 100314 53715 1147116 4417 13518 67619 5747' [null] '84
2. Group by... with cubeThis query has the same results for a combination of conditions as a roll-up query, but multiple combination conditions, this statement will produce a match with each combination field using null, create new record rows and make statistics. This function is not commonly used. A combination of conditions: Select substr (. hylb_dm, 1, 2), count (*) from dj_zt awhere. zczb> 100 group by substr (. hylb_dm, 1, 2) with cube; ----------------------------- '[null] '2017 1102601 35002 1803 272104 4705 122806 23507 29208 247709 21210 13511 143012 42013 11614 8615 98816 817 918 6819' [null] '4 combination conditions. For comparison, clear: First: without the with cube condition: Select substr (. hylb_dm, 1, 2), substr (. qylx_zl, 1, 1), count (*) from dj_zt awhere. zczb> 100and. hylb_dm is not nulland. qylx_zl is not nulland substr (. hylb_dm, 1, 2) Not in ('03', '04 ', '05', '06 ', '07', '08 ', '09', '10 ', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20 ') and. qylx_dm = '01' group by substr (. hylb_dm, 1, 2), substr (. qylx_zl, 4101); ------------------------- 01 1 1802 3 1 4 2: add the with cube condition: Select substr (. hylb_dm, 1, 2), substr (. qylx_zl, 1, 1), count (*) from dj_zt awhere. zczb> 100and. hylb_dm is not nulland. qylx_zl is not nulland substr (. hylb_dm, 1, 2) Not in ('03', '04 ', '05', '06 ', '07', '08 ', '09', '10 ', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20 ') and. qylx_dm = '01' group by substr (. hylb_dm, 1, 2), substr (. qylx_zl, 1, 1) with cube; ------------------------- '[null] '1 45' [null] '3 18' [null] '[null] '000000' [null] '000000' [null] '2017 1 6301 3 1802 1 4
Iii. Core PrinciplesOnly by thoroughly understanding the execution process of these statements can we be well aware and clearly understand how to write SQL statements. The following is the execution process of select statements with where and having: 1. Execute where filtering data 2. Execute group by grouping to form an intermediate grouping Table 3. Execute with rollup/cube to generate statistical analysis data records and add them to the intermediate grouping Table 4. Execute having filtering intermediate grouping table 5. sort
