正文 

当前位置： 首页 > 问答 >

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

     2023-03-24     39

关键词：

【中文标题】bigquery 按除少数之外的所有列分组【英文标题】：bigquery group by all columns except a few 【发布时间】：2019-02-20 17:42:28 【问题描述】：

我有一个包含大量字段的表，我试图按除我要求和的两个值之外的所有值进行分组。我想做类似的事情

SELECT my_table.* except(value_1, value_2)
    , sum(value_1)
    , sum(value_2)
FROM my_table
GROUP BY my_table.* except(value_1, value_2)

但不幸的是GROUP BY my_table.* except(value_1, value_2) 不起作用。请问有什么建议吗？

【问题讨论】：

【参考方案1】：

以下是 BigQuery 标准 SQL 

#standardSQL
SELECT DISTINCT * EXCEPT(value_1, value_2, grp),
  SUM(value_1) OVER(PARTITION BY grp) sum_value_1,
  SUM(value_2) OVER(PARTITION BY grp) sum_value_2
FROM (
  SELECT *, REGEXP_REPLACE(TO_JSON_STRING(t), r'"(?:value_1|value_2)":.+?[,]', '') grp
  FROM `project.dataset.table` t
)

你可以像下面的例子一样使用虚拟数据测试，玩上面的例子

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 value_1, 2 value_2, 3 value_3, 4 value_4 UNION ALL
  SELECT 11, 12, 3, 14 UNION ALL
  SELECT 21, 22, 3, 14
)
SELECT DISTINCT * EXCEPT(value_1, value_2, grp),
  SUM(value_1) OVER(PARTITION BY grp) sum_value_1,
  SUM(value_2) OVER(PARTITION BY grp) sum_value_2
FROM (
  SELECT *, REGEXP_REPLACE(TO_JSON_STRING(t), r'"(?:value_1|value_2)":.+?[,]', '') grp
  FROM `project.dataset.table` t
)

结果为 

Row value_3 value_4 sum_value_1 sum_value_2  
1   3       14      32          34   
2   3       4       1           2

以上内容适用于任意数量的列，您无需全部显式引用 - 只有那些要排除的列才能显式引用 - 本示例中为 value_1 和 value_2

【讨论】：

这太棒了！非常感谢！你能解释一下这部分在做什么吗？ REGEXP_REPLACE(TO_JSON_STRING(t), r'"(?:value_1|value_2)":.+?[,]', '') grp @FiercestJim - 它将整行转换为 json，然后清空 value_1 和 value_2 属性的所有值，以便您可以将其用作 PARTITION BY 的属性

使 .gitignore 忽略除少数文件之外的所有内容

】使.gitignore忽略除少数文件之外的所有内容【英文标题】：Make.gitignoreignoreeverythingexceptafewfiles【发布时间】：2010-11-0211:53:52【问题描述】：我了解.gitignore文件会掩盖Git版本中的指定文件控制。我有一个项目(LaTeX)会生成大量额外... 查看详情

使用 :not(selector) 选择除少数之外的所有元素 [重复]

】使用:not(selector)选择除少数之外的所有元素[重复]【英文标题】：Selectallofoneelementexceptafewusing:not(selector)[duplicate]【发布时间】：2018-10-1908:03:00【问题描述】：我正在尝试选择除前3个之外的所有“类”类。我不确定为什么我的语... 查看详情

如何将 summarise_each 应用于除一列之外的所有列？ [复制]

】如何将summarise_each应用于除一列之外的所有列？[复制]【英文标题】：Howtoapplysummarise_eachtoallcolumnsexceptone?[duplicate]【发布时间】：2016-12-0203:57:36【问题描述】：我正在分析一组包含许多列（近30列）的数据。我想基于两列对数据... 查看详情

Angularjs：限制对除少数页面之外的所有页面的访问

】Angularjs：限制对除少数页面之外的所有页面的访问【英文标题】：Angularjs:restrictaccesstoallpagesexceptfew【发布时间】：2015-05-1018:07:05【问题描述】：我正在将Angularjs与ui-router和JWT（这是一种基于令牌的身份验证工具）一起使用，... 查看详情

使用 Nginx 阻止除少数机器人之外的所有机器人

】使用Nginx阻止除少数机器人之外的所有机器人【英文标题】：BlockingallbotsexceptafewwithNginx【发布时间】：2013-10-2016:28:04【问题描述】：当我输入以下代码时，我想阻止所有标识为机器人但允许Googlebot的http_user_agents：map$http_user_age... 查看详情

