Forums PostgreSQL.fr

meles

Membre

nb de personne par jour

Bonjour,
  soit une une table de ce style :

 create table essai (
  identifiant varchar,
  arrivee date, 
  depart date);

Comment faire pour connaitre le nb d'identifiant présent un jour donné ?
Sur une période donnée ?
Et si on veut que le jour d'arrivée compte pour 1/2 et celui de départ aussi ?

Bref encore des manipulations de date pas simple à mon gout. J'ai bien fait des essais avec "overlaps" mais ça ne m'a mené nulle part.

Merci d'avance.

gleu

Administrateur

Re : nb de personne par jour

Pouvez-vous expliquer un peu plus votre problème ? par exemple en donnant un exemple de données en entrée, ainsi qu'en sortie pour chaque question.

---

Guillaume.

Marc Cousin

Membre

Re : nb de personne par jour

Avec une requête préparée (pour ne pas taper 4 fois la date de parametre…)

PREPARE test (date) as
SELECT sum(case when $1=depart then 0.5 when $1=arrivee then 0.5 when ($1 > arrivee and $1 < depart) then 1 else 0 end) from essai;

---

Marc.

meles

Membre

Re : nb de personne par jour

Pouvez-vous expliquer un peu plus votre problème ? par exemple en donnant un exemple de données en entrée, ainsi qu'en sortie pour chaque question.

Bien sur:

insert into essai values ('dupont','01-01-2011','10-01-2011');
insert into essai values ('dupond','05-01-2011','12-01-2011');
insert into essai values ('durand','07-01-2011','09-01-2011');

pb 1 : Si je veux savoir combien il y a de client présent le 3/1/2011, je devrais trouver 1 (pour le 8/1/2011, il y en a 3)

pb 2 : sur une periode donné, je cherche a obtenir un truc du genre:

2011-01-01 | 1
2011-01-02 | 1
2011-01-03 | 1
2011-01-04 | 1
2011-01-05 | 2
2011-01-06 | 2
2011-01-07 | 3
2011-01-08 | 3
etc...

pb 3 : idem 2 mais :

2011-01-01 | 0.5
2011-01-02 | 1
2011-01-03 | 1
etc...

Je ne suis pas certain dêtre plus clair !

Marc Cousin

Membre

Re : nb de personne par jour

Donc ma requête est bonne ! Non mais !

Enfin je pense, mais merci de vérifier.

---

Marc.

meles

Membre

Re : nb de personne par jour

PREPARE test (date) as
SELECT sum(case when $1=depart then 0.5 when $1=arrivee then 0.5 when ($1 > arrivee and $1 < depart) then 1 else 0 end) from essai;

Marc, je suis largué là !
Comment j'utilise ça ?

Si je veux tester le 03/01/2011, je fais comment ?

MAN PREPARE pour commencer :-)

Cordialement

Marc Cousin

Membre

Re : nb de personne par jour

execute test('03/01/2011')

L'intérêt c'est juste de ne pas taper 4 fois la date (je suis très paresseux). Si c'est un programme qui renseigne le paramètre, ce n'est pas pareil. C'est juste que pour mes tests c'était moins fatigant

---

Marc.

meles

Membre

Re : nb de personne par jour

Si je veux tester le 03/01/2011, je fais comment ?

execute test('03-01-2011')

La prochaine fois, je commencerais par lire la doc :-)

meles

Membre

Re : nb de personne par jour

Oooops, je n'ai vu le message de Marc qu'après (très pratique le "prepare", je vais le mettre sous le coude pour entretenir ma paresse naturelle)

meles

Membre

Re : nb de personne par jour

Bonjour,
les problèmes 1 et 3 étant résolus, il me reste règler mon soucis N°2, calculer le nombre de présents par jour sur une période donnée.
L'approche la plus efficace serait-t-elle de faire un boucle sur une le bout de code donné par Marc ?

