Générer des listes de données
Par footcow le samedi, 2 février 2008, 00:13 - PostgreSQL - Lien permanent
Voilà une fonction peut-être mal connue de PostgreSQL, mais qui permet de réaliser des listes automatiques de données en quelques secondes.
generate_series(start, stop)
Son utilisation peut vous rendre bien des services, tant pour des générations de tableaux rapides, que pour avoir des cohérences de données parfaites sur des données séquentielles manquantes, ou encore pour générer des listes avancées de données comme des dates par exemple.
Mais les exemples sont souvent plus parlant que de longs discours ...
La fonction generate_series existe sous deux formes :
# SELECT generate_series(1, 5);
generate_series
-----------------
1
2
3
4
5
(5 rows)
C'est l'utilisation la plus basique de la fonction ... qui produit donc une liste numérique de 1 à 5.
# SELECT generate_series(10, 1, -2);
generate_series
-----------------
10
8
6
4
2
(5 rows)
On peut introduire, en plus, une notion de pas, qui permet de faire des séries par tranche, et dans l'exemple précédent vous voyez que la série de données peut-être croissante, comme décroissante.
# SELECT CURRENT_DATE + s.pas AS dates FROM generate_series(0, 28, 7) AS s(pas); dates ------------ 2008-02-01 2008-02-08 2008-02-15 2008-02-22 2008-02-29 (5 rows)
On peut en plus se servir de cette fonction pour générer facilement des séries de dates. Notre exemple nous permet de découper le mois de février 2008 en tranches de 7 jours.
# SELECT generate_series(1, 12, 3) AS dt, random() AS nb;
dt | nb
----+-------------------
1 | 0.695022536765329
4 | 0.958599179032538
7 | 0.344619980242392
10 | 0.122875446510909
(4 rows)
# SELECT generate_series AS dt, coalesce(rs.nb, 0) AS nb
FROM generate_series(1, 12) LEFT OUTER JOIN
(SELECT generate_series(1, 12, 3) AS dt, random() AS nb) AS rs on (rs.dt = generate_series);
dt | nb
----+-------------------
1 | 0.582011861532001
2 | 0
3 | 0
4 | 0.817687158853601
5 | 0
6 | 0
7 | 0.617505703408972
8 | 0
9 | 0
10 | 0.686404726321997
11 | 0
12 | 0
(12 rows)
Soit une liste de données qui peut résulter plus logiquement, par exemple d'une requête qui va vous donner des résultats mensuels sur une somme quelconque, mais avec des mois sans résultat. Dans mon exemple je génère une liste aléatoire de données pour 3 mois ... Maintenant, je peux consolider ma liste de données annuelles avec la génération d'une liste de 12 mois et un left outer join sur ma liste de départ. Un vrai régal !
En espérant que vous en aurez bon usage ...
