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

Question

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

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

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

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+ 中编写此查询？这是 ASP.NET MVC3 C# 应用程序，如果有帮助，部分查询也可以在 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 取决于您 session 的 timezone 设置。

如果您需要结果是逗号分隔的字符串，您可以使用另一个带有 string_agg() 的查询层。或者您将以上内容提供给 array_to_string():

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

您的查询已审核:

这是一个实现细节，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.

大胆强调我的。
为了符合标准，您可以这样写:

WITH list AS (
   SELECT <b>g.days,</b> 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, ',' <b>ORDER BY days DESC</b>)
FROM   list;

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