Oracle 12c：基于值组统计学生重复选课次数

Hey there! Let's work through this Oracle 12c problem together—you need to count how many times students have repeated equivalent courses, right? Like that example where student ID 1 took MATH 1020, MATH 101, and MATH 2015 (all equivalent) and you want to track those repeats.

First, let's clarify the table structures since you didn't share them explicitly—I'll go with common, logical schemas that fit your use case:

• Student_Courses (Table 1): I assume this has student_id (the student's unique ID) and course_code (the course they enrolled in). It might also have an enrollment date, but we won't need that unless you want to filter repeats by a specific time frame.
• Equivalent_Courses (Table 2): This should map each course to its equivalence group—columns like course_code and equivalence_group (could be an ID number or a descriptive name like 'MATH_FOUNDATIONAL' for your example courses).

Step 1: Link Enrollments to Equivalence Groups #

First, we need to connect each student's course enrollment to the group that course belongs to. We'll join the two tables on course_code to establish this link.

Step 2: Count Enrollments per Student per Group #

Next, we'll group the data by student and equivalence group to count how many times each student enrolled in courses from the same group. Here's the base query for that:

SELECT
  sc.student_id,
  ec.equivalence_group,
  COUNT(sc.course_code) AS total_enrollments
FROM
  Student_Courses sc
JOIN
  Equivalent_Courses ec ON sc.course_code = ec.course_code
GROUP BY
  sc.student_id,
  ec.equivalence_group

Step 3: Calculate Repeat Counts #

A "repeat" is any enrollment beyond the first one in the same group. So we'll take the total enrollments, subtract 1, and only include groups where the student enrolled more than once. Here's the full query:

SELECT
  student_id,
  equivalence_group,
  (total_enrollments - 1) AS repeat_count
FROM (
  -- Inner query to get total enrollments per student/group
  SELECT
    sc.student_id,
    ec.equivalence_group,
    COUNT(sc.course_code) AS total_enrollments
  FROM
    Student_Courses sc
  JOIN
    Equivalent_Courses ec ON sc.course_code = ec.course_code
  GROUP BY
    sc.student_id,
    ec.equivalence_group
) enrollment_summary
WHERE
  total_enrollments > 1
ORDER BY
  student_id,
  equivalence_group

Extra Customizations #

• If your equivalence groups use an ID instead of a descriptive name, just swap equivalence_group with equivalence_group_id in the query.
• Want to see which specific courses the student took in each group? Add LISTAGG(sc.course_code, ', ') WITHIN GROUP (ORDER BY sc.course_code) AS enrolled_courses to the inner query—this will list all equivalent courses the student enrolled in, separated by commas.
• Need a total of all repeats per student (not broken down by group)? Wrap the above query in another aggregation:

SELECT
  student_id,
  SUM(repeat_count) AS total_repeats_across_groups
FROM (
  -- The previous repeat count query here
) student_repeats
GROUP BY
  student_id

If your actual table structures have different column names or additional constraints, just let me know and we can tweak the query to fit your exact schema!

内容的提问来源于stack exchange，提问作者Withnoe