Comment écrire une fonction d’agrégation de multiplication en SQL – Java, SQL et jOOQ.

Online Coding Courses for Kids

Tout le monde connaît la fonction d’agrégation SQL SUM () (et beaucoup de gens connaissent également sa variante de fonction de fenêtre).

Lors de l’interrogation du Base de données Sakila, nous pouvons obtenir les revenus quotidiens (en utilisant la syntaxe PostgreSQL):

WITH p AS (
  SELECT
    CAST (payment_date AS DATE) AS date,
    amount
  FROM payment
)
SELECT
  date,
  SUM (amount) AS daily_revenue,
  SUM (SUM (amount)) OVER (ORDER BY date) AS cumulative_revenue
FROM p
GROUP BY date
ORDER BY date

Le résultat ressemblera à ceci:

date       |daily_revenue |cumulative_revenue 
-----------|--------------|-------------------
2005-05-24 |29.92         |29.92              
2005-05-25 |573.63        |603.55             
2005-05-26 |754.26        |1357.81            
2005-05-27 |685.33        |2043.14            
2005-05-28 |804.04        |2847.18            
2005-05-29 |648.46        |3495.64            
2005-05-30 |628.42        |4124.06            
2005-05-31 |700.37        |4824.43            
2005-06-14 |57.84         |4882.27            
...

Faire de même avec la multiplication

C’est déjà très utile. Très occasionnellement, cependant, nous n’avons pas besoin d’agréger plusieurs valeurs dans une somme (par addition), mais dans un produit (par multiplication). Je suis juste tombé sur un tel cas sur Stack Overflow, récemment.

La question voulait atteindre le résultat suivant:

date        factor          accumulated
---------------------------------------
1986-01-10  null            1000
1986-01-13  -0.026595745    973.4042548
1986-01-14  0.005464481     978.7234036
1986-01-15  -0.016304348    962.7659569
1986-01-16  0               962.7659569
1986-01-17  0               962.7659569
1986-01-20  0               962.7659569
1986-01-21  0.005524862     968.0851061
1986-01-22  -0.005494506    962.765957
1986-01-23  0               962.765957
1986-01-24  -0.005524862    957.4468078
1986-01-27  0.005555556     962.7659569
1986-01-28  0               962.7659569
1986-01-29  0               962.7659569
1986-01-30  0               962.7659569
1986-01-31  0.027624309     989.3617013
1986-02-03  0.016129032     1005.319148
1986-02-04  0.042328041     1047.872338
1986-02-05  0.04568528      1095.744679

S’il s’agissait d’une feuille de calcul Microsoft Excel, le ACCUMULATED La colonne commencerait simplement par 1000 et aurait la formule suivante dans toutes les autres lignes:

accumulated(i) = accumulated(i - 1) * (1 + factor)

En d’autres termes (valeurs tronquées pour plus de simplicité):

1000.0 = start
 973.4 = 1000.0 * (1 - 0.026)
 978.7 =  973.4 * (1 + 0.005)
 962.7 =  978.7 * (1 - 0.016)
 962.7 =  962.7 * (1 - 0.000)
 962.7 =  962.7 * (1 - 0.000)
 962.7 =  962.7 * (1 - 0.000)
 968.0 =  962.7 * (1 + 0.005)
 ...

C’est passionnant, car nous exigeons non seulement une agrégation multiplicative, mais même une agrégation multiplicative cumulative. Donc, une autre fonction de fenêtre.

Mais malheureusement, SQL n’offre pas de MUL() fonction d’agrégation, même si elle était relativement simple à mettre en œuvre. Nous avons deux options:

  • Implémentation d’une fonction d’agrégation personnalisée (restez à l’écoute pour un futur article de blog)
  • Utiliser une astuce en additionnant des logarithmes, plutôt que de multiplier directement des opérandes

Nous mettons en œuvre ce dernier pour l’instant. Découvrez ce cool Site Wikipedia sur les identités logarithmiques, auquel nous allons faire confiance aveuglément. Au milieu, nous avons:

bx * by = bx + y

Qui conduit à:

logb(x * y) = logb(x) + logb(y)

À quel point cela est cool? Et ainsi:

x * y = blogb(x) + logb(y)

Donc, nous pouvons définir toute multiplication en termes de tas d’exponentiation à une base (disons e) et des logarithmes à une base (disons e). Ou, en SQL:

x * y = EXP(LN(x) + LN(y))

Ou, en tant que fonction agrégée:

MUL(x) = EXP(SUM(LN(x)))

Il h!

Notre problème d’origine peut donc être résolu très facilement en utilisant cela, comme indiqué dans ma réponse de débordement de pile:

SELECT
  date,
  factor,
  EXP(SUM(LN(1000 * (1 + COALESCE(factor, 1)))) 
       OVER (ORDER BY date)) AS accumulated
FROM t

Et nous obtenons le bon résultat comme indiqué précédemment. Vous devrez peut-être remplacer LN() par LOG() en fonction de votre base de données.

Mise en garde: nombres négatifs

Essayez d’exécuter ceci:

SELECT LN(-1)

Tu auras:

SQL Error [2201E]: ERROR: cannot take logarithm of a negative number

