数据聚合与采样
--------------
count()
sum()
avg()
max()
min()
//查询每个customer的订单数
select cid,count(*) from orders group by cid ;
//错,select字段必须出现在group by中。
select cid,orderno ,count(*) from group by cid ;
//去重集合
select cid,collect_set(price) from group by cid ;
//select中出现多个聚合函数
select cid,max(price),min(price) from group by cid ;
//coalesce
返回第一个非空参数
SELECT sum(coalesce(sex_age.age,0)) AS age_sum,
sum(if(sex_age.sex = 'Female',sex_age.age,0))
AS female_age_sum FROM employee;
//不允许嵌套聚合,一下语句错误
SELECT avg(count(*)) AS row_cnt ;
//如果使用count + distinct组合,mapred.reduce.tasks属性失效,使用
//使用一个reduce进行,类似于order by
--------------
count()
sum()
avg()
max()
min()
//查询每个customer的订单数
select cid,count(*) from orders group by cid ;
//错,select字段必须出现在group by中。
select cid,orderno ,count(*) from group by cid ;
//去重集合
select cid,collect_set(price) from group by cid ;
//select中出现多个聚合函数
select cid,max(price),min(price) from group by cid ;
//coalesce
返回第一个非空参数
SELECT sum(coalesce(sex_age.age,0)) AS age_sum,
sum(if(sex_age.sex = 'Female',sex_age.age,0))
AS female_age_sum FROM employee;
//不允许嵌套聚合,一下语句错误
SELECT avg(count(*)) AS row_cnt ;
//如果使用count + distinct组合,mapred.reduce.tasks属性失效,使用
//使用一个reduce进行,类似于order by