postgresql 带有 where 子句的 SQL 窗口函数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39378550/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL window function with a where clause?
提问by MJ.
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.
我正在尝试为用户关联两种类型的事件。我想在“A”事件之前查看该用户的所有事件“B”以及最近的事件“A”。如何实现这一目标？特别是，我试图在 Postgres 中做到这一点。
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.
我希望可以在窗口函数中使用“where”子句，在这种情况下，我基本上可以使用“where event='A'”来执行 LAG()，但这似乎是不可能的。
Any recommendations?
有什么建议吗？
Data example:
数据示例：
|user |time|event|
|-----|----|-----|
|Alice|1 |A |
|Bob |2 |A |
|Alice|3 |A |
|Alice|4 |B |
|Bob |5 |B |
|Alice|6 |B |
Desired result:
想要的结果：
|user |event_b_time|last_event_a_time|
|-----|------------|-----------------|
|Alice|4 |3 |
|Bob |5 |2 |
|Alice|6 |3 |
回答by Cheng Lian
Just tried Gordon's approach using PostgreSQL 9.5.4, and it complained that
刚刚尝试使用 PostgreSQL 9.5.4 的 Gordon 方法，它抱怨说
FILTER is not implemented for non-aggregate window functions
非聚合窗口函数未实现 FILTER
which means using lag()with FILTERis not allowed. So I modified Gordon's query using max(), a different window frame, and CTE:
这意味着不允许使用lag()with FILTER。所以我使用max()不同的窗口框架和 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';
Verified that this works with PostgreSQL 9.5.4.
验证这适用于 PostgreSQL 9.5.4。
Thanks to Gordon for the FILTERtrick!
感谢戈登的FILTER诀窍！
回答by redneb
There is not need for window functions here. Just find all Bevents, and for each one of them, find the most recent Aof the same user via a subquery. Something like that should do it:
这里不需要窗口函数。只需查找所有B事件，并A通过子查询为每个事件查找同一用户的最新事件。应该这样做：
SELECT
"user",
time AS event_b_time,
(SELECT time AS last_event_a_time
FROM t t1
WHERE "user"=t.user AND event='A' AND time<t.time
ORDER BY time DESC LIMIT 1)
FROM t
WHERE event='B';
I assume that the table is called t(I used it twice).
我假设调用了该表t（我使用了两次）。
