---创建表 用户信息表
create table dtl_user_info(
user_id int, -- 用户唯一标识id
phone_no varchar(16), -- 手机号
user_name varchar(128), -- 用户姓名
cert_no varchar(128), -- 身份证
open_time date, -- 开户时间
run_state int -- 1表示正常在用，0表是销号
);
-- 插入数据
insert into dtl_user_info values(1,'13600000001','张三','350000000000000001',to_date('2021-05-01 01:01:00','yyyy-mm-dd hh24:mi:ss'),1);
insert into dtl_user_info values(2,'18700000001','李四','420000000000000099',to_date('2012-05-01 01:01:00','yyyy-mm-dd hh24:mi:ss'),1);
insert into dtl_user_info values(3,'18800000008','王五','42000000000000001X',to_date('2012-05-01 01:01:00','yyyy-mm-dd hh24:mi:ss'),1);
insert into dtl_user_info values(4,'18800000009','赵六','420000000000000055',to_date('2012-05-01 01:01:00','yyyy-mm-dd hh24:mi:ss'),0);
insert into dtl_user_info values(5,'18800000010','张三','350000000000000001',to_date('2012-05-01 01:01:00','yyyy-mm-dd hh24:mi:ss'),0);
-- 创建表 流量使用情况表
create table dtl_user_gprs(
user_id int, -- 用户唯一标识id
gprs int -- 流量使用单位兆M
);
insert into dtl_user_gprs values(1,1024);
insert into dtl_user_gprs values(2,2048);
insert into dtl_user_gprs values(3,4096);
-- 创建表 用户消费金额表
create table dtl_user_fee(
user_id int, -- 用户唯一标识id
arpu int --消费金额元
);
insert into dtl_user_fee values(1,100);
insert into dtl_user_fee values(2,50);
insert into dtl_user_fee values(3,200);
-- 创建表 用户基站归属表
create table dtl_user_belong(
user_id int, -- 用户唯一标识id
belong_code varchar(128) --基站归属编码
);
insert into dtl_user_belong values(1,'10001');
insert into dtl_user_belong values(2,'10002');
insert into dtl_user_belong values(3,'10003');
-- 基站维表
-- 创建表 用户基站归属表
create table code_belong(
code varchar(128),
name varchar(128) --基站归属名称
);
insert into code_belong values('10001','江岸');
insert into code_belong values('10002','江汉');
insert into code_belong values('10003','硚口');
insert into code_belong values('10004','汉阳');
insert into code_belong values('10005','武昌');
insert into code_belong values('10006','青山');
insert into code_belong values('10007','洪山');
insert into code_belong values('10008','东西湖');
insert into code_belong values('10009','汉南');
insert into code_belong values('10010','蔡甸');
----------------------------------------------------------------------------
1.统计统计每个基站归属下有多少个在网客户。字段要求：基站归属编码，基站归属名称，数量。
select a.code 基站归属编码,
a.name 基站归属名称,
count(b.user_id) 数量
from code_belong a
left join (
select x.user_id,
x.belong_code
from dtl_user_belong x
inner join dtl_user_info y on x.user_id=y.user_id
where y.run_state=1
) b on a.code=b.belong_code
group by a.code ,
a.name
2.统计统计每个基站归属的消费金额，并且根据金额划分等级（0-50 为 a，50-100 为b ，100及以上为c）。字段要求：基站归属编码，基站归属名称，合计金额，等级。
select a.code 基站归属编码,
a.name 基站归属名称,
nvl(b.arpu,0) arpu,
case when nvl(b.arpu,0) < 50 then 'a'
when nvl(b.arpu,0) >= 50 and nvl(b.arpu,0) <100 then 'b'
when nvl(b.arpu,0) >=100 then 'c'
else ''
end level_type
from code_belong a
left join (
--做一个统计，将金额统计到基站编码上
select m.belong_code,
sum(m.arpu) arpu
from (
--第一步，找出用户的基站归属和消费金额
select x.user_id,
x.belong_code,
y.arpu --金额
from dtl_user_belong x
left join dtl_user_fee y on x.user_id=y.user_id
) m
group by m.belong_code
) b on a.code=b.belong_code
3.统计每个基站的流量使用情况：基站归属编码，基站归属名称，流量使用合计。
select a.code 基站归属编码,
a.name 基站归属名称,
nvl(b.gprs,0) 流量使用合计
from code_belong a
left join (
select m.belong_code,
sum(m.gprs) gprs
from (
select x.user_id,
x.belong_code,
y.gprs
from dtl_user_belong x
left join dtl_user_gprs y on x.user_id=y.user_id
) m
group by m.belong_code
) b on a.code=b.belong_code
