窗口函数通过当前行过滤

Question

这是该问题的后续内容，其中对我的查询进行了改进，使其使用窗口函数而不是联接内的聚合LATERAL。虽然查询现在快得多，但我发现结果不正确。

我需要在x年尾随时间框架上执行计算。例如，price_to_maximum_earnings每行的计算方法是：max(earnings)十年前移至当前行，然后除以price结果。为了简单起见，我们将使用1年。

SQL Fiddle对此问题进行了解答。（Postgres 9.6）

作为一个简单的例子，price和peak_earnings用于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';

要做到这一点 ，每行 ，我用的是以下情况：

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_earningsandminimum_earnings均为整个数据集的最大值和最小值。它们
应该 是earnings1年前到当前行的最大值/最小值。

这里发生了什么？从这个问题的答案中我知道我不能简单地说FILTER (WHERE date >= date1 AND date <=current_row.date)，那么我是否缺少解决方案？我无法使用窗口框架，因为在任何给定的时间范围内，行数都是不确定的，所以我不能只说OVER (ROWS BETWEEN 365 PRECEDING AND CURRENT ROW)。我可以使用框架 和
滤镜吗？那可能会超过一年，然后筛选器可以捕获每个无效的日期。我已经尝试过了，但是没有成功。

Answer 1

我可以使用框架和滤镜吗？

你 可以的 。但是两者都有限制：

• FILTER子句中的表达式只能看到其获取值的相应行。无法引用窗口函数为其计算值的行。因此 ， 除非我们进行 巨大而昂贵的 交叉 联接， 否则我看不到根据 该 行制定过滤器的方法-同一行用于许多不同的计算。或者，我们又回到了LATERAL那个子查询 可 引用父行。

这些限制使您的特定查询难以实施。现在应该是 正确的 ：

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提琴。

笔记

• 问题中没有什么可以说每个security_id人在同一天都有行。计算security_id子查询中每个的最小/最大日期minmax将为我们提供最短的时间范围。

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

• 在小提琴中，在1月1日的每一行中添加了1行，您可以看到leap年与固定天数365天形成对比的效果。leap年（2001，2005，…）之后，窗框为空。

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

• 可以肯定的是，我们需要ORDER BY在框架定义中包括。

• 我将“ 1年”期间w1用作窗口 名称 。您可以添加w2，等等，每个可以有任意天数。毕竟，如果需要，您可以适应leap年。甚至可能根据当前日期生成整个查询…