【SQL】凭空创造临时表用于统计数据

知识点：用with +select xxx union all创建临时表

一、情景介绍

【情景介绍】

我是在在线教育公司工作，经常会计算一些学员的消完课率，计算率，一般都要考虑分母的取值。我们是采用分群统计，即每一个群进入了多少名学员，这是一个总数，作为分母。学习的记录，如果没有出勤，埋点取不到相关行为数据，便会没有记录。

/* 简化代码 */

select *

from 群学员信息表

left join 学习记录表 on 用户ID

统计总人数用群名单计数，统计各个关卡出勤，使用学习记录中的关卡号计数，未出勤没有数据。

这对于展示已有出勤的关卡是没问题的，但是如果要展示一个完整的数据，就不行。

打个比方说：总共有5个关卡，但是目前进度最快的学员出勤关卡为3，那么获取的数据源可能如下：

群学员	关卡号	出勤时间	课程ID	群ID
1001	1	2021-07-17 00:00:00	1	1
1001	2	2021-07-18 00:00:00	1	1
1001	3	2021-07-19 00:00:00	1	1
1002	1	2021-07-17 00:00:00	1	1
1002	2	2021-07-18 00:00:00	1	1
1003	1	2021-07-17 00:00:00	1	1
1004			1	1

注：群里学员1004未出勤。

 统计各关卡出勤人数和出勤率如下：

群学员总数	关卡号	出勤人数	出勤率	课程ID	群ID
4	1	3	3/4	1	1
4	2	2	2/4	1	1
4	3	1	1/4	1	1

需要等到有学员出勤第5关时，上面的关卡数据才会比较完整，但是如果想展示所有的关卡，之后等着学员出勤的时候，直接更新对应关卡的出勤数据要怎么做呢？展示结果示例如下：

群学员总数	关卡号	出勤人数	出勤率	课程ID	群ID
4	1	3	3/4	1	1
4	2	2	2/4	1	1
4	3	1	1/4	1	1
4	4	0	0	1	1
4	5	0	0	1	1

插图说明：

二、自建临时表

进入今天的主题：自行创建一个临时表，代码如下（课程-关卡号表）。

注：为了方便理解各步骤，采用了中文名称。

with 
课程-关卡号 as(
    select 1 as "关卡号", "1" as "课程ID" union all
    select 2 as "关卡号", "1" as "课程ID" union all
    select 3 as "关卡号", "1" as "课程ID" union all
    select 4 as "关卡号", "1" as "课程ID" union all
    select 5 as "关卡号", "1" as "课程ID"
)
select a.群ID, a.群学员ID, b.课程ID, b.关卡号, c.出勤时间
from 群学员信息表 as a
join 课程-关卡号 as b on b.课程ID=a.课程ID
left join 学习记录表 as c on c.群学员ID=a.群学员ID 
                       and c.关卡号=b.关卡号

然后再进行聚合即可，聚合的时候，为了保证每一行的群学员总数都是4，目前能想到的方法是聚合2步，一个聚合是关卡维度，一个聚合是从群维度。

此处提供一种解法：

with 
课程-关卡号 as(
    select 1 as "关卡号", "1" as "课程ID" union all
    select 2 as "关卡号", "1" as "课程ID" union all
    select 3 as "关卡号", "1" as "课程ID" union all
    select 4 as "关卡号", "1" as "课程ID" union all
    select 5 as "关卡号", "1" as "课程ID"
)
, 基本信息 as(
    select a.群ID, a.群学员ID, b.课程ID, b.关卡号, c.出勤时间
    from 群学员信息表 as a
    join 课程-关卡号 as b on b.课程ID=a.课程ID
    left join 学习记录表 as c on c.群学员ID=a.群学员ID 
                           and c.关卡号=b.关卡号
)
select ca.群ID, ca.关卡号, ca.关卡出勤人数, ga.群学员数, ga.课程id
    , ca.关卡出勤人数/ga.群学员数 as 出勤率
from (
    -- 聚合关卡号，得出出勤人数
    select 群ID,关卡号, count(出勤时间) 关卡出勤人数
    from 基本信息 i
    group by 群ID, 关卡号
    ) as 关卡号-出勤人数 ca
join (
    -- 聚合群ID和课程ID，得出总人数（群ID和课程ID是一对一关系，直接聚合群ID也可以得到一样的群总人数）
    select 群ID, 课程id, count(distinct a.群学员ID) 群学员数
    from 基本信息 i
    group by 群ID, 课程id
    ) as 群ID-总人数 ga on ga.群ID=ca.群ID

如果未出勤字段补 0，在最后一个select 的【关卡出勤人数】和【出勤率】加 if() 判断即可，如：

if(ca.关卡出勤人数 is not null, ca.关卡出勤人数, 0) as 关卡出勤人数

if(ca.关卡出勤人数 is not null, ca.关卡出勤人数/ga.群学员数, 0) as 关卡出勤率

最后的解法，如果大家有更好的解法，欢迎评论交流。。