数据库专题13聚合报表与窗口函数——从“查明细”到“解释趋势”博客运营需要回答每位作者发布了多少篇文章每天新增评论趋势怎样一篇文章在自己标签中排名第几只会select *无法完成这些问题。本篇用GROUP BY、HAVING、窗口函数和 CTE 写出三个报表并说明 NULL、重复 JOIN 和时间分桶的坑。上一篇练习讲解上一篇要求固定详情和列表的 SQL 次数避免 N1count(distinct c.id)防止标签 JOIN 放大评论数软删除评论必须过滤。批量标签查询按 article_id 分组不能在序列化循环中再次查询。1. 作者文章报表selectu.id,u.email,count(a.id)filter(wherea.statuspublished)aspublished_count,count(a.id)filter(wherea.statusdraft)asdraft_count,max(a.updated_at)aslast_updatefromusers uleftjoinarticles aona.author_idu.idgroupbyu.id,u.emailhavingcount(a.id)0orderbypublished_countdesc,u.id;left join先保留没有文章的用户having再决定是否展示如果写成where a.statuspublished会在 JOIN 前过滤掉 draft也会让没有发布文章的用户消失。FILTER比多个子查询更清晰PostgreSQL 原生支持。2. 每日评论趋势selectdate_trunc(day,created_at attimezoneAsia/Shanghai)asday,count(*)filter(wheredeleted_atisnull)asvisible_comments,count(*)filter(wheredeleted_atisnotnull)asremoved_commentsfromcommentswherecreated_atnow()-interval30 daysgroupbydayorderbyday;数据库统一存 UTC报表展示时转换时区。date_trunc后的时间是桶的起点前端不应把它误当作某条评论的准确时间。时区和夏令时会影响“每天”的边界生产报表要固定业务时区并写测试。3. 窗口函数做排名和累计witharticle_likesas(selecta.id,a.title,count(l.user_id)aslike_countfromarticles aleftjoinlikes lonl.article_ida.idwherea.statuspublishedgroupbya.id,a.title)selectid,title,like_count,dense_rank()over(orderbylike_countdesc)ashot_rank,sum(like_count)over(orderbylike_countdescrowsbetweenunboundedprecedingandcurrentrow)ascumulativefromarticle_likesorderbyhot_rank,idlimit20;GROUP BY把多行压成一行窗口函数则在保留每篇文章的同时计算排名。dense_rank在并列时不跳号如果需要唯一顺序用row_number() over(order by like_count desc,id)。窗口排序和最终排序要保持一致否则排名与展示顺序可能矛盾。4. CTE 让报表可读withpublishedas(selectid,author_id,created_atfromarticleswherestatuspublished),author_statsas(selectauthor_id,count(*)astotalfrompublishedgroupbyauthor_id)selectu.email,coalesce(s.total,0)astotalfromusers uleftjoinauthor_stats sons.author_idu.idorderbytotaldesc,u.id;CTE 不是自动缓存复杂查询要通过 EXPLAIN 确认 PostgreSQL 是否内联或物化。coalesce把没有文章的 NULL 转成 0避免 API 序列化出现null与前端数字类型冲突。5. 从 Python 返回报表defauthor_report(conn)-list[dict]:rowsconn.execute(text(AUTHOR_REPORT_SQL)).mappings()return[{email:row[email],published:int(row[published_count]),last_update:row[last_update].isoformat()ifrow[last_update]elseNone}forrowinrows]报表接口设置最大时间范围和超时必要时异步生成 CSV不要让一个管理员请求同步扫描几亿行。统计结果若缓存到 Redis要在主库更新后失效或设置短 TTL并能从 SQL 重建。验收与排错没有评论的文章 - like_count0而不是 NULL 两篇文章点赞并列 - dense_rank 都为1下一名为2 30天趋势 - 日期按 Asia/Shanghai 分桶 删除评论 - visible_comments 减少removed_comments 增加如果统计翻倍检查 JOIN 是否同时连接了评论和标签的多对多关系应先分别聚合再 JOIN。若窗口排名顺序错误统一ORDER BY和 tie-breaker。课后练习写“作者近 7 天发布数与全站排名”报表要求使用窗口函数并解释rank、dense_rank、row_number的差异为报表增加 EXPLAIN 文本和一个无数据测试。下一篇专门做索引和执行计划实验。