如何在SQL中通过开窗函数进行主从表合并统计

COUNT() OVER() 不能替代 JOIN，但能避免主表被 GROUP BY 压缩；它在不改变行数、无需 GROUP BY 的前提下，为每行主表准确统计关联从表记录数，性能优于相关子查询，且语义清晰、容错性强。

如何在sql中通过开窗函数进行主从表合并统计_count over应用

COUNT() OVER() 不能替代 JOIN，但能避免主表被 GROUP BY 压缩

当你需要在主表每行上显示「对应从表记录数」（比如每个订单显示其商品行数、每个用户显示其订单数），又不想丢失主表原始行结构时，COUNT() OVER() 是比 LEFT JOIN + GROUP BY 更轻量、更安全的选择。它不改变行数，也不要求你把所有非聚合列塞进 GROUP BY —— 这正是它解决的核心痛点。

为什么 COUNT(*) OVER(PARTITION BY ...) 比子查询更稳

常见错误是写成：(SELECT COUNT(*) FROM orders o2 WHERE o2.customer_id = o1.id)。这种相关子查询在数据量大时性能陡降，且容易因 NULL 或重复关联字段出错。

COUNT(*) OVER(PARTITION BY customer_id) 在执行计划中通常走一次扫描+哈希分组，效率高得多
如果从表有 WHERE 过滤（如只统计「已支付订单」），必须先用 CTE 或子查询过滤从表，再做 OVER，否则 PARTITION BY 会包含不该计入的行
COUNT(column_name) OVER(...) 会忽略该列值为 NULL 的行；而 COUNT(*) 统计所有行（含 NULL）—— 别混用

主表带条件时，COUNT OVER 必须前置过滤从表

想查「每个用户最近30天的订单数」，不能直接在主表 WHERE 里加时间条件——开窗函数在 SELECT 阶段执行，WHERE 已经过滤完主表，无法影响从表范围。

正确做法：先把从表按时间过滤，再与主表 JOIN 或用 OVER 关联

例如：

WITH recent_orders AS (
  SELECT customer_id
  FROM orders
  WHERE order_time >= DATEADD(day, -30, GETDATE())
)
SELECT u.id, u.name,
       COUNT(ro.customer_id) OVER(PARTITION BY u.id) AS recent_order_cnt
FROM users u
LEFT JOIN recent_orders ro ON u.id = ro.customer_id;

注意这里用了 COUNT(ro.customer_id) 而非 COUNT(*)：LEFT JOIN 后，没订单的用户对应 ro.customer_id 是 NULL，COUNT() 自动跳过，结果为 0 —— 符合语义

ORDER BY 在 COUNT OVER 中只影响累积计数，慎用

COUNT(*) OVER(PARTITION BY x ORDER BY y) 不是「按 y 排序后分组计数」，而是「按 y 排序后，从分区第一行累加到当前行」。多数统计场景不需要这个行为。

要的是「每个用户的总订单数」→ 用 COUNT(*) OVER(PARTITION BY user_id)（无 ORDER BY）
要的是「每个用户按下单时间排序后的累计订单数」→ 才加 ORDER BY order_time，此时第1单显示1，第2单显示2，依此类推
一旦加了 ORDER BY，SQL Server 默认窗口帧是 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，不可跨行取全量——这点极易被忽略

真正难处理的是主从关系存在多层嵌套（比如用户→订单→订单项→退货记录），这时单层 COUNT OVER 不够用，得靠多级 CTE 或 APPLY 嵌套。但第一层聚合统计，COUNT() OVER() 几乎总是最简、最可控的起点。