Сохранение позиций позиций (для заказа) в базе данных эффективно

Ответ 1

Если вы используете комбинацию позиции и временной метки, которую пользователь ставит в заданную позицию, вместо того, чтобы пытаться поддерживать фактическое положение, вы можете достичь довольно простых средств как для SELECT, так и для ОБНОВЛЕНИЯ данных. Например; базовый набор данных:

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

Чтобы переместить одно из ранжирования фильмов, нам нужно обновить позиции и столбцы positionsetdatetime. Например, если userid 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

Затем, если userid 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, но с помощью этого метода вы никогда не увидите этот столбец, вы просто используете его вместе с positionsetdatetime, чтобы определить отсортированный рейтинг для каждого пользователя и ранг вы определяете реальную позицию.

Если в какой-то момент вы хотите, чтобы столбец позиции правильно отражал рейтинги фильмов без ссылки на positionsetdatetime, вы можете использовать movierank из запроса select выше, чтобы обновить значение столбца позиции usermovies, так как это фактически не повлияет на определенные рейтинг фильмов.

Ответ 2

Я боролся с тем, что лучше всего делать с этой ситуацией, и пришел к пониманию, что ПО FAR лучшим решением является список/массив фильмов в том порядке, в котором вы хотите их, например;

userId, moviesOrder

1: [4,3,9,1...]

Очевидно, вы сериализуете свой массив.

', который чувствует... неэффективным'?

считают, что у пользователя был список из 100 фильмов. Поиск по позиции будет одним запросом базы данных, преобразованием строки в массив, а затем movieOrder [index]. Возможно, медленнее, чем прямой поиск в DB, ​​но все еще очень быстро.

OTOH, подумайте, измените ли вы заказ;

с позицией, хранящейся в db, вам нужно до 100 изменений строки по сравнению с сращиванием массива. Идея связанного списка интересна, но не работает как представленная, сломала бы все, если бы один элемент не удался, и выглядит намного медленнее. Другие идеи, такие как устранение пробелов, использование float являются работоспособными, хотя беспорядок, и склонны к отказу в какой-то момент, если вы не GC.

Кажется, должен быть лучший способ сделать это в SQL, но на самом деле этого не происходит.

Ответ 3

Сохраните стиль связанного списка заказа. Вместо сохранения абсолютной позиции сохраните идентификатор предыдущего элемента. Таким образом, любое изменение требует только обновления двух строк.

movieid | userid  | previousid
   1    |    1    | 
   2    |    1    |    1
   3    |    1    |    4
   4    |    1    |    2

Чтобы получить фильмы в порядке...

SELECT movieid WHERE userid = 1 ORDER BY previousid

-> 1, 2, 4, 3

Чтобы (скажем) переместить # 4 в пространство:

DECLARE @previousid int, @currentid int
SET @previousid = SELECT previousid FROM movies WHERE movieid = @currentid

-- current movie previous becomes its preceding preceding
UPDATE movies SET previousid = 
    (SELECT previousid FROM movies WHERE movieid = @previousid)
WHERE movieid = @currentid

-- the preceding movie previous becomes the current one previous
UPDATE movies SET previousid = @currentid WHERE movieid = @previousid

Это все еще 1 чтение + 2, но оно превышает 10000 записей.