Comment calculer une SOMME conditionnelle dans Oracle avec un seuil de réinitialisation ?

SOMME conditionnelle dans les bases de données Oracle

Oracle fournit des fonctionnalités robustes pour l'agrégation conditionnelle, y compris les calculs de SOMME conditionnelle. Cet article aborde le défi de réaliser une telle SOMME, avec une exigence spécifique : réinitialiser la valeur accumulée lorsqu'elle dépasse 15.

Énoncé du problème

L'objectif est de calculer une SOMME conditionnelle sur une série de valeurs, où la SOMME est réinitialisée à zéro chaque fois qu'elle atteint ou dépasse 15. Cette agrégation aboutit à une séquence de SUM partitionnées par la valeur seuil.

Solution : Utiliser le Clause MODEL

Bien que le SQL récursif soit une approche viable, la clause SQL MODEL offre une alternative qui améliore la lisibilité et rationalise le processus d'exécution.

<code class="sql">-- Create a table to represent the sample data
CREATE TABLE test_data ( sort_col VARCHAR2(1), addend NUMBER );
-- Insert sample data
INSERT INTO test_data (sort_col, addend) VALUES ('A', 3), ('B', 7), ('C', 6), ('D', 5), ('E', 9), ('F', 3), ('G', 8);

-- Begin the actual solution
WITH sorted_inputs AS (
  SELECT sort_col,
         ROW_NUMBER() OVER (ORDER BY sort_col) AS sort_order,
         addend,
         0 AS running_sum_max_15  -- Initialize running sum to zero
  FROM test_data
)
SELECT sort_col,
       addend,
       running_sum_max_15
FROM sorted_inputs
MODEL
DIMENSION BY (sort_order)
MEASURES (sort_col, addend, running_sum_max_15)
RULES UPDATE (
  running_sum_max_15[1] = addend[1],  -- Set initial running sum to the first addend
  running_sum_max_15[sort_order > 1] =  -- Calculate running sum for subsequent rows
  CASE
    WHEN running_sum_max_15[CV(sort_order) - 1] < 15 THEN
      running_sum_max_15[CV(sort_order) - 1]  -- Continue running sum if below threshold
    ELSE
      0  -- Reset running sum if threshold is reached or exceeded
  END + addend[CV(sort_order)]  -- Add current addend to running sum
);</code>

Résultats

La requête produit le résultat suivant :

<code class="sql">+----------+--------+--------------------+
| SORT_COL | ADDEND | RUNNING_SUM_MAX_15 |
+----------+--------+--------------------+
| A        |      3 |                  3 |
| B        |      7 |                 10 |
| C        |      6 |                 16 |
| D        |      5 |                  5 |
| E        |      9 |                 14 |
| F        |      3 |                 17 |
| G        |      8 |                  8 |
+----------+--------+--------------------+</code>

Comme évident, la SOMME conditionnelle est calculée correctement, la somme cumulée recommençant après avoir atteint 15.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!