Sqlite-эквивалент row_number() over (partition by...?

Я хотел бы знать, можно ли сделать следующее, используя один оператор sqlite:

Моя таблица выглядит примерно так:

|AnId|UserId|SomeDate|SomeData|
|123 |A     |1/1/2010|aadsljvs|
| 87 |A     |2/9/2010|asda fas|
|193 |A     |2/4/2010|aadsljvs|
|927 |A     |7/3/2010|aadsasdf|
|816 |B     |1/1/2010|aa32973v|
|109 |B     |7/5/2010|aaasfd10|
| 39 |B     |1/3/2010|66699327|
...

Каждая строка имеет уникальный идентификатор, идентификатор пользователя, значение даты и времени и некоторые другие данные.

Я хотел бы удалить записи, поэтому я сохраняю последние 10 записей для каждого пользователя на основе SomeDate.

В sql-сервере я бы использовал что-то вроде этого:

delete d
from data d
inner join (
    select UserId
        ,  AnId
        ,  row_number() over ( partition by UserId order by SomeDate desc ) 
              as RowNum
    from data 
) ranked on d.AnId = ranked.AnId
where ranked.RowNum > 10

Есть ли способ сделать это в sqlite? Редкий случай, когда есть несколько записей с одним и тем же SomeDate, не вызывает особого беспокойства, например. если я сохраню все эти записи, все будет хорошо.

Ответ 1

Я знаю, что этот вопрос старый, но следующий оператор SQLite будет выполнять то, что первоначально запрашивал Рори в одном из операторов. - Удалите все записи для данного UserId, которые не являются 10 самыми последними записями для этого UserId (на основе SomeDate).

DELETE FROM data
WHERE AnId IN (SELECT AnId
               FROM data AS d
               WHERE d.UserId = data.UserId
               ORDER BY SomeDate DESC
               LIMIT -1 OFFSET 10)

Ответ 2

Мне нужно было получить вторую строку для каждого "объекта" в таблице со отношением 1 к многим в таблице "объект".

Обычно в SQL это будет выполняться с использованием ROW_NUMBER() OVER (PARTITION BY object_id ORDER BY primary_id DESC)

В Sqlite мне пришлось придумать этот суб-запрос voodoo, чтобы получить тот же результат

SELECT object_id, MAX(_id)
FROM (SELECT object_id, _id
FROM aTable
EXCEPT
SELECT object_id, MAX(_id)
FROM aTable
GROUP BY object_id)
GROUP BY object_id;

Примечание. _id является первичным ключом aTable, а таблица объектов имеет отношение 1 к многим с запрошенной таблицей

Ответ 3

Это может быть непомерно дорого (возможно, только когда пользователь вставляет новую запись?), но как насчет этого:

for user in users:
  user-records = select * from records where user=user
  if user-records.length > 10:
    delete from records where user=user and date<user-records[10]

(в сочетании SQL и псевдокода)

Ответ 4

Если у вас уже нет ответа. Если это одна таблица, то вам не нужны никакие соединения. Вы можете просто использовать:

Delete From data
where AnId not in (Select AnId
                   from data
                   Order by SomeDate DESC
                   Limit 10)