iseries - sqlite equivalente a row_number() over(partition by...?

rownum db2 as400

Question

Me gustaría saber si es posible hacer lo siguiente con una sola declaración sqlite:

Mi mesa se ve así:

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

Cada fila tiene una identificación única, una identificación de usuario, un valor de fecha y hora y algunos otros datos.

Me gustaría eliminar registros, así que guardo los últimos 10 registros por usuario, basados ​​en SomeDate.

En el servidor SQL, usaría algo como esto:

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

¿Hay alguna manera de hacer esto en sqlite? El caso límite en el que hay varios registros con el mismo SomeDate no es una preocupación particular, por ejemplo, si guardo todos esos registros estarían bien.

Answer 1

Esto puede ser prohibitivamente costoso (¿tal vez solo lo haga cuando un usuario inserta un nuevo registro?), Pero ¿qué tal esto?

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]

(en una mezcla de SQL y pseudocódigo)

Answer 2

Necesitaba buscar la segunda fila para cada "objeto" en una tabla con una relación de 1 a muchos con la tabla "objeto".

Usualmente en SQL esto se hará usando ROW_NUMBER () OVER (PARTITION BY object_id ORDER BY primary_id DESC)

En Sqlite tuve que presentar esta sub consulta de vudú para obtener el mismo resultado

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;

Nota: _id es la clave principal de una tabla y la tabla de objetos tiene una relación de 1 a muchos con la tabla consultada

Answer 3

Sé que esta pregunta es antigua, pero la siguiente declaración SQLite hará lo que Rory estaba pidiendo originalmente en una declaración: elimine todos los registros de una ID de usuario determinada que no sean los 10 registros más recientes para esa ID de usuario (basada en 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)

Answer 4

Si ya no tienes la respuesta. Si se trata de una tabla, entonces no necesita ninguna unión. Puedes simplemente usar:

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