sql - 有效地在数据库中存储项目位置(用于订购)

Question

场景:

有一个用户拥有的电影数据库，电影显示在一个名为“我的电影”的页面上，电影可以按照用户想要的顺序显示。例如位置#1 的“搏击俱乐部”，位置#3 的“驾驶”等等。

显而易见的解决方案是为每个项目存储一个位置，例如:

movieid, userid, position
1 | 1 | 1
2 | 1 | 2
3 | 1 | 3

然后在输出数据的时候是按照位置排序的。此方法适用于输出但在更新时存在问题:项目的位置所有其他位置都需要更新，因为位置是相对的。如果电影 #3 现在位于位置编号 2，则电影 #3 现在需要更新到位置 #2。如果数据库包含 10,000 部电影，并且一部电影从位置 #1 移动到位置 #9999，那么将近 10,000 行要更新!

我唯一的解决方案是单独存储位置，而不是为每个项目位置设置一个单独的字段，它只是一个大的位置数据转储，这些位置在运行时获取并与每个项目相关联(json、xml 等)，但是感觉...效率低下，因为不能留下数据库来进行排序。

我总结的问题:在便于获取和更新的列表中存储项目位置的最有效方法是什么？

Answer 1

2022 年 8 月:请注意，以下内容存在缺陷，在将电影移至列表下方时不起作用。我已经发布了 a new answer here这解决了这个问题。

如果您结合使用用户将电影放在给定位置的位置和时间戳，而不是试图保持实际位置，那么您可以实现一种相当简单的选择和更新数据的方法。例如;一组基本数据:

create table usermovies (userid int, movieid int, position int, positionsetdatetime datetime)

insert into usermovies (userid, movieid, position, positionsetdatetime)
values (123, 99, 1, getutcdate())
insert into usermovies (userid, movieid, position, positionsetdatetime)
values (123, 98, 2, getutcdate())
insert into usermovies (userid, movieid, position, positionsetdatetime)
values (123, 97, 3, getutcdate())
insert into usermovies (userid, movieid, position, positionsetdatetime)
values (123, 96, 4, getutcdate())
insert into usermovies (userid, movieid, position, positionsetdatetime)
values (123, 95, 5, getutcdate())
insert into usermovies (userid, movieid, position, positionsetdatetime)
values (123, 94, 6, getutcdate())

insert into usermovies (userid, movieid, position, positionsetdatetime)
values (987, 99, 1, getutcdate())
insert into usermovies (userid, movieid, position, positionsetdatetime)
values (987, 98, 2, getutcdate())
insert into usermovies (userid, movieid, position, positionsetdatetime)
values (987, 97, 3, getutcdate())
insert into usermovies (userid, movieid, position, positionsetdatetime)
values (987, 96, 4, getutcdate())
insert into usermovies (userid, movieid, position, positionsetdatetime)
values (987, 95, 5, getutcdate())
insert into usermovies (userid, movieid, position, positionsetdatetime)
values (987, 94, 6, getutcdate())

如果您使用如下查询来查询用户的电影:

;with usermovieswithrank as (
  select userid
  , movieid 
  , dense_rank() over (partition by userid order by position asc, positionsetdatetime desc) as movierank
  from usermovies
)
select * from usermovieswithrank where userid=123 order by userid, movierank asc

然后你会得到预期的结果:

USERID  MOVIEID     MOVIERANK
123     99          1
123     98          2
123     97          3
123     96          4
123     95          5
123     94          6

要移动其中一部电影的排名，我们需要更新 position 和 positionsetdatetime 列。例如，如果用户 ID 123 将电影 95 从排名 5 移动到排名 2，那么我们这样做:

update usermovies set position=2, positionsetdatetime=getutcdate() 
where userid=123 and movieid=95 

结果如下(更新后使用上面的 SELECT 查询):

USERID  MOVIEID     MOVIERANK
123     99          1
123     95          2
123     98          3
123     97          4
123     96          5
123     94          6

然后如果用户 ID 123 将电影 96 移动到排名 1:

update usermovies set position=1, positionsetdatetime=getutcdate()
where userid=123 and movieid=96 

我们得到:

USERID  MOVIEID     MOVIERANK
123     96          1
123     99          2
123     95          3
123     98          4
123     97          5
123     94          6

当然，您最终会在 usermovies 表中得到重复的 position 列值，但是使用此方法您永远不会显示该列，您只需将它与 positionsetdatetime 一起使用以确定每个用户的排序排名和排名您确定的是真实位置。

如果在某些时候您希望位置列在不引用 positionsetdatetime 的情况下正确反射(reflect)电影排名，您可以使用上面选择查询中的 movierank 来更新 usermovies 位置列值，因为它实际上不会影响确定的电影排行榜。