大数据面试题-group by | order by| distribute by| sort by| cluser by | partition by 的区别

这个题目在大数据的面试中比较常见，属于所谓的八股文，考察面试者的基本功。

1、order by  和 group by

在普通的sql中比较常见，也比较好理解

1、order by : 排序，属于全局排序。

示例SQL:

select * from student order by age;    查找所有学生，并按照年龄正序排序。

 

2、goup by : 分区

示例SQL:

select * from student group by gender; 查找所有学生，并按照性别分组。

2、distribute by 、sort by 、cluster by 、partition by

在普通的数据库中很少看到，一般在大数据类的数据库中看到，比如hive，以hive SQL为例进行说明

1、distribute by   分组

和 group by 不同的是，distribute by 经常在开窗函数中使用。

所谓开窗函数就是：明细数据和聚合函数一起使用时就会用到开窗函数。

举例说明：

在over窗口中进行分组,对某一字段进行分组统计,窗口大小就是同一个组的所有记录

语法格式：over(distribute by colname[,colname.....])

案例SQL:

# 查看顾客的购买明细及月购买总额
select *,sum(cost) over(distribute by substr(orderdate,1,7) ) from t_order ;
 
t_order.name    t_order.orderdate       t_order.cost    sum_window_0
saml    2018-01-01      10      205
saml    2018-01-08      55      205
tony    2018-01-07      50      205
saml    2018-01-05      46      205
tony    2018-01-04      29      205
tony    2018-01-02      15      205
saml    2018-02-03      23      23
mart    2018-04-13      94      341
saml    2018-04-06      42      341
mart    2018-04-11      75      341
mart    2018-04-09      68      341
mart    2018-04-08      62      341
neil    2018-05-10      12      12
neil    2018-06-12      80      80
Time taken: 2.128 seconds, Fetched: 14 row(s)

2、sort by 

sort by子句会让输入的数据强制排序 （强调：当使用排序时，窗口会在组内逐行变大）也是和开窗函数一起使用的。

sort by 你可以理解为局部排序，order by 理解为全局排序

语法格式：

语法：  over([distribute by colname] [sort by colname [desc|asc]])

案例SQL:

# 查看顾客的购买明细及每个顾客的月购买总额,并且按照日期降序排序
select *,sum(cost) over(distribute by name,month(orderdate) sort by orderdate desc ) from t_order ;

注意：可以使用partition by + order by 组合来代替distribute by+sort by组合

3、partition by 分组

partition by 出镜率挺高，首先和group by 相比，它经常和开窗函数配合使用，和 distribute by 相比，它的使用频率高，一般可以替换，但是partition by 经常和 order by 一起使用，而distribute by 和 sort by 搭班使用。

   

4、cluster by

culster by 是 distribute by 和 sort by 的复合操作

也就是说 culster by  = distribute by + sort by 就是说 当开窗函数中分组字段和排序字段是一个字段时，可以直接使用cluster by 。