Requête PostgreSQL pour compter/Grouper par jour et afficher les jours sans données

je dois créer une requête PostgreSQL qui retourne

  • un jour
  • le nombre d'objets trouvés pour ce jour

il est important que tous les jours apparaissent dans les résultats , même si aucun objet n'a été trouvé ce jour-là. (Cela a déjà été discuté, mais je n'ai pas été en mesure de faire fonctionner les choses dans mon cas particulier.)

D'Abord, Je trouvé une requête sql pour générer une gamme de jours , avec lequel je peux rejoindre: 

SELECT to_char(date_trunc('day', (current_date - offs)), 'YYYY-MM-DD')
AS date 
FROM generate_series(0, 365, 1) 
AS offs

résultats dans: 

    date    
------------
 2013-03-28
 2013-03-27
 2013-03-26
 2013-03-25
 ...
 2012-03-28
(366 rows)

maintenant j'essaie de joindre cela à une table appelée 'sharer_emailshare' qui a une colonne' created': 

Table 'public.sharer_emailshare'
column    |   type  
-------------------
id        | integer
created   | timestamp with time zone
message   | text
to        | character varying(75)

Voici la meilleure "151960920 de la requête" j'ai jusqu'à présent: 

SELECT d.date, count(se.id) FROM (
    select to_char(date_trunc('day', (current_date - offs)), 'YYYY-MM-DD')
    AS date 
    FROM generate_series(0, 365, 1) 
    AS offs
    ) d 
JOIN sharer_emailshare se 
ON (d.date=to_char(date_trunc('day', se.created), 'YYYY-MM-DD'))  
GROUP BY d.date;

les résultats: 

    date    | count 
------------+-------
 2013-03-27 |    11
 2013-03-24 |     2
 2013-02-14 |     2
(3 rows)

désirée résultats: 

    date    | count 
------------+-------
 2013-03-28 |     0
 2013-03-27 |    11
 2013-03-26 |     0
 2013-03-25 |     0
 2013-03-24 |     2
 2013-03-23 |     0
 ...
 2012-03-28 |     0
(366 rows)

si je comprends bien , c'est parce que j'utilise un simple (implicite INNER ) JOIN , et c'est le comportement attendu, comme discuté dans les docs postgres .

j'ai regardé à travers des douzaines de solutions StackOverflow, et toutes celles avec des requêtes de travail semblent spécifiques à MySQL/Oracle/MSSQL et j'ai du mal à les traduire en PostgreSQL.

Le gars qui a demandé cette question a trouvé sa réponse, avec Postgres, mais l'a mise sur un lien pastebin qui a expiré il ya quelque temps.

j'ai essayé de passer à LEFT OUTER JOIN , RIGHT JOIN , RIGHT OUTER JOIN , CROSS JOIN , utilisez une déclaration CASE pour sub dans une autre valeur si null, COALESCE pour fournir une valeur par défaut, etc, mais je n'ai pas été en mesure de les utiliser d'une manière qui me donne ce dont j'ai besoin.

toute assistance est apprécié! Et je te promets de se déplacer à la lecture que le géant de PostgreSQL livre bientôt ;)

50
demandé sur Community 2013-03-29 00:04:09

3 réponses

vous avez juste besoin d'un left outer join au lieu d'un joint intérieur: 

SELECT d.date, count(se.id)
FROM (SELECT to_char(date_trunc('day', (current_date - offs)), 'YYYY-MM-DD') AS date 
      FROM generate_series(0, 365, 1) AS offs
     ) d LEFT OUTER JOIN
     sharer_emailshare se 
     ON d.date = to_char(date_trunc('day', se.created), 'YYYY-MM-DD'))  
GROUP BY d.date;
43
répondu Gordon Linoff 2017-01-22 02:09:26

en prolongeant la réponse utile de Gordon Linoff, je suggérerais quelques améliorations telles que:

  • utiliser ::date au lieu de date_trunc('day', ...)
  • rejoignez un type de date plutôt qu'un type de caractère (c'est plus propre).
  • utilisez des plages de dates spécifiques pour qu'elles soient plus faciles à changer plus tard. Dans ce cas, je choisis un an avant la dernière entrée dans le tableau - quelque chose qui n'aurait pas pu être fait facilement avec le autre requête.
  • calcule les totaux pour une sous-série arbitraire (en utilisant un CTE). Vous n'avez qu'à lancer la colonne d'intérêt sur le type date et l'appeler date_column.
  • inclure une colonne pour le total cumulatif. (Pourquoi pas?)

voici ma requête: 

WITH dates_table AS (
    SELECT created::date AS date_column FROM sharer_emailshare WHERE showroom_id=5
)
SELECT series_table.date, COUNT(dates_table.date_column), SUM(COUNT(dates_table.date_column)) OVER (ORDER BY series_table.date) FROM (
    SELECT (last_date - b.offs) AS date
        FROM (
            SELECT GENERATE_SERIES(0, last_date - first_date, 1) AS offs, last_date from (
                 SELECT MAX(date_column) AS last_date, (MAX(date_column) - '1 year'::interval)::date AS first_date FROM dates_table
            ) AS a
        ) AS b
) AS series_table
LEFT OUTER JOIN dates_table
    ON (series_table.date = dates_table.date_column)
GROUP BY series_table.date
ORDER BY series_table.date

j'ai testé la requête, et elle produit les mêmes résultats, plus la colonne pour total cumulatif.

24
répondu Travis 2014-03-26 22:35:30

en me basant sur la réponse de Gordon Linoff, j'ai réalisé qu'un autre problème était que j'avais une clause WHERE que je n'avais pas mentionnée dans la question originale.

au lieu d'un nu WHERE , j'ai fait un subquery: 

SELECT d.date, count(se.id) FROM (
    select to_char(date_trunc('day', (current_date - offs)), 'YYYY-MM-DD')
    AS date 
    FROM generate_series(0, 365, 1) 
    AS offs
    ) d 
LEFT OUTER JOIN (
    SELECT * FROM sharer_emailshare 
    WHERE showroom_id=5
) se
ON (d.date=to_char(date_trunc('day', se.created), 'YYYY-MM-DD')) 
GROUP BY d.date;
7
répondu Marcel Chastain 2013-03-28 22:20:03