电信用户行为分析

最新推荐文章于 2024-02-29 16:06:51 发布

@莫等闲

最新推荐文章于 2024-02-29 16:06:51 发布

阅读量2.7k

点赞数 3

分类专栏：大数据文章标签： java

本文链接：https://blog.csdn.net/Cloud_Wzh/article/details/126458752

版权

大数据专栏收录该内容

3 篇文章 0 订阅

订阅专栏

1.源数据

{"areacode":"0","countAll":0,"countCorrect":0,"datatime":"7903867","logid":"201803202114010303498949","requestinfo":"{\"sign\":\"4\",\"timestamp\":\"1521551641000\",\"remark\":\"4\",\"subjectPro\":\"123456\",\"interfaceUserName\":\"12345678900987654321\",\"channelno\":\"2\",\"imei\":\"A0273\",\"subjectNum\":\"13989589062\",\"imsi\":\"12345678900987654321\",\"queryNum\":\"13989589062\"}","requestip":"192.168.0.3","requesttime":"2018-03-20 21:14:01","requesttype":"1","responsecode":"010005","responsedata":"1"}
{"areacode":"0","countAll":0,"countCorrect":0,"datatime":"7400570","logid":"201803202114018885872265","requestinfo":"{\"sign\":\"4\",\"timestamp\":\"1521551641000\",\"remark\":\"4\",\"subjectPro\":\"123456\",\"interfaceUserName\":\"12345678900987654321\",\"channelno\":\"2\",\"imei\":\"A0984\",\"subjectNum\":\"13989589062\",\"imsi\":\"12345678900987654321\",\"queryNum\":\"13989589062\"}","requestip":"192.168.0.3","requesttime":"2018-03-20 21:14:01","requesttype":"1","responsecode":"010005","responsedata":"0"}
{"areacode":"1","countAll":0,"countCorrect":0,"datatime":"3848591","logid":"201803202114016724376204","requestinfo":"{\"sign\":\"4\",\"timestamp\":\"1521551641000\",\"remark\":\"4\",\"subjectPro\":\"123456\",\"interfaceUserName\":\"12345678900987654321\",\"channelno\":\"2\",\"imei\":\"A0176\",\"subjectNum\":\"13989589062\",\"imsi\":\"12345678900987654321\",\"queryNum\":\"13989589062\"}","requestip":"192.168.0.1","requesttime":"2018-03-20 21:14:01","requesttype":"1","responsecode":"010005","responsedata":"1"}
{"areacode":"2","countAll":0,"countCorrect":0,"datatime":"4062788","logid":"201803202114011941934248","requestinfo":"{\"sign\":\"4\",\"timestamp\":\"1521551641000\",\"remark\":\"4\",\"subjectPro\":\"123456\",\"interfaceUserName\":\"12345678900987654321\",\"channelno\":\"1\",\"imei\":\"A0954\",\"subjectNum\":\"13989589062\",\"imsi\":\"12345678900987654321\",\"queryNum\":\"13989589062\"}","requestip":"192.168.0.3","requesttime":"2018-03-20 21:14:01","requesttype":"0","responsecode":"010005","responsedata":"1"}
{"areacode":"1","countAll":0,"countCorrect":0,"datatime":"0026111","logid":"201803202114013711623684","requestinfo":"{\"sign\":\"4\",\"timestamp\":\"1521551641000\",\"remark\":\"4\",\"subjectPro\":\"123456\",\"interfaceUserName\":\"12345678900987654321\",\"channelno\":\"1\",\"imei\":\"A0126\",\"subjectNum\":\"13989589062\",\"imsi\":\"12345678900987654321\",\"queryNum\":\"13989589062\"}","requestip":"192.168.0.2","requesttime":"2018-03-20 21:14:01","requesttype":"0","responsecode":"010005","responsedata":"0"}
{"areacode":"1","countAll":0,"countCorrect":0,"datatime":"3955111","logid":"201803202114011326100538","requestinfo":"{\"sign\":\"4\",\"timestamp\":\"1521551641000\",\"remark\":\"4\",\"subjectPro\":\"123456\",\"interfaceUserName\":\"12345678900987654321\",\"channelno\":\"0\",\"imei\":\"A0482\",\"subjectNum\":\"13989589062\",\"imsi\":\"12345678900987654321\",\"queryNum\":\"13989589062\"}","requestip":"192.168.0.2","requesttime":"2018-03-20 21:14:01","requesttype":"1","responsecode":"010005","responsedata":"0"}
{"areacode":"2","countAll":0,"countCorrect":0,"datatime":"9062561","logid":"201803202114018998959868","requestinfo":"{\"sign\":\"4\",\"timestamp\":\"1521551641000\",\"remark\":\"4\",\"subjectPro\":\"123456\",\"interfaceUserName\":\"12345678900987654321\",\"channelno\":\"2\",\"imei\":\"A0330\",\"subjectNum\":\"13989589062\",\"imsi\":\"12345678900987654321\",\"queryNum\":\"13989589062\"}","requestip":"192.168.0.1","requesttime":"2018-03-20 21:14:01","requesttype":"1","responsecode":"010005","responsedata":"0"}
{"areacode":"1","countAll":0,"countCorrect":0,"datatime":"5817222","logid":"201803202114016790612418","requestinfo":"{\"sign\":\"4\",\"timestamp\":\"1521551641000\",\"remark\":\"4\",\"subjectPro\":\"123456\",\"interfaceUserName\":\"12345678900987654321\",\"channelno\":\"0\",\"imei\":\"A0394\",\"subjectNum\":\"13989589062\",\"imsi\":\"12345678900987654321\",\"queryNum\":\"13989589062\"}","requestip":"192.168.0.3","requesttime":"2018-03-20 21:14:01","requesttype":"1","responsecode":"010005","responsedata":"0"}
{"areacode":"0","countAll":0,"countCorrect":0,"datatime":"5384255","logid":"201803202114014192541375","requestinfo":"{\"sign\":\"4\",\"timestamp\":\"1521551641000\",\"remark\":\"4\",\"subjectPro\":\"123456\",\"interfaceUserName\":\"12345678900987654321\",\"channelno\":\"0\",\"imei\":\"A0227\",\"subjectNum\":\"13989589062\",\"imsi\":\"12345678900987654321\",\"queryNum\":\"13989589062\"}","requestip":"192.168.0.1","requesttime":"2018-03-20 21:14:01","requesttype":"1","responsecode":"010005","responsedata":"0"}
{"areacode":"1","countAll":0,"countCorrect":0,"datatime":"9980198","logid":"201803202114011158446251","requestinfo":"{\"sign\":\"4\",\"timestamp\":\"1521551641000\",\"remark\":\"4\",\"subjectPro\":\"123456\",\"interfaceUserName\":\"12345678900987654321\",\"channelno\":\"1\",\"imei\":\"A0056\",\"subjectNum\":\"13989589062\",\"imsi\":\"12345678900987654321\",\"queryNum\":\"13989589062\"}","requestip":"192.168.0.3","requesttime":"2018-03-20 21:14:01","requesttype":"0","responsecode":"010005","responsedata":"0"}
{"areacode":"0","countAll":0,"countCorrect":0,"datatime":"2348684","logid":"201803202114015714942076","requestinfo":"{\"sign\":\"4\",\"timestamp\":\"1521551641000\",\"remark\":\"4\",\"subjectPro\":\"123456\",\"interfaceUserName\":\"12345678900987654321\",\"channelno\":\"1\",\"imei\":\"A0526\",\"subjectNum\":\"13989589062\",\"imsi\":\"12345678900987654321\",\"queryNum\":\"13989589062\"}","requestip":"192.168.0.1","requesttime":"2018-03-20 21:14:01","requesttype":"1","responsecode":"010005","responsedata":"0"}
{"areacode":"0","countAll":0,"countCorrect":0,"datatime":"6849922","logid":"201803202114019976447360","requestinfo":"{\"sign\":\"4\",\"timestamp\":\"1521551641000\",\"remark\":\"4\",\"subjectPro\":\"123456\",\"interfaceUserName\":\"12345678900987654321\",\"channelno\":\"2\",\"imei\":\"A0659\",\"subjectNum\":\"13989589062\",\"imsi\":\"12345678900987654321\",\"queryNum\":\"13989589062\"}","requestip":"192.168.0.3","requesttime":"2018-03-20 21:14:01","requesttype":"0","responsecode":"010005","responsedata":"0"}
{"areacode":"1","countAll":0,"countCorrect":0,"datatime":"3947565","logid":"201803202114011560467740","requestinfo":"{\"sign\":\"4\",\"timestamp\":\"1521551641000\",\"remark\":\"4\",\"subjectPro\":\"123456\",\"interfaceUserName\":\"12345678900987654321\",\"channelno\":\"2\",\"imei\":\"A0193\",\"subjectNum\":\"13989589062\",\"imsi\":\"12345678900987654321\",\"queryNum\":\"13989589062\"}","requestip":"192.168.0.1","requesttime":"2018-03-20 21:14:01","requesttype":"1","responsecode":"010005","responsedata":"0"}
{"areacode":"0","countAll":0,"countCorrect":0,"datatime":"7194862","logid":"201803202114011882996732","requestinfo":"{\"sign\":\"4\",\"timestamp\":\"1521551641000\",\"remark\":\"4\",\"subjectPro\":\"123456\",\"interfaceUserName\":\"12345678900987654321\",\"channelno\":\"1\",\"imei\":\"A0207\",\"subjectNum\":\"13989589062\",\"imsi\":\"12345678900987654321\",\"queryNum\":\"13989589062\"}","requestip":"192.168.0.1","requesttime":"2018-03-20 21:14:01","requesttype":"0","responsecode":"010005","responsedata":"0"}
{"areacode":"1","countAll":0,"countCorrect":0,"datatime":"5394509","logid":"201803202114019616977897","requestinfo":"{\"sign\":\"4\",\"timestamp\":\"1521551641000\",\"remark\":\"4\",\"subjectPro\":\"123456\",\"interfaceUserName\":\"12345678900987654321\",\"channelno\":\"2\",\"imei\":\"A0878\",\"subjectNum\":\"13989589062\",\"imsi\":\"12345678900987654321\",\"queryNum\":\"13989589062\"}","requestip":"192.168.0.1","requesttime":"2018-03-20 21:14:01","requesttype":"0","responsecode":"010005","responsedata":"1"}
{"areacode":"1","countAll":0,"countCorrect":0,"datatime":"8001626","logid":"201803202114018894379100","requestinfo":"{\"sign\":\"4\",\"timestamp\":\"1521551641000\",\"remark\":\"4\",\"subjectPro\":\"123456\",\"interfaceUserName\":\"12345678900987654321\",\"channelno\":\"1\",\"imei\":\"A0288\",\"subjectNum\":\"13989589062\",\"imsi\":\"12345678900987654321\",\"queryNum\":\"13989589062\"}","requestip":"192.168.0.3","requesttime":"2018-03-20 21:14:01","requesttype":"1","responsecode":"010005","responsedata":"0"}
{"areacode":"0","countAll":0,"countCorrect":0,"datatime":"2106501","logid":"201803202114015386384960","requestinfo":"{\"sign\":\"4\",\"timestamp\":\"1521551641000\",\"remark\":\"4\",\"subjectPro\":\"123456\",\"interfaceUserName\":\"12345678900987654321\",\"channelno\":\"2\",\"imei\":\"A0649\",\"subjectNum\":\"13989589062\",\"imsi\":\"12345678900987654321\",\"queryNum\":\"13989589062\"}","requestip":"192.168.0.2","requesttime":"2018-03-20 21:14:01","requesttype":"1","responsecode":"010005","responsedata":"1"}
{"areacode":"1","countAll":0,"countCorrect":0,"datatime":"5421491","logid":"201803202114017187532296","requestinfo":"{\"sign\":\"4\",\"timestamp\":\"1521551641000\",\"remark\":\"4\",\"subjectPro\":\"123456\",\"interfaceUserName\":\"12345678900987654321\",\"channelno\":\"0\",\"imei\":\"A0152\",\"subjectNum\":\"13989589062\",\"imsi\":\"12345678900987654321\",\"queryNum\":\"13989589062\"}","requestip":"192.168.0.2","requesttime":"2018-03-20 21:14:01","requesttype":"0","responsecode":"010005","responsedata":"1"}
{"areacode":"1","countAll":0,"countCorrect":0,"datatime":"0580064","logid":"201803202114014093538560","requestinfo":"{\"sign\":\"4\",\"timestamp\":\"1521551641000\",\"remark\":\"4\",\"subjectPro\":\"123456\",\"interfaceUserName\":\"12345678900987654321\",\"channelno\":\"0\",\"imei\":\"A0792\",\"subjectNum\":\"13989589062\",\"imsi\":\"12345678900987654321\",\"queryNum\":\"13989589062\"}","requestip":"192.168.0.3","requesttime":"2018-03-20 21:14:01","requesttype":"0","responsecode":"010005","responsedata":"1"}
{"areacode":"0","countAll":0,"countCorrect":0,"datatime":"7229294","logid":"201803202114018078553853","requestinfo":"{\"sign\":\"4\",\"timestamp\":\"1521551641000\",\"remark\":\"4\",\"subjectPro\":\"123456\",\"interfaceUserName\":\"12345678900987654321\",\"channelno\":\"1\",\"imei\":\"A0838\",\"subjectNum\":\"13989589062\",\"imsi\":\"12345678900987654321\",\"queryNum\":\"13989589062\"}","requestip":"192.168.0.2","requesttime":"2018-03-20 21:14:01","requesttype":"1","responsecode":"010005","responsedata":"1"}
{"areacode":"0","countAll":0,"countCorrect":0,"datatime":"7451552","logid":"201803202114019675091038","requestinfo":"{\"sign\":\"4\",\"timestamp\":\"1521551641000\",\"remark\":\"4\",\"subjectPro\":\"123456\",\"interfaceUserName\":\"12345678900987654321\",\"channelno\":\"0\",\"imei\":\"A0440\",\"subjectNum\":\"13989589062\",\"imsi\":\"12345678900987654321\",\"queryNum\":\"13989589062\"}","requestip":"192.168.0.2","requesttime":"2018-03-20 21:14:01","requesttype":"0","responsecode":"010005","responsedata":"0"}
{"areacode":"2","countAll":0,"countCorrect":0,"datatime":"7691270","logid":"201803202114018973917543","requestinfo":"{\"sign\":\"4\",\"timestamp\":\"1521551641000\",\"remark\":\"4\",\"subjectPro\":\"123456\",\"interfaceUserName\":\"12345678900987654321\",\"channelno\":\"0\",\"imei\":\"A0759\",\"subjectNum\":\"13989589062\",\"imsi\":\"12345678900987654321\",\"queryNum\":\"13989589062\"}","requestip":"192.168.0.1","requesttime":"2018-03-20 21:14:01","requesttype":"0","responsecode":"010005","responsedata":"0"}
{"areacode":"2","countAll":0,"countCorrect":0,"datatime":"3949849","logid":"201803202114014830043710","requestinfo":"{\"sign\":\"4\",\"timestamp\":\"1521551641000\",\"remark\":\"4\",\"subjectPro\":\"123456\",\"interfaceUserName\":\"12345678900987654321\",\"channelno\":\"0\",\"imei\":\"A0730\",\"subjectNum\":\"13989589062\",\"imsi\":\"12345678900987654321\",\"queryNum\":\"13989589062\"}","requestip":"192.168.0.1","requesttime":"2018-03-20 21:14:01","requesttype":"1","responsecode":"010005","responsedata":"0"}
{"areacode":"1","countAll":0,"countCorrect":0,"datatime":"1325808","logid":"201803202114019981815946","requestinfo":"{\"sign\":\"4\",\"timestamp\":\"1521551641000\",\"remark\":\"4\",\"subjectPro\":\"123456\",\"interfaceUserName\":\"12345678900987654321\",\"channelno\":\"1\",\"imei\":\"A0795\",\"subjectNum\":\"13989589062\",\"imsi\":\"12345678900987654321\",\"queryNum\":\"13989589062\"}","requestip":"192.168.0.1","requesttime":"2018-03-20 21:14:01","requesttype":"1","responsecode":"010005","responsedata":"0"}
{"areacode":"2","countAll":0,"countCorrect":0,"datatime":"5200891","logid":"201803202114013805713631","requestinfo":"{\"sign\":\"4\",\"timestamp\":\"1521551641000\",\"remark\":\"4\",\"subjectPro\":\"123456\",\"interfaceUserName\":\"12345678900987654321\",\"channelno\":\"0\",\"imei\":\"A0453\",\"subjectNum\":\"13989589062\",\"imsi\":\"12345678900987654321\",\"queryNum\":\"13989589062\"}","requestip":"192.168.0.2","requesttime":"2018-03-20 21:14:01","requesttype":"0","responsecode":"010005","responsedata":"1"}
{"areacode":"1","countAll":0,"countCorrect":0,"datatime":"7503401","logid":"201803202114011739114103","requestinfo":"{\"sign\":\"4\",\"timestamp\":\"1521551641000\",\"remark\":\"4\",\"subjectPro\":\"123456\",\"interfaceUserName\":\"12345678900987654321\",\"channelno\":\"1\",\"imei\":\"A0337\",\"subjectNum\":\"13989589062\",\"imsi\":\"12345678900987654321\",\"queryNum\":\"13989589062\"}","requestip":"192.168.0.1","requesttime":"2018-03-20 21:14:01","requesttype":"1","responsecode":"010005","responsedata":"0"}
{"areacode":"2","countAll":0,"countCorrect":0,"datatime":"9854816","logid":"201803202114018756860010","requestinfo":"{\"sign\":\"4\",\"timestamp\":\"1521551641000\",\"remark\":\"4\",\"subjectPro\":\"123456\",\"interfaceUserName\":\"12345678900987654321\",\"channelno\":\"0\",\"imei\":\"A0421\",\"subjectNum\":\"13989589062\",\"imsi\":\"12345678900987654321\",\"queryNum\":\"13989589062\"}","requestip":"192.168.0.1","requesttime":"2018-03-20 21:14:01","requesttype":"0","responsecode":"010005","responsedata":"1"}
{"areacode":"0","countAll":0,"countCorrect":0,"datatime":"7881176","logid":"201803202114019295763686","requestinfo":"{\"sign\":\"4\",\"timestamp\":\"1521551641000\",\"remark\":\"4\",\"subjectPro\":\"123456\",\"interfaceUserName\":\"12345678900987654321\",\"channelno\":\"2\",\"imei\":\"A0773\",\"subjectNum\":\"13989589062\",\"imsi\":\"12345678900987654321\",\"queryNum\":\"13989589062\"}","requestip":"192.168.0.1","requesttime":"2018-03-20 21:14:01","requesttype":"0","responsecode":"010005","responsedata":"0"}
{"areacode":"0","countAll":0,"countCorrect":0,"datatime":"5128445","logid":"201803202114018905894136","requestinfo":"{\"sign\":\"4\",\"timestamp\":\"1521551641000\",\"remark\":\"4\",\"subjectPro\":\"123456\",\"interfaceUserName\":\"12345678900987654321\",\"channelno\":\"2\",\"imei\":\"A0910\",\"subjectNum\":\"13989589062\",\"imsi\":\"12345678900987654321\",\"queryNum\":\"13989589062\"}","requestip":"192.168.0.2","requesttime":"2018-03-20 21:14:01","requesttype":"1","responsecode":"010005","responsedata":"1"}
{"areacode":"2","countAll":0,"countCorrect":0,"datatime":"5589007","logid":"201803202114015031945630","requestinfo":"{\"sign\":\"4\",\"timestamp\":\"1521551641000\",\"remark\":\"4\",\"subjectPro\":\"123456\",\"interfaceUserName\":\"12345678900987654321\",\"channelno\":\"2\",\"imei\":\"A0637\",\"subjectNum\":\"13989589062\",\"imsi\":\"12345678900987654321\",\"queryNum\":\"13989589062\"}","requestip":"192.168.0.3","requesttime":"2018-03-20 21:14:01","requesttype":"0","responsecode":"010005","responsedata":"1"}

