sqlite相当于row_number（）over（分区由...？

I'd like to know if it's possible to do the following using a single sqlite statement:

我想知道是否可以使用单个sqlite语句执行以下操作：

My table looks something like this:

我的表看起来像这样：

|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|
...

Each row has a unique id, a user id, a datetime value, and some other data.

每行都有唯一的ID，用户ID，日期时间值和一些其他数据。

I'd like to delete records so I keep the latest 10 records per user, based on SomeDate.

我想删除记录，以便根据SomeDate保留每个用户的最新10条记录。

In sql server I'd use something like this:

在sql server中我会使用这样的东西：

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

Is there a way to do this in sqlite? The edge case where there are several records with the same SomeDate isn't a particular worry, e.g. if I keep all those records that'd be fine.

有没有办法在sqlite中执行此操作？具有相同SomeDate的多个记录的边缘情况不是特别担心，例如，如果我保留所有这些记录都没问题。

4 个解决方案

#1

I know this question is old, but the following SQLite statement will do what Rory was originally asking for in one statement - Delete all records for a given UserId that are not the 10 most recent records for that UserId (based on SomeDate).

我知道这个问题很老，但是下面的SQLite语句将在一个语句中执行Rory最初要求的操作 - 删除给定UserId的所有记录，这些记录不是该UserId的10个最新记录（基于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

I needed to fetch the second row for each "object" in a table with a 1 to many relationship to the "object" table.

我需要为表中的每个“对象”获取第二行，该对象与“对象”表具有1对多的关系。

Usually in SQL this will be done using ROW_NUMBER() OVER(PARTITION BY object_id ORDER BY primary_id DESC)

通常在SQL中，这将使用ROW_NUMBER（）OVER（PARTITION BY object_id ORDER BY primary_id DESC）完成

In Sqlite I had to come up with this voodoo sub-query to get the same result

在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;

Note: The _id is the primary key of aTable and the object table has a 1 to many relationship with the queried table

注意：_id是aTable的主键，对象表与查询表具有1对多的关系

#3

This might be prohibitively expensive (perhaps only do it when a user inserts a new record?) but how about this:

这可能是非常昂贵的（可能只在用户插入新记录时才这样做？）但是如何：

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]

(in a mix of SQL and pseudocode)

（混合使用SQL和伪代码）

#4

If you already haven't got the answer. If it's one table, then you don't need any joins. You can just use:

如果你还没有得到答案。如果它是一个表，那么您不需要任何连接。你可以使用：

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

#1

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