在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_known值位于顶部(，然后只保留足够的朋友，使他们的friendship_score为100。

例如，Alice只需要Bob，因为她认识Bob的时间最长，而且他们的friendship_score超过了100。鲍勃需要丹尼尔和爱丽丝，因为鲍勃认识丹尼尔的时间更长，但他们的friendship_score只有24岁。然而，添加Alice后，Bob知道的下一个朋友最长，组合的friendship_score在100以上。

我知道我们需要做一些窗口函数和滚动求和，但我很难将这些想法放入代码中，我想知道是否有人能帮上忙。非常感谢。

Answer 1

我对Spark没有太多经验，但文档表明它同时支持窗口函数和Selects From Selects，您需要过滤窗口函数的结果。

请注意，使用窗口UNBOUNDED BELOWER TO CURRENT ROW运行求和会为要保留的最后一条记录生成大于100的求和。您确实想要一个不包括CURRENT ROW的部分和来过滤和保留正确的记录。您可以使用SUM窗口函数执行此操作，然后从当前记录中减去分数。因此，您的窗口函数应读取SUM(friendship_score(OVER(按人员分区按天排序已知下降行未绑定到当前行(-friendship_score as priorit_tal_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添加到外部Select中。