Si oui, comment faire un generate_series sur des dates car manifestement, la fonction ne prend que des int ou bigint en paramètres (j'ai bien vu l'exemple de la doc avec un interval, mais je ne suis vraiment pas à l'aise avec ces trucs là).

Peut-on réutiliser le prepare tel quel, ou le fait de tout encapsulé dans une function règlerait-elle le problème en déclarant les variables ?

Cordialement

meles

Membre

Re : nb de personne par jour

Donc ma requête est bonne ! Non mais !

Oui, Marc, ta requête est bonne à un petit bemol près , si mon individu entre et sort le même jour, il a 0.5 jour de présence pour son entrée et 0.5 pour sa sortie (je sais, c'est tordu).

Rajoutons

insert into essai values ('durond','2011-01-02','2011-01-02');

Ce qui nous amène à :

PREPARE test (date) as
SELECT sum(case when ($1= depart and depart=arrivee) then 1 
                when $1=depart then 0.5 
                when $1=arrivee then 0.5 
                when ($1 > arrivee and $1 < depart) then 1 
                else 0 end)
from essai;

Cordialement

Marc Cousin

Membre

Re : nb de personne par jour

Pour générer le range : «select generate_series('2010-01-01','2010-05-02','1 day'::interval);» ?

---

Marc.

meles

Membre

Re : nb de personne par jour

Bonjour,
  voila ou j'en suis avec mes histoires de présents à une date donnée.

J'ai commencé par créer une fonction pour me donner le nb de present à un date date donnée:

DROP FUNCTION IF EXISTS nb_present(DATE);
CREATE OR REPLACE FUNCTION nb_present(oneday DATE, OUT eff NUMERIC) AS
$$SELECT sum(case when ($1= depart and depart=arrivee) then 1 
                when $1=depart then 0.5 
                when $1=arrivee then 0.5 
                when ($1 > arrivee and $1 < depart) then 1 
                else 0 end)
  from essai;
$$
LANGUAGE sql;

Puis, j'ai créé une seconde foncion (avec le même nom (est-ce mal ?)) qui fait la même chose pour un intervalle de date:

DROP FUNCTION IF EXISTS nb_present(DATE, DATE);
CREATE OR REPLACE FUNCTION nb_present(datdeb DATE, datfin DATE) RETURNS TABLE(ddj DATE, eff NUMERIC) AS
$$
DECLARE
BEGIN
  FOR ddj IN SELECT generate_series(datdeb, datfin,'1 day'::interval)
  LOOP
    RETURN QUERY select ddj , nb_present(ddj);
  END LOOP;
END
$$
LANGUAGE plpgsql;

et ça marche.
Mais, il y a un mais, je suis obligé de faire :

select * from nb_present('2011-01-01','2011-01-11')

pour obtenir le résulat sur deux colonnes. Y'a-t-il un moyen d'obtenir directement un affichage sur deux colonnes en faisant

select nb_present('2011-01-01','2011-01-11')

?

D'ailleurs en y pensant, les deux fonctions ne devrait elle pas renvoyer le même type de retour, à savoir date et effectif ?

Cordialement

Marc Cousin

Membre

Re : nb de personne par jour

select nb_present('2011-01-01','2011-01-11'): non, puisqu'avec cette syntaxe, vous lui demandez de vous retourner un seul élément. Ce qu'il fait: il le met dans un array…

C'est comme pour une vue, vous devez faire select liste_de_champs from vue.

---

Marc.

meles

Membre

Re : nb de personne par jour

Ok, c'est noté.

Je vais profiter encore un peu de vos réponses éclairées.

L'idéal pour moi serait que j'arrive a avoir une fonction de type aggregat qui me permettre de segmenter les resultats par etage par exemple (si je
rajoutais une colonne étage à ma table essai) mais j'ai un peu du mal a conceptualiser la chose. Avez vous des liens ou je puisse voire des fonction aggrégat déclaré après coup dans postgresql, je suis sur que ça m'aiderai pas mal pour arriver à mes fins.

Je suis bien alleé voire CREATE AGGREGATE mais bon, c'est un peu abrupt quand on débute sur ce genre de chose.

Cordialement

Dernière modification par meles (26/01/2011 13:48:52)

Marc Cousin

Membre

Re : nb de personne par jour

Je ne comprends pas l'objectif recherché, désolé

Le create aggregate, c'est pour définir une nouvelle fonction d'aggrégat, comme sum, avg, count…

---

Marc.

meles

Membre

Re : nb de personne par jour

Ne soit pas désolé, au fur et à mesure que je progresse, mes besoins de confort grandisse !

Dons, si je reprends mon exemple de départ (avec la table essai), je récupère bien mon nombre de présent pour un jour donné ou pour une période donnée.

Maintenant, admettons que j'ai un nouvelle colonne étage dans la table, si je veux connaitre le nb de personne par etage pour un jour donné, il va falloir que je refasse une fonction pour les compter.

Si j'ai un fonction d'aggrégat pour mes présents, je pourrait faire mes comptes indifférement sur la table complète, ou par étage, ou pas n'importe qu'elle autre critère de regroupement, seulement ça implique que je puisse écrire un truc du genre:

select nb_present('2011-01-02') from essai; -- pour tous les présent

select etage,nb_present('2011-01-02') from essai; -- pour avoir le décompte par étage

Mais ca implique que d'une part le nom de la table ne soit pas inscrit en dure dans la fonction (et probablement aussi le nom des colonnes correspondant à la date d'arrivée et de départ) mais qu'en plus ma fonction se comporte comme un aggrégat classique.

J'espère que je suis plus clair :-)

Cordialement

Marc Cousin

Membre

Re : nb de personne par jour

Oui, plus clair.

Malheureusement, une fonction d'aggrégat, ça travaille sur les données aggrégée:
select etage, count(id_client) from essai group by etage, ça regroupe les enregistrements par étage, et ça applique la fonction d'aggrégat (count) sur les id_clients.

nb_present n'est pas une fonction des enregistrements regroupés. C'est une fonction du contenu de la table lui même, puisqu'elle réexécute des select.

Ce que je veux dire, c'est qu'on ne pourra pas le faire avec une fonction aggrégat comme ça, ça me semble vraiment trop tordu.

Le problème initial, à mon avis, c'est un problème de modélisation: stocker des plages comme ça, ça rend très difficile l'interrogation des données dessus. Il aurait certainement mieux valu stocker un enregistrement par date de présence de chaque personne. Avec un flag indiquant que c'est un jour de départ ou d'arrivée, au besoin. Ça prend plus de place, mais ça permet d'avoir des enregistrements indépendants capables de répondre directement à la question à laquelle on veut répondre: Mr machin était il à telle date dans telle chambre ?

---

Marc.

meles

Membre

Re : nb de personne par jour

Ok,
  comme c'est pour un besoin ponctuel, la première méthode me va bien. L'avantage que je voyais dans l'agrégat était de faire des sous sélections rapidement. Du coup, je vais peut être faire les chose différemment, à savoir, prendre mon niveau le plus fin d'agrégat et faire des sommes lorsque je n'en ai pas besoin.

Merci de ton aide

Cordialement

meles

Membre

Re : nb de personne par jour

Bon, après avoir rajouté une colonne etage dans ma table d'essai, pour finir, voila a quoi j'arrive:

DROP FUNCTION IF EXISTS nb_present(DATE);
CREATE OR REPLACE FUNCTION nb_present(oneday DATE) RETURNS TABLE(ddj DATE,etage VARCHAR, eff NUMERIC) AS
$$
SELECT $1,etage,sum(case when ($1= depart and depart=arrivee) then 1 
                when $1=depart then 0.5 
                when $1=arrivee then 0.5 
                when ($1 > arrivee and $1 < depart) then 1 
                else 0 end)
from essai group by etage;
$$
LANGUAGE sql;
DROP FUNCTION IF EXISTS nb_present(DATE, DATE);
select * from nb_present('2001-01-01');

DROP FUNCTION IF EXISTS nb_present(DATE,DATE);
CREATE OR REPLACE FUNCTION nb_present(datdeb DATE, datfin DATE) RETURNS TABLE(ddj DATE,etage VARCHAR, eff NUMERIC) AS
$$
DECLARE
  tmp DATE;
BEGIN
  FOR tmp IN SELECT generate_series(datdeb, datfin,'1 day'::interval)
  LOOP
    RETURN QUERY SELECT * from nb_present(tmp);
  END LOOP;
END
$$
LANGUAGE plpgsql;

ce qui me permet de faire ceci:

select * from nb_present('2011-01-01','2011-01-11');

ou ceci, si je ne veux pas le détail par étage:

select ddj, sum(eff) from nb_present('2011-01-01','2011-01-11') group by ddj order by 1;

Merci pour votre aide

Cordialement

meles

Membre

Re : nb de personne par jour

Je pensais en avoir fini mais il y a une dernière chose qui m'intrigue. N'y aurait-il pas moyen de récupérer pour la definition de ma fonction nb_present(DATE,DATE) la type de retour de celle à une date ce qui me permettrai de modifier dynamiquement le type des valeurs renvoyées par celle à deux date en ne modifiant que celle a une date ?

genre Un truc du genre:
CREATE OR REPLACE FUNCTION nb_present(oneday DATE) RETURNS TABLE(ddj DATE,etage VARCHAR, eff NUMERIC) AS .....
et
CREATE OR REPLACE FUNCTION nb_present(datdeb DATE, datfin DATE) RETURNS nb_present(DATE)%ROWTYPE AS ....

C'est sur ce qu'il faudrait mettre à la place de nb_present(DATE)%ROWTYPE que je coince.

Enfin, STABLE, IMMUTABLE ? est-ce utile dans ce cas la ?

Cordialement

Dernière modification par meles (26/01/2011 16:13:57)

Marc Cousin

Membre

Re : nb de personne par jour

Je ne pense pas que ça soit possible.

---

Marc.

meles

Membre

Re : nb de personne par jour

Je ne pense pas que ça soit possible.

Bonjour, comme c'est un peu calme aujourd'hui, voila le fruit de mes dernières cogitations et recherches à ce sujet:

On peut feinter en passant par un type prédéfini:
CREATE type nb_present_type AS (ddj DATE,etage VARCHAR, eff NUMERIC);
puis on le réutilise en faisant:
CREATE OR REPLACE FUNCTION nb_present(oneday DATE) RETURNS SETOF nb_present_type AS .....
et
CREATE OR REPLACE FUNCTION nb_present(datedeb DATE, datefin DATE) RETURNS SETOF nb_present_type AS .....

Cordialement

Marc Cousin

Membre

Re : nb de personne par jour

Oui, bien sûr, on peut le faire avec un type statique. La grosse différence, c'est que le type de retour de nb_present(datedeb DATE, datefin DATE) est dans ce cas bien défini. Pas un type qui est fonction du type de retour d'une autre fonction.

---

Marc.