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
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().
