hive 统计分析地区销量

本次要用到的数据来自三张表:t_order_order,t_member_member,t_agent_agent,由于t_member_member表很多数据的region字段并没有填写,所以将t_order_order订单表的数据分为能在t_member_member里面找到region的与不能找到region的,不能找到region的通过关联另一张表t_agent_agent,截取里面的address字段,截取完是单独的市的再做判断是属于哪个省

测试:

##region不为空的部分

SELECT b.region as region,count(*) as count,sum(a.amount) AS sum from
(select distinct validOrder.accountid as accountid,amount from
(SELECT case WHEN isreturn = 0 and status IN (2,16,32) and type in (1,2) and statustime16 >= '2018-01-01 00:00:00' and statustime16 < '2018-02-01 00:00:00' Then 1
     WHEN isreturn = 1 and status =2 and type in (1,2) and statustime2 >= '2018-01-01 00:00:00' and statustime2 < '2018-02-01 00:00:00' Then 1
     else 0 end as valid,accountid,amount
FROM  t_order_order where mark>0 and d='20180514') validOrder where validOrder.valid>0) a
JOIN
(SELECT region,accountid from t_member_member WHERE d = '20180514' AND length(region)>0 AND address not like '%杜鹃路%') b
on a.accountid = b.accountid
group by b.region
order by sum desc

##region为空的部分

SELECT address as region,count(*) as count,sum(amount) as sum from
(SELECT a.amount,c.* from
  (select distinct validOrder.accountid as accountid,amount from
  (SELECT case WHEN isreturn = 0 and status IN (2,16,32) and type in (1,2) and statustime16 >= '2018-01-01 00:00:00' and statustime16 < '2018-02-01 00:00:00' Then 1
     WHEN isreturn = 1 and status =2 and type in (1,2) and statustime2 >= '2018-01-01 00:00:00' and statustime2 < '2018-02-01 00:00:00' Then 1
     else 0 end as valid,accountid,amount
FROM  t_order_order where mark>0 and d='20180514') validOrder where validOrder.valid>0) a
JOIN
(SELECT region,accountid from t_member_member WHERE d = '20180514' AND length(region)=0 AND address not like '%杜鹃路%') b
on a.accountid = b.accountid
JOIN
(SELECT accountid,case when substr(address,0,2) IN ('苏州','徐州','盐城','无锡','南京','南通','连云','常州','扬州','镇江','淮安','泰州','宿迁','江阴','宜兴','邳州','新沂','溧阳','昆山','常熟',
                                                    '太仓','如皋','海门','启东','海安','东台','仪征','高邮','丹阳','扬中','句容','兴化','靖江','泰兴') then '江苏'
    when substr(address,0,2) IN (' 上','松江','宝山','金山','嘉定','南汇','青浦','浦东','奉贤','闵行','徐汇','静安','黄浦','普陀','杨浦','虹口','闸北','长宁','崇明','卢湾') then '上海'
    when substr(address,0,2) IN ('和平','北辰','河西','西青','津南','东丽','武清','宝坻','红桥','大港','汉沽','静海','宁河','塘沽','蓟县','南开','河东') then '天津'
    when substr(address,0,2) IN ('杭州','宁波','嘉兴','温州','海宁','绍兴','湖州','临海','西湖','慈溪','萧山','衢州','拱墅') then '浙江'
    when substr(address,0,2) IN ('深圳','广州','珠海','东莞','佛山','中山','惠州','汕头','江门','茂名','肇庆','湛江','梅州','汕尾','河源','清远','韶关','揭阳','阳江','潮州','云浮') then '广东' 
    when substr(address,0,2) IN ('济南','青岛','淄博','枣庄','东营','烟台','潍坊','济宁','泰安','威海','日照','滨州','德州','聊城','临沂','菏泽','莱芜' ) then '山东'
    when substr(address,0,2) IN ('朝阳','海淀','通州','房山','丰台','昌平','大兴','顺义','西城','延庆','石景','宣武','怀柔','崇文','密云','东城','门头','平谷') THEN '北京'
    when substr(address,0,2) IN ('福州','厦门','莆田','泉州','漳州','龙岩','三明','南平','宁德') then '福建' 
    when substr(address,0,2) IN ('郑州','开封','洛阳','平顶','安阳','鹤壁','新乡','焦作','濮阳','许昌','漯河','三门','南阳','商丘','信阳','周口','驻马','唐河') then '河南'
    when substr(address,0,2) IN ('西安','渭南','咸阳','延安','商洛','安康','汉中','宝鸡','铜川','榆林','杨凌','洛川') then '陕西' 
    when substr(address,0,2) IN ('太原','大同','朔州','阳泉','长治','忻州','吕梁','晋中','临汾','运城','晋城') then '山西' 
    when substr(address,0,2) IN ('芜湖','合肥','六安','宿州','阜阳','安庆','马鞍','蚌埠','淮北','淮南','宣城','黄山','铜陵','亳州','池州','巢湖','滁州') then '安徽' 
    when substr(address,0,2) IN ('沈阳','大连','鞍山','抚顺','本溪','丹东','锦州','营口','阜新','辽阳','盘锦','铁岭','朝阳','葫芦') then '辽宁' 
    when substr(address,0,2) IN ('成都','自贡','攀枝','泸州','德阳','绵阳','广元','遂宁','内江','乐山','南充','眉山'