driving_school-6-根据学员的年龄、性别和报名课程类型进行市场细分，分析不同细分市场的规模和增长潜力。

用户：

业务背景

这是某个驾校管理系统的数据库片段。业务流程大致如下：学员注册后选择课程和班级进行报名，下单支付后可约课。约课时可以选择具体时间和教练，若是实践课，会使用特定的车辆进行实践学习。完成课程后参加考试。

数仓表格

-- 学员信息表
CREATE TABLE Student (
    StudentID INT COMMENT '学员ID；主键；自增',
    Name VARCHAR(100) COMMENT '姓名；例如：张三',
    IDNumber VARCHAR(50) COMMENT '身份证号；例如：510***********1234',
    Gender ENUM('男', '女', '其他') COMMENT '性别；值为：男/女/其他',
    Phone VARCHAR(20) COMMENT '联系电话；例如：138********',
    RegistrationDate DATE COMMENT '注册日期；格式为YYYY-MM-DD'
) COMMENT='学员信息表';

-- 教练信息表
CREATE TABLE Instructor (
    InstructorID INT COMMENT '教练ID；主键；自增',
    Name VARCHAR(100) COMMENT '姓名；例如：李四',
    Gender ENUM('男', '女', '其他') COMMENT '性别；值为：男/女/其他',
    Phone VARCHAR(20) COMMENT '联系电话；例如：139********',
    DrivingExperience INT COMMENT '驾龄；单位：年'
) COMMENT='教练信息表';

-- 班级类型表
CREATE TABLE ClassType (
    TypeID INT COMMENT '班级类型ID；主键；自增',
    TypeName VARCHAR(100) COMMENT '类型名称；例如："周末班", "集中培训班"',
    Description TEXT COMMENT '描述'
) COMMENT='班级类型表';

-- 课程信息表
CREATE TABLE Course (
    CourseID INT COMMENT '课程ID；主键；自增',
    CourseName VARCHAR(100) COMMENT '课程名称；如：F本-轻便摩托车、C2-自动挡小汽车',
    TheoryHours INT COMMENT '理论课时数',
    PracticeHours INT COMMENT '实践课时数'
) COMMENT='课程信息表';

-- 班级信息表
CREATE TABLE Class (
    ClassID INT COMMENT '班级ID；主键；自增',
    CourseID INT COMMENT '课程ID；外键：课程ID',
    ExpireDate DATE COMMENT '过期日期；格式为YYYY-MM-DD',
    ClassTypeID INT COMMENT '班级类型ID；外键：班级类型ID'
) COMMENT='班级信息表';

-- 课节
CREATE TABLE Lesson (
    LessonID INT COMMENT '课节ID；主键；自增',
    StudentID INT COMMENT '学员ID；外键：学员ID',
    ClassID INT COMMENT '班级ID；外键：班级ID',
    InstructorID INT COMMENT '教练ID；外键：教练ID',
    TheoryHours INT COMMENT '理论课时数',
    PracticeHours INT COMMENT '实践课时数'
    LessonStatusCode VARCHAR(31) COMMENT '课节状态代码；未开始/已完成/学员未赴约/教练未赴约'
    VehicleID INT COMMENT '车辆ID；外键：车辆ID',
    LessonDate DATE COMMENT '课节日期',
    LessonTime TIME COMMENT '课节时间',
    Rating INT COMMENT '学员打分；1-5分，5分满分',
    Feedback VARCHAR(255) COMMENT '学员反馈'
);

-- 学习报名记录表
CREATE TABLE Enrollment (
    EnrollmentID INT COMMENT '报名记录ID；主键；自增',
    StudentID INT COMMENT '学员ID；外键：学员ID',
    ClassID INT COMMENT '班级ID；外键：班级ID',
    EnrollmentDate DATE COMMENT '报名日期；格式为YYYY-MM-DD',
    CompletionStatus ENUM('未完成', '已完成', '进行中') COMMENT '完成状态；值为：未完成/已完成/进行中'
) COMMENT='学习报名记录表';

