如何在Postgres中获取过去12周的发票数量

Question

发票数据库包含发票日期：

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

仪表板需要逗号分隔的列表，其中包含过去 12 周的发票数量，例如

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

列表始终包含 12 个元素。如果大约 7 天间隔内没有发票，则应显示 0。每个元素都应包含 7 天的发票数量。

查询应查找当前日期之前的最大日期：

select max(invoicedate) as last_date from dok;

之后可能使用 count（*） 和 string_agg（） 来创建列表。

最后一个（第 12 个）元素应包含

  last_date .. last_date-interval'6days'

11 元素（最后一个之前）应包含天数的发票数量

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

等。

如何在Postgres 9.1+中编写此查询？这是 MVC3 C# 应用程序 ASP.NET，如果有帮助，查询的某些部分也可以在 C# 代码中完成。

我以

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 generate_series()的最新日期，后跟主表的LEFT JOIN。

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
   );

假设表中至少有一个有效条目，
这将返回一个 bigint （bigint[]） 数组，最近一周排在第一位。

current_date取决于会话的timezone设置。

如果需要结果为逗号分隔的字符串，则可以改用另一个带有 string_agg() 的查询图层。或者你把上面的东西喂给array_to_string()：

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

您的查询已审核：

这是一个实现细节，但它有文档记录：

聚合函数array_agg、json_agg、jsonb_agg、 json_object_agg、jsonb_object_agg、string_agg和xmlagg，如 以及类似的用户定义的聚合函数，产生有意义的 不同的结果值取决于输入值的顺序。 默认情况下，此顺序未指定，但可以通过 在聚合调用中编写 ORDER BY 子句，如 第 4.2.7 节.或者，从 排序的子查询通常有效。例如：

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

请注意，如果外部查询级别包含 其他处理，例如联接，因为这可能会导致 在计算聚合之前要重新排序的子查询输出。

大胆强调我的。
为了保持标准合规性，您可以编写：

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;

但这有点慢。此外，CTE 在技术上不是必需的，也比子查询慢一点。
SELECT array_to_string(ARRAY( SELECT ...), ',')像我建议的那样是最快的，因为数组构造函数对于单个结果比聚合函数string_agg()更快。