Tableau计算字段重复数据问题：按runner分组计算时间差均值出错

核心问题 #

由于customers_order_table中同一order_id对应多条记录，关联runners_orders_table后，同一订单的时间差会被重复计算，导致平均值失真。我们需要确保每个订单仅被统计一次，无需创建新表即可解决。

方案1：先提取唯一订单再关联 #

先从订单表中提取唯一的order_id和对应的order_time（假设同一订单的下单时间一致），再与跑单表关联计算时差，最后按runner_id分组求平均：

SELECT 
  ro.runner_id,
  AVG(TIMESTAMPDIFF(MINUTE, co.order_time, ro.pickup_time)) AS avg_time_diff
FROM (
  -- 去重获取唯一订单及下单时间
  SELECT DISTINCT order_id, order_time
  FROM customers_order_table
) co
JOIN runners_orders_table ro ON co.order_id = ro.order_id
WHERE ro.pickup_time IS NOT NULL -- 排除未接单的无效记录
GROUP BY ro.runner_id;

方案2：聚合时使用DISTINCT #

在AVG函数中加入DISTINCT，直接对每个唯一订单的时间差计算平均值（前提是同一订单的order_time和pickup_time无差异）：

SELECT 
  ro.runner_id,
  AVG(DISTINCT TIMESTAMPDIFF(MINUTE, co.order_time, ro.pickup_time)) AS avg_time_diff
FROM customers_order_table co
JOIN runners_orders_table ro ON co.order_id = ro.order_id
WHERE ro.pickup_time IS NOT NULL
GROUP BY ro.runner_id;

方案3：用窗口函数过滤重复订单 #

通过窗口函数为同一订单的记录标记序号，仅保留每组第一条记录后再计算平均：

WITH ranked_orders AS (
  SELECT 
    ro.runner_id,
    TIMESTAMPDIFF(MINUTE, co.order_time, ro.pickup_time) AS time_diff,
    -- 按订单分组，标记每条记录的序号
    ROW_NUMBER() OVER (PARTITION BY co.order_id ORDER BY co.order_id) AS rn
  FROM customers_order_table co
  JOIN runners_orders_table ro ON co.order_id = ro.order_id
  WHERE ro.pickup_time IS NOT NULL
)
SELECT 
  runner_id,
  AVG(time_diff) AS avg_time_diff
FROM ranked_orders
WHERE rn = 1 -- 仅保留每个订单的第一条记录
GROUP BY runner_id;

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