[PYTHON] Postgres - 누락 된 데이터가 0 인 행을 반환하는 방법?

Postgres - 누락 된 데이터가 0 인 행을 반환하는 방법?

나는 몇 년 동안 (2003 ~ 2008 년) 데이터를 고르지 않게 분산시켰다. PostgreSQL 8.3 (http://www.postgresql.org/docs)에서 지원되는 간격 (일, 주, 월, 분기, 연도)에 따라 데이터를 그룹화하여 주어진 시작 및 종료 날짜 집합에 대한 데이터를 쿼리하려고합니다. /8.3/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC).

문제는 쿼리 중 일부가 필요한 기간 동안 결과를 제공한다는 것입니다. 이 하나로서 :

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)

그러나 일부는 데이터가 존재하지 않기 때문에 일정 간격을 놓치게됩니다.

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)

여기서 필요한 결과 집합은 다음과 같습니다.

  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)

누락 된 항목 수가 0입니다.

Stack Overflow에 대한 이전 토론을 보았지만 내 그룹화 기간이 (일, 주, 월, 분기, 일) 중 하나이며 응용 프로그램의 런타임에 결정된 이후로 내 문제를 해결하지 못합니다. 따라서 캘린더 테이블 또는 시퀀스 테이블과의 왼쪽 조인과 같은 접근 방식은 내가 추측하는 데 도움이되지 않습니다.

이에 대한 현재의 해결책은 달력 모듈을 사용하여 파이썬 (Turbogears App의 경우)에서 이러한 틈을 메우는 것입니다.

이 작업을 수행하는 더 좋은 방법이 있습니까?

해결법

1. ==============================

   1.작년의 모든 첫날 목록을 만들 수 있습니다 (말하십시오).

   작년의 모든 첫날 목록을 만들 수 있습니다 (말하십시오).

   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
   

   그런 다음 해당 시리즈에 가입 할 수 있습니다.

2. ==============================

   2.이 질문은 오래되었습니다. 그러나 동료 사용자가 새 복제본을 마스터로 선택했기 때문에 적절한 대답을 추가하고 있습니다.

   이 질문은 오래되었습니다. 그러나 동료 사용자가 새 복제본을 마스터로 선택했기 때문에 적절한 대답을 추가하고 있습니다.

   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;
   

3. ==============================

   3.런타임에 임시 테이블을 만들고 그 테이블에 왼쪽 조인을 할 수 있습니다. 그것은 가장 합리적인 것처럼 보입니다.

   런타임에 임시 테이블을 만들고 그 테이블에 왼쪽 조인을 할 수 있습니다. 그것은 가장 합리적인 것처럼 보입니다.

from https://stackoverflow.com/questions/346132/postgres-how-to-return-rows-with-0-count-for-missing-data by cc-by-sa and MIT license