根据条件对 BigQuery 中的行进行重复数据删除

】根据条件对BigQuery中的行进行重复数据删除【英文标题】：DeduplicatingrowsinBigQuerybasedoncondition【发布时间】：2020-03-0213:04:14【问题描述】：我目前正在尝试对我的一张表在GoogleBigQuery中的行进行重复数据删除。基本上，我有一个... 查看详情

如何在 Hive 中除按列分组之外的列上应用 max 子句

】如何在Hive中除按列分组之外的列上应用max子句【英文标题】：howtoapplymaxclauseoncolumnotherthangroupbycolumnsinHive【发布时间】：2019-09-2703:32:57【问题描述】：我有一个包含如下数据的配置单元表。Table---------------------c1c2c3a17a26a33a31a32... 查看详情

Bigquery/标准 SQL：如何使用 sum() 聚合所有列（大约 100 列）？

】Bigquery/标准SQL：如何使用sum()聚合所有列（大约100列）？【英文标题】：Bigquery/StandardSQL:HowcanIaggregateallcolumns(about100columns)withsum()?【发布时间】：2018-06-1115:21:48【问题描述】：我正在寻找一种使用sum()函数聚合包含大约100列的Big... 查看详情

R：如何删除数据框中的所有列，除了指定的少数字符串[重复]

】R：如何删除数据框中的所有列，除了指定的少数字符串[重复]【英文标题】：R:HowtoDeleteAllColumnsinaDataframeExceptASpecifiedFewByString[duplicate]【发布时间】：2016-08-0215:05:40【问题描述】：我在R中有一个数据框，它包含大约400个变量（... 查看详情

如何在 Bigquery 中将 csv 的所有列作为字符串导入 [关闭]

】如何在Bigquery中将csv的所有列作为字符串导入[关闭]【英文标题】：HowtoimportallcolumnsofacsvasStringsinBigquery[closed]【发布时间】：2022-01-1403:35:41【问题描述】：我正在使用气流将CSV文件（例如FILE__YYYYMMDD.csv）从GCS存储桶解压缩到BigQu... 查看详情

核心数据迁移：除了少数特定对象之外，是不是可以删除旧存储？

】核心数据迁移：除了少数特定对象之外，是不是可以删除旧存储？【英文标题】：CoreDataMigration:Possibletodeleteoldstoreexceptforafewspecificobjects?核心数据迁移：除了少数特定对象之外，是否可以删除旧存储？【发布时间】：2015-07-1620:... 查看详情

使用 BigQuery SQL 计算同一 ID 的所有列值的模式

】使用BigQuerySQL计算同一ID的所有列值的模式【英文标题】：CalculatemodeofallcolumnvaluesforthesameIDusingBigQuerySQL【发布时间】：2021-05-0612:15:36【问题描述】：假设我有一个Bigquery表，其中包含id、species、genre和level列。在某些情况下，对... 查看详情

有没有办法从 BigQuery 的表中获取所有列的不同值？

】有没有办法从BigQuery的表中获取所有列的不同值？【英文标题】：IsthereanywaytoobtaindistinctvaluesacrossallthecolumnsfromwithinatableinBigQuery?【发布时间】：2020-05-1906:10:10【问题描述】：我尝试使用从特定表中获取列名列表Selectcolumn_namefromp... 查看详情

BigQuery：QUANTILES 的 GROUP BY 子句

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

选择除一列之外的所有列[重复]

】选择除一列之外的所有列[重复]【英文标题】：selectallcolumnsexceptone[duplicate]【发布时间】：2013-03-2316:23:20【问题描述】：我需要从表“ticket”中选择所有列，除了一列“depname”以下准备好的语句没有按预期工作。PREPAREstmt1FROM\'... 查看详情

更新查询适用于除一列之外的所有列

】更新查询适用于除一列之外的所有列【英文标题】：Updatequeryworksforallcolumnsexceptone【发布时间】：2012-10-1910:05:12【问题描述】：我的.xsd文件中有如下更新查询：UPDATEFactorsSETCodeFactor=@CodeFactor,Date=@Date,MobileNumber=@MobileNumber,Descriptio... 查看详情

BigQuery拆分列并获取每个子字符串的计数[重复]

】BigQuery拆分列并获取每个子字符串的计数[重复]【英文标题】：BigQuerysplitcolumnandgetcountofcounteachsubstring[duplicate]【发布时间】：2018-03-0623:58:57【问题描述】：在BigQuery中，我想创建一个查询来计算cmets字段中单词的出现次数，并按... 查看详情

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

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