How to get number of invoices for last 12 weeks in Postgres

Question

Invoice database contains invoice dates:

create table dok (
  dokumnr serial primary key,
  invoicedate date not null
);

Dashboard requires comma separated list containing number of invoices for last 12 weeks, e.q

4,8,0,6,7,6,0,6,0,4,5,6

List contains always 12 elements. If there are no invoices for some 7 day interval, 0 should appear. Every element should contain number of invoices for 7 days.

Query should find maximum date before current date:

select max(invoicedate) as last_date from dok;

And after that probably use count(*) and string_agg() to create list.

Last (12th) element should contain number of invoices for

  last_date .. last_date-interval'6days'

11 element (one before last) should contain number of invoices for days

  last_date-interval'7days' .. last_date-interval'14days'

etc.

How to write this query in Postgres 9.1+ ? This is ASP.NET MVC3 C# application and some parts of query can also done in C# code if this helps.

I ended with

with list as (

SELECT count(d.invoicedate) as cnt
   FROM  (
      SELECT max(invoicedate) AS last_date
      FROM   dok
      WHERE   invoicedate< current_date
      ) l
   CROSS  JOIN generate_series(0, 11*7, 7) AS g(days)
   LEFT   JOIN dok d ON d.invoicedate>  l.last_date - g.days - 7
                    AND d.invoicedate<= l.last_date - g.days
   GROUP  BY g.days
   ORDER  BY g.days desc
)

SELECT string_agg( cnt::text,',')
from list

Answer 1

CROSS JOIN the latest date to generate_series(), followed by a LEFT JOIN to the main table.

SELECT ARRAY(
   SELECT count(d.invoicedate) AS ct
   FROM  (
      SELECT max(invoicedate) AS last_date
      FROM   dok
      WHERE  invoicedate < current_date  -- "maximum date before current date"
      ) l
   CROSS  JOIN generate_series(0, 11*7, 7) AS g(days)
   LEFT   JOIN dok d ON d.invoicedate >  l.last_date - g.days - 7
                    AND d.invoicedate <= l.last_date - g.days
   GROUP  BY g.days
   ORDER  BY g.days
   );

Assuming there is at least one valid entry in the table,
this returns an array of bigint (bigint[]) with the latest week first.

current_date depends on the timezone setting of your session.

If you need the result to be a comma-separated string you could use another query layer with string_agg() instead. Or you feed the above to array_to_string():

SELECT array_to_string(ARRAY(SELECT ...), ',');

Your query audited:

It's an implementation detail, but it's documented:

The aggregate functions array_agg, json_agg, jsonb_agg, json_object_agg, jsonb_object_agg, string_agg, and xmlagg, as well as similar user-defined aggregate functions, produce meaningfully different result values depending on the order of the input values. This ordering is unspecified by default, but can be controlled by writing an ORDER BY clause within the aggregate call, as shown in Section 4.2.7. Alternatively, supplying the input values from a sorted subquery will usually work. For example:

SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;

Beware that this approach can fail if the outer query level contains additional processing, such as a join, because that might cause the subquery's output to be reordered before the aggregate is computed.

Bold emphasis mine.
To stay standard compliant, you could write:

WITH list AS (
   SELECT g.days, count(d.invoicedate)::text AS cnt
   FROM  (
      SELECT max(invoicedate) AS last_date
      FROM   dok
      WHERE  invoicedate < current_date
      ) l
   CROSS  JOIN generate_series(0, 11*7, 7) AS g(days)
   LEFT   JOIN dok d ON d.invoicedate >  l.last_date - g.days - 7
                    AND d.invoicedate <= l.last_date - g.days
   GROUP  BY 1
   )
SELECT string_agg(cnt, ',' ORDER BY days DESC)
FROM   list;

But this is a bit slower. Also, the CTE is not technically necessary and also a bit slower than a subquery.
SELECT array_to_string(ARRAY( SELECT ...), ',') like I proposed is fastest because the array constructor is faster for a single result than the aggregate function string_agg().