1
votes

Renvoie la valeur 0 ou Null s'il n'y a pas de données dans une table pour une date particulière?

J'ai une application de base de données qui stocke une série de lectures d'un type particulier de compteur.

Supposons que pour un compteur individuel, il n'y ait pas de lectures à une date particulière. Ensuite, je demande ce compteur sur une plage de dates. Il n'y aura aucune information pour cette date particulière, ce qui laisse un vide dans la sortie affichée.

Ma question est donc de savoir comment renvoyer une valeur 0 pour cette date manquante. Par exemple, considérez ce code lorsque le compteur n'a pas de données pour 2019-07-02 et 2019-07-03. Je voudrais obtenir des zéros comme indiqué dans l'exemple de sortie plutôt que d'afficher une lacune dans les données.

2019-07-01  92589492 96
2019-07-02  92589492  0
2019-07-03  92589492  0
2019-07-04  92589492 96
2019-07-05  92589492 96
2019-07-06  92589492 96
2019-07-07  92589492 96
2019-07-08  92589492 96

select m.sdate,
       n.meter_number,
       COALESCE(CAST(n.meter_number as CHAR, 0) AS number_of_cases,
       CASE  WHEN n.count IS NULL THEN '0' ELSE n.count END count
from   (
  SELECT date(dates) as sdate
  FROM   generate_series(
           CAST('2019-07-01' as TIMESTAMP),
           CAST('2019-07-08' as TIMESTAMP),
           interval '1 day'
         ) AS dates
) m
LEFT JOIN (
  SELECT meter_number,
         date(read_time) as rdate,
         COUNT(*)  FROM meter_data.x
  WHERE  date(read_time) BETWEEN '2019-07-01' and  '2019-07-08'
  and    meter_number in ('92589492')
  GROUP BY meter_number,date(read_time)
)n
ON m.sdate=n.rdate ;

postgresql sql

3 commentaires

Veuillez ne pas ajouter de balises inutiles à vos questions. Cette question ne concerne pas la conception d'une base de données ou l'administration de la base de données et n'a besoin d'aucune de ces balises. Si vous passez la souris sur les balises, vous pouvez voir leur description et où elles sont pertinentes.

Je ne sais pas quel est votre problème, puisque nous n'avons aucun exemple de données, il n'y a aucune chance de valider votre requête. Mais sans cela: utiliser generate_series () est la bonne approche.

Quelle est la structure de la table et quel est le problème avec la sortie actuellement? À première vue, il semble que les 5 premières lignes ne sont pas nécessaires et il vous suffit d'ajouter COALESCE (COUNT (*)) à la première clause SELECT .

3 Réponses :

0
votes

Faire des hypothèses sur la structure de vos données (avec lesquelles vous pouvez jouer dans ceci db-fiddle , une syntaxe plus concise fonctionnera. L'astuce consiste à commencer par effectuer un CROSS JOIN sur les dates et les numéros de compteurs qui vous intéressent (cela crée essentiellement une matrice vide ) puis un LEFT JOIN de toutes les lignes pertinentes du meter_data (en les attachant aux cellules de la matrice appropriées) et enfin agréger avec COUNT () (ou tout aussi facilement, vous pouvez SUM ou AVERAGE ou MIN ou MAX ).

Dans ce snippet, j'utilise SELECT DISTINCT meter_number FROM meter_data pour obtenir la liste des numéros de compteurs qui pourraient nous intéresser:

select to_char(d,'YYYY-MM-DD'), mn.meter_number, count(meter_data.*)
from 
    generate_series ('2019-07-01'::date, '2019-07-06'::date, '1 day') d
    cross join 
    (select 92589492 as meter_number) mn
left join meter_data on meter_data.read_time=d and mn.meter_number=meter_data.meter_number
group by d, mn.meter_number
order by mn.meter_number, d;

Si vous le souhaitez recherchez un seul numéro de compteur, vous pouvez le brancher dans la requête de plusieurs façons, par exemple en remplaçant la sous-requête par S CHOISISSEZ 92589492 comme numéro_mètre :

select to_char(d,'YYYY-MM-DD'), mn.meter_number, count(meter_data.*)
from 
    generate_series ('2019-07-01'::date, '2019-07-06'::date, '1 day') d
    cross join 
    (select distinct meter_number from meter_data) mn
left join meter_data on meter_data.read_time=d and mn.meter_number=meter_data.meter_number
group by d, mn.meter_number
order by mn.meter_number, d;

essayez-le: db-fiddle

0 commentaires

0
votes

Vous avez déjà obtenu des réponses plus intelligentes que ce que je peux offrir, mais quelque chose à propos de COALESCE a attiré mon attention. Si vous n'avez pas de résultat, la fusion ne fait rien. La fusion nécessite une ligne pour travailler contre, donc si vous n'avez pas de résultat, vous ne pouvez pas en générer un synthétiquement avec une simple instruction COALESCE. Voici un exemple simple, où il n'y a pas de ligne avec un ID = 1:

- Vous n'obtenez rien car il n'y a rien contre lequel fusionner.

select coalesce(
        (select foo from bar where id = 1),
        'Undefined')

- Vous get Undefined car coalesce fonctionne ici. La requête imbriquée renvoie NULL, qui devient le premier argument à fusionner. Comme il est NULL et que le deuxième argument est "Undefined", vous récupérez une ligne avec "Undefined".

select coalesce(foo,'Undefined') from bar where id = 1;

0 commentaires

0
votes

Veuillez vérifier ma solution.

sélectionnez m.sdate, COALESCE (m.meterno, '') metre_number, CAS LORSQUE n.count EST NULL ALORS '0' ELSE n.count END count de (sélectionnez la date (d) comme sdate, mn.meterno from generate_series (CAST ('2019-07-01' comme TIMESTAMP), CAST ('2019-07-06' comme TIMESTAMP), intervalle '1 jour') d jointure croisée (sélectionnez regexp_split_to_table ('92590714_92590130_92589492_92590381', '_') comme meterno) mn) m JOINT GAUCHE (SELECT meter_number, date (read_time) comme rdate, COUNT (*) FROM metre_data.load_survey WHERE date (heure de lecture) ENTRE '2019-07-01' et '2019-07-06' et numéro_mètre dans ('92590714', '92590130', '92589492', '92590381')
GROUP BY numéro_mètre, date (heure de lecture)) n ON m.sdate = n.rdate et m.meterno = n.meter_number GROUP BY m.sdate, m.meterno, n.count

0 commentaires