SQL:按组选择行，直到满足条件 [英] SQL: Select rows by group until a condition is met

问题描述

如果给出以下用户在特定时间戳采取的操作的示例表:

If given the sample table below of actions taken by users at specific timestamps:

id |    ts     | action
---+-----------+------
 1 | 10039000  | scroll_10
 1 | 10039002  | scroll_20
 1 | 10039004  | click
 1 | 10039006  | scroll_30
 2 | 10044000  | scroll_10
 2 | 10044002  | bounce

我需要能够在第一个 non-scroll 操作之前为每个 ID 提取结果.

I need to be able to pull the results before the first non-scroll action, for each ID.

如果在第一个 non-scroll 操作之后发生在 timestamps 处的每个用户有额外的 scroll 操作，则不应拉取它们.

If there are additional scroll actions for each user that occur at timestamps after the first non-scroll action, they should not be pulled.

上述示例的预期输出为:

Expected output for the sample above would be:

id | action
---+-------
 1 | scroll_10
 1 | scroll_20
 2 | scroll_10

我尝试了以下查询:

SELECT id, action
FROM user_actions
WHERE ts < (SELECT MIN(ts)
            FROM user_actions
            WHERE action NOT LIKE '%scroll%'
            )

这给了我一个结果:

id | action
---+-------
 1 | scroll_10
 1 | scroll_20

所以这对于第一个id"是正确的，但我需要它来寻找下一个id"的下一个非滚动"操作并将其包含在返回中，我似乎无法得到它要做.试图自联接"到同一个表，但似乎不太正确.

So that's correct for the first 'id', but I need it to then look for the next 'non-scroll' action for the next 'id' and include that in the return, which I can't seem to get it to do. Tried to 'self-join' to the same table but can't quite seem to get the syntax correct.

推荐答案

您可以使用相关子查询:

You can use a correlated subquery:

select t.*
from t
where t.timestamp < (select min(t2.timestamp)
                     from t t2
                     where t2.id = t.id and t2.action not like 'scroll%'
                    );

这篇关于SQL:按组选择行，直到满足条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！