用SQL/Spark中的窗口函数执行特定筛选

Question

我目前有一个大型数据集，但为了简单起见，它看起来如下所示：

Person, Friend, Friendship_Score, Days_Known
Alice, Bob, 120, 56
Alice, Candy, 20, 23
Bob, Daniel, 24, 77
Bob, Alice, 120, 56
Candy, Alice, 20, 23
Daniel, Bob, 24, 77
Daniel, Ed, 56, 65
Daniel, Fin, 52, 54
Daniel, Gin, 22, 50
...

我想在此数据集上使用一个窗口函数使其看起来如下所示：

Alice, Bob, 120, 56
Bob, Daniel, 24, 77
Bob, Alice, 120, 56
Candy, Alice, 20, 23
Daniel, Bob, 24, 77
Daniel, Ed, 56, 65
Daniel, Fin, 52, 54

过滤器背后的逻辑应该是，对于每个人，我们按照他们认识的时间长短对他们的朋友进行排序（较高的days_knowled值位于顶部），然后只保留足够的朋友，以便他们的friendship_score为100。

例如，Alice只需要Bob，因为她认识他的时间最长，而且他们的friendship_score超过100。Bob需要Daniel和Alice，因为Bob认识Daniel的时间更长，但是他们的Friendship_Score只有24。但是，在添加Alice之后，Bob认识的时间最长的下一个朋友，Friendship_Score的组合值高于100。

我知道我们需要做一些窗口函数和滚动求和，但我很难把这些想法放进代码中，想知道是否有人可以帮助这一点。谢谢！

Answer 1

我对Spark没有太多的经验，但文档表明它既支持窗口函数，也支持从选择中选择，您需要从选择中选择来筛选窗口函数的结果。

请注意，使用window UNBOUNDED TO CURRENT ROW的运行总和将为要保留的最后一条记录生成一个大于100的总和。您确实需要不包括当前行的部分和来筛选和保留正确的记录。您可以使用求和窗口函数来完成此操作，然后从当前记录中减去得分。因此，您的窗口函数应该将SUM(friendship_score)读取为prior_total_score（按人分区、按天排序_knowled desc行，下面无界到当前行）-friendship_score（prior_total_score）

Select person, friend, friendship_score,days_known
From (
      Select *, SUM(friendship_score) OVER (Partition By person Order By Days_Known desc ROWS UNBOUNDED BELOW TO CURRENT ROW) - friendship_score as prior_total_score
      From MyTable
     )
Where prior_total_score < 100

您可以根据需要向外部选择添加Order By。