使用row_number函数查询每月曝光量Top3帖子的SQL问题求助

问题排查 #

• 语法错误：外层SELECT的Post_tipo字段后多了冗余逗号，子查询缺少闭合的右括号，会直接触发语法报错
• 执行顺序逻辑错误：SQL执行优先级中，WHERE过滤早于窗口函数计算，在子查询同层直接过滤RowNumber = 1时，row_number()还未完成计算，会触发字段不存在的报错，属于窗口函数使用的常见误区
• 分区逻辑可按需调整：当前写法的PARTITION BY 日期是按天分组排序，最终取到的是指定月份每天曝光最高的帖子；如果需求是整个指定月份所有帖子统一排序取TOP，无需添加PARTITION BY逻辑即可。

修正后SQL（按天取每日TOP1版本） #

SELECT 
    Post_timestamp, 
    Post_impressions, 
    Post_tipo
FROM (
    SELECT 
        Post_timestamp, 
        Post_impressions, 
        Post_tipo,
        FORMAT_DATE("%Y-%m-%d",DATE_TRUNC(TIMESTAMP(Post_timestamp), DAY)) as TheDate,
        ROW_NUMBER() OVER (
            PARTITION BY FORMAT_DATE("%Y-%m-%d",DATE_TRUNC(TIMESTAMP(Post_timestamp), DAY)) 
            ORDER BY Post_impressions DESC
        ) AS RowNumber
    FROM `***DATABASENAME***`
    WHERE FORMAT_DATE("%Y-%m-%d",DATE_TRUNC(TIMESTAMP(Post_timestamp), DAY)) BETWEEN "2021-07-01" AND "2021-07-31"
) t
WHERE RowNumber = 1;

修正后SQL（全月统一排序取TOP版本） #

如果需要整个7月所有帖子按曝光降序全量排序，取TOP N可使用以下写法：

SELECT 
    Post_timestamp, 
    Post_impressions, 
    Post_tipo
FROM (
    SELECT 
        Post_timestamp, 
        Post_impressions, 
        Post_tipo,
        ROW_NUMBER() OVER (
            ORDER BY Post_impressions DESC
        ) AS RowNumber
    FROM `***DATABASENAME***`
    WHERE FORMAT_DATE("%Y-%m-%d",DATE_TRUNC(TIMESTAMP(Post_timestamp), DAY)) BETWEEN "2021-07-01" AND "2021-07-31"
) t
WHERE RowNumber <= 10; -- 修改此处数值即可取对应TOP N结果

补充说明 #

如果存在同一天/全月多个帖子曝光量完全一致的情况，ROW_NUMBER()会随机给相同曝光的帖子分配不同排名，如果需要相同曝光的帖子排名一致且都保留，可替换为RANK()或DENSE_RANK()函数。

内容的提问来源于stack exchange，提问作者GuiAT