-- 车辆信息表
CREATE TABLE Vehicle (
    VehicleID INT COMMENT '车辆ID；主键；自增',
    LicensePlate VARCHAR(20) COMMENT '车牌号；例如：川A12345',
    Model VARCHAR(50) COMMENT '车型；例如：奥迪A4',
    PurchaseDate DATE COMMENT '购置日期；格式为YYYY-MM-DD',
    Status ENUM('使用中', '维修中', '闲置') COMMENT '状态；值为：使用中/维修中/闲置'
) COMMENT='车辆信息表';

-- 考试记录表
CREATE TABLE Exam (
    ExamID INT COMMENT '考试记录ID；主键；自增',
    StudentID INT COMMENT '学员ID；外键：学员ID',
    ClassID INT COMMENT '班级ID；外键：班级ID',
    ExamType ENUM('科目一', '科目二', '科目三', '科目四') COMMENT '考试类型；值为：科目一/科目二/科目三/科目四',
    ExamDate DATE COMMENT '考试日期；格式为YYYY-MM-DD',
    Score DECIMAL(5, 2) COMMENT '成绩；例如：85.50',
    Passed INT COMMENT '是否通过；1为通过，0为未通过'
) COMMENT='考试记录表';

-- 订单信息表
CREATE TABLE Order (
    OrderID INT COMMENT '订单ID；主键；自增',
    StudentID INT COMMENT '学员ID；外键：学员ID',
    ClassID INT COMMENT '班级ID；外键：班级ID',
    OrderDate DATE COMMENT '下单日期；格式为YYYY-MM-DD',
    TotalAmount DECIMAL(10, 2) COMMENT '总金额；例如：2000.00',
    Status ENUM('待支付', '已支付', '已取消') COMMENT '状态；值为：待支付/已支付/已取消'
) COMMENT='订单信息表';

-- 支付记录表
CREATE TABLE Payment (
    PaymentID INT COMMENT '支付记录ID；主键；自增',
    OrderID INT COMMENT '订单ID；外键：订单ID',
    PaymentDate DATE COMMENT '支付日期；格式为YYYY-MM-DD',
    Amount DECIMAL(10, 2) COMMENT '支付金额；例如：2000.00',
    PaymentMethod ENUM('现金', '信用卡', '在线支付') COMMENT '支付方式；值为：现金/信用卡/在线支付'
) COMMENT='支付记录表';

数据查询

根据学员的年龄、性别和报名课程类型进行市场细分，分析不同细分市场的规模和增长潜力。

任务描述

请将上述数据查询在给定的数仓表格之上实现为SQL语句。该SQL语句必须能在该数仓上执行并得到正确的结果。如果发现给定的数仓表格缺少SQL实现所需要的表和字段，请报告错误，给出原因，并终止任务。
注意，该数仓仅支持MySQL的SQL方言。

助手：

我们先对查询的目标结果做结构分析，然后对查询的计算方案展开分析，然后将计算方案拆解为子任务并依次实现，最后给出完整的SQL实现。

查询目标结果结构分析

我们假设查询的目标结果可以用一个数据表表示，我们从如下三个方面分析该数据表的结构。

行粒度是什么？
包含哪些行？是否有明确的范围要求？
包含哪些列？是已经存在的属性字段，还是需要计算的字段？

假设用客户数来衡量市场规模，用当前的市场规模相比前一年市场规模的增长率来衡量市场潜力

该查询目标结果的行粒度是市场，其范围没有明确的要求。

该查询目标结果的列需要包含：“年龄段”、“性别”、“课程类型名称”、“该市场的客户数”、“该市场当前的市场规模相比前一年市场规模的增长率”。

其中“年龄段”、“性别”、“课程类型名称”是数仓表格中已经存在的属性字段。“该市场的客户数”和“该市场当前的市场规模相比前一年市场规模的增长率”需要计算。

查询计算方案分析

我们尝试分析上述目标结果如何计算得到，将其计算过程拆解为一系列简单的子任务。计算方案分析一方面需要分析计算的逻辑和步骤顺序，一方面需要分析计算所需的输入字段和表格、以及表格之间的连接方式。

要分析不同市场的客户数和当前的市场规模相比前一年市场规模的增长率，我们需要先对学员进行市场细分，然后计算每个市场的客户数，再计算每个细分市场的当前的市场规模相比前一年市场规模的增长率，最后提取所需属性字段。