2.数据说明


{
    区域 areacode ( 0:浙江省丽水市; 1:福建省南平市; 2:福建省福州市 ) 。"areacode":"0",
    "countAll":0,
    "countCorrect":0,
    "datatime":"7903867",
   登录时间 logid。 "logid":"201803202114010303498949",
   
    "requestinfo":"{\"sign\":\"4\",\"timestamp\":\"1521551641000\",\"remark\":\"4\",\"subjectPro\":\"123456\",
	\"interfaceUserName\":\"12345678900987654321\",
	渠道 channelno ( 0:手机; 1:PC机; 2:平板电脑 ) 。\"channelno\":\"2\",
	用户编号 imei ( A0001 ~ A1000 ) 。\"imei\":\"A0273\",
	\"subjectNum\":\"13989589062\",
	\"imsi\":\"12345678900987654321\",\"queryNum\":\"13989589062\"}",
	
    IP 地址 requestip ( 0:192.168.0.1; 1:192.168.0.2; 2:192.168.0.3 ) "requestip":"192.168.0.3",
    "requesttime":"2018-03-20 21:14:01",
    请求类型 requesttype ( 0:GET; 1:POST ) 。"requesttype":"1",
    "responsecode":"010005",
    请求结果 responsedata ( 0:无查询结果; 1:查询结果成功 ) 。 "responsedata":"1"
}

