sql之group by分析

group by表示by一定的规则进行分组。 通过一定的规则将一个数据集划分成若干个小的区域，然后针对若干个小区域进行数据处理。
group by只能返回group by的字段与聚合函数的计算结果。
如果在返回集字段中，这些字段要么就要包含在Group By语句的后面，作为分组的依据；要么就要被包含在聚合函数中。顾名思议，聚合表示将多个值汇为一个值以便与group by的字段一一对应。具体分析见示例1.

当理解了group by与聚合函数，就很好理解count（distinct field）是否可用。

示例1 .
FruitName   ProductPlace  Price  Discount
apple            china                 $1.1    0.8
apple            japan                 $2.1    0.9
apple            usa                 $2.5    0.9
orange         china                 $0.8     null
banana       china                 $3.1       null
需求：想知道每个国家有多少种水果。

group by:
 一旦需求提到"每个**",很容易想到group by。按照出产国家(ProductPlace)将数据集进行分组，然后分别按照各个组来统计各自的记录数量。
SELECTCOUNT(*)AS水果种类,ProductPlaceAS出产国 FROMT_TEST_FRUITINFO GROUPBY ProductPlace

返回值分析：
将Group By操作想象成如下的一个过程，首先系统根据SELECT 语句得到一个结果集，如最开始的那个水果、出产国家、单价的一个详细表。
然后根据分组字段，将具有相同分组字段的记录归并成了一条记录。这个时候剩下的那些不存在于Group By语句后面作为分组依据的字段就有可能出现多个值，但是目前一种分组情况只有一条记录，一个数据格是无法放入多个数值的，所以这里就需要通过一定的处理将这些多值的列转化成单值，然后将其放在对应的数据格中，那么完成这个步骤的就是聚合函数。
这就是为什么这些函数叫聚合函数(aggregate functions)了。



示例2：
memberid  externalid
1                    sina1001
1                    qq2002
2                    sina1002
需求：查询externalid没有重复值的memberid.

group by: 如果想判断某个字段是否有重复值，可以先by该字段分组，如果分组中的数据大于一条，那么等于该字段的记录肯定有多条。如果执行group by b.memberid having count(1) >1，对于1用户，很明显count(1)=2，1用户是应该排除在外的。
select * from t_external_member a where a.memberid in (select b.memberid from t_external_member b group by b.memberid having count(1) >1)



示例3：
场景：表中memberid表示用户,geoid表示用户去过的城市，由于一个城市下有多个景点，这样如果用户每去过一个景点写一条记录的话，geoid有重复值的。

需求：统计每一个用户去过的所有城市数量。

设计：先by用户划分各自用户去过景点的小的区域。在小的区域里面count(distinct geoid)就可以消除重复的geoid并使用聚合函数得到一个数值与memberid对应起来。


低效的sql：先利用了group by去重的思路，然后再count。
select a.memberid,count(a.geoid) as want_geo_count from
(
select a.memberid,a.geoid from t_daodao_member_location a  group by a.memberid,a.geoid order by a.memberid
) as a group by a.memberid

memberid, want_geo_count
47    1
52    2
60    3
63    1

上面的sql子查询利用group by以memberid＋geoid分组，这样相同的memberid下的相同geoid会被归为一组。
memberid   geoid
47    55229
52    45963
52    294212
60    60763
60    294217
60    528733
63    60763

外面的查询在基于上面的数据集by memberid二次分组。计算每memberid对应的每个count值。


正常的sql：先by memberid分组，针对每个分组中的数据，肯定可以做count操作返回唯一值与memberid对应，count(field)肯定可以演化为count(distinct field)。这样使用distinct而不是group by去重。
select a.memberid,count(distinct a.geoid) as want_geo_count from t_daodao_member_location a  group by a.memberid order by a.memberid

 

group by与其它关键词的执行顺序：

当一个查询语句同时出现了where,group by,having,order by的时候，执行顺序和编写顺序是：

1.执行where xx对全表数据做筛选，返回第1个结果集。

2.针对第1个结果集使用group by分组，返回第2个结果集。

3.针对第2个结果集中的每1组数据执行select xx，有几组就执行几次，返回第3个结果集。

4.针对第3个结集执行having xx进行筛选，返回第4个结果集。

5.针对第4个结果集排序。

例子：

完成一个复杂的查询语句，需求如下：

按由高到低的顺序显示个人平均分在70分以上的学生姓名和平均分，为了尽可能地提高平均分，在计算平均分前不包括分数在60分以下的成绩，并且也不计算贱人（jr）的成绩。

分析：

1．要求显示学生姓名和平均分

因此确定第1步select s_name,avg(score) from student

2．计算平均分前不包括分数在60分以下的成绩，并且也不计算贱人（jr）的成绩

因此确定第2步 where score>=60 and s_name!=’jr’

3．显示个人平均分

相同名字的学生（同一个学生）考了多门科目 因此按姓名分组

确定第3步 group by s_name

4．显示个人平均分在70分以上

因此确定第4步 having avg(s_score)>=70

5．按由高到低的顺序

因此确定第5步 order by avg(s_score) desc

 

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

 

group by联想

group by 多字段对于数据库的数据源非常方便，下面是网上的一道笔试题，里面有类似group by的需求，只不过此时数据源变成了日志文件，无法再使用sql语句，需要应用程序设计数据结构实现：

1，用户访问网站时，用随机的一个字符串表示该用户，同一个用户访问别的页面也用相同的字符串表示,用户id称为userId。
2，网站的每个页面用英文字符串来表示，称为page_type_id;
3，网站的访问日志的格式是：<userId 空格 page_type_id >
4,访问路径：找出用户访问最多三节访问路径，所谓三节路径是：home--prouduct---prouduct detail;
或者 prouduct--surport --faq  等等。
要求假设已经有了一个这样的一个日志文件，用java写一个程序，找出最多的三节路径。
下面是日志文件的示例：
123 home
234 product
456 product detail
123 product
456 product

123 product detail
......
解决思路：二个hashmap，m1:HashMap<userid,id1_id2_id3>   m2:HashMap<id1_id2_id3, count>

按照group by的思路，m1相当于按照group by userid, id1_id2_id3，m2相当于聚合函数count(id1_id2_id3)的感觉。

现在的问题是如何用应用程序构造id1_id2_id3这样的值，同时如何通过累加实现count的功能。

思路如下：

第一个HashMap<userID,page_type_id>,因为日志是顺序添加的，比如：
userID page_type_id
123    home                1
456    production          2
123    home                3

那么第一个HashMap保存['123','home'］，同理，第三行标示用户‘123‘在相同页面刷新，那么可以跳过（等同于HashMap保存了这个值的话），如果没有遇到相同的话，
比如：
userID page_type_id
123    home                4
123    production          5
这样路径就变成了home-production，Map=['123','home,production'］,如果在第N行，用户123的路径，没有在home-production的话，比如：
userID page_type_id
123    detail              N
那么，这个时候第一个HashMap=['123','home,production,detail'］,第二HashMap保存［‘home,production,detail‘，1］（count=1）.

接下来，又遇到了用户“123”的话，把第一个HashMap['123','home,production,detail'］的值替换掉，即又变成了[‘123’，‘home’]，重复前面的做法，当路径符合三节的时候，把用户123在一个HashMap上面的值取出来，跟第二个 HashMap［‘home,production,detail‘，1］的键(‘home,production,detail‘)比较，如果有的 话，+1，如果没有的话，添加新的key给第二个HashMap。

二个累加器：一个用来计数，判断是否达到三层。另一个取map的key，取到加1，取不到放入,置1. 典型的hashmap的用法。