BigQuery SQL根据标准获得前3名的结果

Question

我为一个客户从Amazon上收集了一些数据。我需要使用这些数据来创建一个“高于折叠”的报告，这基本上意味着我已经在页面上显示了我所刮过的每一个关键字的前3名结果。

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

我需要设置的规则是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：

• for test1，结果应该是test2的前3名赞助结果，test2的
• ，test3的前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之前。在同一关键字类型的多个记录的情况下，排序使用较低的级别。