3.数据清洗

 //数据清洗
    static class TelecomMapper extends Mapper<LongWritable, Text, Text, NullWritable> {
        ArrayList arrayList1 = new ArrayList<Integer>();
        ArrayList arrayList2 = new ArrayList<Integer>();
        String areacodess = "";
        String channelnoss="";
        String imeiss="";
        String requesttypess="";
        @Override
        protected void setup(Context context) throws IOException, InterruptedException {
            arrayList1.add("0");
            arrayList1.add("1");
            arrayList1.add("2");
            arrayList2.add("0");
            arrayList2.add("1");
        }
        @Override
        protected void map(LongWritable key, Text value, Context context) throws IOException, InterruptedException {

            String string=  value.toString();
            JSONObject jsonObject = JSONUtil.parseObj(string);
            //区域编号
            String areacodes = jsonObject.getStr("areacode");
            if (arrayList1.contains(areacodes)){
                  areacodess=areacodes;
            }
            //渠道编号
            String channelnos = jsonObject.getJSONObject("requestinfo").getStr("channelno");
            if (arrayList1.contains(channelnos)){
                  channelnoss=channelnos;
            }
            //用户编号
            String imeis = jsonObject.getJSONObject("requestinfo").getStr("imei");
            if (imeis.length()==5){
                  imeiss=imeis;
            }
            //请求类型
            String requesttypes = jsonObject.getStr("requesttype");
            if (arrayList2.contains(requesttypes)){
                  requesttypess=requesttypes;
            }
            String toalstring=areacodess+","+channelnoss+","+imeiss+","+requesttypess;
            context.write(new Text(toalstring),NullWritable.get());
        }
    }