如何进行市场细分？

市场细分可以基于学员的年龄、性别和报名的课程类型进行。我们需要从学员信息表Student中提取学员的性别，从学员ID中截取出生日期计算年龄段，然后结合学习报名记录表Enrollment和班级信息表Class来确定学员报名的课程类型ID。计算的输入需要用到学员信息表Student，报名记录表Enrollment和班级信息表Class。

如何计算每个细分市场的客户数？

要计算每个细分市场的客户数，需要筛选各学员，按照年龄、性别和报名的课程类型进行分组，对学员ID聚合计数即可。计算的输入需要用到市场细分的表，可以复用上面的计算结果。

如何计算每个细分市场的当前的市场规模相比前一年市场规模的增长率？

市场增长潜力可以通过比较不同时间段内的市场客户数变化来评估。此处，市场增长潜力具体用该市场客户数较一年前的增长率表示。

如何计算一年前的每个市场客户数？

要计算一年前的每个市场客户数，需要筛选一年前报名的各学员，按照年龄、性别和报名的课程类型进行分组，对学员ID聚合计数即可。计算的输入需要用到市场细分的表，可以复用上面的计算结果。

如何计算市场客户数较一年前的增长率

“市场客户数较一年前的增长率”可以可按照以下公式计算：

“市场客户数较一年前的增长率” = （“目前该市场客户数”-“一年前的该市场客户数”） / “一年前的该市场客户数”

“目前该市场客户数”可复用上面的计算结果。

如何提取所需属性字段

“年龄段”、“性别”字段，在行范围计算中就会给出，无需再加考虑。

其余属性字段只有“课程类型名称”。“课程类型名称”仅存在于课程类型信息表Course中，需要在行范围计算完成后，通过课程类型ID左连接Course提取该字段。

子任务拆解与实现

基于上述分析，我们尝试将该数据查询的SQL实现拆解成一系列简单的子任务。其中后面的子任务可以使用前面的子任务的计算结果。每个子任务都可以用一个简单的SQL语句实现。这些SQL语句中没有嵌套子查询或者只有非常简单的嵌套子查询。为了SQL实现的简洁性，提取各剩余属性字段的工作会尽量合并到一个子任务中。我们会给子任务的结果起名字，方便后续依赖方引用。

按照这种思路，我们将计算方案拆解成如下子任务（按照拓扑序列出）：

子任务1：计算学员的年龄段。结果记为T1。
子任务2：计算学员的报名课程类型。结果记为T2。
子任务3：连接T1和T2，提取学员性别和学员报名时间，进行市场细分。结果记为T3。
子任务4：计算每个细分市场的客户数。结果记为T4。T4定义了查询结果的行范围。
子任务5：计算一年前每个细分市场的客户数。结果记为T5。
子任务6：T4左连接T5，处理null值，评估每个细分市场的当前的市场规模相比前一年市场规模的增长率，左连接Course提取“课程类型名称”。所得即完整的查询结果。

下面依次实现各个子任务，所有子任务的SQL片段可以拼装出原查询完整的SQL实现。构造子任务的SQL实现时，需要遵守以下要求：

在写SQL之前，将计算逻辑和所用表格字段重述一遍。
后面的子任务若要使用前面子任务的结果，直接用名字引用，不要重复实现。
SQL中不要给表取别名，尽量使用表的原名。大家熟悉原名，这样写SQL更容易理解。
构造SQL查询条件时，要考虑索引利用的有效性，尽量避免对字段做转换后再做比较。比如要表达“订单日期大于等于2022年”的查询条件，应使用order_date>='2022-01-01'，而不是YEAR(order_date)>='2022'。

子任务1：计算学员的年龄段。结果记为T1。

如前文分析，要计算学员的年龄段，我们可以从学员信息表Student中提取学员的出生日期，然后计算年龄，并将年龄分段:其中年龄小于18岁为少年，18到35岁为青年，36到60岁中年，60岁以上为老年。

“出生日期”对应字段Student.BirthDate

SQL实现如下：

