正文 

当前位置： 首页 > 源码 >

BigQuery - 按数组分组

     2023-03-25     133

关键词：

【中文标题】BigQuery - 按数组分组【英文标题】：BigQuery - Group by on arrays 【发布时间】：2018-11-19 20:16:24 【问题描述】：

我想在一个数组上分组。

示例查询：

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'compute' description, '["key":"application","value":"scaled-server","key":"department","value":"hrd"]' labels, 0.323316 cost UNION ALL
  SELECT 'compute' description, '["key":"application","value":"scaled-server","key":"department","value":"hrd"]' labels, 0.342825 cost 
)
SELECT 
  description, 
  ARRAY(
    SELECT AS STRUCT 
      JSON_EXTRACT_SCALAR(kv, '$.key') key, 
      JSON_EXTRACT_SCALAR(kv, '$.value') value 
    FROM UNNEST(SPLIT(labels, ',')) kv_temp, 
    UNNEST([CONCAT('', REGEXP_REPLACE(kv_temp, r'^\[|]$', ''), '')]) kv
  ) labels,
  cost
FROM `project.dataset.table`

以上查询结果：

Row description labels.key      labels.value    cost     
1   compute         application     scaled-server   0.323316     
                    department      hrd      
2   compute         application    scaled-server  0.342825     
                    department     hrd

我想要如下结果：

Row description labels.key      labels.value    cost     
1   compute         application     scaled-server   0.666141     
                    department      hrd

【问题讨论】：

分组的逻辑是什么？是吗 - 所有键和值都应该相同才能进行分组？ 我刚刚更新了问题，意图是，描述列有什么服务，比如VM，cloudSQL，BQ，标签列告诉我们关于标签的信息，比如vm标签（env= prod）所以，如果我想获得具有 env=staging 的计算或虚拟机的成本，那么一天结束。这就是为什么我需要先按描述分组，然后按关键值分组 【参考方案1】： 
#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'compute' description, '["key":"application","value":"scaled-server","key":"department","value":"hrd"]' labels, 0.323316 cost UNION ALL
  SELECT 'compute' description, '["key":"application","value":"scaled-server","key":"department","value":"hrd"]' labels, 0.342825 cost 
), temp AS (
  SELECT description, labels, SUM(cost) AS cost
  FROM `project.dataset.table`
  GROUP BY description, labels
)
SELECT 
  description, 
  ARRAY(
    SELECT AS STRUCT 
      JSON_EXTRACT_SCALAR(kv, '$.key') key, 
      JSON_EXTRACT_SCALAR(kv, '$.value') value 
    FROM UNNEST(SPLIT(labels, ',')) kv_temp, 
    UNNEST([CONCAT('', REGEXP_REPLACE(kv_temp, r'^\[|]$', ''), '')]) kv
  ) labels,
  cost
FROM temp

【讨论】：

将数组分组为数组 bigquery

】将数组分组为数组bigquery【英文标题】：groupingarraysintoarraysbigquery【发布时间】：2022-01-1706:33:38【问题描述】：我有一个如下所示的表格：OrderidItem_idAvailabilityDate1abc13(F1,F2)11-10-20211xxx12(F1,F3)11-10-20212xxx12(F2,F3)13-10-2021......我想通... 查看详情

按 1 分钟间隔分组操作链 sql BigQuery

】按1分钟间隔分组操作链sqlBigQuery【英文标题】：Groupby1minuteintervalforthechainofactionssqlBigQuery【发布时间】：2021-01-0516:50:42【问题描述】：我需要以1分钟的间隔对数据进行分组，以进行操作链。我的数据如下所示：idMetroIdTimeActionNa... 查看详情

Google BigQuery 中超出的资源

】GoogleBigQuery中超出的资源【英文标题】：ResourcesExceededinGoogleBigQuery【发布时间】：2019-07-0815:25:48【问题描述】：我正在尝试根据组织所在的城市和州从我们的数据库中检索组织列表，并按属性org_name分组。这些组织具有多个嵌... 查看详情

按用户 ID 分组的 BigQuery Firebase 事件日志

】按用户ID分组的BigQueryFirebase事件日志【英文标题】：BigQueryFirebaseEventLogsGroupedByUserId【发布时间】：2018-07-3016:21:04【问题描述】：问题：我想为我的android用户提取按用户ID分组并按发生时间排序的Firebase事件数据流。我在下面创... 查看详情

在 BigQuery 中获取按天分组的最大数量的唯一 ID

