driving_school-9-统计近三个月每个月取消课节的原因分布。
用户:
业务背景
这是某个驾校管理系统的数据库片段。业务流程大致如下:学员注册后选择课程和班级进行报名,下单支付后可约课。约课时可以选择具体时间和教练,若是实践课,会使用特定的车辆进行实践学习。完成课程后参加考试。
数仓表格
1 | -- 学员信息表 |
数据查询
统计近三个月每个月取消课节的原因分布。
任务描述
请将上述数据查询在给定的数仓表格之上实现为SQL语句。该SQL语句必须能在该数仓上执行并得到正确的结果。如果发现给定的数仓表格缺少SQL实现所需要的表和字段,请报告错误,给出原因,并终止任务。
注意,该数仓仅支持MySQL的SQL方言。
助手:
我们先对查询的目标结果做结构分析,然后对查询的计算方案展开分析,然后将计算方案拆解为子任务并依次实现,最后给出完整的SQL实现。
查询目标结果结构分析
我们假设查询的目标结果可以用一个数据表表示,我们从如下三个方面分析该数据表的结构。
- 行粒度是什么?
- 包含哪些行?是否有明确的范围要求?
- 包含哪些列?是已经存在的属性字段,还是需要计算的字段?
假设原因分布用原因取消的取消次数来刻画。
该查询目标结果的行粒度是月份+取消原因,其范围有明确的要求:“近三个月”。
该查询目标结果的列需要包含:“月份”、“取消原因”、“该月该原因取消的取消次数”。
其中“月份”、“取消原因”是数仓中已存在的属性字段。“该月该原因取消的取消次数”需要计算。
查询计算方案分析
我们尝试分析上述目标结果如何计算得到,将其计算过程拆解为一系列简单的子任务。计算方案分析一方面需要分析计算的逻辑和步骤顺序,一方面需要分析计算所需的输入字段和表格、以及表格之间的连接方式。
要统计“近三个月每个月某原因取消的取消次数”,我们需要筛选出“课节日期”在近三个月内且状态为’学员未赴约’或’教练未赴约’的课节记录,然后按照“月份”和“取消原因”进行分组,对每组进行计数即可。计算的输入需要用到课节表Lesson
。
子任务拆解与实现
基于上述分析,我们尝试将该数据查询的SQL实现拆解成一系列简单的子任务。其中后面的子任务可以使用前面的子任务的计算结果。每个子任务都可以用一个简单的SQL语句实现。这些SQL语句中没有嵌套子查询或者只有非常简单的嵌套子查询。为了SQL实现的简洁性,提取各剩余属性字段的工作会尽量合并到一个子任务中。我们会给子任务的结果起名字,方便后续依赖方引用。
按照这种思路,我们将计算方案拆解成如下子任务(按照拓扑序列出):
- 子任务1:计算“近三个月每个月某原因取消的取消次数”。所得即完整的查询结果。
下面依次实现各个子任务,所有子任务的SQL片段可以拼装出原查询完整的SQL实现。构造子任务的SQL实现时,需要遵守以下要求:
- 在写SQL之前,将计算逻辑和所用表格字段重述一遍。
- 后面的子任务若要使用前面子任务的结果,直接用名字引用,不要重复实现。
- SQL中不要给表取别名,尽量使用表的原名。大家熟悉原名,这样写SQL更容易理解。
- 构造SQL查询条件时,要考虑索引利用的有效性,尽量避免对字段做转换后再做比较。比如要表达“课节日期大于等于2022年”的查询条件,应使用
lesson_date>='2022-01-01'
,而不是YEAR(lesson_date)>='2022'
。
子任务1:计算“近三个月每个月某原因取消的取消次数”。所得即完整的查询结果。
如前文分析,要统计“近三个月每个月某原因取消的取消次数”,我们需要筛选出“课节日期”在近三个月内且状态为’学员未赴约’或’教练未赴约’的课节记录,然后按照“月份”和“取消原因”进行分组,对每组进行计数即可。计算的输入需要用到课节表Lesson
。
“课节日期”对应字段
Lesson.LessonDate
“课节状态代码”对应字段
Lesson.LessonStatusCode
SQL实现如下:
1 | SELECT DATE_FORMAT(LessonDate, '%Y-%m') AS Month, |
答案
使用WITH子句拼装各个子任务的SQL片段,并添加注释保证易读性,得到对原数据查询的完整的SQL实现:
1 | -- 子任务1:计算“近三个月每个月某原因取消的取消次数”。所得即完整的查询结果。 |