PostgreSQL窗口函数带过滤条件：多类型最近两次值获取方案

Got it, so you need to get the most recent and second-most recent dates for types A/B/C that are earlier than the current row's date, but PostgreSQL doesn't support putting a FILTER clause directly in window functions for this specific use case. No problem—here are a couple of solid approaches to achieve what you want:

Approach 1: Use LATERAL Joins (Intuitive & Flexible) #

LATERAL joins let you run a subquery for each row in your main table, which is perfect here because we need to fetch the top 2 dates per type that are older than the current row's date.

First, let's assume your table tab_A has columns like id, type, and date_col (adjust the column names to match your actual schema). Here's the query:

SELECT
    t.id,
    t.type,
    t.date_col,
    a.lag1 AS A_lag_1,
    a.lag2 AS A_lag_2,
    b.lag1 AS B_lag_1,
    b.lag2 AS B_lag_2,
    c.lag1 AS C_lag_1,
    c.lag2 AS C_lag_2
FROM tab_A t
LEFT JOIN LATERAL (
    SELECT
        date_col AS lag1,
        -- Get the second-most recent by lagging in the sorted list
        LAG(date_col) OVER (ORDER BY date_col DESC) AS lag2
    FROM tab_A
    WHERE type = 'A' AND date_col < t.date_col
    ORDER BY date_col DESC  -- Sort newest first
    LIMIT 1  -- Only need the top row, since LAG grabs the one before it
) a ON true
LEFT JOIN LATERAL (
    SELECT
        date_col AS lag1,
        LAG(date_col) OVER (ORDER BY date_col DESC) AS lag2
    FROM tab_A
    WHERE type = 'B' AND date_col < t.date_col
    ORDER BY date_col DESC
    LIMIT 1
) b ON true
LEFT JOIN LATERAL (
    SELECT
        date_col AS lag1,
        LAG(date_col) OVER (ORDER BY date_col DESC) AS lag2
    FROM tab_A
    WHERE type = 'C' AND date_col < t.date_col
    ORDER BY date_col DESC
    LIMIT 1
) c ON true
ORDER BY t.date_col;

How this works:

• For each row in tab_A, we run three separate LATERAL subqueries (one per type).
• Each subquery filters for the target type and dates older than the current row's date, sorts them from newest to oldest.
• We use LAG() within the subquery to grab the second-most recent date (since we sorted descending, the row before the top one is the second newest).
• LEFT JOIN ensures we get NULL values when there are no matching older dates for a type, which is probably what you want.

Approach 2: Pre-Aggregate Dates into Arrays #

If you have a small number of fixed types (like A/B/C here), you can pre-aggregate all dates per type into an array, then query that array to get the top 2 older dates. This can be more efficient if your table is large, as we only aggregate once instead of per row.

WITH type_date_arrays AS (
    SELECT
        type,
        -- Aggregate all dates for the type, sorted ascending
        ARRAY_AGG(date_col ORDER BY date_col) AS sorted_dates
    FROM tab_A
    GROUP BY type
)
SELECT
    t.id,
    t.type,
    t.date_col,
    -- Get most recent A date older than current
    (SELECT elem FROM UNNEST(tda.sorted_dates) elem WHERE elem < t.date_col ORDER BY elem DESC LIMIT 1) AS A_lag_1,
    -- Get second-most recent A date
    (SELECT elem FROM UNNEST(tda.sorted_dates) elem WHERE elem < t.date_col ORDER BY elem DESC OFFSET 1 LIMIT 1) AS A_lag_2,
    -- Repeat for B
    (SELECT elem FROM UNNEST(tdb.sorted_dates) elem WHERE elem < t.date_col ORDER BY elem DESC LIMIT 1) AS B_lag_1,
    (SELECT elem FROM UNNEST(tdb.sorted_dates) elem WHERE elem < t.date_col ORDER BY elem DESC OFFSET 1 LIMIT 1) AS B_lag_2,
    -- Repeat for C
    (SELECT elem FROM UNNEST(tdc.sorted_dates) elem WHERE elem < t.date_col ORDER BY elem DESC LIMIT 1) AS C_lag_1,
    (SELECT elem FROM UNNEST(tdc.sorted_dates) elem WHERE elem < t.date_col ORDER BY elem DESC OFFSET 1 LIMIT 1) AS C_lag_2
FROM tab_A t
LEFT JOIN type_date_arrays tda ON tda.type = 'A'
LEFT JOIN type_date_arrays tdb ON tdb.type = 'B'
LEFT JOIN type_date_arrays tdc ON tdc.type = 'C'
ORDER BY t.date_col;

How this works:

• The CTE type_date_arrays creates an array of sorted dates for each type.
• For each row in tab_A, we unnest the array for each type, filter dates older than the current row's date, sort them descending, and pick the first (most recent) and second (offset 1) entries.

Performance Tip #

If your table is large, add an index to speed up the filtering and sorting in the subqueries:

CREATE INDEX idx_tab_a_type_date ON tab_A(type, date_col DESC);

This index will make the LATERAL subqueries run much faster, as PostgreSQL can quickly find the top 2 dates for each type without scanning the entire table.

内容的提问来源于stack exchange，提问作者joshlk