Hive实例-农产品统计

数据样例

建表语句

农产品表

create external table farm_1(
type string,
price decimal(6,2),
name string,
provience string,
city string)
row format delimited fields terminated by '\t'
location '/farm_product/farm_1';

省份表

create table provience(pro string)
row format delimited fields terminated by '\t';

案例需求

1.统计每个省份的农产品市场总数

select provience,count(distinct name)
from farm_1
where name != ''
and provience != ''
group by provience;

2.统计没有农产品市场的省份有哪些

select b.pro
from (select provience
from farm_1
group by provience) a
right outer join provience b
on substr(a.provience,1,2) = substr(b.pro,1,2)
where a.provience is null;

3.统计山东省售卖蛤蜊的农产品市场占全省农产品市场的比例

select round(gala/sum,2)
from (select count(distinct name) as sum
from farm_1
where name != ''
and provience = '山东') as a,
(select count(distinct name) as gala
from farm_1
where name != ''
and provience = '山东'
and type='蛤蜊') as b;

4.统计每个省农产品种类总数

select provience,count(distinct type)
from farm_1
where provience != ''
and type != ''
group by provience;

5.统计排名前 3 的省份共同拥有的农产品类型

• a. 计算前3省份的名称
• b. 计算前3省份的所有去重产品名称
• c. 计算共同拥有的产品

select c.type
from (
select a.provience,a.type
from(select provience,type
from farm_1
group by provience,type) a
left semi join(select provience,count(distinct type) as cnt
from farm_1
group by provience
order by cnt desc limit 3) b
on a.provience = b.provience)c
group by c.type;

6.计算山西省的每种农产品的价格波动趋势， 即计算每天价格均值， 并按照时间先后顺序排列该值。

某种农产品的价格均值计算公式：
P AVG = (P M1 +P M2 +…+P Mn -max§-min§)/(N-2)
其中，P 表示价格，Mn 表示 market，即农产品市场。P M1 表示 M1 农产品市场的该产品价
格，max§表示价格最大值，min§价格最小值。

select a.type,
IF(
count(a.type)>2,
round((sum(a.price)-max(a.price)-min(a.price))/(count(a.price)-2),2),
round(sum(a.price)/count(a.price),2)
)
from (select type,price
from farm_1
where provience='山西'
and type !=''
and price is not null
) a
group by a.type;