SQL-DAY 7（SQL查询语句的应用案例：汽车新销售）

---

一、涉及业务介绍

  某头部汽车新零售企业立足下沉市场，通过网络渠道和品牌广告获取大量汽车销售线索，并借助大量地面销售在线下门店完成客户成交孵化，该企业最终依靠自建供应链建立的护城河来赚取采购差价，同时也通过给客户提供低首付金融租赁产品来赚取金融费用。该公司商业分析师需要从数据层面去驱动投放团队去做效率优化，以助力业务正常健康发展。
头部汽车：top级，行业排名靠前
下沉市场：三四线城市及以下消费市场
网络渠道：汽车之家、懂车帝、易车等
品牌广告：微信朋友圈、微博、抖音
低首付金融租赁产品：车贷

二、涉及业务数据及字典

数据库：business_sample_car_retail
涉及表: clue_day,order_day

线索表clue_day

column	Type	Comment
clue_id	bigint	线索id
city_id	bigint	用户城市id(线索产生时的城市)
deal_city_id	bigint	交易城市id
district_id	bigint	区县id
cc_id	bigint	客户id
guid	varchar	设备id
customer_phone	varchar	客户手机号(密文)
ca_s	varchar	一级渠道(网络渠道、品牌广告)
ca_n	varchar	二级渠道（具体平台抖音、微博、微信等）
ca_s_new	varchar	归因后一级渠道（记录最近一次渠道信息）
ca_n_new	varchar	归因后二级渠道
clue_platform	varchar	电销侧来源平台
cainfo	varchar	附加信息
platform	varchar	（手机wap端/pc端）
newcar_product_type	integer	新车产品类型，0-4s店带看，1-付一成，2是回访，3是在线客服，4是400，-250战败工单
is_high_quality	integer	是否高质量线索标识
is_valid	integer	线索是否有效
is_new	integer	是否新线索
clue_created_at	varchar	创建时间

成交订单表order_day

column	Type	Comment
order_id	int	订单id
customer_name	string	客户姓名
phone	string	电话，加密
city_id	int	城市ID
brand_id	int	品牌ID
chexi_id	int	车系ID
chexing_id	int	车型ID
sku_id	int	sku_id
vin	string	车辆vin码
engine_num	string	发动机号
hegezheng_pic	string	合格证照片
apply_id	string	金融支付id
color	int	购买车型颜色
created_at	string	创建时间（成交订单时间）
clue_id	int	线索ID
saler_id	int	销售id
downpayment_paid_time	string	首付完成时间
finished_time	string	订单完成时间

创建数据库、数据表

#创建数据库
create database business_sample_car_retail character set utf8;

#创建数据表，clue_day
create table clue_day (
	clue_id int (8),
	city_id int (3),
	deal_city_id int (3),
	cc_id int (9),
	guid varchar (36),
	customer_phone varchar (26),
	ca_s varchar (18),
	ca_n varchar (13),
	ca_s_new varchar (12),
	ca_n_new varchar (13),
	clue_platform varchar (23),
	cainfo varchar (318),
	platform varchar (5),
	newcar_product_type int (1),
	is_high_quality int (1),
	is_valid int (1),
	is_new int (1),
	clue_created_at datetime,
	district_id int (4)
);

#创建数据表，order_day
create table order_day(
	created_at datetime,
	order_id int(5),
	clue_id int(10),
	city_id int(5),
	chexing_id int(10),
	sku_id int(5),
	vin varchar(46),
	engine_num varchar(26)
);

三、需求与代码实现

• 需求1：查询线索(二级渠道jdsc)后续转化成交车型详情
  解析：线索和渠道 --> clue表
      成交购买的车型 --> order表
      需要进行clue表和order表的关联查询（使用内连接进行关联查询）
  条件：二级渠道jdsc
  返回结果：线索id、线索所属渠道名称、购买的车型

#隐式内连接
select c.clue_id,ca_n,chexing_id
from clue_day c,order_day o
where c.clue_id = o.clue_id and ca_n = 'jdsc';

#显式内连接
select c.clue_id,ca_n,chexing_id
from clue_day c inner join order_day o on c.clue_id = o.clue_id
where ca_n = 'jdsc';

• 需求2：统计所有渠道（按照二级渠道）的转化率
  解析：转化率=成单数（order表）/线索数（clue表）
      渠道 --> clue表

