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

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 前表示，并且在过去 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 表中没有行，< em>不排除使用窗口函数。

事实上，这个查询比我本地测试中 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 引起的，其中包括 和 上边框。这样，门票在关闭当天仍被视为“开放”。

结果中的每一行都反射(reflect)了当天结束时未结工单的数量。

解释

查询将窗口函数与聚合函数结合。

• 子查询a 统计每天创建的工单数量。这导致每天一行，使休息更容易。
  子查询 b 对已关闭的工单执行相同的操作。

• 使用 LEFT JOIN 连接到子查询 t 中生成的天数列表。
  小心加入多个未聚合的表!这可能会触发连接表之间的 CROSS JOIN 以获取每行的多个匹配项，从而生成不正确的结果。比较:
  Two SQL LEFT JOINS produce incorrect result

• 最后使用两个窗口函数计算已创建工单与已关闭工单的运行总数。
  另一种方法是在外部 SELECT

  中使用它

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

  在我的测试中表现相同。

-> SQLfiddle demo.

旁白:我绝不会在表格中存储“days_ago”，而是存储绝对日期/时间戳。看起来像是为了这个问题的目的而进行的简化。