一、背景说明
在移动运营商的业务中，用户会根据自己的需求选择不同的套餐，并且套餐通常有一定的使用期限。当套餐即将到期时，运营商会向用户发送应续订套餐的请求，用户可能会选择续订或不续订。通过计算用户的套餐续订率，可以了解用户对现有套餐的满意度和忠诚度，从而为运营商制定营销策略、优化套餐内容和服务提供数据支持。
二、问题描述
请编写一个SQL查询来查找每个用户的套餐续订率。其中，用户的“套餐续订率”是“成功续订套餐”的数量除以“应续订套餐的请求总数”，没有应续订套餐请求的用户的套餐续订率为 0。
本次以两种不同数据库进行分析和用例讲解，分别是梧桐数据库，oracle 。
三、表结构说明
- 梧桐数据库建表语句
简单的客户注册与套餐续订表主要字段。
create table customer_signups (customer_id int primary key,time_stamp date not null
);create table subscription_confirm (customer_id int,time_stamp date,response char(3),primary key (customer_id, time_stamp)
);
2.oracle建表语句
简单的客户注册与套餐续订表主要字段。
create table customer_signups (customer_id number primary key,time_stamp date
);create table subscription_confirm (customer_id number,time_stamp date,response char(3),primary key (customer_id, time_stamp),foreign key (customer_id) references customer_signups(customer_id)
);
四、表数据插入
梧桐数据库、oracle的insert插入语句基本一致，下面只写梧桐数据库中的insert语句来统一代表。
insert into customer_signups (customer_id, time_stamp)
values (1, '2023-01-01'),(2, '2023-01-02'),(3, '2023-01-03');insert into subscription_confirm (customer_id, time_stamp, response)
values (1, '2023-01-30', 'yes'),(1, '2023-02-28', 'yes'),(2, '2023-01-31', 'no'),(2, '2023-02-28', 'yes'),(3, '2023-01-30', 'no');
五、sql实现思路分解
1、计算续订次数和总请求次数，通过连接两个表来获取每个用户的续订信息。这里我们会用到左连接（LEFT JOIN）以确保即使某些用户没有续订记录也能出现在结果中。 2、计算续订率，有了基本的续订次数和总请求次数后，我们需要计算续订率（将续订次数除以总请求次数得到）。需要注意的是，如果总请求次数为 0，则续订率为 0。
六、sql实现
梧桐数据库实现：
select a.customer_id,round(sum(case when b.response = 'yes' then 1.00 else 0.00 end) / count(a.customer_id), 2) as confirmation_rate
from customer_signups a
left join subscription_confirm b on a.customer_id = b.customer_id
group by a.customer_id;
oracle实现：
with temp as (
select cs.customer_id,count(case when sc.response = 'yes' then 1 end) as renewals,count(sc.response) as total_requests,case when count(sc.response) = 0 then 0else count(case when sc.response = 'yes' then 1 end) * 1.0 / count(sc.response)end as confirmation_rate
from customer_signups cs
left join subscription_confirm sc on cs.customer_id = sc.customer_id
group by cs.customer_id
)
select customer_id,round(confirmation_rate,2) as confirmation_rate
from temp;
七、解释sql每个部分的功能
梧桐数据库实现
SELECT 子句：
a.customer_id: 选择 customer_signups 表中的 customer_id 字段。
sum(case when b.response = 'yes' then 1.00 else 0.00 end): 使用 case 语句统计续订次数,如果 subscription_confirm 表中的 response 字段值为 'yes'，则计数为 1；否则计数为 0。sum 函数对所有符合条件的记录求和。
count(b.response): 统计 subscription_confirm 表中所有记录的数量，即总请求次数。
round函数：用来四舍五入保存套餐续订率的数值。
FROM 子句：
customer_signups a: 将 customer_signups 表别名为 a,为主表。
LEFT JOIN 子句：
left join subscription_confirm b on a.customer_id = b.customer_id: 将 subscription_confirm 表别名为 b 并与 customer_signups 表进行左连接。左连接确保即使某些用户没有续订记录也能出现在结果中。
GROUP BY 子句：
group by a.customer_id: 按 customer_id 分组数据，以便为每个用户计算续订次数和总请求次数。
Oracle 实现 WITH 子句：
with temp as (...): 创建一个临时表 temp，用于存储中间结果。
SELECT 子句：
cs.customer_id: 选择 customer_signups 表中的 customer_id 字段。
count(case when sc.response = 'yes' then 1 end) as renewals: 使用 case 语句统计续订次数。如果 subscription_confirm 表中的 response 字段值为 'yes'，则计数为 1。count 函数对所有符合条件的记录求和。
count(sc.response) as total_requests: 统计 subscription_confirm 表中所有记录的数量，即总请求次数。
FROM 子句：
customer_signups cs: 将 customer_signups 表别名为 cs。
LEFT JOIN 子句：
left join subscription_confirm sc on cs.customer_id = sc.customer_id: 将 subscription_confirm 表别名为 sc 并与 customer_signups 表进行左连接。
GROUP BY 子句：
group by cs.customer_id: 按 customer_id 分组数据。
最终 SELECT 子句：
select customer_id, round(confirmation_rate,2) as confirmation_rate from temp: 从临时表 temp 中选择 customer_id 和 confirmation_rate 列作为输出结果。
author:chenhui
