SQL sélectionner des éléments dans laquelle somme de champ est inférieur à N
étant donné que j'ai une table avec le contenu suivant, très simple:
# select * from messages;
id | verbosity
----+-----------
1 | 20
2 | 20
3 | 20
4 | 30
5 | 100
(5 rows)
je voudrais sélectionner n messages, dont la somme de verbosité est inférieure à Y (pour des fins de test disons qu'il devrait être 70, puis les résultats corrects seront des messages avec id 1,2,3). C'est vraiment important pour moi, Cette solution devrait être indépendante de la base de données (elle devrait fonctionner au moins sur Postgres et SQLite).
j'essayais quelque chose comme:
SELECT * FROM messages GROUP BY id HAVING SUM(verbosity) < 70;
cependant, il ne semble pas fonctionner comme prévu, parce qu'il ne fait pas réellement la somme de toutes les valeurs de la colonne de verbosité.
je serais très reconnaissant pour tous les conseils/aide.
2 réponses
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;
cela suppose un unique, Ascendant id comme vous avez dans votre exemple.
moderne Postgres - ou plus généralement avec moderne standard SQL (mais pas dans 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 récursive
devrait être plus rapide pour les grandes tables où vous ne récupérez qu'un petit ensemble.
WITH RECURSIVE cte AS (
( -- parentheses required
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;
toutes les caractéristiques standard, sauf LIMIT .
à proprement parler, il n'y a pas une telle chose comme "base de données indépendante". Il existe différentes normes SQL, mais aucun RDBMS n'est entièrement conforme. LIMIT travaille pour PostgreSQL et SQLite (et quelques autres). Utilisez TOP 1 pour SQL Server, rownum pour Oracle. Voici une liste complète de sur Wikipedia.
Le SQL:2008 standard serait:
...
FETCH FIRST 1 ROWS ONLY
... qui PostgreSQL supporte-mais à peine tous les autres RDBM.
l'alternative pure qui fonctionne avec plus de systèmes serait de l'envelopper dans un sous-vernis et
SELECT max(total) FROM <subquery>
mais c'est lent et lourd.
ça va marcher...
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
)
cependant, vous devez comprendre que SQL est conçu comme un langage basé sur un ensemble, plutôt que comme un langage itératif, donc il a été conçu pour traiter les données comme un ensemble, plutôt que sur une base de ligne en ligne.