如何按组对计数值归一化以消除多地点公司统计偏差？

核心思路是消除多地点公司的重复权重，给每个公司的每条记录分配1/该公司总地点数的权重，再按Value汇总权重得到归一化计数。

步骤分解 #

• 第一步：计算每个公司的不同地点数量，得到对应权重系数（1/地点数）
• 第二步：为原始数据的每条记录匹配所属公司的权重系数
• 第三步：按Value分组，求和所有记录的权重，得到最终归一化计数

SQL实现示例 #

假设数据表名为business_hours，用窗口函数可一步完成：

SELECT
    Value,
    SUM(1 / company_location_count) AS Count
FROM (
    SELECT
        *,
        COUNT(DISTINCT Location) OVER (PARTITION BY Company) AS company_location_count
    FROM business_hours
) AS weighted_data
GROUP BY Value
ORDER BY Value;

结果验证 #

执行后会得到目标输出：

逻辑说明：

• Burgers公司有2个地点，两条记录各占0.5权重
• Donuts公司仅1个地点，对应记录占1权重
• 按Value求和后，Monday总权重为0.5+1=1.5，Sunday为0.5

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