SELECT StudentID,
       TIMESTAMPDIFF(YEAR, str_to_date(SUBSTR(IDNumber,7,8),'%Y%m%d'), CURDATE()) AS Age,
       CASE
           WHEN TIMESTAMPDIFF(YEAR, str_to_date(SUBSTR(IDNumber,7,8),'%Y%m%d'), CURDATE()) < 18 THEN '少年'
           WHEN TIMESTAMPDIFF(YEAR, str_to_date(SUBSTR(IDNumber,7,8),'%Y%m%d'), CURDATE()) BETWEEN 18 AND 35 THEN '青年'
           WHEN TIMESTAMPDIFF(YEAR, str_to_date(SUBSTR(IDNumber,7,8),'%Y%m%d'), CURDATE()) BETWEEN 36 AND 60 THEN '中年'
           ELSE '老年'
       END AS AgeGroup
FROM Student

子任务2：提取学员的报名课程类型。结果记为T2。

如前文分析，要提取学员的报名课程类型，我们需要用班级ID连接学习报名记录表Enrollment和班级信息表Class来获取学员报名的课程类型ID。

“班级ID”对应字段Class.ClassID
“课程类型ID”对应字段Class.CourseID

SQL实现如下：

SELECT Enrollment.StudentID,
       Class.CourseID
FROM Enrollment
JOIN Class
    ON Enrollment.ClassID = Class.ClassID

子任务3：连接T1和T2，提取学员性别和学员报名时间，进行市场细分。结果记为T3。

T1和T2通过StudentID连接,和学员信息表也通过StudentID连接。SQL实现如下：

  SELECT T1.StudentID,
         T1.AgeGroup,
         T2.CourseID,
         Student.Gender,
Student.RegistrationDate
  FROM T1
  JOIN T2
      ON T1.StudentID = T2.StudentID
  JOIN Student
      ON T1.StudentID = Student.StudentID

子任务4：计算每个细分市场的客户数。结果记为T4。T4定义了查询结果的行范围。

如前文分析，要计算每个细分市场的客户数，需要筛选各学员，按照年龄、性别和报名的课程类型进行分组，对学员ID聚合计数即可。计算的输入需要用到T3。

SQL实现如下：

SELECT AgeGroup,
       CourseName,
       Gender,
       COUNT(StudentID) AS MarketSize
FROM T3
GROUP BY AgeGroup, CourseName, Gender

子任务5：计算一年前每个细分市场的客户数。结果记为T5。

如前文分析，要计算一年前的每个市场客户数，需要筛选一年前报名的各学员，按照年龄、性别和报名的课程类型进行分组，对学员ID聚合计数即可。计算的输入需要用到T3。

SQL实现如下：

SELECT AgeGroup,
       CourseID,
       Gender,
       COUNT(StudentID) AS MarketSize
FROM T3
WHERE RegistrationDate <= DATE_SUB(CURDATE(), INTERVAL 1 YEAR) 
GROUP BY AgeGroup, CourseID, Gender

子任务6：T4左连接T5，处理null值，评估每个细分市场的当前的市场规模相比前一年市场规模的增长率，左连接`Course`提取“课程类型名称”。所得即完整的查询结果。

市场增长潜力可以通过比较不同时间段内的市场客户数变化来评估。此处，市场增长潜力具体用该市场客户数较一年前的增长率表示。如果去年该市场的客户数为null，将其置0。“课程类型名称”仅存在于课程类型信息表Course中，需要在行范围计算完成后，通过课程类型ID左连接Course提取该字段。SQL实现如下：

SELECT T4.AgeGroup,
       Course.CourseName,
       T4.Gender,
       T4.MarketSize,
       (T4.MarketSize - IFNULL(T5.MarketSize, 0)) / IFNULL(T5.MarketSize, 1) AS GrowthPotential
FROM T4
LEFT JOIN  T5
    ON T4.AgeGroup = T5.AgeGroup
    AND T4.CourseID = T5.CourseID
    AND T4.Gender = T5.Gender
LEFT JOIN Course ON T4.CourseID=Course.CourseID

答案

使用WITH子句拼装各个子任务的SQL片段，并添加注释保证易读性，得到对原数据查询的完整的SQL实现：