】在BigQuery中获取按天分组的最大数量的唯一ID【英文标题】：GettingUniqueIDfoeamaximumamountgroupedbydaysinBigQuery【发布时间】：2016-01-0610:52:34【问题描述】：我在BigQuery中有这个查询：SELECTID,max(amount)asmoney,STRFTIME_UTC_USEC(TIMESTAMP(time),\'%j\'... 查看详情

Bigquery - 选择一列而不将它们分组在按子句中

】Bigquery-选择一列而不将它们分组在按子句中【英文标题】：Bigquery-Selectacolumnwithnotgroupingthemingroupbyclause【发布时间】：2021-02-1615:43:33【问题描述】：我有基于device_category(desktop/mobile/tablet)和user_type(newuser/returninguser)的google分析数... 查看详情

BigQuery 从嵌套数组中选择和别名字段而不分组

】BigQuery从嵌套数组中选择和别名字段而不分组【英文标题】：BigQueryselectandaliasfieldfromnestedarraywithoutgrouping【发布时间】：2020-01-0903:08:27【问题描述】：我在BigQuery表中有一些数据，其中一个字段是结构数组。我试图基本上做一... 查看详情

在 bigquery Standard sql 中按问题分组，并且无法获取唯一记录

】在bigqueryStandardsql中按问题分组，并且无法获取唯一记录【英文标题】：groupbyissueinbigqueryStandardsqlandUnabletogetuniquerecord【发布时间】：2018-03-2706:15:21【问题描述】：通过“错误：SELECT列表表达式引用的列id_coupon既不分组也不聚合... 查看详情

BigQuery 在选择不同行时按一个字段中的最大值分组

】BigQuery在选择不同行时按一个字段中的最大值分组【英文标题】：BigQuerygroupbymaximumvalueinonefieldwhileselectingdistinctrows【发布时间】：2019-02-0415:09:11【问题描述】：对于每20分钟的时间间隔，我试图找到带宽的最大值（下表中的mbps... 查看详情

bigquery row_number 根据特定字段按某些行分组

】bigqueryrow_number根据特定字段按某些行分组【英文标题】：bigqueryrow_numbergroupbysomerowsbasedonspecificfield【发布时间】：2015-05-0507:10:23【问题描述】：我有这样的数据即城市名称stateId城市文本152城市文本252城市文本352城市Exp172城市Exp... 查看详情

如何在 BigQuery 中按外键分组？

】如何在BigQuery中按外键分组？【英文标题】：HowtoGROUPBYaforeignkeyinBigQuery?【发布时间】：2016-01-1511:38:20【问题描述】：我在BigQuery中工作。我有三个表：分支、区域（分支的集合）和按月按分支的支出。CREATETABLEregion(idintegerNOTNULL... 查看详情

BigQuery 按数组条目过滤行

】BigQuery按数组条目过滤行【英文标题】：BigQueryFilterRowsbyArrayEntry【发布时间】：2019-09-2315:12:47【问题描述】：我需要创建从条目中的数组值派生的列。下面的示例表有4个条目，称为“会话”，每个会话都有多个访问过的应用程... 查看详情

bigquery 按除少数之外的所有列分组

】bigquery按除少数之外的所有列分组【英文标题】：bigquerygroupbyallcolumnsexceptafew【发布时间】：2019-02-2017:42:28【问题描述】：我有一个包含大量字段的表，我试图按除我要求和的两个值之外的所有值进行分组。我想做类似的事情SE... 查看详情

在 BigQuery 中按字母顺序连接列

】在BigQuery中按字母顺序连接列【英文标题】：ConcatenatecolumnsinalphabeticorderinBigQuery【发布时间】：2020-05-1204:17:01【问题描述】：我在BigQuery中有一个表：|c1|c2|c3|----------|a1|\'b\'|c1|----------|a1|\'a\'|c2|----------我想按c1和array_aggc2分组，... 查看详情

javascript数组按字段分组(代码片段)

查看详情

用于读取记录数组的 BigQuery Java API：“不支持按名称检索字段值”异常

】用于读取记录数组的BigQueryJavaAPI：“不支持按名称检索字段值”异常【英文标题】：BigQueryJavaAPItoreadanArrayofRecord:"Retrievingfieldvaluebynameisnotsupported"exception【发布时间】：2018-01-1811:41:01【问题描述】：我在BigQuery中的当前... 查看详情

BigQuery：QUANTILES 的 GROUP BY 子句

】BigQuery：QUANTILES的GROUPBY子句【英文标题】：BigQuery:GROUPBYclauseforQUANTILES【发布时间】：2012-09-1916:51:40【问题描述】：根据bigquery查询参考，目前分位数不允许按另一列进行任何类型的分组。我主要对按特定列分组的中位数感兴趣... 查看详情

如何按特定的子数组值对多维数组进行分组？

】如何按特定的子数组值对多维数组进行分组？【英文标题】：Howtogroupamultidimensionalarraybyaparticularsubarrayvalue?【发布时间】：2011-01-1211:25:00【问题描述】：我有一个多维数组，并试图根据特定列中的值对它们进行分组。我正在尝... 查看详情