现有下列三张表，创作者和粉丝关系表a，创作者和内容关系表b，粉丝在内容上的行为明细表c，现在想统计出粉丝CTR，请写出对应的SQL语句。 注：CTR为点击率，等于“总阅读次数总曝光次数”。 创作者和粉丝关系表 author_id fans_id create_date 332579 985035 202211 332579 849602 2022115 332579 952566 2022320 970382 930554 202261 970382 985035 2022923 960725 590742 20221110 960725 985035 2022126 960725 940672 2023112 960725 392056 2023210 创作者和内容关系表 author_id content_id 332579 790046624 332579 391056935 970382 486962456 970382 359325633 970382 899054463 960725 125225355 490626 738258392 490626 678329246 230563 239587593 粉丝在内容上的行为明细表 content_id fans_id show_num read_num like_num comment_num 790046624 985035 1 1 0 0 790046624 583951 1 1 0 0 391056935 123573 1 1 0 1 391056935 395621 1 1 0 1 391056935 985035 2 1 0 1 486962456 985035 1 1 1 1 486962456 838495 1 1 1 1 359325633 952566 2 0 0 0 899054463 952566 1 0 0 0 125225355 985035 1 1 1 0 输出示例 fans_ctr 0.8000
示例1

输入

drop table if exists a;
create table `a` (
author_id varchar(32) not null,
fans_id varchar(32) not null,
create_date varchar(32) not null
);
insert into a values ('332579','985035','2022/1/1');
insert into a values ('332579','849602','2022/1/15');
insert into a values ('332579','952566','2022/3/20');
insert into a values ('970382','930554','2022/6/1');
insert into a values ('970382','985035','2022/9/23');
insert into a values ('960725','590742','2022/11/10');
insert into a values ('960725','985035','2022/12/6');
insert into a values ('960725','940672','2023/1/12');
insert into a values ('960725','392056','2023/2/10');


drop table if exists b;
create table `b` (
author_id varchar(32) not null,
content_id varchar(32) not null
);
insert into b values ('332579','790046624');
insert into b values ('332579','391056935');
insert into b values ('970382','486962456');
insert into b values ('970382','359325633');
insert into b values ('970382','899054463');
insert into b values ('960725','125225355');
insert into b values ('490626','738258392');
insert into b values ('490626','678329246');
insert into b values ('230563','239587593');


drop table if exists c;
create table `c` (
content_id varchar(32) not null,
fans_id varchar(32) not null,
show_num int not null,
read_num int not null,
like_num int not null,
comment_num int not null
);
insert into c values ('790046624','985035',1,1,0,0);
insert into c values ('790046624','583951',1,1,0,0);
insert into c values ('391056935','123573',1,1,0,1);
insert into c values ('391056935','395621',1,1,0,1);
insert into c values ('391056935','985035',2,1,0,1);
insert into c values ('486962456','985035',1,1,1,1);
insert into c values ('486962456','838495',1,1,1,1);
insert into c values ('359325633','952566',2,0,0,0);
insert into c values ('899054463','952566',1,0,0,0);
insert into c values ('125225355','985035',1,1,1,0);

输出

fans_ctr
0.8000
加载中...