窗口函数过滤当前行

这是后续this https://stackoverflow.com/questions/48000013/optimize-slow-aggregates-in-lateral-join问题，我的查询被改进为使用窗口函数而不是内部的聚合LATERAL加入。虽然现在查询速度快得多，但我发现结果不正确。

我需要对 x 年跟踪时间范围执行计算。例如，price_to_maximum_earnings通过获取每行计算max(earnings)十多年前到当前行，并除以price通过结果。为简单起见，我们在这里使用 1 年。

SQL小提琴 http://sqlfiddle.com/#!17/648ff/11对于这个问题。 （Postgres 9.6）

举个简单的例子，price and peak_earnings for 2010-01-01可以像这样单独计算：

SELECT price
FROM security_data
WHERE date = '2010-01-01'
AND security_id = 'SPX';

SELECT max(earnings) AS min_earnings
FROM bloomberg.security_data
WHERE date >= '2000-01-01'
AND date <= '2010-01-01'
AND security_id = 'SPX';

去做这个per row，我使用以下内容：

SELECT security_id, date, price
     , CASE WHEN date1 >= min_date
            THEN price / NULLIF(max(earnings) FILTER (WHERE date >= date1) OVER w, 0) END AS price_to_peak_earnings
FROM
(
  SELECT record_id, security_id, price, date, earnings
           , (date - interval '1 y')::date AS date1
           , min(date) OVER (PARTITION BY security_id) AS min_date
      FROM   security_data
) d
WINDOW w AS (PARTITION BY security_id);

我相信这里的问题源于使用FILTER，因为它似乎没有按照我想要的方式工作。请注意，在链接的 SQL Fiddle 中，我显示了以下结果FILTER，并且对于每一行peak_earnings and minimum_earnings只是整个数据集的最大值和最小值。他们should是最大/最小值earnings从 1 年前到当前行。

这里发生了什么？我从答案中知道this https://stackoverflow.com/questions/31396434/referencing-current-row-in-filter-clause-of-window-function我不能简单地说出的问题FILTER (WHERE date >= date1 AND date <= current_row.date)，那么我缺少什么解决方案吗？我不能使用窗框，因为在任何给定的时间范围内我的行数都不确定，所以我不能只是说OVER (ROWS BETWEEN 365 PRECEDING AND CURRENT ROW)。我可以使用框架吗and过滤器？这可能是一年多以前，然后过滤器可以捕获每个无效日期。我已经尝试过这个但没有成功。

我可以使用框架和过滤器吗？

You can。但两者都有限制：

• 中的表达式为FILTER子句只能看到它获取值的相应行。无法引用窗口函数计算值的行。所以我没有找到一种方法来制定过滤器that行除非我们做了一个巨大、昂贵交叉连接 - 同一行用于许多不同的计算。或者我们回到LATERAL子查询can引用父行。

• 另一方面，框架定义根本不允许变量。它需要一个固定的数字，正如您引用的相关答案中所讨论的：

  • 在窗口函数的 FILTER 子句中引用当前行 https://stackoverflow.com/questions/31396434/referencing-current-row-in-filter-clause-of-window-function

这些限制使您的特定查询难以实现。这应该是correct now:

SELECT *
FROM  (
   SELECT record_id, security_id, date, price
        , CASE WHEN do_calc THEN                max(earnings) OVER w1     END AS peak_earnings
        , CASE WHEN do_calc THEN                min(earnings) OVER w1     END AS minimum_earnings
        , CASE WHEN do_calc THEN price / NULLIF(max(earnings) OVER w1, 0) END AS price_to_peak_earnings
        , CASE WHEN do_calc THEN price / NULLIF(min(earnings) OVER w1, 0) END AS price_to_minimum_earnings
   FROM  (
      SELECT *, (date - 365) >= min_date AND s.record_id IS NOT NULL AS do_calc
      FROM  (
         SELECT security_id, min_date
              , generate_series(min_date, max_date, interval '1 day')::date AS date
         FROM  (
            SELECT security_id, min(date) AS min_date, max(date) AS max_date
            FROM   security_data
            GROUP  BY 1
            ) minmax
         ) d
      LEFT   JOIN  security_data s USING (security_id, date)
      ) sub1
   WINDOW w1 AS (PARTITION BY security_id ORDER BY date ROWS BETWEEN 365 PRECEDING AND 1 PRECEDING)
   ) sub2
WHERE  record_id IS NOT NULL 
ORDER  BY 1, 2;

SQL 小提琴。 http://sqlfiddle.com/#!17/b0fd2/2

Notes

• 问题中没有任何内容表明每个security_id将会有同一天的行。计算最小/最大日期security_id在子查询中minmax给我们最短的时间范围。

• 计算的时间范围正好是该行当前日期之前的 365 天，并且not包括当前行（ROWS BETWEEN 365 PRECEDING AND 1 PRECEDING）。它通常更有用exclude聚合中的当前行与当前行进行比较。
  我将计算条件调整为同一时间范围，以避免出现极端情况：(date - 365) >= min_date

• In the fiddle http://sqlfiddle.com/#!17/b0fd2/2，您为每年 1 月 1 日添加 1 行，您可以看到闰年与固定天数 365 天的对比效果。闰年（2001 年、2005 年……）之后窗框是空的。

• 我正在使用所有子查询，这通常比 CTE 快一点。

• 可以肯定的是，我们需要包括ORDER BY在框架定义中。我相应地更新了您链接到的旧答案：

  • 在窗口函数的 FILTER 子句中引用当前行 https://stackoverflow.com/questions/31396434/referencing-current-row-in-filter-clause-of-window-function

• I use w1作为窗口name，“1 年”期间。你可能会添加w2等，并且每个可以有任意天数。如果你需要的话，你毕竟可以适应闰年。甚至可能根据当前日期生成整个查询......