BigQuerySQL根据条件获得前三名结果

Question

我为一个客户从亚马逊上刮取了一些数据。我需要使用这些数据来创建一个"高于折叠"报告，这基本上意味着我已经在页面上显示了我抓取的每个关键词的前三名结果。

现在，在亚马逊上，搜索结果是赞助结果(付费(和有机结果(免费(的混合。首先列出赞助结果，然后列出有机结果。

我需要设置的规则是for each keyword and each date, get the top 3 sponsored results. If there are no sponsored results, get the top three organic results。然而，增加的复杂性是；如果有两个赞助结果，我需要得到这两个，然后得到第一个有机结果。

我的表格的简化视图如下：

keyword report_date rank_no result_type
test1   2022-10-10  1   Sponsored
test1   2022-10-10  2   Sponsored
test1   2022-10-10  3   Sponsored
test1   2022-10-10  4   Sponsored
test1   2022-10-10  5   Sponsored
test1   2022-10-10  1   Organic
test1   2022-10-10  2   Organic
test1   2022-10-10  3   Organic
test2   2022-10-10  1   Organic
test2   2022-10-10  2   Organic
test2   2022-10-10  3   Organic
test2   2022-10-10  4   Organic
test3   2022-10-10  1   Sponsored
test3   2022-10-10  2   Sponsored
test3   2022-10-10  1   Organic
test3   2022-10-10  2   Organic
test3   2022-10-10  3   Organic

基于rank_no:

• 对于test1，结果应该是赞助的前三名结果
• 对于测试2，结果应该是排名前三的有机产品
• 对于测试3，结果应该是排名前2的赞助产品和排名前的有机产品

我一直在谷歌上寻找这个问题的解决方案，但我还没有找到任何东西。如果你能帮我找到一个解决方案，或者建议我如何在谷歌上找到这个问题的解决方案，我将不胜感激。

提前感谢您的帮助！

Answer 1

ROW_NUMBER()可以在这里工作：

WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY keyword
ORDER BY result_type DESC, rank_no) rn
FROM yourTable
)
SELECT keyword, report_date, rank_no, result_type
FROM cte
WHERE rn <= 3
ORDER BY keyword, result_type DESC, rank_no;

上述ROW_NUMBER()中使用的排序将Sponsored记录置于Organic之前。在同一关键字类型的多个记录的情况下，排序使用较低的级别。