QuickSight如何创建计算字段实现sum of average聚合计算

核心计算字段公式 #

直接新建计算字段，输入以下公式即可实现「先按国家-日维度去重取唯一浏览量，再按国家对选中日期范围的浏览量求和」的逻辑，不需要在可视化中添加day字段：

sumOver(
    {total_views_per_country_and_day} / countOver(
        1,
        [{country}, {day}],
        PRE_AGG
    ),
    [{country}],
    POST_AGG_FILTER
)

公式逻辑说明 #

• 内层countOver(1, [{country}, {day}], PRE_AGG)：在应用日期筛选器后、数据聚合前，统计每个国家+日期组合对应的明细行数
• 每行的total_views_per_country_and_day除以对应国家-日的明细行数，相当于把当日总浏览量平摊到该组下的每一行，同一国家-日组内所有行的平摊值加总刚好等于当日总浏览量，从根源上避免重复计数
• 外层sumOver(..., [{country}], POST_AGG_FILTER)：按国家分区汇总所有行的平摊值，自动响应仪表板的day维度筛选，最终输出选中时间范围内每个国家的正确总浏览量

转化率指标公式 #

基于上述总浏览量计算字段（假设命名为country_total_views），可直接派生转化率指标，公式如下：

sum({num_purchases}) / {country_total_views}

如果不需要单独留存总浏览量字段，也可以直接写嵌套公式：

sum({num_purchases}) / sumOver(
    {total_views_per_country_and_day} / countOver(
        1,
        [{country}, {day}],
        PRE_AGG
    ),
    [{country}],
    POST_AGG_FILTER
)

使用注意事项 #

• 将计算字段拖入表格值区域时，聚合方式选择「无聚合（No aggregation）」即可，行维度仅需保留country字段，不需要额外添加day字段
• 公式兼容单日、多日筛选场景：筛选单日时结果与直接对total_views_per_country_and_day求avg的结果一致；筛选多日时自动累加每日去重值，不会出现按明细行数加权平均的错误
• 以示例中USA数据验证：若两日分别对应5行、10行明细，平摊后每行值分别为900/5=180、1000/10=100，按USA汇总后总和为5*180 + 10*100 = 1900，完全符合预期

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