Postgres default aggregation value for empty row

I have written a query that's doing an aggregation of X.

It starts from this week's Monday up to current day.

select 
  extract(dow from time_sent) days
  , count(id)
from messages
where time_sent between date_trunc('week', now()) and now()
group by days
order by days;

I would like a hint on how to set a default value for Monday when no row was added on Monday.

ie, Monday was empty, so I'm getting this:

[ anonymous { day: 2, count: '1' },
  anonymous { day: 3, count: '1' },
  anonymous { day: 4, count: '1' },
  anonymous { day: 5, count: '1' },
  anonymous { day: 6, count: '1' } ]

Expected result:

[ anonymous { day: 1, count: '0' },
  anonymous { day: 2, count: '1' },
  anonymous { day: 3, count: '1' },
  anonymous { day: 4, count: '1' },
  anonymous { day: 5, count: '1' },
  anonymous { day: 6, count: '1' } ]

Edit: Add table structure and sample output.

id | time_sent 
1  | 2018-01-13 15:26:21.443828+08
2  | 2018-01-12 15:26:21.44755+08
3  | 2018-01-11 15:26:21.459208+08
4  | 2018-01-10 15:26:21.440648+08
5  | 2018-01-09 15:26:21.457262+08

Query #1:

select 
  coalesce(extract(dow from time_sent), d) days
  , count(message_batch_id)
from generate_series(0,6) d
left join messages 
on d = extract(dow from time_sent)
where time_sent between date_trunc('week', now()) and now()
group by days
order by days;

Query #1 output:

days | count
2    | 1
3    | 1
4    | 1
5    | 1
6    | 1

Edit: I need to clarify that the messages table didn't have any row entry on Monday.

Edit:

For some reason, this query returns the row structure that I wanted:

select 
    extract(dow from d.*) days, 
    count(id) 
from GENERATE_SERIES(DATE_TRUNC('WEEK', NOW()), NOW(), '1 
DAY'::INTERVAL) d
left outer join messages m
on m.time_sent::DATE = d
group by days
order by days;

2 answers

  • answered 2018-01-13 17:19 Vao Tsun

    try:

    select 
      coalesce(extract(dow from time_sent),d) days
      , count(id)
    from generate_series(0,6) d
    left outer join messages on d = extract(dow from time_sent)
    where time_sent between date_trunc('week', now()) and now()
    group by days
    order by days;
    

    (did not test)

  • answered 2018-01-13 17:20 clemens

    The predicate can't extend the rows. You have to generate the dates with GENERATE_SERIES and join this with messages:

    SELECT extract(dow from time_sent) days, coalesce(count(id), 0)
    FROM GENERATE_SERIES(DATE_TRUNC('WEEK', NOW()), NOW(), '1 DAY'::INTERVAL) d 
        LEFT JOIN messages ON time_sent::DATE = d
    GROUP BY days
    ORDER BY days;