postgresql - 如何从一行中对先前非规范化的数据进行分组

Question

我有一张包含教师开设的类(class)的表格，我想获取教学天数并按年份和教师身份拆分这些类(class)。

该表包含以下字段:

id  teacher_id   course_name  course_date  course_duration  teacher_status
--------------------------------------------------------------------------
1   Teacher_01   Course_AA    2012-02-01   2                volunteer
2   Teacher_02   Course_BB    2012-02-01   7                employee
3   Teacher_03   Course_BB    2013-02-01   7                contractor
4   Teacher_01   Course_AA    2014-02-01   2                paid volunteer
5   Teacher_04   Course_AA    2014-06-01   2                paid volunteer

教师可以在各种状态下开设类(class):志愿者、付费志愿者、承包商、雇员等。给定教师的状态会随着时间而改变。类(class)持续时间以天数表示。

我已经可以收集教师授课天数的总和，按状态划分。这是由

SELECT
    teacher_status,
    sum(course_duration)  AS "Taught days"
FROM
    my_table
GROUP BY
    teacher_status
;

但数据未规范化，不同的状态家族混合在一起。所以我想收集相同的信息(教学天数)拆分:

• 按 3 种身份划分:志愿者、付费志愿者、所有其他身份，
• 按年计算。

预期的是:

Year    Teacher_status      Taught_days
---------------------------------------
2012    volunteer           2
2012    employee            7
2013    contractor          7
2014    paid volunteer      4

我尝试了聚合函数、GROUP BY/HAVING/ROLLUP 语句的各种组合，但没有成功。我该如何实现？

Answer 1

您需要选择 complex expression然后 GROUP BY ，而不只是按原始列值。您可以重复该表达式，或者在 Postgres 中也引用列别名:

SELECT
    EXTRACT(year FROM course_date) as year,
    (CASE teacher_status
       WHEN 'volunteer' THEN 'volunteer'
       WHEN 'paid volunteer' THEN 'paid'
       ELSE 'other'
    END) AS status,
    SUM(course_duration) AS "Taught days"
FROM
    my_table
GROUP BY
    year,
    status;