python - outer - postgresql join multiple tables

Postgres: ¿cómo devolver filas con 0 conteo de datos faltantes?

Question

He distribuido datos de manera desigual (fecha wrt) durante algunos años (2003-2008). Deseo consultar datos para un conjunto determinado de fechas de inicio y finalización, agrupando los datos por cualquiera de los intervalos admitidos (día, semana, mes, trimestre, año) en PostgreSQL 8.3 ( http://www.postgresql.org/docs) /8.3/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC ).

El problema es que algunas de las consultas dan resultados continuos durante el período requerido, como este:

select to_char(date_trunc(''month'',date), ''YYYY-MM-DD''),count(distinct post_id) 
from some_table where category_id=1 and entity_id = 77  and entity2_id = 115 
and date <= ''2008-12-06'' and date >= ''2007-12-01'' group by 
date_trunc(''month'',date) order by date_trunc(''month'',date);
          to_char   | count 
        ------------+-------
         2007-12-01 |    64
         2008-01-01 |    31
         2008-02-01 |    14
         2008-03-01 |    21
         2008-04-01 |    28
         2008-05-01 |    44
         2008-06-01 |   100
         2008-07-01 |    72
         2008-08-01 |    91
         2008-09-01 |    92
         2008-10-01 |    79
         2008-11-01 |    65
        (12 rows)

pero algunos pierden algunos intervalos porque no hay datos presentes, como este:

select to_char(date_trunc(''month'',date), ''YYYY-MM-DD''),count(distinct post_id) 
from some_table where category_id=1 and entity_id = 75  and entity2_id = 115 
and date <= ''2008-12-06'' and date >= ''2007-12-01'' group by 
date_trunc(''month'',date) order by date_trunc(''month'',date);

        to_char   | count 
    ------------+-------

     2007-12-01 |     2
     2008-01-01 |     2
     2008-03-01 |     1
     2008-04-01 |     2
     2008-06-01 |     1
     2008-08-01 |     3
     2008-10-01 |     2
    (7 rows)

donde el resultado requerido es:

  to_char   | count 
------------+-------
 2007-12-01 |     2
 2008-01-01 |     2
 2008-02-01 |     0
 2008-03-01 |     1
 2008-04-01 |     2
 2008-05-01 |     0
 2008-06-01 |     1
 2008-07-01 |     0
 2008-08-01 |     3
 2008-09-01 |     0
 2008-10-01 |     2
 2008-11-01 |     0
(12 rows)

Un recuento de 0 para entradas faltantes.

He visto discusiones anteriores sobre Stack Overflow, pero parece que no resuelven mi problema, ya que mi período de agrupación es uno de (día, semana, mes, trimestre, año) y la aplicación lo decidió en tiempo de ejecución. Por lo tanto, un enfoque como el de unirse a la izquierda con una tabla de calendario o tabla de secuencias no ayudará, supongo.

Mi solución actual a esto es llenar estos vacíos en Python (en una aplicación Turbogears) usando el módulo de calendario.

Hay una mejor manera de hacer esto.

Answer 1

Podrías crear una tabla temporal en tiempo de ejecución y unirla a ella. Eso parece tener más sentido.

Answer 2

Puede crear la lista de todos los primeros días del último año (por ejemplo) con

select distinct date_trunc(''month'', (current_date - offs)) as date 
from generate_series(0,365,28) as offs;
          date
------------------------
 2007-12-01 00:00:00+01
 2008-01-01 00:00:00+01
 2008-02-01 00:00:00+01
 2008-03-01 00:00:00+01
 2008-04-01 00:00:00+02
 2008-05-01 00:00:00+02
 2008-06-01 00:00:00+02
 2008-07-01 00:00:00+02
 2008-08-01 00:00:00+02
 2008-09-01 00:00:00+02
 2008-10-01 00:00:00+02
 2008-11-01 00:00:00+01
 2008-12-01 00:00:00+01

Entonces puedes unirte a esa serie.

Answer 3

^{Esta pregunta es vieja. Pero como otros usuarios lo escogieron como maestro para un nuevo duplicado, estoy agregando una respuesta adecuada.}

Solución adecuada

SELECT *
FROM  (
   SELECT day::date
   FROM   generate_series(timestamp ''2007-12-01''
                        , timestamp ''2008-12-01''
                        , interval  ''1 month'') day
   ) d
LEFT   JOIN (
   SELECT date_trunc(''month'', date_col)::date AS day
        , count(*) AS some_count
   FROM   tbl
   WHERE  date_col >= date ''2007-12-01''
   AND    date_col <= date ''2008-12-06''
-- AND    ... more conditions
   GROUP  BY 1
   ) t USING (day)
ORDER  BY day;

• Use LEFT JOIN , por supuesto.

• generate_series() puede producir una tabla de marcas de tiempo sobre la marcha, y muy rápido.

• En general, es más rápido agregar antes de unirse. Recientemente proporcioné un caso de prueba en sqlfiddle.com en esta respuesta relacionada:

  • PostgreSQL - ordenar por una matriz

• Transmite la timestamp de timestamp a la date ( ::date ) para un formato básico. Para más uso to_char() .

• GROUP BY 1 es la sintaxis abreviada para hacer referencia a la primera columna de salida. Podría ser GROUP BY day también, pero eso podría entrar en conflicto con una columna existente del mismo nombre. O GROUP BY date_trunc(''month'', date_col)::date pero eso es demasiado largo para mi gusto.

• Funciona con los argumentos de intervalo disponibles para date_trunc() .

• count() nunca produce NULL ( 0 para ninguna fila), pero lo hace LEFT JOIN .
  Para devolver 0 lugar de NULL en el SELECT externo, use COALESCE(some_count, 0) AS some_count . El manual.

• Para una solución más genérica o intervalos de tiempo arbitrarios, considere esta respuesta estrechamente relacionada:

  • La mejor forma de contar registros por intervalos de tiempo arbitrarios en Rails + Postgres