4.创建数据库



创建数据原始明细表;
CREATE TABLE cleanMap (
 imei varchar(10) DEFAULT NULL COMMENT '用户编号',
 areacode varchar(11) DEFAULT NULL COMMENT ' 登录区域',
 requesttype varchar(11) DEFAULT NULL COMMENT '请求类型',
 channelno varchar(11) DEFAULT NULL COMMENT '渠道'
)
创建区域维表;
CREATE TABLE t_dim_area (
 areacode varchar(11) DEFAULT NULL COMMENT '区域编码',
 areaname varchar(100) DEFAULT NULL COMMENT '区域名称'
) 
INSERT INTO t_dim_area VALUES ('0', '浙江省丽水市');
INSERT INTO t_dim_area VALUES ('1', '福建省南平市');
INSERT INTO t_dim_area VALUES ('2', '福建省福州市');
创建渠道维表;
CREATE TABLE t_dim_channel (
 channelno varchar(11) DEFAULT NULL COMMENT '渠道编号',
 channelname varchar(100) DEFAULT NULL COMMENT '渠道名杯'
)
INSERT INTO t_dim_channel VALUES ('0', '手机');
INSERT INTO t_dim_channel VALUES ('1', 'PC');
INSERT INTO t_dim_channel VALUES ('2', '平板电脑');
创建请求类型维表;
CREATE TABLE t_dim_requesttype (
 requesttype varchar(11) DEFAULT NULL COMMENT '请求类型',
 requesttypename varchar(100) DEFAULT NULL COMMENT '请求类型名称'
)
INSERT INTO t_dim_requesttype VALUES ('0', 'GET方式');
INSERT INTO t_dim_requesttype VALUES ('1', 'POST方式');
创建五个视图。

