Forums PostgreSQL.fr

azimut2000

Membre

Requete SELECT step by step

Bonjour,

Je ne sais pas si je suis dans le bon forum, mais je tente ma question...

Je me suis lancé dans l'écriture d'une application qui présente des graphiques de chiffres récoltés par un batch et stocké dans une base PostgreSQL...

Je me retrouve à devoir écrire un certain nombre de requête SQL, chose que j'arrive à faire sans trop de problème, mais j'arrive à un point ou je bloque... Voici ma problématique :

Je possède une TABLE avec plusieurs colonnes décrivant une date, une heure et différentes données d'un item par colonne.
Pour afficher mon graphique, j'aurais besoin de ne prendre qu'une ligne sur 3 par exemple, avec pour retour dans ma requête SELECT, un retour de la date et de l'heure et au niveau données, une moyenne des 3 valeurs d'une donnée / d'une colonne. Voici un exemple :

Ma TABLE ressemble à cela :

date           | heure      |  user   |  nice  |  system
03/05/2020 | 13:00:00 | 2        |  2      |  5
03/05/2020 | 13:01:00 | 5        |  2      |  3
03/05/2020 | 13:02:00 | 6        |  2      |  2
03/05/2020 | 13:03:00 | 4        |  2      |  2
03/05/2020 | 13:04:00 | 3        |  2      |  5

Je souhaite prendre une valeur de retour d'une valeur sur 3:

-> Via une requête de type SELECT heure, user FROM matable, avoir un retour de ce type : 'heure' qui renvoi '13:02:00', 'user' renvoie la moyenne de la ligne 13:00 / 13h01 / 13h02 = (2+5+6)/3 = 4.33... Idem pour les autres colonnes comme "nice" ou "system"

J'ai déjà posé la question à des DBA mais pas beaucoup de solution trouvée.... Je suis en Postgres 10 et je code en PHP... mais tout cela n'a que peu d'importance pour trouver une solution à mon problème ;-)

Quelqu'un aurait il une idée comment je pourrais écrire une requête qui me fasse ce genre de sortie ?

Merci à tous,

Thierry

dverite

Membre

Re : Requete SELECT step by step

Les fonctions de fenêtrages correspondent à ce besoin.

Voir https://docs.postgresql.fr/10/tutorial-window.html

---

@DanielVerite
http://blog-postgresql.verite.pro/

azimut2000

Membre

Re : Requete SELECT step by step

Les fonctions de fenêtrages correspondent à ce besoin.

Voir https://docs.postgresql.fr/10/tutorial-window.html

Merci pour la piste...
Cela semble pouvoir me laisser trouver une solution, je ne sais pas encore comment, mais une fois tout lu, peut être que j'y verrais plus clair ;-)

La seule chose par contre que je ne vois pas pour l'instant, c'est comment je vais pouvoir rassembler mes éléments : si je veux prendre les lignes de 13:00 à 13h03 uniquement pour calculer ma première moyenne et pas la ligne 13:04, j'ai encore à creuser :-(

dverite

Membre

Re : Requete SELECT step by step

Rassembler se fait généralement avec GROUP BY. En numérotant les lignes dans un certain ordre on peut grouper sur ce numéro.

Voici un exemple très schématique pour être concret:

1. soit jeu de test avec un timestamp et une valeur

CREATE TEMP TABLE test AS
select
(now()+n*'1 second'::interval)::timestamp(0) as t,
random()*1000 as v
from generate_series(1,10) as n;

select * from test;

          t          |        v         
---------------------+------------------
2020-05-06 13:51:59 | 82.4452051892877
2020-05-06 13:52:00 | 339.041672646999
2020-05-06 13:52:01 | 824.749575462192
2020-05-06 13:52:02 | 717.561565339565
2020-05-06 13:52:03 | 764.736431185156
2020-05-06 13:52:04 | 604.845985304564
2020-05-06 13:52:05 | 82.1709437295794
2020-05-06 13:52:06 | 402.444486506283
2020-05-06 13:52:07 | 747.045868076384
2020-05-06 13:52:08 | 624.612233601511

2. numérote les lignes en partant de 0 dans l'ordre des timestamps. C'est la partie fenêtrage:

select row_number() over(order by t)-1 as rn, t, v from test;

rn |          t          |        v         
----+---------------------+------------------
  0 | 2020-05-06 13:51:59 | 82.4452051892877
  1 | 2020-05-06 13:52:00 | 339.041672646999
  2 | 2020-05-06 13:52:01 | 824.749575462192
  3 | 2020-05-06 13:52:02 | 717.561565339565
  4 | 2020-05-06 13:52:03 | 764.736431185156
  5 | 2020-05-06 13:52:04 | 604.845985304564
  6 | 2020-05-06 13:52:05 | 82.1709437295794
  7 | 2020-05-06 13:52:06 | 402.444486506283
  8 | 2020-05-06 13:52:07 | 747.045868076384
  9 | 2020-05-06 13:52:08 | 624.612233601511

3. regroupe par bloc de 3 lignes consécutives avec la moyenne des valeurs. C'est la partie group by par-dessus le fenêtrage:

select rn/3 as bloc ,avg(v) from (
select row_number() over(order by t)-1 as rn, t, v from test
) s group by rn/3 order by rn/3;

bloc |       avg       
------+------------------
    0 | 415.412151099493
    1 | 695.714660609762
    2 | 410.553766104082
    3 | 624.612233601511

---

@DanielVerite
http://blog-postgresql.verite.pro/

azimut2000

Membre

Re : Requete SELECT step by step

Whaaoooouuuu !
Merci, merci, merci ;-)
Super tuto !

Non seulement, je viens de découvrir la notion et l'utilisation du fenêtrage, mais en plus j'ai découvert des syntaxes que je ne pensais pas pouvoir faire... Bref !

Juste une dernière question : que signifie le "s" dans "s group by rn/3 order by rn/3;" ???
J'ai du mal à comprendre sa signification... Je comprend bien le GROUP BY et ORDER BY que j'utilise de temps en temps, mais c'est le "s" (qui pourrait être un a/b/c/d/...), je ne vois pas pourquoi et si bien sur on ne le met pas, la requête échoue.

Merci encore pour tes lumières,

Thierry

dverite

Membre

Re : Requete SELECT step by step

Le s donne un nom à la sous-requête; effectivement c'est obligatoire syntaxiquement.
On peut aussi ajouter le mot clef AS pour que ce soit plus lisible, du style:

select .. from ( ...sous-requête...)  AS nom1, (... autre sous-requête...) AS nom2

---

@DanielVerite
http://blog-postgresql.verite.pro/