JOIN后COUNT翻倍是因笛卡尔膨胀导致行数增加；应先聚合明细再用窗口函数广播结果，或用ROW_NUMBER()+QUALIFY过滤、FIRST_VALUE()带值、COUNT(DISTINCT)需视引擎支持选择替代方案。
为什么 JOIN 后的 COUNT 结果总是翻倍？
这是多表关联后用 COUNT() 或 SUM() 聚合时最典型的症状——不是数据算错了，是行被笛卡尔膨胀了。比如订单主表 orders 一条记录关联到订单明细表 order_items 的 3 行，JOIN 后这条订单就变成 3 行，再 GROUP BY 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（否则一单多付就变多行）：
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 行返回同一个值 - 所以即使某订单有 5 笔付款，结果仍是 5 行，但
first_payment列全一样；后续可加DISTINCT ON (order_id)（PostgreSQL）或GROUP BY去重 - 别直接对
payments表GROUP BY order_id再JOIN，那会丢失未付款订单（LEFT JOIN失效）
COUNT(DISTINCT ...) 在窗口里怎么写才不报错？
很多引擎（如 Presto、Trino、Spark SQL）允许 COUNT(DISTINCT x) OVER (...)，但 MySQL 8.0 和早期 Hive 不支持。遇到报错 Window function 'COUNT' with DISTINCT is not supported，得换策略：
- 先用
GROUP BY汇总明细：比如统计每个订单有多少种商品类别，先在order_items上GROUP BY order_id, category去重，再汇总 - 或改用
APPROX_COUNT_DISTINCT() OVER (...)（如果精度可接受且引擎支持） - 或用
STRING_AGG(DISTINCT category)拼字符串再算长度（仅小数据量应急）
窗口函数不是万能胶，它解决的是“计算逻辑与行结构解耦”的问题。真正容易被忽略的点是：窗口函数本身不减少行数，你得自己决定要不要去重、用什么粒度去重、以及在哪一层（JOIN 前/后/外）做这步操作。
