Window Functions Filter Through Current Row

This is a follow-up to this question, where my query was improved to use window functions instead of aggregates inside a LATERAL join. While the query is now much faster, I've foun

Solution 1:

Can I use a frame and a filter?

You can. But either has restrictions:

• The expression in the FILTER clause only sees the respective row where it fetches values. There is no way to reference the row for which your window function computes values. So I don't see a way to formulate a filter depending on that row unless we make a huge, expensive cross join - the same row is used for many different computations. Or we are back to LATERAL subqueries that can reference the parent row.

• The frame definition on the other hand does not allow variables at all. It demands a fixed number, as discussed in the related answer you referenced:

  • Referencing current row in FILTER clause of window function

These restrictions make your particular query hard to implement. This should be correct now:

SELECT*FROM  (
   SELECT record_id, security_id, date, price
        , CASEWHEN do_calc THENmax(earnings) OVER w1     ENDAS peak_earnings
        , CASEWHEN do_calc THENmin(earnings) OVER w1     ENDAS minimum_earnings
        , CASEWHEN do_calc THEN price /NULLIF(max(earnings) OVER w1, 0) ENDAS price_to_peak_earnings
        , CASEWHEN do_calc THEN price /NULLIF(min(earnings) OVER w1, 0) ENDAS price_to_minimum_earnings
   FROM  (
      SELECT*, (date-365) >= min_date AND s.record_id ISNOTNULLAS do_calc
      FROM  (
         SELECT security_id, min_date
              , generate_series(min_date, max_date, interval'1 day')::dateASdateFROM  (
            SELECT security_id, min(date) AS min_date, max(date) AS max_date
            FROM   security_data
            GROUPBY1
            ) minmax
         ) d
      LEFTJOIN  security_data s USING (security_id, date)
      ) sub1
   WINDOW w1 AS (PARTITIONBY security_id ORDERBYdateROWSBETWEEN365 PRECEDING AND1 PRECEDING)
   ) sub2
WHERE  record_id ISNOTNULLORDERBY1, 2;

SQL Fiddle.

Notes

• Nothing in the question says that every security_id would have rows for the same days. Calculating min / max date per security_id in subquery minmax give us the minimum time frame.

• The time frame for calculations is exactly 365 day preceding the current date of the row and not including the current row (ROWS BETWEEN 365 PRECEDING AND 1 PRECEDING). It's typically more useful to exclude the current row from aggregations to be compared with the current row. I adapted the condition for calculations to the same time frame to avoid corner case oddities: (date - 365) >= min_date

• In the fiddle, where you added 1 row for every 1st of Jan, you can see the effect of leapyears contrasting with a fixed number of 365 day. The window frame is empty after leapyears (2001, 2005, ...).

• I am using all subqueries, which is typically a bit faster than CTEs.

• To be sure, we need to include ORDER BY in the frame definition. I updated my old answer you linked to accordingly:

  • Referencing current row in FILTER clause of window function

• I use w1 as window name, for the "1 year" period. You might add w2, etc. and can have any number of days for each. You could adapt to leapyears after all if you should need to. Might even generate the whole query depending on the current date ...