SQL窗口函数带有where子句？ [英] SQL window function with a where clause?

问题描述

我正在尝试为用户关联两种类型的事件。我想查看所有事件 B以及该用户在 A事件之前的最新事件 A。一个人如何做到这一点？

I'm trying to correlate two types of events for users. I want to see all event "B"s along with the most recent event "A" for that user prior to the "A" event. How would one accomplish this? In particular, I'm trying to do this in Postgres.

我希望可以在window函数中使用 where子句，在这种情况下，我希望在Postgres中执行此操作。基本上可以用 where event ='A'做一个LAG（），但这似乎是不可能的。

I was hoping it was possible to use a "where" clause in a window function, in which case I could essentially do a LAG() with a "where event='A'", but that doesn't seem to be possible.

任何建议吗？

数据示例：

|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                |

推荐答案

只需使用PostgreSQL 9.5.4尝试Gordon的方法，并且它抱怨

Just tried Gordon's approach using PostgreSQL 9.5.4, and it complained that

未对非聚合窗口函数实现过滤器

FILTER is not implemented for non-aggregate window functions

表示不允许将 lag（）与 FILTER 一起使用。因此，我使用了 max（），另一个窗口框架和CTE修改了戈登的查询：

which means using lag() with FILTER is not allowed. So I modified Gordon's query using max(), a different window frame, and CTE:

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。

Verified that this works with PostgreSQL 9.5.4.

感谢Gordon的 FILTER 技巧！

Thanks to Gordon for the FILTER trick!

这篇关于SQL窗口函数带有where子句？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！