Les logarithmes sont définis uniquement pour les nombres strictement positifs, à moins que votre base de données ne soit également capable de gérer des nombres complexes. Dans le cas où une seule valeur zéro romprait encore l’agrégation.

Mais si votre ensemble de données est défini pour ne contenir que des nombres strictement positifs, tout ira bien. Donnez ou prenez des erreurs d’arrondi à virgule flottante. Ou, vous allez faire un peu de gestion des signes, qui ressemble à ceci:

WITH v(i) AS (VALUES (-2), (-3), (-4))
SELECT 
  CASE 
    WHEN SUM (CASE WHEN i < 0 THEN -1 END) % 2 < 0 
    THEN -1 
    ELSE 1 
  END * EXP(SUM(LN(ABS(i)))) multiplication1
FROM v;

WITH v(i) AS (VALUES (-2), (-3), (-4), (-5))
SELECT 
  CASE 
    WHEN SUM (CASE WHEN i < 0 THEN -1 END) % 2 < 0 
    THEN -1 
    ELSE 1 
  END * EXP(SUM(LN(ABS(i)))) multiplication2
FROM v;

Ce qui précède

multiplication1      
--------------------
-23.999999999999993 


multiplication2     
-------------------
119.99999999999997 

Assez proche.

Mise en garde: zéro

Essayez d'exécuter ceci:

SELECT LN(0)

Tu auras:

SQL Error [2201E]: ERROR: cannot take logarithm of zero

Zéro est différent des nombres négatifs. Un produit qui a un opérande nul est toujours nul, donc nous devrions être capables de gérer cela. Nous le ferons en deux étapes:

  • Exclure des valeurs nulles de l'agrégation réelle qui utilise EXP() et LN()
  • Ajouter un supplément CASE expression qui vérifie si l'un des opérandes est nul

La première étape peut ne pas être nécessaire selon la façon dont votre optimiseur de base de données exécute la deuxième étape.

WITH v(i) AS (VALUES (2), (3), (0))
SELECT 
  CASE 
    WHEN SUM (CASE WHEN i = 0 THEN 1 END) > 0
    THEN 0
    WHEN SUM (CASE WHEN i < 0 THEN -1 END) % 2 < 0 
    THEN -1 
    ELSE 1 
  END * EXP(SUM(LN(ABS(NULLIF(i, 0))))) multiplication
FROM v;

Extension: DISTINCT

Calculer le produit de tous DISTINCT les valeurs nécessitent de répéter la DISTINCT mot-clé dans 2 des 3 sommes ci-dessus:

WITH v(i) AS (VALUES (2), (3), (3))
SELECT 
  CASE 
    WHEN SUM (CASE WHEN i = 0 THEN 1 END) > 0
    THEN 0
    WHEN SUM (DISTINCT CASE WHEN i < 0 THEN -1 END) % 2 < 0 
    THEN -1 
    ELSE 1 
  END * EXP(SUM(DISTINCT LN(ABS(NULLIF(i, 0))))) multiplication
FROM v;

Le résultat est maintenant:

multiplication |
---------------|
6              |

Notez que le premier SUM() qui vérifie la présence de NULL les valeurs ne nécessitent pas de DISTINCT , nous l'omettons donc pour améliorer les performances.

Extension: fonctions de fenêtre

Bien sûr, si nous pouvons émuler un PRODUCT() fonction d'agrégation, nous aimerions aussi la transformer en fonction de fenêtre. Cela peut se faire simplement en transformant chaque individu SUM() dans une fonction de fenêtre:

WITH v(i, j) AS (
  VALUES (1, 2), (2, -3), (3, 4), 
         (4, -5), (5, 0), (6, 0)
)
SELECT i, j, 
  CASE 
    WHEN SUM (CASE WHEN j = 0 THEN 1 END) 
      OVER (ORDER BY i) > 0
    THEN 0
    WHEN SUM (CASE WHEN j < 0 THEN -1 END) 
      OVER (ORDER BY i) % 2 < 0 
    THEN -1 
    ELSE 1 
  END * EXP(SUM(LN(ABS(NULLIF(j, 0)))) 
    OVER (ORDER BY i)) multiplication
FROM v;

Le résultat est maintenant:

i |j  |multiplication      |
--|---|--------------------|
1 | 2 |2                   |
2 |-3 |-6                  |
3 | 4 |-23.999999999999993 |
4 |-5 |119.99999999999997  |
5 | 0 |0                   |
6 | 1 |0                   |

Trop cool! Le produit cumulatif devient de plus en plus gros jusqu'à ce qu'il atteigne le premier zéro, à partir de ce moment, il reste nul.

Prise en charge de jOOQ

jOOQ 3.12 le supportera également et l'émulera correctement sur toutes les bases de données:
https://github.com/jOOQ/jOOQ/issues/5939

Une note sur les performances d'Oracle

Notez qu'Oracle est très lent à calculer LN(number_type). Ça peut être BEAUCOUP plus rapide à calculer, à la place LN(binary_double_type). Une conversion de type explicite a produit une amélioration des performances de 100x dans un test simple, documenté ici.

Close Menu