如何不使用子查询实现DISTINCT ON去重后按其他列GROUP BY统计行数

你当前的查询存在两个核心问题：

• 窗口函数count(1) over (partition by order_num)统计的是单个订单号对应的总更新次数，不是你需要的按日期、区域分组的订单数
• 缺少按date、region分组聚合的逻辑，查询返回的是每个订单最新记录的明细，自然会出现重复的日期+区域组合

首先说明：你需要的逻辑本质是「先按订单号过滤最新记录、再按日期区域分组统计」，用子查询/CTE拆分两步逻辑是可读性最高、性能最优的写法，不需要刻意避免嵌套。

方案1：DISTINCT ON + CTE（最简洁，PostgreSQL专属） #

WITH latest_orders AS (
  SELECT DISTINCT ON (order_num)
    date,
    region
  FROM orders_table
  ORDER BY order_num, timestamp_updated DESC
)
SELECT 
  date,
  region,
  COUNT(*) AS order_count
FROM latest_orders
GROUP BY date, region;

方案2：窗口函数ROW_NUMBER实现（兼容所有支持窗口函数的SQL引擎） #

SELECT 
  date,
  region,
  COUNT(*) AS order_count
FROM (
  SELECT
    date,
    region,
    ROW_NUMBER() OVER (PARTITION BY order_num ORDER BY timestamp_updated DESC) AS rn
  FROM orders_table
) t
WHERE rn = 1 -- 仅保留每个订单的最新记录
GROUP BY date, region;

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