当前位置: 首页 > 面试题库 >

如何将聚合函数应用于从Google BigQuery中的JSON提取的数据?

岳曦
2023-03-14
问题内容

我正在使用从BigQuery列中提取JSON数据JSON_EXTRACT。现在,我要提取值列表并对其运行汇总函数(如AVG)。.objects[*].v在http://jsonpath.curiousconcept.com/上测试JsonPath表达式成功。但是查询:

SELECT
  JSON_EXTRACT(json_column, "$.id") as id,
  AVG(JSON_EXTRACT(json_column, "$.objects[*].v")) as average_value
FROM [tablename]

在BigQuery上引发 JsonPath解析错误在BigQuery上可能吗?
还是我需要预处理数据以便对JSON内部的数据运行聚合函数?

我的数据看起来像这样:

# Record 1
{
  "id": "abc",
  "objects": [
    {
      "id": 1,
      "v": 1
    },
    {
      "id": 2,
      "v": 3
    }
  ]
}
# Record 2
{
  "id": "def",
  "objects": [
    {
      "id": 1,
      "v": 2
    },
    {
      "id": 2,
      "v": 5
    }
  ]
}

更新:
通过运行两个查询可以简化此问题。首先,运行JSON_EXTRACT并将结果保存到视图中。其次,针对此视图运行合计函数。但是即使那样,我仍然需要更正JsonPath表达式$.objects[*].v以防止JSONPathparse error


问题答案:

利用SPLIT()将可重复字段旋转到单独的行中。将其放入子查询中并将AVG置于外部可能更容易/更干净:

SELECT id, AVG(v) as average 
FROM (
SELECT 
    JSON_EXTRACT(json_column, "$.id") as id, 
    INTEGER( 
      REGEXP_EXTRACT(
        SPLIT(
          JSON_EXTRACT(json_column, "$.objects")
          ,"},{"
          )
        ,r'\"v\"\:([^,]+),')) as v FROM [mytable] 
)
GROUP BY id;


 类似资料:
  • 聚合功能让你可以汇总或更改数据的粒度。 点击字段框中的向下箭头。 选择“聚合”,然后选择一个聚合函数。 函数 描述 数字 总计 返回所有值的总和。Null 值将被忽略。 平均 返回所有值的平均值。Null 值将被忽略。 计数 返回项目数量。Null 值不计算在内。 计数(非重复) 返回不同项目的数量。Null 值不计算在内。 最小 返回所有记录的最小值。Null 值将被忽略。 最大 返回所有记录的

  • 聚合功能让你可以汇总或更改数据的粒度。 点击字段框中的向下箭头。 选择“聚合”,然后选择一个聚合函数。 函数 描述 数字 总计 返回所有值的总和。Null 值将被忽略。 平均 返回所有值的平均值。Null 值将被忽略。 计数 返回项目数量。Null 值不计算在内。 计数(非重复) 返回不同项目的数量。Null 值不计算在内。 最小 返回所有记录的最小值。Null 值将被忽略。 最大 返回所有记录的

  • 聚合功能让你可以汇总或更改数据的粒度。 点击字段框中的向下箭头。 选择“聚合”,然后选择一个聚合函数。 函数 描述 数字 总计 返回所有值的总和。Null 值将被忽略。 平均 返回所有值的平均值。Null 值将被忽略。 计数 返回项目数量。Null 值不计算在内。 计数(非重复) 返回不同项目的数量。Null 值不计算在内。 最小 返回所有记录的最小值。Null 值将被忽略。 最大 返回所有记录的

  • 在执行时,是否有一种方法可以将聚合函数应用于dataframe的所有列(或列表)?换句话说,是否有一种方法可以避免对每个列都这样做:

  • 问题内容: 这旨在作为一般参考问题和答案,涵盖许多永无止境的 “如何访问JSON中的数据?” 问题。它是在这里处理在PHP中解码JSON和访问结果的广泛基础知识。 我有JSON: 如何在PHP中对此进行解码并访问结果数据? 问题答案: 介绍 首先,您有一个字符串。JSON不是数组,对象或数据结构。JSON是基于文本的序列化格式,因此是花哨的字符串,但仍然只是字符串。使用解码PHP。 在其中您可能会

  • 问题内容: 我只是在学习MySQL-是否有组合(或嵌套)聚合函数的方法? 给定一个查询: 这将给我每个用户回答的问题数量。我真正想要的是每个用户回答的平均问题数量…… 计算此统计信息的正确方法是什么? 如果有可能,是否有办法针对每个问题分解此统计信息?(用户可以多次回答相同的问题)。就像是: 问题答案: 您必须使用子查询: 您不能将一个聚合与另一个聚合一起包装。如果MySQL支持分析/排序/窗口功