时间戳

首页 > 解决方案 > 按月分组查找外键多次出现的所有实例

sql - 按月分组查找外键多次出现的所有实例

问题描述

我对 SQL 不太熟悉，而且我的任务是坦率地说我不知道​​如何去做。

我只是将表格简化到只考虑必要字段的程度。表格如下所示。

Submission(course(string), student(foreign_key), date-submitted)
Student(id)

我需要做的是每月制作一个活跃学生表，每门课程的总数。活跃的学生是指每月提交超过 4 次的任何人。我只看特定的课程，所以我需要硬编码我需要的值，例如“CourseA”和“CourseB”

结果应该如下

month | courseA | CourseB | Total
------------------------------------------
03/2020    50       27        77
02/2020    25       12        37
01/2020    43       20        63

任何帮助将不胜感激

标签： sqlpostgresql

解决方案

您可以使用两个级别的聚合来做到这一点：首先按月、课程和学生（同时过滤提交超过 4 个的学生），然后按月（在旋转数据集时）：

select
    month_submitted,
    count(*) filter(where course = 'courseA') active_students_in_courseA,
    count(*) filter(where course = 'courseB') active_students_in_courseB,
    count(*) total
from (
    select 
        date_trunc('month', date_submitted) month_submitted,
        course,
        student_id,
        count(*) no_submissions
    from submission
    where course in ('courseA', 'courseB')
    group by 1, 2, 3
    having count(*) > 4
) t
group by 1

推荐阅读