如何调整此查询以使用窗口函数

Question

当我开始解决这个问题时，我想，"这将是一个了解窗口函数的好查询。"我最终没能让它与窗口函数一起工作，但我可以使用联接获得我想要的东西。

您将如何调整此查询以使用窗口函数：

SELECT
    day,
    COUNT(i.project) as num_open
FROM generate_series(0, 364) as t(day)
    LEFT JOIN issues i on (day BETWEEN i.closed_days_ago AND i.created_days_ago)
GROUP BY day
ORDER BY day;

上面的查询获取了一个问题列表，其范围由created_days_ago和closed_days ago表示，在过去365天内，它将统计该特定日期已创建但尚未关闭的问题数量。

http://sqlfiddle.com/#！15/663f6/2

issues表看起来像：

CREATE TABLE issues (
  id SERIAL,
  project VARCHAR(255),
  created_days_ago INTEGER,
  closed_days_ago INTEGER);

我想的是，给定日期的分区应该包括问题中的所有行，其中日期介于创建日期和关闭日期之间。类似SELECT day, COUNT(i.project) OVER (PARTITION day BETWEEN created_days_ago AND closed_days_ago) ...

我以前从未使用过窗口函数，所以我可能缺少一些基本的东西，但这似乎正是使窗口函数如此出色的查询类型。

Answer 1

使用generate_series()来创建完整的日期范围，包括没有更改的日期，因此issues表中没有行，这一事实并不排除使用窗口函数。

事实上，这个查询的运行速度是我本地测试中Q中的查询的50倍：

SELECT t.day
      ,  COALESCE(sum(a.created) OVER (ORDER BY t.day DESC), 0)
       - COALESCE(sum(b.closed)  OVER (ORDER BY t.day DESC), 0) AS open_tickets
FROM   generate_series(0, 364) t(day)
LEFT   JOIN (SELECT created_days_ago AS day, count(*) AS created
             FROM   issues GROUP BY 1) a USING (day)
LEFT   JOIN (SELECT closed_days_ago AS day, count(*) AS closed
             FROM   issues GROUP BY 1) b USING (day)
ORDER  BY 1;

这也是正确的，与问题中的查询相反，该查询在第0天导致17张未结门票，尽管所有门票都已关闭
该错误是由于联接条件中的BETWEEN导致的，该条件包括上边界和下边界。通过这种方式，门票在关闭当天仍被视为"开放"。

结果中的每一行都反映了当天结束时的未结票证数量。

解释

查询将窗口函数与聚合函数组合在一起。

• 子查询a统计每天创建的票证数量。这样每天只排一排，让休息变得更容易
  子查询b对已关闭的票证执行相同操作。

• 使用LEFT join联接到子查询t中生成的天数列表
  小心加入多个未聚合的表！这可能会在联接的表中触发每行多个匹配的CROSS JOIN，从而生成不正确的结果。比较：
  两个SQL LEFT JOIN产生错误的结果

• 最后，使用两个窗口函数来计算已创建票证与已关闭票证的运行总数
  另一种选择是将其用于外部SELECT

  sum(COALESCE(a.created, 0)
    - COALESCE(b.closed,  0)) OVER (ORDER BY t.day DESC) AS open_tickets
  

  在我的测试中执行相同的操作。

->SQLfiddle演示

旁白：我永远不会在表中存储"days_ago"，而是绝对日期/时间戳。看起来是为了这个问题的目的而简化的。