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()etLN() - Ajouter un supplément
CASEexpression 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.