#左外连接
select ca_n,count(o.clue_id)/count(c.clue_id) '转化率'
from clue_day c left join order_day o on c.clue_id = o.clue_id
group by ca_n;

• 需求3：查询各城市线索数并计算所有城市线索总数
  解析：城市、线索

#方法一
select city_id,count(*) 
from clue_day 
group by city_id;
select count(clue_id) from clue_day;

#方法二
select city_id,count(clue_id) 
from clue_day 
group by city_id;
select count(clue_id) from clue_day;

#方法三（合并查询）
select city_id,count(clue_id) as clue_count from clue_day group by city_id
union all
select '总计' as city_id,count(clue_id) from clue_day;

• 需求4：找出优质渠道，作重点投入
  解析：优质渠道（以二级渠道为基准）
      该二级渠道内平均转化周期<整体平均转化周期
      该二级渠道的线索量>各二级渠道平均线索量
      转化周期 = 成单日期 - 线索创建日期

各二级渠道内平均转化周期，各二级渠道的线索量

select ca_n,
		avg(datediff(created_at,clue_created_at)),
		count(o.clue_id)
from clue_day c left join order_day o on c.clue_id = o.clue_id clue_num
where o.created_at is not null and c.clue_created_at is not null
group by ca_n;

各二级渠道内平均线索量,其中ceil()为向上取整

#方法一
select ceil(avg(clue_num)) from(
	select ca_n,
			avg(datediff(created_at,clue_created_at)),
			count(o.clue_id) clue_num
	from clue_day c left join order_day o on c.clue_id = o.clue_id 
	where o.created_at is not null and c.clue_created_at is not null
	group by ca_n
) a;
#方法二
#with··· as··· 在一个sql中提取公共的子查询
with ca_value as(
	select ca_n,
			avg(datediff(created_at,clue_created_at)),
			count(o.clue_id) clue_num 
	from clue_day c left join order_day o on c.clue_id = o.clue_id
	where o.created_at is not null and c.clue_created_at is not null
	group by ca_n
)
select ceil(avg(clue_num)) from ca_value;

整体线索平均转化周期

select avg(datediff(created_at,clue_created_at))
from clue_day c left join order_day o on c.clue_id = o.clue_id
where o.created_at is not null and c.clue_created_at is not null

需求查询,以上代码的整合

with ca_value as(
	select ca_n,
			avg(datediff(created_at,clue_created_at)) avg_time,
			count(o.clue_id) clue_num 
	from clue_day c left join order_day o on c.clue_id = o.clue_id
	where o.created_at is not null and c.clue_created_at is not null
	group by ca_n
)
select ca_n,avg_time from ca_value
# 该二级渠道内平均转化周期<整体平均转化周期
where 
	avg_time <(
		select avg(datediff(created_at,clue_created_at))
		from clue_day c left join order_day o on c.clue_id = o.clue_id
		where o.created_at is not null and c.clue_created_at is not null
	)
#该二级渠道的线索量>各二级渠道平均线索量
	and clue_num >(select ceil(avg(clue_num)) from ca_value);

---

扩展认识

数学函数

函数	作用
abs(x)	返回x的绝对值
floor(x),ceil(x),round(x)	floor向下取整,ceil向上取整,round四舍五入
rand()	返回0~1的随机数
pi()	返回圆周率的值
mod(x.y)	返回x除以y以后的余数

字符串函数

函数	作用
concat(s1,s2···)	将字符串拼接，连接为一个字符串
left(s,n)	返回左起，从字符串s开始的n个字符
trim(S)	移除掉字串中s的字头或字尾处空格
replace(s,s1,s2)	用字符串s2替代字符串s重的字符串s1
substring(s,n,len) , mid(s,n.len)	截取字符串s中第n个位置开始，长度为len的字符串
reverse(s)	将字符串s的顺序翻转过来

日期时间函数

函数	作用
curdate()	当前日期
curtime()	当前时间
now()	当前日期时间
month(curdate() )	获取月份

条件判断函数

函数	作用
if (expo,v1,v2)	表达式成立，执行v1,否则执行v2
case when	用于计算条件列表并返回多个可能结果表达式之一

其中，case when的代码格式

case when 条件1 then 结果1
	when 条件2 then 结果2
	when ···  then ···
else 结果n+1
end