带有where子句的SQL窗口函数？

Question

我正在尝试为用户关联两种类型的事件。我想查看所有事件“B”以及该用户在“A”事件之前的最新事件“A”。如何做到这一点？特别是，我正在尝试在 Postgres 中执行此操作。

我希望可以在窗口函数中使用“where”子句，在这种情况下，我基本上可以使用“where event='A'”执行 LAG()，但这似乎不行有可能。

有什么建议吗？

数据示例:

|user |time|event|
|-----|----|-----|
|Alice|1   |A    |
|Bob  |2   |A    |
|Alice|3   |A    |
|Alice|4   |B    |
|Bob  |5   |B    |
|Alice|6   |B    |

期望的结果:

|user |event_b_time|last_event_a_time|
|-----|------------|-----------------|
|Alice|4           |3                |
|Bob  |5           |2                |
|Alice|6           |3                |

Answer 1

刚刚使用 PostgreSQL 9.5.4 尝试了 Gordon 的方法，它提示说

FILTER is not implemented for non-aggregate window functions

这意味着不允许将 lag() 与 FILTER 一起使用。因此，我使用 max()、不同的窗口框架和 CTE 修改了 Gordon 的查询:

WITH subq AS (
  SELECT
    "user", event, time as event_b_time,
    max(time) FILTER (WHERE event = 'A') OVER (
      PARTITION BY "user"
      ORDER BY time
      ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    ) AS last_event_a_time
  FROM events
  ORDER BY time
)
SELECT
  "user", event_b_time, last_event_a_time
FROM subq
WHERE event = 'B';

已验证这适用于 PostgreSQL 9.5.4。

感谢 Gordon 的 FILTER 技巧!