SQL seleccionan elementos donde la sum del campo es menor que N

Dado que tengo una tabla con el siguiente contenido muy simple:

# select * from messages; id | verbosity ----+----------- 1 | 20 2 | 20 3 | 20 4 | 30 5 | 100 (5 rows) 

Me gustaría seleccionar N posts, cuya sum de verbosidad es inferior a Y (para fines de testing digamos que debería ser 70, entonces los resultados correctos serán posts con id 1,2,3). Es realmente importante para mí, esa solución debe ser independiente de la database (debería funcionar al less en Postgres y SQLite).

Estaba intentando con algo como:

 SELECT * FROM messages GROUP BY id HAVING SUM(verbosity) < 70; 

Sin embargo, no parece funcionar como se esperaba, porque en realidad no sum todos los valores de la columna de verbosidad.

Estaría muy agradecido por cualquier pista / ayuda.

 SELECT m.id, sum(m1.verbosity) AS total FROM messages m JOIN messages m1 ON m1.id <= m.id WHERE m.verbosity < 70 -- optional, to avoid pointless evaluation GROUP BY m.id HAVING SUM(m1.verbosity) < 70 ORDER BY total DESC LIMIT 1; 

Esto supone una id Única ascendente como la que tiene en su ejemplo.


En Postgres moderno, o generalmente con un estándar moderno de SQL (pero no en SQLite):

Simple CTE

 WITH cte AS ( SELECT *, sum(verbosity) OVER (ORDER BY id) AS total FROM messages ) SELECT * FROM cte WHERE total <= 70 ORDER BY id; 

CTE recursivo

Debería ser más rápido para tables grandes donde solo recuperas un set pequeño.

 WITH RECURSIVE cte AS ( ( -- parentheses requinetworking SELECT id, verbosity, verbosity AS total FROM messages ORDER BY id LIMIT 1 ) UNION ALL SELECT c1.id, c1.verbosity, c.total + c1.verbosity FROM cte c JOIN LATERAL ( SELECT * FROM messages WHERE id > c.id ORDER BY id LIMIT 1 ) c1 ON c1.verbosity <= 70 - c.total WHERE c.total <= 70 ) SELECT * FROM cte ORDER BY id; 

Todas las características estándar, excepto LIMIT .

Estrictamente hablando, no existe tal cosa como "independiente de la database". Existen varios estándares SQL, pero ningún RDBMS cumple completamente. LIMIT funciona para PostgreSQL y SQLite (y algunos otros). Utilice TOP 1 para SQL Server, rownum para Oracle. Aquí hay una list completa en Wikipedia.

El estándar SQL: 2008 sería:

 ... FETCH FIRST 1 ROWS ONLY 

… que PostgreSQL admite, pero casi ningún otro RDBMS.

La alternativa pura que funciona con más sistemas sería envolverla en una subconsulta y

 SELECT max(total) FROM <subquery> 

Pero eso es lento y difícil de manejar.

SQL Fiddle.

Esto funcionará …

 select * from messages where id<= ( select MAX(id) from ( select m2.id, SUM(m1.verbosity) sv from messages m1 inner join messages m2 on m1.id <=m2.id group by m2.id ) v where sv<70 ) 

Sin embargo, debe comprender que SQL está diseñado como un lenguaje basado en sets, en lugar de uno iterativo, por lo que está diseñado para tratar los datos como un set, en lugar de una fila por fila.