mysql - 在 SQL 中处理顺序/有序数据

Question

如果这个问题已经被问过，尝试搜索但找不到相关主题，我深表歉意。

我得到了一个半大型数据源(约 1500 万条记录)，我需要对其执行一些分析以确定用户行为。数据源包括用户 ID 字段、交易日期以及指示交易是否具有特定特征的标志。显然，我在这里进行了简化以了解问题的核心。用户的交易数量会有很大差异(从 1 到 200+)，日期分布会有所不同，标志的分布也会有所不同。

考虑下表:

ID          User ID         Date             Flag
1           1               2015-01-03       Y
2           1               2015-03-15       N
3           1               2015-07-20       N
4           1               2015-11-18       N
5           1               2015-11-29       N
6           2               2015-02-16       Y
7           2               2015-03-03       N
8           2               2015-06-10       Y
9           2               2015-08-10       Y

如何根据同一用户之前或之后的其他记录的特征来查询此数据以隔离记录？

例如:

1. 如何识别带有“Y”标志的记录，以及后面跟随着带有“N”标志的同一用户 ID 的其他三个记录(按日期排序)？ [将返回上表中的 1]

2. 如果 50% 或更多带有“Y”标志的交易发生在前 20% 的交易中，如何识别用户 ID？ [将返回上表中的用户 ID 1]

我希望这个问题足够清楚。

*Edit: 下面的答案是正确的，但是他不知道我用的是MySQL作为数据库(他回答后我在标签里加了)。 MySQL 不支持这些功能，Oracle 或 SQL Server 都可以实现这些功能。

Answer 1

这个问题假设一个合理的数据库支持窗口/分析功能。

第一个问题可以使用lead()来处理:

select t.*
from (select t.*,
             lead(flag, 1) over (partition by userid order by date) as flag_1,
             lead(flag, 2) over (partition by userid order by date) as flag_2,
             lead(flag, 3) over (partition by userid order by date) as flag_3
      from t
     ) t
where flag = 'Y' and flag_1 = 'N' and flag_2 = 'N' and flag_3 = 'N';

第二个也使用窗口函数:

select user_id
from (select t.*,
             row_number() over (partition by user_id order by date) as seqnum,
             count(*) over (partition by user_id) as cnt
      from t
     ) t
group by user_id
having sum(case when flag = 'Y' and seqnum/0.2 <= cnt then 1 else 0 end) >=
           0.5 * sum(case when flag = 'Y' then 1 else 0 end);

因此，您的问题的答案基本上是:了解窗口(分析)函数。