Присоединиться к запросу на счетчик generate_series в postgres, а также получить нулевые значения как "0"

То, что я хочу получить, - это статистика по каждому месяцу из generate_series и сумма подсчитанных идентификаторов за каждый месяц. Этот SQL работает в PostgreSQL 9.1:

  SELECT (to_char(serie,'yyyy-mm')) AS year, sum(amount)::int AS eintraege FROM (
    SELECT  
       COUNT(mytable.id) as amount,   
       generate_series::date as serie   
       FROM mytable  

    RIGHT JOIN generate_series(  

       (SELECT min(date_from) FROM mytable)::date,   
       (SELECT max(date_from) FROM mytable)::date,  
       interval '1 day') ON generate_series = date(date_from)  
       WHERE version = 1   
       GROUP BY generate_series       
       ) AS foo  
  GROUP BY Year   
  ORDER BY Year ASC;

И это мой вывод

"2006-12" | 4  
"2007-02" | 1  
"2007-03" | 1

Но то, что я хочу получить, является этим выводом (значение "0" в январе):

"2006-12" | 4  
"2007-01" | 0  
"2007-02" | 1  
"2007-03" | 1

Так что если есть месяц без идентификатора, он должен быть указан в списке. Есть идеи как решить это?

Вот некоторые примеры данных:

drop table if exists mytable;
create table mytable(id bigint, version smallint, date_from timestamp without time zone);
insert into mytable(id, version, date_from) values

('4084036', '1', '2006-12-22 22:46:35'),
('4084938', '1', '2006-12-23 16:19:13'),
('4084938', '2', '2006-12-23 16:20:23'),
('4084939', '1', '2006-12-23 16:29:14'),
('4084954', '1', '2006-12-23 16:28:28'),
('4250653', '1', '2007-02-12 21:58:53'),
('4250657', '1', '2007-03-12 21:58:53')
;
Источник

1 ответ

Решение

Не распутанный, упрощенный и исправленный, это может выглядеть так:

SELECT to_char(s.tag,'yyyy-mm') AS monat
     , count(t.id) AS eintraege
FROM  (
   SELECT generate_series(min(date_from)::date
                        , max(date_from)::date
                        , interval '1 day'
          )::date AS tag
   FROM   mytable t
   ) s
LEFT   JOIN mytable t ON t.date_from::date = s.tag AND t.version = 1   
GROUP  BY 1
ORDER  BY 1;

дБ<> скрипка здесь

Среди всего шума, вводящих в заблуждение идентификаторов и нетрадиционного формата здесь была скрыта настоящая проблема:

WHERE version = 1

Пока вы правильно использовали RIGHT [OUTER] JOINВы отменили усилие, добавив WHERE пункт, который требует отличного значения от mytable- преобразование RIGHT JOIN к JOIN эффективно.

Вытянуть пункт в JOIN условие, чтобы сделать эту работу.

Я упростил некоторые другие вещи.

Связанные с:

Источник
Другие вопросы по тегам