Apache Hive LOGO

Hive实战－求北上广深等地知名商场的日均到访

# 输入数据

有商场数据如下：

id name

1 北京-华联商厦

2 北京-朝阳大悦城

3 武汉-武商摩尔城

4 武汉-光谷世界城

5 青岛-海信广场

6 重庆-龙湖天街

7 上海-晶品购物中心

8 深圳-龙华九方购物中心

9 广州-中港皮具城

......

# 数据传到能执行hdfs命令所在服务器

scp id_name.txt zyl@hive_svc:/home/zyl/data

# 选择Hive所使用库

use zyl_product;

# 创建外部分区表,添加分区

create external table tmp_id_name_tab (mallid string,mallname string)

PARTITIONED BY(dt String)

row format delimited fields terminated by 't'

stored as textfile location '/user/zyl/tmp_id_name_tab/init_meta';

ALTER TABLE tmp_id_name_tab

ADD PARTITION (dt='zsy_20171018_01')

location '/user/zyl/tmp_id_name_tab/zsy_20171018/01';

# 数据传到Hive外部表分区zsy_20171018_01所在的HDFS上

hdfs dfs -put id_name.txt /user/zyl/tmp_id_name_tab/zsy_20171018/01

# 查看是否有数据(要确保有数据)

select * from tmp_id_name_tab a where a.dt = 'zsy_20171018_01' limit 10;

# 商场日均平均数

select mallid,mallname,ceil(avg(cnt)) avg_ceil from

(

select mallid,day,count(1) cnt from

(

select /*+mapjoin(b)*/mallid,personid from zyl_product.mall_probe_data a

join

(select mallid,mallname from zyl_product.tmp_id_name_tab a where a.dt = 'zsy_20171018_01') b

on (a.buildingid = b.id)

) a group by id,day

) a

group by id,mallname

代码截图

# 简单说明

1.表简要介绍

mall_probe_data表是基础数据,里面有商场id、persoinid、日期等

2.SQL思路介绍

先用mapjoin在内存中装载id映射表(zyl_product库tmp_id_name_tab表),然后求商场每天的去重到访数,最后求商场日均到访数并向上取整.

难道没人发现bug吗?

需要去重按天对mac去重,加上group by mallid,personid

修复版如下：

# 商场日均平均数

select mallid,ceil(avg(cnt)) avg_ceil from

(

select mallid,count(1) cnt from

(

select /*+mapjoin(b)*/mallid,personid from zyl_product.mall_probe_data a

join

(select mallid,mallname from zyl_product.tmp_id_name_tab a where a.dt = 'zsy_20171018_01') b

on (a.buildingid = b.id)

group by mallid,personid

) a group by id,day

) a

group by id,mallname