#用户渠道趋势分析
create or REPLACE view v_area_channel as
select b.areaname, c.channelname, count(distinct imei) num
from cleanMap a,t_dim_area b,t_dim_channel c
where a.areacode = b.areacode and a.channelno = c.channelno
group by b.areaname,c.channelname;
#用户请求类型对比情况
create or REPLACE view v_area_requesttype as
select b.areaname,c.requesttypename, count(distinct imei) num
from cleanMap a,t_dim_area b,t_dim_requesttype c
where a.areacode = b.areacode and a.requesttype = c.requesttype
group by b.areaname,c.requesttypename;
#用户渠道饼图情况
create or REPLACE view v_channelno as
select b.channelname, count(distinct imei) num
from cleanMap a,t_dim_channel b
where a.channelno = b.channelno
group by b.channelname;

v_area_channel: 
字段包括区域名称、渠道名称、用户数量，该视图用于用户渠道趋势分析，如 “在福建省福州市通过手机登录电信公司网站的用户数量是 9”。

v_area_requesttype: 
字段包括区域名称、请求类型名称、用户数量，该视图用于用户请求类型对比情况分析，如 “在浙江省丽水市请求类型为 GET 方式的用户数量是 15”。

v_channelno: 
字段包括渠道名称和用户数量，该视图用于用户渠道饼图分析，如 “通过手机登录电信公司网站的用户数量是 35”。