sql - 如何在 Ruby on Rails 迁移中使用 'gaps' 修复列的​​顺序

Question

我有一个 Ruby on Rails 应用程序(Rails 版本:5.1.1 和 Ruby 版本 2.3.1)。如果重要的话也可以使用 PostgreSQL。我有一个映射表的数据完整性问题，该映射表将学生映射到选定的类(class)，其中名为 ranking 的列错误地在排名中有“差距”，需要固定为顺序(每个学生)没有任何间隙。

示例:假设我有一个名为 student_course_rankings 的表，学生可以在其中选择多门类(class)并根据他们最喜欢的类(class)对它们进行排名。

(student_id, course_id, ranking) 有唯一约束，因此学生不能选择同一门类(class)两次。

ranking 列只有一个 NOT NULL 约束，但没有其他约束，并且是 integer 类型。

在 Ruby 迁移中执行此操作的最佳方法是什么？

class FixStudentCourseRankings < ActiveRecord::Migration[5.1]
  def change

    # Deletes duplicate courses (keeps the first)
    StudentCourse.where.not(
      id: StudentCourse.group(:course_id, :student_id).pluck('min(student_courses.id)')
    ).delete_all

    # Adds unique constraint so students can't accidentally select the same course more than once
    add_index :student_courses, [:course_id, :student_id, :ranking], :unique => true

    # HERE: Fix ranking order???
  end
end

下面是我的表格的简化版本以及两个学生目前的样子。

 id | student_id | course_id | ranking 
----+------------+-----------+---------
  1 |          1 |         2 |       1
  2 |          1 |         3 |       2
  3 |          1 |         5 |       4
  4 |          1 |         9 |       5
  5 |          1 |         6 |       6
  6 |          2 |         3 |       2
  7 |          2 |         6 |       4
  8 |          2 |         5 |       5

我希望它看起来像什么:

 id | student_id | course_id | ranking 
----+------------+-----------+---------
  1 |          1 |         2 |       1
  2 |          1 |         3 |       2
  3 |          1 |         5 |       3
  4 |          1 |         9 |       4
  5 |          1 |         6 |       5
  6 |          2 |         3 |       1
  7 |          2 |         6 |       2
  8 |          2 |         5 |       3

如有任何帮助，我们将不胜感激!

Answer 1

PostgreSQL 有一些 window finctions用于有/无间隙的排名。你需要一个没有间隙的 - ROW_NUMBER :

class FixStudentCourseRankings < ActiveRecord::Migration[5.1]
  def change

    # Deletes duplicate courses (keeps the first)
    StudentCourse.where.not(
      id: StudentCourse.group(:course_id, :student_id).pluck('min(student_courses.id)')
    ).delete_all

    execute <<~SQL
      UPDATE student_courses
      SET ranking = ranked.ranking
      FROM (
        SELECT
          id,
          ROW_NUMBER() OVER(
            PARTITION BY student_id
            ORDER BY id ASC
          ) AS ranking
        FROM student_courses
      ) ranked
      WHERE student_courses.id = ranked.id
    SQL

    # Adds unique constraint so students can't accidentally select the same course more than once
    add_index :student_courses, %i[course_id student_id ranking], unique: true
  end
end

Db-Fiddle