Oracle中的group by到底能干什么

Oracle中的group by到底能干什么

在谈oracle的group by的时候，大多数人的第一印象都是group by不就是用于分组的吗？有什么好说的呢，但是在实际操作中，却经常会出现需要group by但是又不能模范的写出group by，拿到想要的数据的操作。本篇写下自己对于group by的一些见解。
1.对于group by，首先我们什么场景使用？
常见的场景有：
1.分组求聚合，使用聚合函数做一些操作，比如max，min，sum等等
思考下：在使用group by之前，先弄清楚为什么有些字段要放在group by后面，有些不要放，为什么没有group by的字段不能出现在select之后，而如果我想使用应该怎么办？
group by是对数据进行分组返回，返回的select后的字段其实是一组已经分好了组的特定值。
要对哪些数据做分组操作，哪些字段就放在group by后面。
例1：表A，包含字段name，age，求每个年龄有多少人
数据为

name	age
zhangsan	18
lisi	19
wangwu	18

SQL非常简单：
select age,count(1) from A group by age;–正确
select age,count(1),name from A group by age;–错误
为什么select后不可以出现name？因为分组后返回的分组字段是单行记录，age单行代表了属于同一个age的name的一组数据。可以理解为age是一组数据的入口，而同一组age里面的name是这组数据。这是决定了name不能出现在select后的原因，系统没有办法明确到你要出现的每一个name值，因为按照age分组则必然相同的age是定值，name是不定值。那么如果我想要使用name字段怎么办，要么name也分组，要么求age组内的name聚合操作，如最大最小值等。
过程数据为：

age	name
18	zhangsan
18	wangwu
19	lisi

那么经过上面的步骤，下一步的数据会是怎么样的呢？
对age求个数统计，则18的age一组有两个，19的age一组有一个，返回结果：

age	count
18	2
19	1

例2：表A，包含字段money，age，求每个年龄最高的工资
数据为

money	age
1000	18
200	19
3000	18

SQL非常简单：
select age,max(money) money from A group by age;

age	money
18	1000
18	3000
19	200

结果：

age	money
18	3000
19	200

上述数据中对age进行分组，同样的age代表了一组一样的age的money的数据，对age求最大值就是统计在相同的age组内，最大值，从上图可以得知，age18的最大值为3000，age19的最大值为200。
求聚合函数一定要明确一点，聚合函数操作的一定是分组字段所代表的一组的数据，按照多少字段分组，就操作分组字段特定值的一组数据。所以使用group by不是为了分组而分组，而是为了实现自己的需求而分组。将分组字段求不同值，操作其组内数据是分组的意义。
为什么不使用group by就可以操作全表使用聚合函数，比如常见的计数：
select count(1) from tableName ; 在于对全表而言，已经是相当于使用同一个分组字段
而select count(1),name from tableName ;–错误。在于没有对name分组，就无法对name求组间聚合值，系统不明确你的需求。
例3：表A，包含字段money，age 求每个年龄最高的工资
select age,max(money) from A group by age;
字段没有分组但是可以出现的就是对字段分组后，分组字段所包含的组内数据做聚合函数，形成单行记录的分组字段加聚合函数值。
例4：表A，包含字段money，age求每个年龄总工资超过600的工资
数据为

money	age
1000	18
200	19
3000	18

SQL非常简单：
select age from A group by age having sum(money)>=600;
过程数据：

age	money
18	1000
18	3000
19	200

。

age	money
18	4000
19	200

由having筛选掉不满足条件数据
结果

age	money
18	4000

having是对group by字段后的组间数据操作后，进行过滤。只要记得group by后操作的数据范围，然后再进行过滤即可。
那么我们怎么确定使用group by，使用group by后使用什么函数，要不要使用having条件
1.明确拿到的业务需求，即到底是需要什么，业务需求可以转换为什么什么部门，什么什么情况下怎么怎么样的基本可以考虑使用group by
2.业务需求可以转换为什么特定条件下满足多少多少，大于小于多少多少，最大最小这种做聚合的，就可以考虑使用聚合函数
3.需要转换为什么什么部门，并且是什么什么的情况下的可以考虑使用多字段分组。
4.理解group by 可以先从select count(1)/max(字段)/min(字段)/sum(字段开始理解) from tableName；对全表的数据就是一次聚合，再去按照一个字段进行聚合，观察数据的变化，再通过多个字段去聚合，观察数据的变化，再通过对分组后的条件过滤，继续加深理解
5.有些时候要区分掉where和having的区别，对组内函数过滤使用having，否则就用where
2.对数据去重，保证数据的唯一性
group by分组可以保证分组字段值只有一个，多分组字段则多字段相同情况只有一个。
例1：表A，包含字段name，age，求一共有多少种年龄和名称的组合
SQL：
select name,age from A group by name,age;

name	age
zhangsan	18
lisi	19
wangwu	18
zhangsan	19
zhangsan	19

过程数据：

name	age
zhangsan	18
zhangsan	19
zhangsan	19
lisi	19
wangwu	18

每个分组字段和另一个分组字段组成一组唯一值，zhangsan 19出现了两次，则这两个字段这样组合的情况只会出现一次，如果使用聚合函数，则对zhangsan 19这一组分组字段数据内的数据进行处理，如count则包含2个。按照什么字段分组，分组字段组成的值都是唯一值，操作的都是分组字段唯一值的组内数据。可以把多分组字段如name age看做同一个字段来理解多分组字段，因为name和age不同的组成的值也是不同的。
结果：

name	age
zhangsan	18
zhangsan	19
lisi	19
wangwu	18

例2：表A，包含字段name,age求每个age有多少个名字不同的人
数据为：

age	name
18	zhangsan
18	zhangsan
18	wangwu
19	lisi

SQL：
select count(distinct name) from A group by age;
或再分组：select age,count(1) from (select age,name from A group by age,name ) A group by age–含义是先统计age和name的不重复组合，再根据age获取count值，写法自己选择
过程数据：
按照age进行分组，对age代表的一组数据进行去重，然后统计这个组不同的数据有多少

age	name
18	zhangsan
18	wangwu
19	lisi

结果：

age	count
18	2
19	1

例3：当需要和别的表需要关联时，使用join条件不能满足表唯一性数据要求的时候
如：
表A

age	name
18	zhangsan
18	zhangsan
18	wangwu
19	lisi

表B

money	name
18	zhangsan
18	zhangsan
18	wangwu
19	lisi

需要左边为基准，求出左表一条工资数据，不能出现左表一条关联右表多条的情况
select a.*,bb.money from A a join (
select b.name,max(b.money) money from B b group by b.name
) bb on a.name=bb.name;
注意：再使用对表唯一性过滤时，如目前的业务场景，右表只求一条时，要注意分组的特殊情况，是使用单字段分组，如果使用多字段分组，join可能会出现多条数据，使用group by做唯一性数据过滤时，一定要注意与别表的关联关系。
单字段唯一性适用于关联一条数据或单组数据求聚合值
多字段唯一性适用于组合情况，组合数据提取
总结：group by 不是你想by想by就能by，学好分组分了什么，要做什么最重要，先分析业务场景的适用情况，再去动手写sql，不要一次想写好所有的，对sql进行拆分，你的组合会更加简单高效