如何在BigQuery中计算考虑样本各条目权重的几何平均值

问题描述 #

我之前了解到可以使用EXP(AVG(LN(x)))计算几何均值，这个方法非常实用。现在我需要计算带权重的几何均值，也就是计算时要考虑样本中每个条目的权重。
对应的加权几何均值计算公式如下：

想咨询下如何在BigQuery中实现这个计算？有没有可以参考的、能纳入各条目权重的实现方案？
我使用的样例数据如下：

SELECT STRUCT(JSON_EXTRACT_SCALAR(mass, '$.subs_sum') AS subs, JSON_EXTRACT_SCALAR(mass, '$.division') AS division) mass  FROM UNNEST (
    [
        '''{
            "subs_sum": "188292",
            "division": "0.7708596151869399"
        }''',
        '''{
            "subs_sum": "1182",
            "division": "0.8344408128719736"
        }''',
        '''{
            "subs_sum": "142559",
            "division": "0.9539818702339475"
        }''',
        '''{
            "subs_sum": "14047",
            "division": "0.7836811141666864"
        }''',
        '''{
            "subs_sum": "70344",
            "division": "0.7724158684628387"
        }''',
        '''{
            "subs_sum": "101516",
            "division": "0.8676896770665041"
        }''',
        '''{
            "subs_sum": "12459",
            "division": "0.8029440607145902"
        }''',
        '''{
            "subs_sum": "26070",
            "division": "0.9793106723267602"
        }''',
        '''{
            "subs_sum": "151959",
            "division": "0.839048212451375"
        }''',
        '''{
            "subs_sum": "5234",
            "division": "0.684263034290403"
        }'''
    ]
) mass 

解决方案 #

加权几何均值的实现可以在普通几何均值的基础上调整：把原来的求平均操作替换为加权平均即可，对应公式的逻辑为：先对每个数值取自然对数，乘以对应权重求和后除以总权重，最后取指数就能得到结果。
针对你提供的样例数据，subs_sum是权重字段，division是要计算均值的数值字段，实现代码如下：

WITH sample_data AS (
  -- 先处理样例数据，把字符串字段转成数值类型
  SELECT 
    CAST(mass.subs_sum AS INT64) AS weight,
    CAST(mass.division AS FLOAT64) AS value
  FROM (
    SELECT STRUCT(JSON_EXTRACT_SCALAR(mass, '$.subs_sum') AS subs, JSON_EXTRACT_SCALAR(mass, '$.division') AS division) mass  
    FROM UNNEST (
        [
            '''{
                "subs_sum": "188292",
                "division": "0.7708596151869399"
            }''',
            '''{
                "subs_sum": "1182",
                "division": "0.8344408128719736"
            }''',
            '''{
                "subs_sum": "142559",
                "division": "0.9539818702339475"
            }''',
            '''{
                "subs_sum": "14047",
                "division": "0.7836811141666864"
            }''',
            '''{
                "subs_sum": "70344",
                "division": "0.7724158684628387"
            }''',
            '''{
                "subs_sum": "101516",
                "division": "0.8676896770665041"
            }''',
            '''{
                "subs_sum": "12459",
                "division": "0.8029440607145902"
            }''',
            '''{
                "subs_sum": "26070",
                "division": "0.9793106723267602"
            }''',
            '''{
                "subs_sum": "151959",
                "division": "0.839048212451375"
            }''',
            '''{
                "subs_sum": "5234",
                "division": "0.684263034290403"
            }'''
        ]
    ) mass 
  )
)
SELECT 
  EXP(SUM(LN(value) * weight) / SUM(weight)) AS weighted_geometric_mean
FROM sample_data

运行上述代码得到的加权几何均值结果约为0.833。如果需要按分组计算不同维度的加权几何均值，只需要在最后查询时加上对应的GROUP BY字段即可。

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