Comment identifier les lignes par groupe avant un certain écart de valeur?

EXISTS sur une sous-requête d'agrégation:

UPDATE 3
 main | sub_id | value_t 
------+--------+---------
    1 |      1 |       8
    1 |      2 |       7
    1 |      3 |       3
    1 |      4 |        
    1 |      5 |        
    2 |      1 |       3
    2 |      2 |       1
    2 |      3 |       1
    2 |      4 |       8
    2 |      5 |        
(10 rows)

Résultat:

UPDATE test u
SET value_t = NULL
WHERE EXISTS (
        SELECT * FROM (
                SELECT main,sub_id
                , value_t , ABS(value_t - lag(value_t)
                       OVER (PARTITION BY main ORDER BY sub_id) ) AS absdiff
                FROM test
                ) x
        WHERE x.main = u.main
        AND x.sub_id <= u.sub_id
        AND x.absdiff >= 10
        )
        ;

SELECT * FROM test
ORDER BY main, sub_id;

Votre question était difficile à comprendre, la colonne "value_t" n'était pas pertinente pour la question, et vous avez oublié de définir la colonne "diff" dans votre SQL.

Quoi qu'il en soit, voici votre solution:

WITH data AS (
  SELECT main, sub_id, value_t
       , abs(value_t
             - lead(value_t) OVER (PARTITION BY main ORDER BY sub_id)) > 10 is_evil
  FROM test
)
SELECT main, sub_id, value_t
     , CASE max(is_evil::int)
            OVER (PARTITION BY main ORDER BY sub_id
                  ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
       WHEN 1 THEN NULL ELSE 1 END newval
FROM data;

J'utilise un CTE pour préparer les données (calculant si une ligne est "mauvaise"), puis la fonction de fenêtre "max" est utilisée pour vérifier s'il y avait des lignes "mauvaises" avant celle actuelle, par partition.

SELECT base

Aussi vite que possible:

UPDATE test AS t
SET    newval = 1
FROM  (
   SELECT main, sub_id
        , bool_and(diff BETWEEN -10 AND 10) OVER (PARTITION BY main ORDER BY sub_id) AS flag
   FROM (
      SELECT main, sub_id
           , value_t - lag(value_t, 1, value_t) OVER (PARTITION BY main ORDER BY sub_id) AS diff
      FROM   test
      ) sub
   ) u
WHERE (t.main, t.sub_id) = (u.main, u.sub_id)
AND    u.flag;

Points fins

• Votre modèle de pensée évolue autour de la fonction de fenêtre lead() . Mais son homologue lag() est un peu plus efficace pour cela, car il n'y a pas d'erreur de décalage lors de l'inclusion de la ligne avant le grand écart. Vous pouvez également utiliser lead() avec un ordre de tri inversé ( ORDER BY sub_id DESC ).

• Pour éviter NULL pour la première ligne de la partition, fournissez value_t par défaut comme troisième paramètre, ce qui rend le diff 0 au lieu de NULL. lead() et lag() ont cette capacité.

• diff BETWEEN -10 AND 10 est légèrement plus rapide que @diff < 11 (plus clair et plus flexible également). ( @ étant l'opérateur "valeur absolue" , équivalent à la fonction abs() .)

• bool_or() ou bool_and() dans la fonction de fenêtre externe est probablement le moins cher pour marquer toutes les lignes jusqu'au grand espace.

Votre UPDATE

Jusqu'à ce que le seuil soit atteint, je voudrais marquer chaque ligne passée ET la ligne où la condition est FALSE en remplissant la colonne newval avec une valeur par exemple 1 .

Encore une fois, aussi vite que possible.

SELECT *, bool_and(diff BETWEEN -10 AND 10) OVER (PARTITION BY main ORDER BY sub_id) AS flag
FROM (
   SELECT *, value_t - lag(value_t, 1, value_t) OVER (PARTITION BY main ORDER BY sub_id) AS diff
   FROM   test
   ) sub;

Points fins

• Le calcul de toutes les valeurs dans une seule requête est généralement beaucoup plus rapide qu'une sous-requête corrélée.

• L'ajout de la condition WHERE AND u.flag garantit que nous ne mettons à jour que les lignes qui nécessitent réellement une mise à jour.
  Si certaines des lignes peuvent déjà avoir la bonne valeur dans newval , ajoutez une autre clause pour éviter ces mises à jour vides également: AND t.newval IS DISTINCT FROM 1 Voir:

  • Comment puis-je (ou puis-je) sélectionner DISTINCT sur plusieurs colonnes?

• SET newval = 1 assigne une constante (même si nous pourrions utiliser la valeur réellement calculée dans ce cas), c'est un peu moins cher.

db <> violon ici