时间戳

首页 > 解决方案 > 如何计算变体列中嵌套数组中浮点数的平均值

snowflake-cloud-data-platform - 如何计算变体列中嵌套数组中浮点数的平均值

问题描述

我有一个 VARIANT 列，其中包含来自 Web 服务的 JSON 响应。它包含一个带有浮点值的嵌套数组，我想将其聚合并作为平均值返回。这是我正在使用的示例 SnowSQL 命令：

select 
value:disambiguated.id,
value:mentions 
from TABLE(
    FLATTEN(input => 
                   PARSE_JSON('{    "entities": [{"count": 2,"disambiguated": {"id": 123},"label": "Coronavirus Disease 2019","mentions": [{"confidence": 0.5928,}, {"confidence": 0.5445,}],"type": "MEDICAL"}]}'):entities
                 )
)

返回：

VALUE:DISAMBIGUATED.ID  VALUE:MENTIONS
123                     [    {      "confidence": 0.5928    },    {      "confidence": 0.5445    }  ]

我想返回的是两个“置信度”值平均为 0.56825 的东西。我能够添加第二个FLATTEN语句，该语句隔离“提及”数组并允许我提取每个“置信度”值。我似乎无法弄清楚如何对记录进行分组以计算平均值。如果可能，希望使用内置的AVG()函数。提前感谢您提供的任何帮助。

标签： snowflake-cloud-data-platform

解决方案

使用您的示例，您可以使用 LATERAL FLATTEN 创建所需的展平字段，然后像往常一样聚合。在此示例中，我根据数据中的 ID 进行分组，但您也可以使用y.index或z.index根据您想要为您的AVG().

WITH x AS (
    SELECT PARSE_JSON('{    "entities": [{"count": 2,"disambiguated": {"id": 123},"label": "Coronavirus Disease 2019","mentions": [{"confidence": 0.5928,}, {"confidence": 0.5445,}],"type": "MEDICAL"}]}') as json_str
)
SELECT
y.value:disambiguated.id as id,
avg(z.value:confidence)
from x,
LATERAL FLATTEN(input => json_str:entities) y,
LATERAL FLATTEN(input => y.value:mentions) z
GROUP BY id
;

推荐阅读