aggregats et statistiques

Ah, les plaisirs des moving average et autres sommes intermédiaires...

Sommes intermédiaires

Un exemple qui nous viens tout droit du site http://www.udel.edu/evelyn/SQL-Class3/SQL3_Stat.html

Il s'agit de calculer pour chaque ligne de résultat la somme des éléments déjà affichés jusque là, sans pour autant modifier la requête d'une quelconque autre façon.

L'idée est d'utiliser une jointure sur la même table, et de limiter cette jointure aux tuples déjà pris en compte jusque-là. Vous aurez donc à adapter le code suivant à vos données, bien sûr, mais aussi à votre façon de limiter la somme intermédiaire :

SELECT a.OrderID, a.OrderDate, Sum(b.OrderTotal) AS RunningTotal
FROM RATTC_ForRunSum AS a, RATTC_ForRunSum AS b
WHERE b.OrderDate <= a.OrderDate
GROUP BY a.OrderID, a.OrderDate;

Tableau Accumulateur

Plutôt que de faire une somme, on peut avoir besoin de cumuler l'ensemble des valeurs différentes d'un GROUP BY dans un tableau. On peut à cet effet utiliser l'agrégat suivant :


CREATE AGGREGATE array_acc (
BASETYPE = anyelement,
SFUNC = array_append,
STYPE = anyarray,
INITCOND = '{}'
);

Il s'utilise tout simplement comme suit :


pgloader=# begin;
BEGIN
pgloader=# create table foo (a int, b text);
CREATE TABLE
pgloader=# insert into foo values(1, 'foo'), (1, 'bar'), (2, 'baz'), (1, 'zoinx');
INSERT 0 4
pgloader=# CREATE AGGREGATE array_acc (
pgloader(# BASETYPE = anyelement,
pgloader(# SFUNC = array_append,
pgloader(# STYPE = anyarray,
pgloader(# INITCOND = '{}'
pgloader(# );
CREATE AGGREGATE
pgloader=# select a, array_acc(b) from foo group by a;
a | array_acc
---+-----------------
2 | {baz}
1 | {foo,bar,zoinx}
(2 lignes)
pgloader=# rollback;
ROLLBACK
pgloader=# begin;
BEGIN
pgloader=# create table foo (a int, b text);
CREATE TABLE
pgloader=# insert into foo values(1, 'foo'), (1, 'bar'), (2, 'baz'), (1, 'zoinx');
INSERT 0 4
pgloader=# CREATE AGGREGATE array_acc (
pgloader(# BASETYPE = anyelement,
pgloader(# SFUNC = array_append,
pgloader(# STYPE = anyarray,
pgloader(# INITCOND = '{}'
pgloader(# );
CREATE AGGREGATE
pgloader=# select a, array_acc(b) from foo group by a;
a | array_acc
---+-----------------
2 | {baz}
1 | {foo,bar,zoinx}
(2 lignes)

pgloader=# rollback;
ROLLBACK