2020-06-08 阅读量: 1012
练习1
create table user_info(
phone varchar(20),
star_sign varchar(10),
area varchar(10),
city varchar(10),
customer_name varchar(10),
age int,
gender varchar(10),
curr_bal decimal,
uncurr_bal decimal
);
insert into user_info values('13100000001','金牛','河北','石家庄','邓伦',27,'男',80,200),
('13100000002','双子','北京','北京','黄磊',49,'男',30,400),
('13100000003','处女', '河南','濮阳',null,35,'男',600,0),
('13100000004','白羊','湖南','长沙','张艺兴',28,'男',20,10),
('13100000005','天秤','新疆','乌鲁木齐','迪丽热巴',27,'女',10,50);
select * from user_info;
-- 查询实名账户（姓名不为空）：手机号，姓名
select phone,customer_name from user_info where customer_name is not null;
-- 查询账户余额大于40小于1000（可提现余额+不可提现余额）：手机号，姓名
select phone,customer_name from user_info where curr_bal+uncurr_bal between 40 and 1000;
-- 查询年龄30-59（包括30和59）：手机号，姓名
select phone,customer_name from user_info where age between 30 and 59;
练习2
create database cda;
use cda;
create table cmn_investment_request(
Created_at datetime,
User_id varchar(10),
invest_item varchar(10),
invest_amount decimal(38,10)
);
create table dim_agent(
User_id varchar(10),
Start_date datetime,
End_date datetime,
Agent_id varchar(10)
);
insert into cmn_investment_request values
('2017-11-01 01:32:00','A123','CFH',100000),
('2017-12-25 03:42:00','A123','AX',450000),
('2017-12-11 17:42:00','A123','CH',700000),
('2017-12-06 20:06:00','B456','CFH',1500000),
('2017-12-16 14:32:00','B456','AX',800000),
('2017-12-26 17:22:00','B456','AX',600000),
('2018-11-01 14:32:00','C789','JUIN',300000);
insert into dim_agent values
('A123','2016-01-01 00:00:00','2017-12-04 23:59:59',10001),
('A123','2017-12-05 00:00:00','3001-12-31 23:59:59',10002),
('B456','2015-10-31 00:00:00','2016-12-15 23:59:59',10001),
('B456','2016-12-16 00:00:00','3001-12-31 23:59:59',10003),
('C789','2015-01-01 00:00:00','3001-12-31 23:59:59',10002);
select * from cmn_investment_request;
select * from dim_agent;
#1、计算2017年每笔投资均大于50万的用户
select user_id
from cmn_investment_request
where year(created_at)=2017
group by user_id
having min(invest_amount)>500000;
Select distinct User_id
from cmn_investment_request
where year(Created_at)="2017" and User_id not in (select distinct User_id from cmn_investment_request where invest_amount<=500000);
#2、计算2017年仅投资过CFH和AX产品的用户
select user_id
from cmn_investment_request
where year(created_at)=2017
group by user_id
having group_concat(distinct invest_item order by invest_item desc)='cfh,ax';
#3、计算归属于10002业务员的投资金额
-- 方法一
select agent_id,sum(invest_amount)
from cmn_investment_request c
left join dim_agent d on c.user_id=d.user_id
where created_at between start_date and end_date and agent_id='10002';
-- 方法二
select agent_id,sum((created_at between start_date and end_date)*invest_amount)
from cmn_investment_request c
left join dim_agent d on c.user_id=d.user_id
where agent_id='10002';
-- 方法三
select agent_id,sum(invest_amount)
from cmn_investment_request c
left join dim_agent d on c.user_id=d.user_id and agent_id='10002'
where created_at between start_date and end_date;
练习3
create database meicaiwang;
use meicaiwang;
create table driver_daily(
driver_id varchar(10),
driver_name varchar(10),
city_id varchar(10),
city_name varchar(10),
order_id varchar(10),
d_year int,
d_month int,
d_day int
);
insert into driver_daily values
('111','王**','32','厦门市','12233',2017,7,1),
('111','王**','32','厦门市','12234',2017,7,1),
('111','王**','32','厦门市','12235',2017,7,1),
('111','王**','32','厦门市','12236',2017,7,1),
('111','王**','32','厦门市','12237',2017,7,1),
('111','王**','32','厦门市','12238',2017,7,1),
('111','王**','32','厦门市','12239',2017,7,1),
('111','王**','32','厦门市','12240',2017,7,1),
('111','王**','32','厦门市','12241',2017,7,1),
('111','王**','32','厦门市','12242',2017,7,1),
('111','王**','32','厦门市','12243',2017,7,1),
('111','王**','32','厦门市','12244',2017,7,1),
('111','王**','32','厦门市','12245',2017,7,1),
('111','王**','32','厦门市','12246',2017,7,1),
('111','王**','32','厦门市','12247',2017,7,1),
('111','王**','32','厦门市','12248',2017,7,1),
('111','王**','32','厦门市','12249',2017,7,1),
('111','王**','32','厦门市','12250',2017,7,1),
('111','王**','32','厦门市','12251',2017,7,1),
('111','王**','32','厦门市','12252',2017,7,1),
('202','林**','32','厦门市','32234',2017,7,1),
('202','林**','32','厦门市','32235',2017,7,1),
('202','林**','32','厦门市','32236',2017,7,2),
('202','林**','32','厦门市','32237',2017,7,2),
('202','林**','32','厦门市','32238',2017,7,3),
('202','林**','32','厦门市','32239',2017,7,3),
('202','林**','32','厦门市','32240',2017,7,4),
('202','林**','32','厦门市','32241',2017,7,4),
('202','林**','32','厦门市','32242',2017,7,5),
('202','林**','32','厦门市','32243',2017,7,5),
('202','林**','32','厦门市','32244',2017,7,6),
('202','林**','32','厦门市','32245',2017,7,6),
('202','林**','32','厦门市','32246',2017,7,7),
('202','林**','32','厦门市','32247',2017,7,7),
('202','林**','32','厦门市','32248',2017,7,7),
('202','林**','32','厦门市','32249',2017,7,8),
('202','林**','32','厦门市','32250',2017,7,8),
('202','林**','32','厦门市','32251',2017,7,8),
('202','林**','32','厦门市','32252',2017,7,9),
('202','林**','32','厦门市','32253',2017,7,9),
('202','林**','32','厦门市','32254',2017,7,10),
('202','林**','32','厦门市','32255',2017,7,11);
select * from driver_daily;
-- 2017年7月1日-2017年7月31日，有过10天以上的完单并且总完单量在20单以上的司机id，司机姓名，司机完单天数、司机完单数
select driver_id,driver_name,count(distinct concat(d_year,d_month,d_day)) 完单天数,count(distinct order_id) 完单数
from driver_daily
where d_year=2017 and d_month=7
group by driver_id
having count(distinct concat(d_year,d_month,d_day))>10 and count(distinct order_id)>20;
select driver_id,driver_name,count(distinct concat(d_year,d_month,d_day)) as 完单天数,count(distinct order_id) as 完单数
from driver_daily
where d_year=2017 and d_month=7
group by driver_id
having 完单数>20 and 完单天数>10; -- having子句中能用select里的别名
create table driver_info(
driver_id varchar(10),
driver_name varchar(10),
driver_phone varchar(20)
);
insert into driver_info values('110','王**','159****4134'),
('111','林**','159****7134'),
('222','张**','159****8134');
create table driver_collect(
driver_id varchar(10),
order_id varchar(10),
d_year int,
d_month int,
d_day int
);
insert into driver_collect values('111','111',2017,7,1),
('222','112',2017,7,1),
('222','113',2017,7,2),
('222','114',2017,7,3),
('222','115',2017,7,4),
('222','116',2017,7,5),
('222','117',2017,7,6),
('222','118',2017,7,7),
('222','119',2017,7,8),
('222','120',2017,7,9),
('222','121',2017,7,10),
('222','122',2017,7,11),
('222','123',2017,7,12),
('222','124',2017,7,13),
('222','125',2017,7,14),
('222','126',2017,7,15),
('222','127',2017,7,16),
('222','128',2017,7,17),
('222','129',2017,7,18),
('222','130',2017,7,19),
('222','131',2017,7,20),
('222','132',2017,7,21),
('222','133',2017,7,22),
('222','134',2017,7,23),
('222','135',2017,7,24),
('222','136',2017,7,25),
('222','137',2017,7,26),
('222','138',2017,7,27),
('222','139',2017,7,28),
('222','140',2017,7,29),
('222','141',2017,7,30),
('222','142',2017,7,31),
('222','143',2017,9,31);
select * from driver_info;
select * from driver_collect;
-- 近2017.07.01-2017.07.31完单大于30单的司机姓名及电话
select d1.driver_name,driver_phone
from driver_info d1
left join driver_collect d2 on d1.driver_id=d2.driver_id
where d_year='2017' and d_month='7'
group by d1.driver_id
having count(distinct order_id)>30;
select driver_name,driver_phone
from driver_info
where driver_id in (select driver_id from driver_collect where d_year=2017 and d_month=7 group by driver_id having count(order_id)>30);
练习4
create database aaa;
use aaa;
create table app_list
(apply_date date,
loan_no varchar(10) primary key,
apply_prin int,
result varchar(10));
insert into app_list values
("2018-2-5","GM290144",10000,"pass"),
("2018-3-1","GM290937",10000,"reject"),
("2018-4-17","GM296833",8000,"pass"),
("2018-5-11","GM310938",6000,"pass"),
("2018-5-25","GM327400",15000,"reject"),
("2018-6-18","GM350939",1000,"pass"),
("2018-10-12","GM380936",12000,"pass"),
("2018-11-5","GM400940",20000,"reject"),
("2018-2-5","GM290140",10000,"pass"),
("2018-3-1","GM290938",10000,"pass"),
("2018-4-17","GM296843",8000,"pass"),
("2018-5-11","GM310939",6000,"pass"),
("2018-5-25","GM327401",15000,"pass"),
("2018-6-18","GM350966",1000,"pass"),
("2018-10-12","GM380976",12000,"pass"),
("2018-11-5","GM400949",20000,"pass"),
("2018-2-5","GM290114",10000,"pass"),
("2018-3-1","GM290923",10000,"reject"),
("2018-4-17","GM29571",8000,"pass"),
("2018-5-11","GM310928",6000,"pass"),
("2018-5-25","GM32411",15000,"reject"),
("2018-6-18","GM351939",1000,"pass"),
("2018-10-12","GM376936",12000,"pass"),
("2018-11-5","GM441940",20000,"pass");
select * from app_list;
-- 每天的审批通过率及审批通过的平均申请金额
select apply_date,sum(result='pass')/count(loan_no) 审批通过率,avg((result='pass')*apply_prin) 平均申请金额
from app_list
group by apply_date;
select * from app_list
where result='pass';
select apply_date,count(loan_no) from app_list
group by apply_date;
create table loan_list
(loan_date date,
loan_no varchar(15),
id_no varchar(25),
loan_prin int,
paid_principal int,
product_rate varchar(2),
overdue_days int);
insert into loan_list values
("2018-2-5","GM290144","1100001990",10000,8000,"A",null),
("2018-4-17","GM296833","5500001992",8000,1500,"D",11),
("2018-5-11","GM310938","2300001991",6000,5500,"D",null),
("2018-6-18","GM350939","4500001989",1000,0,"B",432),
("2018-4-18","GM296834","5100001992",6000,1500,"D",31),
("2018-4-20","GM296894","5100001982",60000,15000,"D",40),
("2018-3-20","GM296874","5100001987",13000,10000,"D",60);
select * from loan_list;
-- 2018年2-5月份，不同费率的放款笔数、放款金额、30天以上金额逾期率（剩余本金/放款金额）
select product_rate 费率,count(product_rate) 放款笔数,sum(loan_prin) 放款金额,
ifnull(sum((overdue_days>30)*(loan_prin-paid_principal))/sum(loan_prin),0) 30天以上金额逾期率
from loan_list
where year(loan_date)=2018 and month(loan_date) between 2 and 5
group by product_rate;
create table customer(id_no varchar(25),groupp varchar(25),age int);
insert into customer values
("1100001990","house",29),
("5500001992","creditcard",27),
("2300001991","creditcard",28),
("4500001989","creditcard",30),
("4500001988","house",31),
("5100001992","car",46),
("5100001982","car",35),
("5100001987","house",31);
select * from customer;
-- 所有放款客户中，不同客群类型的人数占比
select groupp, count(distinct l.id_no)/(select count(distinct id_no ) from loan_list) 人数占比
from loan_list l
left join customer c on c.id_no=l.id_no
group by groupp;
