SQL利用窗口函数解决多表关联带来的重复行问题

SQL利用窗口函数解决多表关联带来的重复行问题

免费影视、动漫、音乐、游戏、小说资源长期稳定更新！ 👉 点此立即查看 👈

先明确一个核心问题：JOIN后COUNT翻倍，本质是笛卡尔积导致的行数膨胀。正确的解决思路是：先聚合明细数据，再利用窗口函数将聚合结果“广播”回主表；或者，使用ROW_NUMBER()配合QUALIFY进行过滤，以及用FIRST_VALUE()带值。至于COUNT(DISTINCT)，则需要根据具体数据库引擎的支持情况选择替代方案。

为什么 JOIN 后的 COUNT 结果总是翻倍？

这恐怕是多表关联后进行聚合计算时，最让人困惑的现象之一了。数据本身没错，问题出在行数被“撑大”了。举个例子：订单主表orders里的一条记录，关联到明细表order_items的三行数据。一旦JOIN，这条订单在结果集里就会“复制”成三行。此时再按orders.id分组做COUNT()，结果自然是3而不是1。

窗口函数的妙处就在这里——它不改变原始结果集的行数，只是在每一行上“附加”计算结果，从而巧妙地绕开了JOIN带来的重复行干扰。

关键思路其实很清晰：先对明细数据进行聚合，然后利用窗口函数把聚合好的结果“贴”回主表的每一行。这样一来，主表就不会被明细数据拖累而膨胀了。

用 ROW_NUMBER() + QUALIFY 过滤重复主表行

当你需要获取每个订单的最新一条明细（比如最后发货时间），但又不想写复杂的子查询或依赖DISTINCT时，ROW_NUMBER()配合QUALIFY子句堪称最优雅的解决方案（适用于Snowflake、BigQuery、Doris等数据库）。

SELECT order_id, item_name, shipped_at
FROM order_items
QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY shipped_at DESC) = 1;

• PARTITION BY order_id：这确保了编号是在每个订单内部独立进行的。
• ORDER BY shipped_at DESC：按发货时间降序排列，最新的那条自然就排到了第1位。
• QUALIFY：这个语法糖可以直接过滤窗口函数的结果，比额外嵌套一层子查询再用WHERE rn = 1要简洁得多。
• 需要注意的是，PostgreSQL和MySQL 8.0+并不支持QUALIFY语法，在这些数据库中，你仍然需要使用CTE（公共表表达式）或多层子查询来实现相同逻辑。

用 FIRST_VALUE() 把明细聚合值带进主表而不膨胀

设想一个场景：你手头有orders订单表和payments付款表，想在每条订单记录上直接看到“该订单的首笔付款金额”。如果直接JOIN，一个订单对应多笔付款就会产生多行，数据就“炸”了。这时，FIRST_VALUE()窗口函数就能派上用场。

SELECT
  o.order_id,
  o.order_date,
  FIRST_VALUE(p.amount) OVER (
    PARTITION BY p.order_id
    ORDER BY p.payment_time
  ) AS first_payment
FROM orders o
LEFT JOIN payments p ON o.order_id = p.order_id;

• 这里虽然用了LEFT JOIN，结果集行数依然会膨胀（一个订单对应几笔付款就有几行）。但FIRST_VALUE()的魔力在于，对于同一个order_id分组内的所有付款行，它都返回同一个值（按payment_time排序后的第一个值）。
• 所以，即便某个订单有5笔付款，查询结果中这个订单会出现5行，但每行的first_payment列值是完全相同的。之后，你可以根据需求，使用DISTINCT ON (order_id)（PostgreSQL特有）或外层GROUP BY来去除重复行。
• 一个常见的误区是，先对payments表按order_id分组聚合，再与orders表关联。这样做会丢失那些没有付款记录的订单，因为内连接会过滤掉它们，让LEFT JOIN的本意失效。

COUNT(DISTINCT ...) 在窗口里怎么写才不报错？

很多现代数据分析引擎（如Presto、Trino、Spark SQL）是允许在窗口函数中使用COUNT(DISTINCT x)的。但如果你用的是MySQL 8.0或早期版本的Hive，很可能会遇到Window function 'COUNT' with DISTINCT is not supported这样的报错。别慌，我们有替代方案：

• 先聚合，后开窗：如果目标是统计每个订单涉及多少种商品类别，可以先在order_items表上按order_id, category分组去重，得到一个中间结果，再对这个结果使用窗口函数进行累计等计算。
• 使用近似计数：如果业务可以接受一定的精度误差，并且数据库引擎支持，可以尝试APPROX_COUNT_DISTINCT() OVER (...)这类近似去重计数函数，它的性能通常更好。
• 字符串聚合应急：对于数据量不大的临时需求，可以先用STRING_AGG(DISTINCT category)将不同类别拼接成字符串，再计算字符串的复杂度或长度来间接判断。但这只是权宜之计，不推荐用于生产环境。

说到底，窗口函数是一把解决“计算逻辑与行结构解耦”问题的利器，但它并非万能。一个容易被忽略的核心要点是：窗口函数本身并不减少行数。你必须自己做出决策：是否需要去重？在哪个粒度上去重？以及，这个去重操作应该放在JOIN之前、之后，还是作为最外层的处理？想清楚这些，问题就迎刃而解了。