如何在SQL中按分区计算去重后的平均值？（每个session仅统计一次）

需求说明 #

计算每个id分区内的item_count平均值，要求每个session对应的item_count仅统计一次，避免重复session的数据被多次计算。

示例数据 #

id     session      item_count    random_field_1 
1      weoifn2      3             A
1      weoifn2      3             B
1      iuboiwe      2             K
2      oeino33      5             R
2      vergeeg      8             C
2      feooinn      9             P
2      feooinn      9             M

计算逻辑 #

• id = 1: (3 + 2) / 2 = 2.5
• id = 2: (5 + 8 + 9) / 3 = 7.33

预期输出 #

id      avg
1       2.5
2       7.33

原查询问题 #

原查询会重复统计同一session的item_count，导致结果不符合预期：

SELECT 
   id
 , AVG(item_count) OVER (PARTITION BY id) AS avg
FROM my_table

修正方案 #

要实现每个session仅参与一次计算，需先对id+session组合去重，再计算平均值，以下是两种常用实现方式：

方式1：先去重再聚合 #

通过DISTINCT获取每个id+session对应的唯一item_count，再对去重后的结果计算平均值：

SELECT
  id,
  AVG(item_count) AS avg
FROM (
  SELECT DISTINCT
    id,
    session,
    item_count
  FROM my_table
) AS unique_sessions
GROUP BY id;

方式2：窗口函数结合去重标记 #

如果需要保留更多字段，可使用ROW_NUMBER()标记每个session的第一条记录，仅取第一条参与计算：

SELECT
  id,
  AVG(item_count) OVER (PARTITION BY id) AS avg
FROM (
  SELECT
    id,
    session,
    item_count,
    ROW_NUMBER() OVER (PARTITION BY id, session ORDER BY (SELECT NULL)) AS rn
  FROM my_table
) AS ranked_sessions
WHERE rn = 1;

注：ORDER BY (SELECT NULL)用于兼容不同数据库，无需特定排序时可使用，也可替换为实际存在的字段。

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