WITH T1 AS (
    -- 子任务1：计算学员的年龄段。结果记为T1。
    SELECT StudentID,
           TIMESTAMPDIFF(YEAR, str_to_date(SUBSTR(IDNumber,7,8),'%Y%m%d'), CURDATE()) AS Age,
           CASE
               WHEN TIMESTAMPDIFF(YEAR, str_to_date(SUBSTR(IDNumber,7,8),'%Y%m%d'), CURDATE()) < 18 THEN '少年'
               WHEN TIMESTAMPDIFF(YEAR, str_to_date(SUBSTR(IDNumber,7,8),'%Y%m%d'), CURDATE()) BETWEEN 18 AND 35 THEN '青年'
               WHEN TIMESTAMPDIFF(YEAR, str_to_date(SUBSTR(IDNumber,7,8),'%Y%m%d'), CURDATE()) BETWEEN 36 AND 60 THEN '中年'
               ELSE '老年'
           END AS AgeGroup
    FROM Student
),
T2 AS (
    -- 子任务2：计算学员的报名课程类型。结果记为T2。
    SELECT Enrollment.StudentID,
           Class.CourseID
    FROM Enrollment
    JOIN Class
        ON Enrollment.ClassID = Class.ClassID
),
T3 AS (
    -- 子任务3：连接T1和T2，提取学员性别和学员报名时间，进行市场细分。结果记为T3。
    SELECT T1.StudentID,
           T1.AgeGroup,
           T2.CourseID,
           Student.Gender,
					 Student.RegistrationDate
    FROM T1
    JOIN T2
        ON T1.StudentID = T2.StudentID
    JOIN Student
        ON T1.StudentID = Student.StudentID
),
T4 AS (
    -- 子任务4：计算每个细分市场的客户数。结果记为T4。T4定义了查询结果的行范围。
    SELECT AgeGroup,
           CourseID,
           Gender,
           COUNT(*) AS MarketSize
    FROM T3
    GROUP BY AgeGroup, CourseID, Gender
),
T5 AS (
    -- 子任务5：计算一年前每个细分市场的客户数。结果记为T5。
    SELECT AgeGroup,
           CourseID,
           Gender,
           COUNT(StudentID) AS MarketSize
    FROM T3
    WHERE RegistrationDate <= DATE_SUB(CURDATE(), INTERVAL 1 YEAR) 
    GROUP BY AgeGroup, CourseID, Gender
)
-- 子任务6：T4左连接T5，处理null值，评估每个细分市场的当前的市场规模相比前一年市场规模的增长率，左连接`Course`提取“课程类型名称”。结果记为T6。所得即完整的查询结果。
SELECT T4.AgeGroup,
       Course.CourseName,
       T4.Gender,
       T4.MarketSize,
       (T4.MarketSize - IFNULL(T5.MarketSize, 0)) / IFNULL(T5.MarketSize, 1) AS GrowthPotential
FROM T4
LEFT JOIN  T5
    ON T4.AgeGroup = T5.AgeGroup
    AND T4.CourseID = T5.CourseID
    AND T4.Gender = T5.Gender
LEFT JOIN Course ON T4.CourseID=Course.CourseID

用户：

业务背景

数仓表格

数据查询

任务描述

助手：

查询目标结果结构分析

查询计算方案分析

如何进行市场细分？

如何计算每个细分市场的客户数？

如何计算每个细分市场的当前的市场规模相比前一年市场规模的增长率？

如何计算一年前的每个市场客户数？

如何计算市场客户数较一年前的增长率

如何提取所需属性字段

子任务拆解与实现

子任务1：计算学员的年龄段。结果记为T1。

子任务2：提取学员的报名课程类型。结果记为T2。

子任务3：连接T1和T2，提取学员性别和学员报名时间，进行市场细分。结果记为T3。

子任务4：计算每个细分市场的客户数。结果记为T4。T4定义了查询结果的行范围。

子任务5：计算一年前每个细分市场的客户数。结果记为T5。

子任务6：T4左连接T5，处理null值，评估每个细分市场的当前的市场规模相比前一年市场规模的增长率，左连接Course提取“课程类型名称”。所得即完整的查询结果。

答案

子任务6：T4左连接T5，处理null值，评估每个细分市场的当前的市场规模相比前一年市场规模的增长率，左连接`Course`提取“课程类型名称”。所得即完整的查询结果。