定级评测

SELECT
        *
FROM
(
(
SELECT
        a.`完成测评月份` as '完成测评月份',
        -- a.`当前阶段` as '当前阶段_降阶',
        a.`分数段_降阶`,
        count(a.`当前阶段`) as '数量_降阶'
FROM
(
SELECT
        dj.当前阶段,
        dj.完成测评月份,
        case when dj.平均分 < 60 then '0-60' when dj.平均分 > 60 and 
        dj.平均分 < 80 then '60-80' when dj.平均分 > 80 and dj.平均分 <=100 then '80-100'
        end as '分数段_降阶'
FROM
        wd_new_djcp_0927 dj
WHERE
        dj.升降标识 = '降阶'
) a
WHERE
        a.`完成测评月份` in (6,7,8)
        AND a.`分数段_降阶` is not null
GROUP BY
        a.`完成测评月份`,a.`分数段_降阶`
) x
RIGHT JOIN
(
SELECT
        a.`完成测评月份` as '完成测评月份',
        -- a.`当前阶段` as '当前阶段_不变',
        a.`分数段_不变`,
        count(a.`当前阶段`) as '数量_不变'
FROM
(
SELECT
        dj.当前阶段,
        dj.完成测评月份,
        case when dj.平均分 < 60 then '0-60' when dj.平均分 > 60 and 
        dj.平均分 < 80 then '60-80' when dj.平均分 > 80 and dj.平均分 <=100 then '80-100'
        end as '分数段_不变'
FROM
        wd_new_djcp_0927 dj
WHERE
        dj.升降标识 = '不变'
) a
WHERE
        a.`完成测评月份` in (6,7,8)
        AND a.`分数段_不变` is not null
GROUP BY
        a.`完成测评月份`,a.`分数段_不变`
) y
ON x.完成测评月份 = y.完成测评月份  and x.分数段_降阶 = y.分数段_不变

)

正课出席率


SELECT
        a.*,
        a.`实际出席人数`/a.`应出席人数` as '正课出席率'
FROM
(
SELECT
        zk.上课时间月,
        zk.阶段,
        count(zk.课节ID) as '应出席人数',
        sum(case when zk.考勤 = '出席' then 1 else 0 end) as '实际出席人数'
FROM
        wd_lesson_zk_0924 zk
WHERE
        zk.阶段 NOT IN ('S2','S4','100以内','20以内','L','L1','L2')
GROUP BY
        zk.上课时间月,zk.阶段
) a        

满班率

SELECT
        a.*,
        a.`人数满班班级数`/a.`总班级数` as '满班率'
FROM
(
SELECT
        month(mbl.开班时间) as '月份',
        mbl.班级容量,
        sum(case when mbl.满班率 = '100.00%' then 1 else 0 end ) as '人数满班班级数',
        count(mbl.阶段) as '总班级数'
FROM
        wd_mbl_0924 mbl
GROUP BY
        month(mbl.开班时间),mbl.班级容量
) a

DEMO试听成交

-- 按月 级别 demo课 统计新demo课的试听课次、成交课次

SELECT
        上课月份,
        级别,
        课程名称,
        COUNT(课节ID) as '试听课次',
        SUM(CASE WHEN 是否成交 = '成交' THEN 1 ELSE 0 END) as '成交课次'
FROM
        wd_lesson_demo_0301_0922
WHERE
        级别 IN ('S1','S2','S3','S4','S5')
        AND 课程名称 IN ('甜蜜订单','火车乐园新','皮皮虎的礼物','博物馆探秘','新智闯悬浮岛','新美丽的装饰','魔法集市奇遇记','逃离暴风雨','家庭聚会')
GROUP BY
        上课月份,级别,课程名称

线上作业提交率、完成率

SELECT
        MONTH(wlp.`上课时间`) as '月份',
        SUM(CASE WHEN wlp.`考勤` = '签到' THEN 1 ELSE 0 END) as '应提交课次',
        SUM(CASE WHEN wlp.`是否提交线上作业` = '是'  THEN 1 ELSE 0 END) as '实际提交课次',
        SUM(CASE WHEN wlp.`是否提交线上作业` = '是' AND wlp.`是否完成在线作业` = '完成' THEN 1 ELSE 0 END) as '实际完成课次'
FROM
        wd_lesson_plan wlp
GROUP BY
        MONTH(wlp.`上课时间`)

文档更新时间: 2019-10-12 09:53   作者:admin